Re: Helping me find the quilty SQL, which wreck my package all the time

"Joe Bayer" <joebayerii(no-spam)@hotmail.com> a écrit dans le message de news:sHoXb.10992$5W3.5167_at_nwrddc02.gnilink.net...
> We are using 9i on Linux.
> One of our package is always become invalid, and we have to have another
> crontab job running every minute to re-compile it. The funny thing is, the
> application will fail when calling it if the package is invalid, (Is it
> default that Oracle will try to re-compile it if the package is invalid, but
> in our case, oracle just returns an error and application will fail). Any
> suggestions?
>
> I need to find the quilty sql, so I can tell developer to put one more line
> like 'Alter package ... compile ' in their
> code.
>
> Based on Tom Kyte's suggestion, I created a trigger trying to catch the
> quilty sql.
>
> create or replace trigger ddl_monitor_trigger
> before create or alter or drop on schema
> declare
> l_sysevent varchar2(25);
> l_status varchar2(25);
> begin
> select ora_sysevent into l_sysevent from dual;
> select status into l_status from user_objects where object_name =
> 'MY_PACKAGE';
> if (l_status = 'INVALID' )
> then
> if ( l_sysevent in ('DROP','CREATE') )
> then
> insert into system.quilty_sql (insert_time,command_type)
> select sysdate,ora_sysevent from dual;
> elsif ( l_sysevent = 'ALTER' )
> then
> insert into system.quilty_sqltext
> select sysdate, *
> from v$open_cursor
> where upper(sql_text) like 'ALTER%' ;
> end if;
> end if;
> end;
>
>
> The problem is, sql like 'ALTER TABLE bla truncate partition', 'ALTER table
> split partition ', etc ,etc
> will not be catched into v$open_cursor, if we take a snapshot of v$sql,
> there will be too many potential quilty ones, and it will be difficult for
> me to tell which one is the real ONE. (the package dependency shows 1200
> items).
>
> Thanks for your help.
>

To add filters you can use:
ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type to check if the alter is related to your package. sys_context('USERENV','SESSIONID') can help you find the current SID by searching with audsid column in v$session, and a join with v$sql gives you the current sql statement.

Regards
Michel Cadot Received on Sat Feb 14 2004 - 10:16:08 CST