Tuesday, July 17, 2012

Identifying tables and indexes belonging to the same tablespace

SELECT   tab.table_name,
         ind.index_name,
         ind.tablespace_name ind_tbs,
         tab.tablespace_name tab_tbs
  FROM   dba_tables tab, dba_indexes ind
 WHERE       TAB.TABLE_NAME = IND.TABLE_NAME
         AND ind.tablespace_name = tab.tablespace_name
         AND tab.owner NOT IN ('SYS', 'SYSTEM')
         AND ind.owner NOT IN ('SYS', 'SYSTEM')
         AND ind.tablespace_name != 'SYSTEM'
         AND tab.tablespace_name != 'SYSTEM'

No comments:

Post a Comment