Oracle PL/SQL/System Tables Views/DBA Tables

Data Dictionary Scope

  
SQL>
SQL>
SQL> select owner, table_name, tablespace_name
  2  from dba_tables
  3  where owner in ("SCOTT", "HR") and rownum < 50
  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>

Get owner, table name and table space name from dba_tables

 
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>

Query dba_tables by owners

  
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.

Query dba_tables for table info

  
SQL>
SQL> set verify off
SQL> set linesize 76
SQL> set pagesize 9999
SQL>
SQL> break on tablespace_name skip 1
SQL> column tablespace_name  format a15
SQL> column table_name       format a30
SQL> column table_properties format a30 word_wrapped
SQL>
SQL> select tablespace_name, table_name,
  2         decode( partitioned, "YES", "Partitioned ",
  3           decode( logging, "NOLOGGING", "Non-logging table " ) ) ||
  4         decode( temporary, "Y", "Temporary (" ||
  5                 decode( duration, "SYS$SESSION", "Session-based",
  6                                                  "Transaction-based" ) ||
  7                 ")",
  8         "" ) ||
  9         decode( iot_type, null, "", "Index-organized " ) ||
 10         decode( nested, "YES", "Nested ", "" )
 11         table_properties
 12    from dba_tables
 13   where rownum < 50
 14  /
Tablespace Name Table Name
--------------- ------------------------------
TABLE_PROPERTIES
------------------------------
SYSTEM          ICOL$

                CON$

                UNDO$

                PROXY_ROLE_DATA$

                FILE$

                UET$

                IND$

                SEG$

                COL$

                CLU$

                PROXY_DATA$

                TS$

                BOOTSTRAP$

                FET$

                CCOL$

                USER$

                OBJ$

                TAB$

                CDEF$

                OBJAUTH$

                UGROUP$

                TSQ$

                SYN$

                VIEW$

                TYPED_VIEW$

                SUPEROBJ$

                SEQ$

                PROCEDURE$

                PROCEDUREINFO$

                ARGUMENT$

                SOURCE$

                IDL_UB1$

                IDL_CHAR$

                IDL_UB2$

                IDL_SB4$

                DIR$

                JAVAOBJ$

                ERROR$

                SETTINGS$

                TRIGGER$

                TRIGGERCOL$

                TRIGGERJAVAF$

                TRIGGERJAVAS$

                TRIGGERJAVAC$

                TRIGGERJAVAM$

                SYSAUTH$

                OBJPRIV$

                DEFROLE$

                PROFILE$


49 rows selected.
SQL>
SQL> --

select cluster_name, owner, table_name from dba_tables

  
  
SQL> select cluster_name, owner, table_name
  2    from dba_tables
  3   where cluster_name is not null
  4   order by cluster_name
  5  /
CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_COBJ#                        SYS
CDEF$
C_COBJ#                        SYS
CCOL$
C_FILE#_BLOCK#                 SYS
UET$

CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_FILE#_BLOCK#                 SYS
SEG$
C_MLOG#                        SYS
MLOG$
C_MLOG#                        SYS
SLOG$

CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_OBJ#                         SYS
TAB$
C_OBJ#                         SYS
CLU$
C_OBJ#                         SYS
COL$

CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_OBJ#                         SYS
IND$
C_OBJ#                         SYS
ICOL$
C_OBJ#                         SYS
SUBCOLTYPE$

CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_OBJ#                         SYS
ICOLDEP$
C_OBJ#                         SYS
LIBRARY$
C_OBJ#                         SYS
OPQTYPE$

CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_OBJ#                         SYS
NTAB$
C_OBJ#                         SYS
TYPE_MISC$
C_OBJ#                         SYS
VIEWTRCOL$

CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_OBJ#                         SYS
ATTRCOL$
C_OBJ#                         SYS
REFCON$
C_OBJ#                         SYS
COLTYPE$

CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_OBJ#                         SYS
LOB$
C_OBJ#_INTCOL#                 SYS
HISTGRM$
C_RG#                          SYS
RGROUP$

CLUSTER_NAME                   OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_RG#                          SYS
RGCHILD$
C_TOID_VERSION#                SYS
TYPE$
C_TOID_VERSION#                SYS
COLLECTION$

36 rows selected.
SQL>
SQL>
SQL>
SQL>