BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_ARGUMENTS view tips

Oracle Tips by Burleson Consulting

DBA_ARGUMENTS

<< Return to the index

Oracle 11g makes the following comments about the DBA_ARGUMENTS table: "All arguments for objects in the database"

Column description of the DBA_ARGUMENTS view:

OWNER

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

OBJECT_NAME

Description of DBA_ARGUMENTS.OBJECT_NAME: "Procedure or function name"

PACKAGE_NAME

Description of DBA_ARGUMENTS.PACKAGE_NAME: "Package name"

OBJECT_ID

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

OVERLOAD

Description of DBA_ARGUMENTS.OVERLOAD: "Overload unique identifier"

SUBPROGRAM_ID

Description of DBA_ARGUMENTS.SUBPROGRAM_ID: "Unique sub-program Identifier"

ARGUMENT_NAME

Description of DBA_ARGUMENTS.ARGUMENT_NAME: "Argument name"

POSITION

Description of DBA_ARGUMENTS.POSITION: "Position in argument list, or null for function return value"

SEQUENCE

Description of DBA_ARGUMENTS.SEQUENCE: "Argument sequence, including all nesting levels"

DATA_LEVEL

Description of DBA_ARGUMENTS.DATA_LEVEL: "Nesting depth of argument for composite types"

DATA_TYPE

Description of DBA_ARGUMENTS.DATA_TYPE: "Datatype of the argument"

DEFAULTED

Description of DBA_ARGUMENTS.DEFAULTED: "Is the argument defaulted?"

DEFAULT_VALUE

Description of DBA_ARGUMENTS.DEFAULT_VALUE: "Default value for the argument"

DEFAULT_LENGTH

Description of DBA_ARGUMENTS.DEFAULT_LENGTH: "Length of default value for the argument"

IN_OUT

Description of DBA_ARGUMENTS.IN_OUT: "Argument direction (IN, OUT, or IN/OUT)"

DATA_LENGTH

Description of DBA_ARGUMENTS.DATA_LENGTH: "Length of the column in bytes"

DATA_PRECISION

Description of DBA_ARGUMENTS.DATA_PRECISION: "Length: decimal digits (NUMBER) or binary digits (FLOAT)"

DATA_SCALE

Description of DBA_ARGUMENTS.DATA_SCALE: "Digits to right of decimal point in a number"

RADIX

Description of DBA_ARGUMENTS.RADIX: "Argument radix for a number"

CHARACTER_SET_NAME

Description of DBA_ARGUMENTS.CHARACTER_SET_NAME: "Character set name for the argument"

TYPE_OWNER

Description of DBA_ARGUMENTS.TYPE_OWNER: "Owner name for the argument type in case of object types"

TYPE_NAME

Description of DBA_ARGUMENTS.TYPE_NAME: "Object name for the argument type in case of object types"

TYPE_SUBNAME

Description of DBA_ARGUMENTS.TYPE_SUBNAME: "Subordinate object name for the argument type in case of object types"

TYPE_LINK

Description of DBA_ARGUMENTS.TYPE_LINK: "Database link name for the argument type in case of object types"

PLS_TYPE

Description of DBA_ARGUMENTS.PLS_TYPE: "PL/SQL type name for numeric arguments"

CHAR_LENGTH

Description of DBA_ARGUMENTS.CHAR_LENGTH: "Character limit for string datatypes"

CHAR_USED

Description of DBA_ARGUMENTS.CHAR_USED: "Is the byte limit (B) or char limit (C) official for this string?"

DBA_ARGUMENTS View Source

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

select
u.name, /* OWNER */
nvl(a.procedure$,o.name), /* OBJECT_NAME */
decode(a.procedure$,null,null, o.name), /* PACKAGE_NAME */
o.obj#, /* OBJECT_ID */
decode(a.overload#,0,null,a.overload#), /* OVERLOAD */
a.procedure#, /* SUBPROGRAM ID */
a.argument, /* ARGUMENT_NAME */
a.position#, /* POSITION */
a.sequence#, /* SEQUENCE */
a.level#, /* DATA_LEVEL */
decode(a.type#, /* DATA_TYPE */
0, null,
1, decode(a.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(a.scale, -127, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(a.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(a.charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
102, 'REF CURSOR',
104, 'UROWID',
105, 'MLSLABEL',
106, 'MLSLABEL',
110, 'REF',
111, 'REF',
112, decode(a.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'OBJECT',
122, 'TABLE',
123, 'VARRAY',
178, 'TIME',
179, 'TIME WITH TIME ZONE',
180, 'TIMESTAMP',
181, 'TIMESTAMP WITH TIME ZONE',
231, 'TIMESTAMP WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR TO MONTH',
183, 'INTERVAL DAY TO SECOND',
250, 'PL/SQL RECORD',
251, 'PL/SQL TABLE',
252, 'PL/SQL BOOLEAN',
'UNDEFINED'),
decode(default#, 1, 'Y', 'N'), /* DEFAULTED */
default$, /* DEFAULT_VALUE */
deflength, /* DEFAULT_LENGTH */
decode(in_out,null,'IN',1,'OUT',2,'IN/OUT','Undefined'), /* IN_OUT */
length, /* DATA_LENGTH */
precision#, /* DATA_PRECISION */
decode(a.type#, 2, scale, 1, null, 96, null, scale), /* DATA_SCALE */
radix, /* RADIX */
decode(a.charsetform, 1, 'CHAR_CS', /* CHARACTER_SET_NAME */
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(a.charsetid),
4, 'ARG:'||a.charsetid),
a.type_owner, /* TYPE_OWNER */
a.type_name, /* TYPE_NAME */
a.type_subname, /* TYPE_SUBNAME */
a.type_linkname, /* TYPE_LINK */
a.pls_type, /* PLS_TYPE */
decode(a.type#, 1, a.scale, 96, a.scale, 0), /* CHAR_LENGTH */
decode(a.type#,
1, decode(bitand(a.properties, 128), 128, 'C', 'B'),
96, decode(bitand(a.properties, 128), 128, 'C', 'B'), 0) /* CHAR_USED */
from sys."_CURRENT_EDITION_OBJ" o,argument$ a,user$ u
where o.obj# = a.obj#
and o.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