RE: Obtain stats on packages and functions within packages
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <sbecker6925@xxxxxxxxx>, <gus.spier@xxxxxxxxx>
- Date: Thu, 27 Jan 2011 10:32:24 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sandra Becker
Sent: Thursday, January 27, 2011 10:14 AM
To: gus.spier@xxxxxxxxx
Cc: gajav@xxxxxxxxx; 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@xxxxxxxxx<mailto:gus.spier@xxxxxxxxx>> 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.
Other related posts:
