Monday, August 29, 2011

Travel Back In Time - Retrieve Dropped Procedures With Flashback Query

Each procedure is stored in DBA_SOURCE table. Assume that you dropped accidentally the procedure and you don't have the code to retrieve it back. FLASHBACK QUERY can save your life.

CREATE OR REPLACE PROCEDURE EG_TEST()
AS
BEGIN
 FOR NUMB IN 1..1000
  LOOP
   DBMS_OUTPUT.put_line(NUMB);      
  END LOOP;
END;

DROP PROCEDURE EG_TEST;

CONNECT / AS SYSDBA;

SELECT     TEXT
FROM       DBA_SOURCE
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE
WHERE      NAME = 'EG_TEST'
ORDER BY   LINE

TEXT
-----------------------------------------------------------------
CREATE OR REPLACE PROCEDURE EG_TEST()
AS
BEGIN
  FOR NUMB IN 1..1000
  LOOP
     DBMS_OUTPUT.put_line(NUMB);      
  END LOOP;
END;

Just copy and paste to a text file and you have just traveled back in time.

No comments:

Post a Comment