Home > Oracle, Performance > Who dropped my procedure?

Who dropped my procedure?


In past two posts ( from current schema and as SYS user ) , I have explained how we can restore a package.  Some one posed me a question – how can I identify who dropped the package.  Even though we are seeing a procedure or package as database object, it is stored as a table rows in the SOURCE$ table just like other tables in the database.  So, any CREATE or DROP procedure / package are just INSERT or DELETE or UPDATE operations to the SOURCE$ table.  We can use this along with FLASHBACK feature to identify who dropped the procedure, it is simple.

First thing is to identify what are various transactions happened to the SOURCE$ table.  Login as SYS user and use FLASHBACK query to identify the transactions to the SOURCE$.

<pre>SQL> set linesize 200
SQL> col TABLE_NAME for a15
SQL> col TABLE_OWNER for a15
SQL> SELECT distinct start_scn, commit_scn, operation, table_name, table_owner,xid,logon_user
FROM flashback_transaction_query
where table_name='&Table_name';
Enter value for table_name: SOURCE$
old 3: where table_name='&Table_name'
new 3: where table_name='SOURCE$'

 START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER XID LOGON_USER
---------- ---------- -------------------------------- --------------- --------------- ---------------- ------------------------------
 7335974 7335984 DELETE SOURCE$ SYS 0002000B00000E1E APPLE
 7347338 7347344 INSERT SOURCE$ SYS 0005001D0000140F APPLE
 7347355 7347365 DELETE SOURCE$ SYS 0008000300000E0B THOMAS
 7317378 7317385 INSERT SOURCE$ SYS 0008001900000E05 APPLE</pre>

In fact I am missing a PROCEDURE from APPLE schema.  So there are two DELETE and two INSERT statements happened to the SOURCE$ table.  I am not going to worry about the INSERT operation as  the expected operation is DELETE.  So, I took the XID  ’0008000300000E0B’ and checked what are the UNDO SQLs for that transaction.

<pre>SQL> set linesize 200
SQL> SELECT undo_sql
 2 FROM flashback_transaction_query
 3 WHERE xid = HEXTORAW('&xid');
Enter value for xid: 0008000300000E0B
old 3: WHERE xid = HEXTORAW('&xid')
new 3: WHERE xid = HEXTORAW('0008000300000E0B')
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYS"."OBJERROR$"("OBJ#") values ('22781');
update "SYS"."OBJ$" set "OBJ#" = '22781', "DATAOBJ#" = NULL, "TYPE#" = '7', "CTIME" = TO_DATE('21-FEB-13', 'DD-MON-RR'), "MTIME" = TO_DATE('21-FEB-13', 'DD-MON-RR'), "STIME" = TO_DATE('21-FEB-13', 'DD
-MON-RR'), "STATUS" = '3', "FLAGS" = '32768', "OID$" = NULL, "SPARE1" = '6', "SPARE2" = '65535' where ROWID = 'AAAAASAABAAAGTIAAY';
insert into "SYS"."DEPENDENCY$"("D_OBJ#","D_TIMESTAMP","ORDER#","P_OBJ#","P_TIMESTAMP","D_OWNER#","PROPERTY","D_ATTRS","D_REASON") values ('22781',TO_DATE('21-FEB-13', 'DD-MON-RR'),'0','1177',TO_DATE(
'18-APR-06', 'DD-MON-RR'),NULL,'1',NULL,NULL);
insert into "SYS"."ERROR$"("OBJ#","SEQUENCE#","LINE","POSITION#","TEXTLENGTH","TEXT","PROPERTY","ERROR#") values ('22781','4','5','1','29','PL/SQL: SQL Statement ignored','0','0');
insert into "SYS"."ERROR$"("OBJ#","SEQUENCE#","LINE","POSITION#","TEXTLENGTH","TEXT","PROPERTY","ERROR#") values ('22781','3','5','8','47','PL/SQL: ORA-00942: table or view does not exist','0','0');
insert into "SYS"."ERROR$"("OBJ#","SEQUENCE#","LINE","POSITION#","TEXTLENGTH","TEXT","PROPERTY","ERROR#") values ('22781','2','4','1','29','PL/SQL: SQL Statement ignored','0','0');
insert into "SYS"."ERROR$"("OBJ#","SEQUENCE#","LINE","POSITION#","TEXTLENGTH","TEXT","PROPERTY","ERROR#") values ('22781','1','4','8','47','PL/SQL: ORA-00942: table or view does not exist','0','0');
insert into "SYS"."SETTINGS$"("OBJ#","PARAM","VALUE") values ('22781','plsql_compiler_flags','INTERPRETED,NON_DEBUG');
insert into "SYS"."SETTINGS$"("OBJ#","PARAM","VALUE") values ('22781','plscope_settings','IDENTIFIERS:NONE');
insert into "SYS"."SETTINGS$"("OBJ#","PARAM","VALUE") values ('22781','plsql_ccflags',NULL);
insert into "SYS"."SETTINGS$"("OBJ#","PARAM","VALUE") values ('22781','plsql_warnings','DISABLE:ALL');
insert into "SYS"."SETTINGS$"("OBJ#","PARAM","VALUE") values ('22781','nls_length_semantics','BYTE');
insert into "SYS"."SETTINGS$"("OBJ#","PARAM","VALUE") values ('22781','plsql_debug','FALSE');
insert into "SYS"."SETTINGS$"("OBJ#","PARAM","VALUE") values ('22781','plsql_code_type','INTERPRETED');
insert into "SYS"."SETTINGS$"("OBJ#","PARAM","VALUE") values ('22781','plsql_optimize_level','2');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','7','end;');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','6','commit;
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','5','update emp set comm=sal*0.10 where empno = v_empno;
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','4','update emp set sal=sal*(1+v_percent/100) where empno = v_empno;
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','3','begin
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','2','is
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','1','procedure Update_emp (v_empno number,v_percent number)
');
insert into "SYS"."PROCEDURE$"("OBJ#","AUDIT$","STORAGESIZE","OPTIONS") values ('22781','--------------------------------------',NULL,'0');</pre>

There you go, I am particularly interested in the below statements, which is undoing the ‘DROP’

<pre>insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','5','update emp set comm=sal*0.10 where empno = v_empno;
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','4','update emp set sal=sal*(1+v_percent/100) where empno = v_empno;
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','3','begin
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','2','is
');
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('22781','1','procedure Update_emp (v_empno number,v_percent number)
');</pre>

From this, it is clear that the user THOMAS dropped the procedure.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: