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