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>