Mega Code Archive

 

Categories / Oracle PLSQL / System Tables Views

 

List table, table partition, index, index partition and lob from dba_objects

SQL> SQL> set echo off SQL> set verify off SQL> set pagesize 9999 SQL> SQL> column object_name format a30 SQL> column tablespace_name format a30 SQL> column object_type format a12 SQL> column status format a1 SQL> SQL> break on object_type skip 1 SQL> SQL> select object_type, object_name,   2         decode( status, 'INVALID', '*', '' ) status,   3         decode( object_type,   4                  'TABLE', (select tablespace_name   5                              from dba_tables   6                             where table_name = object_name   7                               and owner = upper('&1')),   8                  'TABLE PARTITION', (select tablespace_name   9                                        from dba_tab_partitions  10                                       where partition_name = subobject_name  11                                         and owner = upper('&1')),  12                  'INDEX', (select tablespace_name  13                              from dba_indexes  14                             where index_name = object_name  15                               and owner = upper('&1')),  16                  'INDEX PARTITION', (select tablespace_name  17                                        from dba_ind_partitions  18                                       where partition_name = subobject_name  19                                         and owner = upper('&1')),  20                  'LOB', (select tablespace_name  21                            from dba_segments  22                           where segment_name = object_name  23                             and owner = upper('&1')),  24                  null ) tablespace_name  25    from dba_objects a  26   where owner = upper('&1')  27   order by object_type, object_name  28  / Enter value for 1: column status format a10 Enter value for 1: Enter value for 1: --