list table, table partition, index, index partition and lob from dba_objects : dba_objects « System Tables Views « Oracle PL / SQL






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: --

   
    
    
  








Related examples in the same category

1.Query dba_objects table group by object_type
2.Query object name by object id against dba_objects
3.List all not valid database objects
4.List all sys owned package name
5.Get all system packages