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

    Unanswered: SQL to list tables and its indexes

    Hi,

    Is there any other way of doing what am trying to achieve here besides this SQL that am using now?

    Basically, what am trying to achieve is to list the table/s and its corresponding index/es. I will be scripting it later on. Just want to know if there is another way of doing it beside the SQL that am running now below.

    Also, will all partition index appear on DBA_INDEXES and all partition table also appear on DBA_TABLES or are those information only available from the DBA_TAB_PARTITIONS or DBA_IND_PARTITIONS? Just need a reminder ...

    Code:
    select table_name, table_name, last_analyzed
    from   dba_tables
    where  table_name in ( 'LOCADDRESS', 'EMS_MEMO' )
           and owner = 'APP01'
    UNION
    select table_name, index_name, last_analyzed
    from   dba_indexes
    where  table_name in ( 'LOCADDRESS', 'EMS_MEMO' ) and table_owner = 'APP01'
    order  by 1
    Sample OUTPUT below:

    Code:
    TABLE_NAME                     TABLE_NAME                     LAST_ANALYZED
    ------------------------------ ------------------------------ --------------------------
    EMS_MEMO                       EMS_MEMO                       13-SEP-2009 07:13:12 TABLE
    EMS_MEMO                       EMS_MEMO_ACCOUNT_IDX           13-SEP-2009 07:17:04 INDEX
    EMS_MEMO                       EMS_MEMO_CONSUMER_IDX          13-SEP-2009 07:13:47 INDEX
    EMS_MEMO                       EMS_MEMO_CUSTOMER_IDX          13-SEP-2009 07:16:17 INDEX
    EMS_MEMO                       EMS_MEMO_DATETIME_IDX          13-SEP-2009 07:17:18 INDEX
    EMS_MEMO                       EMS_MEMO_INSTALLMASTER_IDX     13-SEP-2009 07:16:22 INDEX
    EMS_MEMO                       EMS_MEMO_INSTALL_IDX           13-SEP-2009 07:16:27 INDEX
    EMS_MEMO                       EMS_MEMO_LEGAL_IDX             13-SEP-2009 07:16:32 INDEX
    EMS_MEMO                       EMS_MEMO_METER_IDX             13-SEP-2009 07:16:39 INDEX
    EMS_MEMO                       EMS_MEMO_PK                    13-SEP-2009 07:16:52 INDEX
    EMS_MEMO                       EMS_MEMO_PROSPECT_IDX          13-SEP-2009 07:13:33 INDEX
    EMS_MEMO                       EMS_MEMO_SERVREQ_IDX           13-SEP-2009 07:13:32 INDEX
    EMS_MEMO                       EMS_MEMO_TYPE_IDX              13-SEP-2009 07:13:26 INDEX
    
    TABLE_NAME                     TABLE_NAME                     LAST_ANALYZED
    ------------------------------ ------------------------------ --------------------------
    LOCADDRESS                     LOCADDRESS                     25-AUG-2009 22:39:34 TABLE
    LOCADDRESS                     LOCADDRESS_BUILDING_SEARCH_IDX 25-AUG-2009 22:39:55 INDEX
    LOCADDRESS                     LOCADDRESS_EXTRACTADDRESS_IDX  25-AUG-2009 22:40:32 INDEX
    LOCADDRESS                     LOCADDRESS_ITEMID_IDX          25-AUG-2009 22:40:08 INDEX
    LOCADDRESS                     LOCADDRESS_PK                  25-AUG-2009 22:40:20 INDEX
    LOCADDRESS                     LOCADDRESS_STREET_SEARCH_IDX   25-AUG-2009 22:40:07 INDEX

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool To join or not to join...

    Try this:
    Code:
    SELECT   t.table_name, t.last_analyzed, i.index_name, i.last_analyzed
        FROM dba_tables t
             LEFT JOIN dba_indexes i
             ON i.owner = t.owner AND i.table_name = t.table_name
       WHERE t.owner = 'APP01' AND t.table_name IN ('LOCADDRESS', 'EMS_MEMO')
    ORDER BY 1, 3
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2008
    Posts
    41
    Hi,

    Thanks for the quick response. Why have I not think of that ?

  4. #4
    Join Date
    Sep 2008
    Posts
    41
    Hi LKBrwn_DBA and others

    Do you know of any link to learn or familiarize with using LEFT JOIN, FULL OUTER JOIN, INNER JOIN syntax?

    Am kinda lost using these .. are these what is usually referred to as ANSI SQL, so I Google for ANSI SQL tutorial ...

    Thanks in advance.

Posting Permissions

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