DBA_IDENTIFIERS view tips

Oracle Tips by Burleson Consulting

DBA_IDENTIFIERS

<< Return to the index

Oracle 11g makes the following comments about the DBA_IDENTIFIERS table: "Identifiers in stored objects accessible to sys"

Column description of the DBA_IDENTIFIERS view:

OWNER

No comments exist for the OWNER column of the DBA_IDENTIFIERS table.

NAME

Description of DBA_IDENTIFIERS.NAME: "Name of the identifier"

SIGNATURE

Description of DBA_IDENTIFIERS.SIGNATURE: "Signature of the identifier"

TYPE

Description of DBA_IDENTIFIERS.TYPE: "Type of the identifier"

OBJECT_NAME

Description of DBA_IDENTIFIERS.OBJECT_NAME: "Name of the object where the identifier action occurred"

OBJECT_TYPE

Description of DBA_IDENTIFIERS.OBJECT_TYPE: "Type of the object where the identifier action occurred"

USAGE

Description of DBA_IDENTIFIERS.USAGE: "Type of the identifier action"

USAGE_ID

Description of DBA_IDENTIFIERS.USAGE_ID: "Number of the identifier action which is unique within the object"

LINE

Description of DBA_IDENTIFIERS.LINE: "Line number of the identifier action"

COL

Description of DBA_IDENTIFIERS.COL: "Column number of the identifier action"

USAGE_CONTEXT_ID

Description of DBA_IDENTIFIERS.USAGE_CONTEXT_ID: "Context action number of the identifier action"

DBA_IDENTIFIERS View Source

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

select u.name, i.symrep, i.signature,
decode(i.type#, 1, 'VARIABLE', 2, 'ITERATOR', 3, 'DATE DATATYPE',
4, 'PACKAGE', 5, 'PROCEDURE', 6, 'FUNCTION', 7, 'FORMAL IN',
8, 'SUBTYPE', 9, 'CURSOR', 10, 'INDEX TABLE', 11, 'OBJECT',
12, 'RECORD', 13, 'EXCEPTION', 14, 'BOOLEAN DATATYPE', 15, 'CONSTANT',
16, 'LIBRARY', 17, 'ASSEMBLY', 18, 'DBLINK', 19, 'LABEL',
20, 'TABLE', 21, 'NESTED TABLE', 22, 'VARRAY', 23, 'REFCURSOR',
24, 'BLOB DATATYPE', 25, 'CLOB DATATYPE', 26, 'BFILE DATATYPE',
27, 'FORMAL IN OUT', 28, 'FORMAL OUT', 29, 'OPAQUE',
30, 'NUMBER DATATYPE', 31, 'CHARACTER DATATYPE',
32, 'ASSOCIATIVE ARRAY', 33, 'TIME DATATYPE', 34, 'TIMESTAMP DATATYPE',
35, 'INTERVAL DATATYPE', 36, 'UROWID', 37, 'SYNONYM', 38, 'TRIGGER',
'UNDEFINED'),
o.name,
decode(o.type#, 5, 'SYNONYM', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
22, 'LIBRARY', 33, 'SPEC OPERATOR', 87, 'ASSEMBLY',
'UNDEFINED'),
decode(a.action, 1, 'DECLARATION', 2, 'DEFINITION', 3, 'CALL', 4, 'REFERENCE',
5, 'ASSIGNMENT', 'UNDEFINED'),
a.action#, a.line, a.col, a.context#
from sys."_CURRENT_EDITION_OBJ" o, sys.plscope_identifier$ i, sys.plscope_action$ a, sys.user$ u
where i.signature = a.signature
and o.obj# = a.obj#
and o.owner# = u.user#
and ( o.type# in (5, 7, 8, 9, 11, 12, 14, 22, 33, 87) OR
( o.type# = 13 AND o.subname is null))
and o.owner# = userenv('SCHEMAID')

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