в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:10Z

1 версия

Новая страница

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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/System Tables Views/DBA Tables - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия