DBA_OBJECTS_AE view tips

Oracle Tips by Burleson Consulting

DBA_OBJECTS_AE

<< Return to the index

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

Column description of the DBA_OBJECTS_AE view:

OWNER

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

OBJECT_NAME

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

SUBOBJECT_NAME

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

OBJECT_ID

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

DATA_OBJECT_ID

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

OBJECT_TYPE

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

CREATED

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

LAST_DDL_TIME

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

TIMESTAMP

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

STATUS

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

TEMPORARY

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

GENERATED

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

SECONDARY

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

NAMESPACE

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

EDITION_NAME

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

DBA_OBJECTS_AE View Source

Oracle 11g's data dictionary defines the DBA_OBJECTS_AE 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', 10, 'NON-EXISTENT',
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."_ACTUAL_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and (o.type# != 1 /* INDEX - handled below */
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