Map user objects to tablespaces.
SQL>
SQL> set pagesize 120
SQL> break on Tablespace on Owner
SQL> column Objects format A20
SQL> select
2 Tablespace_Name,
3 Owner,
4 COUNT(*)||' tables' Objects
5 from DBA_TABLES
6 group by
7 Tablespace_Name,
8 Owner
9 union
10 select
11 Tablespace_Name,
12 Owner,
13 COUNT(*)||' indexes' Objects
14 from DBA_INDEXES
15 group by
16 Tablespace_Name,
17 Owner;
TABLESPACE_NAME OWNER OBJECTS
------------------------------ ------------------------------ --------------------
SYSAUX CTXSYS 26 tables
SYSAUX 47 indexes
SYSAUX DBSNMP 17 tables
SYSAUX 8 indexes
SYSAUX FLOWS_020100 160 tables
SYSAUX 422 indexes
SYSAUX FLOWS_FILES 1 tables
SYSAUX 5 indexes
SYSAUX SYS 175 tables
SYSAUX 247 indexes
SYSAUX SYSTEM 21 indexes
SYSAUX 22 tables
SYSAUX TSMSYS 1 tables
SYSAUX 2 indexes
SYSAUX XDB 11 tables
SYSAUX 384 indexes
SYSTEM DEFINER 1 indexes
SYSTEM INV10 1 indexes
SYSTEM INV11 1 indexes
SYSTEM INV12 1 indexes
SYSTEM INV13 1 indexes
SYSTEM INV14 1 indexes
SYSTEM INV15 1 indexes
SYSTEM INV16 1 indexes
SYSTEM INV17 1 indexes
SYSTEM INV18 1 indexes
SYSTEM INV19 1 indexes
SYSTEM INV20 1 indexes
SYSTEM JAVA2S 12 indexes
SYSTEM 14 tables
SYSTEM MDSYS 30 tables
SYSTEM 51 indexes
SYSTEM OUTLN 3 tables
SYSTEM 4 indexes
SYSTEM SYS 402 tables
SYSTEM 513 indexes
SYSTEM SYSTEM 145 indexes
SYSTEM 85 tables
USERS HR 19 indexes
USERS 6 tables
CTXSYS 11 tables
DBSNMP 2 indexes
4 tables
DEFINER 1 tables
FLOWS_020100 4 tables
HR 1 tables
INV10 1 tables
INV11 1 tables
INV12 1 tables
INV13 1 tables
INV14 1 tables
INV15 1 tables
INV16 1 tables
INV17 1 tables
INV18 1 tables
INV19 1 tables
INV20 1 tables
JAVA2S 3 indexes
9 tables
MDSYS 3 indexes
7 tables
SYS 28 indexes
92 tables
SYSTEM 31 tables
36 indexes
XDB 1 indexes
SQL>
SQL>
Related examples in the same category