RE: Obtain stats on packages and functions within packages

From: <>
Date: Thu, 27 Jan 2011 10:32:24 -0500
Message-ID: <>

Quick script from Sqlplus:

set pause off
set heading off
set trimout on
set linesize 300
set pagesize 1000
select text from user_source where name like upper('&1')  order by TYPE, name, line, text;
set heading on
set linesize 300
set pagesize 300

undefine 1


Alternatively, using dbms_metadata (also Sqlplus).

SET LONG 1000000
SET LONGCHUNK 1000000 begin

        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', FALSE);

Then here are a copy sample queries that you can modify to your purposes.

(change object_type, etc. you can put object_name in, or name the object with quotes.

select dbms_metadata.get_ddl('MATERIALIZED_VIEW',object_name,'LAWCRP') from dba_objects where object_type = 'MATERIALIZED VIEW' and owner = 'LAWCRP';

select dbms_metadata.get_ddl(object_type,'SNAP_ACGLACCT','LAWCRP') from dba_objects where owner = 'LAWCRP' and object_name = 'SNAP_ACGLACCT';

Joel Patterson
Database Administrator
904 727-2546

From: [] On Behalf Of Sandra Becker Sent: Thursday, January 27, 2011 10:14 AM To:
Cc:; oracle-l
Subject: Re: Obtain stats on packages and functions within packages

Unfortunately, no. Although I have mentioned it more than once, development refuses to even consider it. I've got meetings all day today but tomorrow I want to start looking into the DBA_HIST views to see if I can figure out how to pull out the package/function information. I was hoping someone had already written a script so I don't have to re-invent the wheel. Never hurts to learn things on your own of course. Sandy

On Wed, Jan 26, 2011 at 6:02 PM, Gus Spier <<>> wrote: Sandra, is there any chance that the packages and functions have been "instrumented"? That is, has anybody taken advantage of DBMS_APPLICATION_INFO? that would certainly make your task easier.


Transzap, Inc.

Received on Thu Jan 27 2011 - 09:32:24 CST

Original text of this message