DBA_OBJECTS describes all objects in the database. Its columns are the same as those in "ALL_OBJECTS".
·
ALL_OBJECTS describes all objects accessible to the current user.
·
USER_OBJECTS describes all objects owned by the current user. This view does not display the OWNER column.
Column
|
Datatype
|
NULL
|
Description
|
OWNER | VARCHAR2(30) | NOT NULL |
Owner of the object
|
OBJECT_NAME | VARCHAR2(30) | NOT NULL |
Name of the object
|
SUBOBJECT_NAME | VARCHAR2(30) |
Name of the subobject (for example, partition)
| |
OBJECT_ID | NUMBER | NOT NULL |
Dictionary object number of the object
|
DATA_OBJECT_ID | NUMBER |
Dictionary object number of the segment that contains the object
| |
Note:
OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that the Oracle Database assigns to row objects in object tables in the system. | |||
OBJECT_TYPE | VARCHAR2(19) |
Type of the object (such as
TABLE, INDEX) | |
CREATED | DATE | NOT NULL |
Timestamp for the creation of the object
|
LAST_DDL_TIME | DATE | NOT NULL |
Timestamp for the last modification of the object resulting from a DDL statement (including grants and revokes)
|
TIMESTAMP | VARCHAR2(20) |
Timestamp for the specification of the object (character data)
| |
STATUS | VARCHAR2(7) |
Status of the object (
VALID, INVALID, or N/A) | |
TEMPORARY | VARCHAR2(1) |
Whether the object is temporary (the current session can see only data that it placed in this object itself)
| |
GENERATED | VARCHAR2(1) |
Indicates whether the name of this object was system generated (
Y) or not (N) | |
SECONDARY | VARCHAR2(1) |
Whether this is a secondary object created by the
ODCIIndexCreate method of the Oracle Data Cartridge (Y | N) | |
NAMESPACE | NUMBER |
Namespace for the object
| |
EDITION_NAME | VARCHAR2(30) |
Name of the Application Edition where the object is identified
|
Note:
1. For indexes of types
IOT - NESTED and LOB, you can get the whole information from DBA_INDEXES rather than using DBA_OBJECTS.
2. When
TIMESTAMP and LAST_DDL_TIME columns will differ? (Metalink note 309235.1)
·
LAST_DDL_TIME：Timestamp for the last modification of the object resulting from a DDL command (including grants and revokes)
·
TIMESTAMP：Timestamp for the specification of the object
Example 1:
Created a table called YY
SQL> select TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') DDL, timestamp from
dba_objects where object_name='YY';
DDL TIMESTAMP
-------------------- -------------------
30-MAR-2005 21:08:34 2005-03-30:21:08:34
As it can be seen when the table is created both has the same value
SQL> GRANT SELECT ON YY TO PUBLIC; <-----------DDL performed
Grant succeeded.
SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from
dba_objects where object_name='YY';
DDL TIMESTAMP
-------------------- -------------------
30-MAR-2005 21:13:44 2005-03-30:21:08:34
It is seen that
last_ddl_time is different. TIMESTAMP remains same as object creation
Now we will see under what circumstances last_ddl_time and timestamp will differ.
1. When DDL is performed on the dependent objects of the table. For example when an index is created or altered.
2. When a table is truncated.
3. When roles/privileges are granted/revoked.
Example 2: A small example to better understand point 1 :
SQL> CREATE TABLE YY(A NUMBER);
Table created.
SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from
dba_objects where object_name='YY';
DDL TIMESTAMP
-------------------- -------------------
31-MAR-2005 15:16:54 2005-03-31:15:16:54
SQL> CREATE INDEX YYI ON YY(A);
Index created.
SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL, timestamp from
dba_objects where object_name='YY';
DDL TIMESTAMP
-------------------- --------------
31-MAR-2005 15:21:53 2005-03-31:15:16:54
It can be clearly seen that though we did not perform any DDL operation on the table but the creation of an index on the table changed its last_ddl_time. Timestamp does not change.
|
3. Scripts using DBA_OBJECTS
--Distribution of objects and data: Which schemas are taking up all of the space
select obj.owner, obj_cnt, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
(select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
|
--Is JAVA installed in the database? This will return 9000'ish if it is...
select count(*)
from dba_objects
where object_type like '%JAVA%'
and owner = 'SYS'
|
--to determining which segments have many buffers in the pool
SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD
AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*) desc;
|
-- report the type and count of objects by user
select owner,
sum(decode(object_type,'TABLE',1,0)) tables,
sum(decode(object_type,'INDEX',1,0)) indexes,
sum(decode(object_type,'SYNONYM',1,0)) synonyms,
sum(decode(object_type,'SEQUENCE',1,0)) sequences,
sum(decode(object_type,'VIEW',1,0)) views,
sum(decode(object_type,'CLUSTER',1,0)) clusters,
sum(decode(object_type,'DATABASE LINK',1,0)) database_links,
sum(decode(object_type,'PACKAGE',1,0)) packages,
sum(decode(object_type,'PACKAGE BODY',1,0)) package_bodies,
sum(decode(object_type,'PROCEDURE',1,0)) procedures
from dba_objects
group by owner;
|
-- The script produces information about locks being held or waited on in the database
select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
DECODE(B.ID2, 0, A.OBJECT_NAME,'Trans-'||to_char(B.ID1)) OBJECT_NAME,
B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Lock Mode",
DECODE(B.REQUEST,
0,' ',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Req Mode"
from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1
and B.SID = C.SID
and C.USERNAME is not null
order by B.SID, B.ID2;
|
-- The script generates scripts to compile all invalid objects in the database
select
decode(OBJECT_TYPE, 'PACKAGE BODY', 'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from dba_objects
where STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' )
order by OBJECT_TYPE, OBJECT_NAME;
|
