RE: Obtain stats on packages and functions within packages

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 27 Jan 2011 10:32:24 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA16013A04F3_at_JAXMSG01.crowley.com>

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,'PRETTY',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', FALSE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE);
        dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS',FALSE);

end;
/

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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandra Becker Sent: Thursday, January 27, 2011 10:14 AM To: gus.spier_at_gmail.com
Cc: gajav_at_yahoo.com; 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 <gus.spier_at_gmail.com<mailto:gus.spier_at_gmail.com>> 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.

Gus

Sandy
Transzap, Inc.

--
http://www.freelists.org/webpage/oracle-l

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