Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba tables
dba_tables
SQL> desc dba_tables;
Name Null? Type
----------------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
DROPPED VARCHAR2(3)
SQL>
SQL> select owner, table_name, tablespace_name
2 from dba_tables
3 where owner in ("SCOTT", "HR")
4 order by owner, tablespace_name, table_name;
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
HR DEPARTMENTS USERS
HR EMPLOYEES USERS
HR JOBS USERS
HR JOB_HISTORY USERS
HR LOCATIONS USERS
HR REGIONS USERS
HR COUNTRIES
7 rows selected.
SQL>
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 sqle 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
sqle 3 indexes
9 tables
MDSYS 3 indexes
7 tables
SYS 28 indexes
92 tables
SYSTEM 31 tables
36 indexes
XDB 1 indexes
SQL>
SQL>