BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_OBJECTS view tips

Oracle Tips by Burleson Consulting

DBA_OBJECTS

<< Return to the index

Oracle 11g makes the following comments about the DBA_OBJECTS table: "All objects in the database"

Related notes on DBA_OBJECTS:

Column description of the DBA_OBJECTS view:

OWNER

Description of DBA_OBJECTS.OWNER: "Username of the owner of the object"

OBJECT_NAME

Description of DBA_OBJECTS.OBJECT_NAME: "Name of the object"

SUBOBJECT_NAME

Description of DBA_OBJECTS.SUBOBJECT_NAME: "Name of the sub-object (for example, partititon)"

OBJECT_ID

Description of DBA_OBJECTS.OBJECT_ID: "Object number of the object"

DATA_OBJECT_ID

Description of DBA_OBJECTS.DATA_OBJECT_ID: "Object number of the segment which contains the object"

OBJECT_TYPE

Description of DBA_OBJECTS.OBJECT_TYPE: "Type of the object"

CREATED

Description of DBA_OBJECTS.CREATED: "Timestamp for the creation of the object"

LAST_DDL_TIME

Description of DBA_OBJECTS.LAST_DDL_TIME: "Timestamp for the last DDL change (including GRANT and REVOKE) to the object"

TIMESTAMP

Description of DBA_OBJECTS.TIMESTAMP: "Timestamp for the specification of the object"

STATUS

Description of DBA_OBJECTS.STATUS: "Status of the object"

TEMPORARY

Description of DBA_OBJECTS.TEMPORARY: "Can the current session only see data that it place in this object itself?"

GENERATED

Description of DBA_OBJECTS.GENERATED: "Was the name of this object system generated?"

SECONDARY

Description of DBA_OBJECTS.SECONDARY: "Is this a secondary object created as part of icreate for domain indexes?"

NAMESPACE

Description of DBA_OBJECTS.NAMESPACE: "Namespace for the object"

EDITION_NAME

Description of DBA_OBJECTS.EDITION_NAME: "Name of the edition in which the object is actual"

DBA_OBJECTS View Source

Oracle 11g's data dictionary defines the DBA_OBJECTS view using the following source query:

select u.name, o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj#=o.obj#
and bitand(s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'EDITION', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
'UNDEFINED'),
o.ctime, o.mtime,
to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
o.namespace,
o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and (o.type# not in (1 /* INDEX - handled below */,
10 /* NON-EXISTENT */)
or
(o.type# = 1 and 1 = (select 1
from sys.ind$ i
where i.obj# = o.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags, 128) = 0
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
'DATABASE LINK',
l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL
from sys.link$ l, sys.user$ u
where l.owner# = u.user#

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books