Oracle PL/SQL/System Tables Views/DBA Tables

Data Dictionary Scope

   <source lang="sql">
 

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>

 </source>
   
  

Get owner, table name and table space name from dba_tables

   <source lang="sql">

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>

 </source>
   
  

Query dba_tables by owners

   <source lang="sql">
 

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.

 </source>
   
  

Query dba_tables for table info

   <source lang="sql">
 

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> --

 </source>
   
  

select cluster_name, owner, table_name from dba_tables

   <source lang="sql">
 
 

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>

 </source>