Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    41

    Unanswered: Base tables of the VIEWS ... ???

    Hi,

    Does anyone have a script or SQL that I can use to quickly list the base tables of a view or is there a dynamic view that have that information?

    What am looking for something like this:

    Example:

    SELECT name, phone from phone_directory

    where phone_directory is a view that references people and phone. But at the moment, the only way I would know that is if I generate the DDL for the phone_directory view or do a datapump exp of the view and then imp with show=y to a log file. Alternatively, I supposed I could use a tool with freeware TOAD but I prefer some sort of script though if it is at all possible.

    Tried doing a SELECT TEXT from DBA_VIEWS of the view but it is not showing the full text? Do I need to set something up to show the full text?

    BTW, reason am wanting to do this is 'coz I need to check the LAST_ANALYZED of a set of tables but these tables are accessed from the views and I do not have the DDLs that created these views.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I do not have the DDLs that created these views.
    DBMS_METADATA.GET_DDL can be used to extract the DDL for any view.

    Post Operating System (OS) name & version for DB server system.
    Post results of SELECT * from v$version.
    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.

  3. #3
    Join Date
    Sep 2008
    Posts
    41
    Thanks anacedent,

    OS = AIX

    SELECT * FROM V$VERSION:

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    CORE 10.2.0.3.0 Production
    TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
    NLSRTL Version 10.2.0.3.0 - Production

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    SELECT * 
    FROM user_dependencies
    WHERE name = 'PHONE_DIRECTORY';

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    set long 32000

    select text from user_views
    where view_name = PHONE_DIRECTORY';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Sep 2008
    Posts
    41
    Thanks, the USER_DEPENDENCIES/DBA_DEPENDENCIES does the trick

Posting Permissions

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