BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_PROCEDURES view tips

Oracle Tips by Burleson Consulting

DBA_PROCEDURES

<< Return to the index

Oracle 11g makes the following comments about the DBA_PROCEDURES table: "Description of the dba functions/procedures/packages/types/triggers"

Related notes on DBA_PROCEDURES:

Oracle DBA View List

Column description of the DBA_PROCEDURES view:

OWNER

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

OBJECT_NAME

Description of DBA_PROCEDURES.OBJECT_NAME: "Name of the object: top level function/procedure/package/type/trigger name"

PROCEDURE_NAME

Description of DBA_PROCEDURES.PROCEDURE_NAME: "Name of the package or type subprogram"

OBJECT_ID

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

SUBPROGRAM_ID

Description of DBA_PROCEDURES.SUBPROGRAM_ID: "Unique sub-program identifier"

OVERLOAD

Description of DBA_PROCEDURES.OVERLOAD: "Overload unique identifier"

OBJECT_TYPE

Description of DBA_PROCEDURES.OBJECT_TYPE: "The typename of the object"

AGGREGATE

Description of DBA_PROCEDURES.AGGREGATE: "Is it an aggregate function ?"

PIPELINED

Description of DBA_PROCEDURES.PIPELINED: "Is it a pipelined table function ?"

IMPLTYPEOWNER

Description of DBA_PROCEDURES.IMPLTYPEOWNER: "Name of the owner of the implementation type (if any)"

IMPLTYPENAME

Description of DBA_PROCEDURES.IMPLTYPENAME: "Name of the implementation type (if any)"

PARALLEL

Description of DBA_PROCEDURES.PARALLEL: "Is the procedure parallel enabled ?"

INTERFACE

No comments exist for the INTERFACE column of the DBA_PROCEDURES table.

DETERMINISTIC

No comments exist for the DETERMINISTIC column of the DBA_PROCEDURES table.

AUTHID

No comments exist for the AUTHID column of the DBA_PROCEDURES table.

DBA_PROCEDURES View Source

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

(select u.name, o.name, pi.procedurename, o.obj#, pi.procedure#,
decode(pi.overload#, 0, NULL, pi.overload#),
decode(o.type#, 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',
22, 'LIBRARY', 28, 'JAVA SOURCE', 29, 'JAVA CLASS',
30, 'JAVA RESOURCE', 87, 'ASSEMBLY', 'UNDEFINED'),
decode(bitand(pi.properties,8),8,'YES','NO'),
decode(bitand(pi.properties,16),16,'YES','NO'),
u2.name, o2.name,
decode(bitand(pi.properties,32),32,'YES','NO'),
decode(bitand(pi.properties,512),512,'YES','NO'),
decode(bitand(pi.properties,256),256,'YES','NO'),
decode(bitand(pi.properties,1024),1024,'CURRENT_USER','DEFINER')
from sys."_CURRENT_EDITION_OBJ" o, user$ u, procedureinfo$ pi,
sys."_CURRENT_EDITION_OBJ" o2, user$ u2
where u.user# = o.owner# and o.obj# = pi.obj#
and pi.itypeobj# = o2.obj# (+) and o2.owner# = u2.user# (+))
union all
(select tabobj.owner, tabobj.object_name, NULL,
tabobj.object_id,
case tabobj.object_type
when 'TRIGGER' then 1
else 0
end,
NULL, tabobj.object_type, 'NO', 'NO', NULL, NULL, 'NO', 'NO', 'NO',
case tabobj.object_type
WHEN 'TRIGGER' then 'DEFINER'
else
decode(bitand(pi.properties,1024),
NULL,NULL,
1024,'CURRENT_USER','DEFINER')
end case
from dba_objects tabobj, procedureinfo$ pi
where
((tabobj.object_id = pi.obj# (+)) AND
(tabobj.object_type IN ('TRIGGER', 'PACKAGE')) AND
((pi.procedure# is null) OR (pi.procedure# = 1))))

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