Map user objects to tablespaces. : dba_tables « System Tables Data Dictionary « Oracle PL/SQL Tutorial






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>








30.43.dba_tables
30.43.1.dba_tables
30.43.2.Map user objects to tablespaces.