DBA_OBJECT_SIZE

Wednesday, December 3, 2008

DBA_OBJECT_SIZE

DBA_OBJECT_SIZE lists the sizes, in bytes, of various PL/SQL objects.

Related View

· USER_OBJECT_SIZE lists the size of PL/SQL objects owned by the current user.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the object
`NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the object
`TYPE`	`VARCHAR2(12)`		Type of the object: `TYPE, TYPE BODY, TABLE, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, JAVA SOURCE, JAVA CLASS` or `JAVA RESOURCE`
`SOURCE_SIZE`	`NUMBER`		Size of the source in bytes. Must be in memory during compilation, or dynamic recompilation.
`PARSED_SIZE`	`NUMBER`		Size of the parsed form of the object, in bytes. Must be in memory when an object is being compiled that references this object.
`CODE_SIZE`	`NUMBER`		Code size, in bytes. Must be in memory when this object is executing.
`ERROR_SIZE`	`NUMBER`		Size of error messages, in bytes. In memory during the compilation of the object when there are compilation errors.

Note:

1. Oracle Metalink (Note 1020288.6) provides a script to report size of stored objects (PL/SQL packages, functions, procedures, Java objects, sequences, types and triggers).

column num_instances heading "Num" format 99999

column type heading "Object Type" format a12

column source_size heading "Source" format 99,999,999

column parsed_size heading "Parsed" format 99,999,999

column code_size heading "Code" format 99,999,999

column error_size heading "Errors" format 999,999

column size_required heading "Total" format 999,999,999

compute sum of size_required on report

select count(name) num_instances

      ,type

      ,sum(source_size) source_size

      ,sum(parsed_size) parsed_size

      ,sum(code_size) code_size

      ,sum(error_size) error_size

      ,sum(source_size)

      +sum(parsed_size)

      +sum(code_size)

      +sum(error_size) size_required

from dba_object_size

group by type

order by 2

NUM_INSTANCES	TYPE	SOURCE_SIZE	PARSED_SIZE	CODE_SIZE	ERROR_SIZE	SIZE_REQUIRED
362	FUNCTION	252230	195189	459916	0	907335
16447	JAVA CLASS	0	0	81741773	935	81742708
298	JAVA DATA	0	0	8671612	0	8671612
792	JAVA RESOURCE	0	0	10784295	0	10784295
8	JAVA SOURCE	0	0	33522	0	33522
879	PACKAGE	5479198	5459040	2814865	0	13753103
803	PACKAGE BODY	16519276	0	19127102	4239	35650617
144	PROCEDURE	301150	127219	391309	0	819678
763	SEQUENCE	0	365802	0	0	365802
818	SYNONYM	0	147102	0	0	147102
90	TABLE	0	115466	0	0	115466
2483	TRIGGER	2212113	0	2648689	1353	4862155
1617	TYPE	843995	1267411	612502	0	2723908
149	TYPE BODY	243634	8393	881753	0	1133780
16	VIEW	0	1595	0	929	2524