Nsryjdtyk

I have a procedure which was working until I mistakenly replaced by other code.
So my procedure started with CREATE OR REPLACE PROCEDURE xxx.yyy, and I mistakenly replaced with some simple code and saved it.

Since I had replaced in my procedure so it got replaced but now it doesn't work the way I want any more. How can I get my old code that was in that procedure (before replaced)?

When you're connected at xxx schema, try to use :

select s.text 

  from user_source 

  as of timestamp systimestamp - interval '1' day s

 where s.name = 'YYY';

to get your procedure's source. It gives your the data of one day before. You may convert '1' day to '10' hour, '150' minute .. etc. depending on your need, in the case your db_flashback_retention_target database parameter has been set as large as enough.

If there are archive logs for the search period. You can use the utility LogMiner.
for example:

   CREATE OR REPLACE PACKAGE BODY acs_ss2.rest_of_day is



    /*------------------------------------------------------------------*/

    procedure starting(p_date date) is

      rec_newrest   r_rest%rowtype;

      rec_oldrest   r_rest%rowtype;

      rec_rest      r_rest%rowtype;

      v_station   integer := 8902;

      v_dt1 date;

      v_dt2 date;

    begin

SQL CODE...

Determine the desired log files by the time of the incident.

select name, first_time,  next_time

  from v$archived_log

where first_time >sysdate -3/24



/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf  22-ноя-2018 10:56:12    22-ноя-2018 12:13:48

/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf  22-ноя-2018 12:13:48    22-ноя-2018 13:17:06

/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf  22-ноя-2018 13:17:06    22-ноя-2018 13:39:38

Run the logminer utility.

EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf', OPTIONS => DBMS_LOGMNR.NEW); 

EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile); 

EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile); 



EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Run SQL for restore our CODE, see column sql_undo.

SELECT 

to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),

operation,  

sql_redo,

sql_undo

 FROM v$logmnr_contents

where 

seg_owner='SYS' and  

seg_name='SOURCE$'

==>

TO_CHAR(TIMESTAMP,'DD-MM-YYYYHH24:MI:SS')       OPERATION                              SQL_REDO                                                                                             SQL_UNDO                                                                                             

----------------------------------------------- -------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 

22-11-2018 13:39:14                             DELETE                                 delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '1' and "SOURCE" = 'package body re insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','1','package body rest_of_day i 

22-11-2018 13:39:14                             DELETE                                 delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '2' and "SOURCE" = '

' and ROWID =  insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','2','

');                       

22-11-2018 13:39:14                             DELETE                                 delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '3' and "SOURCE" = '/*------------- insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','3','/*------------------------ 

22-11-2018 13:39:14                             DELETE                                 delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '4' and "SOURCE" = 'procedure start insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','4','procedure starting(p_date  

22-11-2018 13:39:14                             DELETE                                 delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '5' and "SOURCE" = '  rec_newrest r insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','5','  rec_newrest r_rest%rowty