데이터베이스 내에 생성되어 있는 모든 테이블, 뷰, 동의어, 프로시저등의 정보
데이터베이스 내의 모든 객체 목록을 포함한다.
【예제】
다음은 recyclebin와 관련된 object들이다.
SQL> select substr(object_name,1,10),substr(object_type,1,5),
2 substr(owner,1,4) from dba_objects
3 where object_name LIKE '%RECYCLEBIN%';
SUBSTR(OBJECT_NAME,1 SUBSTR(OBJ SUBSTR(O
-------------------- ---------- --------
RECYCLEBIN TABLE SYS
RECYCLEBIN INDEX SYS
RECYCLEBIN INDEX SYS
RECYCLEBIN INDEX SYS
USER_RECYC VIEW SYS
USER_RECYC SYNON PUBL
RECYCLEBIN SYNON PUBL
DBA_RECYCL VIEW SYS
DBA_RECYCL SYNON PUBL
9 rows selected.
SQL>
【예제】
SQL> select object_type, count(*)
2 from dba_objects
3 group by object_type;
OBJECT_TYPE COUNT(*)
------------------ ----------
CLUSTER 9
CONSUMER GROUP 4
FUNCTION 13
INDEX 336
INDEXTYPE 4
JAVA CLASS 4012
JAVA RESOURCE 4
LIBRARY 33
OPERATOR 10
PACKAGE 287
PACKAGE BODY 270
PROCEDURE 12
QUEUE 4
RESOURCE PLAN 1
SEQUENCE 43
SYNONYM 5127
TABLE 293
TRIGGER 1
TYPE 131
TYPE BODY 16
UNDEFINED 20
VIEW 1299
22 rows selected.
SQL>
【예제】
$ cat zz.sql
set echo off
set verify off
set pagesize 9999
column object_name format a30
column tablespace_name format a30
column object_type format a12
column status format a1
break on object_type skip 1
select object_type, object_name,
decode( status, 'INVALID', '*', '' ) status,
decode( object_type,
'TABLE', (select tablespace_name
from dba_tables
where table_name = object_name
and owner = upper('&1')),
'TABLE PARTITION', (select tablespace_name
from dba_tab_partitions
where partition_name = subobject_name
and owner = upper('&1')),
'INDEX', (select tablespace_name
from dba_indexes
where index_name = object_name
and owner = upper('&1')),
'INDEX PARTITION', (select tablespace_name
from dba_ind_partitions
where partition_name = subobject_name
and owner = upper('&1')),
'LOB', (select tablespace_name
from dba_segments
where segment_name = object_name
and owner = upper('&1')),
null ) tablespace_name
from dba_objects a
where owner = upper('&1')
order by object_type, object_name
/
column status format a10
# sqlplus '/ as sysdba‘
SQL> @zz scott
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX PK_DEPT SYSTEM
PK_EMP SYSTEM
SYS_C001217 SYSTEM
SYS_C001218 SYSTEM
PACKAGE LOADLOBS
PACKAGE BODY LOADLOBS
TABLE BONUS SYSTEM
CREATE$JAVA$LOB$TABLE SYSTEM
DEPT SYSTEM
EMP SYSTEM
JAVA$CLASS$MD5$TABLE SYSTEM
SALGRADE SYSTEM
UNDEFINED SYS_LOB0000012047C00002$$
13 rows selected.
SQL>