Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: query to show dropped base tables of invalid views

    Hi
    Is there anyone that has a script to list the DROPPED base tables of views?
    I know that you can see the definitions of view e.g in *_views or the dependencies in *_dependencies, but the entries of base tables are deleted when a base table is deleted.

    So what I need is a query or procedure that lists all the deleted base tables of the views in a schema. With this query I will generate 'drop view' statements. There are more than 1000 invalid view, that reference dropped base tables.

    I would appreciate if anyone has a solution for this.

    Thanks and regards,
    Chris

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    *Maybe* you could use recyclebin (if it is not purged).

    Let's create a table, a view based on that table, and then drop the table:
    Code:
    SQL> create table a (col number);
    
    Table created.
    
    SQL> create view va as select * from a;
    
    View created.
    
    SQL> drop table a;
    
    Table dropped.
    
    SQL>
    The view is now invalid:
    Code:
    SQL> select object_name from user_objects where object_type = 'VIEW' and status = 'INVALID';
    
    OBJECT_NAME
    ---------------------------------------------------------------------------------------------
    VA
    
    SQL>
    What was it referencing?
    Code:
    SQL> select referenced_name, referenced_type from user_dependencies where name = 'VA';
    
    REFERENCED_NAME                                                  REFERENCED_TYPE
    ---------------------------------------------------------------- -----------------
    BIN$rxPBRbRwRLuKYVIaEdnHTw==$0                                   TABLE
    
    SQL>
    Check the recyclebin:
    Code:
    SQL> select original_name from recyclebin where object_name = 'BIN$rxPBRbRwRLuKYVIaEdnHTw==$0';
    
    ORIGINAL_NAME
    --------------------------------
    A
    
    SQL>
    Here we are! It is our table!

    Unfortunately, if recyclebin is purged, you're out of luck - information is lost:
    Code:
    SQL> purge recyclebin;
    
    Recyclebin purged.
    
    SQL> select referenced_name, referenced_type from user_dependencies where name = 'VA';
    
    no rows selected
    
    SQL>
    But, if you want to drop ALL invalid views (regardless the reason that makes them invalid), no problem - a little piece of dynamic SQL can do that in no time.

  3. #3
    Join Date
    May 2013
    Posts
    2
    Thanks for your answer. This is no option as the recyclebin is purged already. So I have no access to the droped tables.

    My idea is that someone already made a query that gets the base tables of a view out of the view definition ( *_views ). I believe that this is the only way how you can do this.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    08:09:14 SQL> select name, referenced_name from user_dependencies where name = 'EMP_DETAILS_VIEW';
    
    NAME
    ------------------------------
    REFERENCED_NAME
    ----------------------------------------------------------------
    EMP_DETAILS_VIEW
    COUNTRIES
    
    EMP_DETAILS_VIEW
    DEPARTMENTS
    
    EMP_DETAILS_VIEW
    EMPLOYEES
    
    
    NAME
    ------------------------------
    REFERENCED_NAME
    ----------------------------------------------------------------
    EMP_DETAILS_VIEW
    JOBS
    
    EMP_DETAILS_VIEW
    LOCATIONS
    
    EMP_DETAILS_VIEW
    REGIONS
    
    
    6 rows selected.
    
    08:10:23 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    @anacedent, USER_DEPENDENCIES is empty after underlying tables have been dropped (and recyclebin purged).

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •