Here is the correct and simplest way to restore an Oracle PACKAGE / PACKAGE BODY using Flashback features.
Oracle does not flashback individual objects directly — but you can restore them with the following supported methods.
Restore Package from Oracle FLASHBACK QUERY (Best & Easiest)
If the package existed earlier and you want the old version:
Step 1 — Query old source using flashback timestamp
SELECT text
FROM dba_source AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR)
WHERE name = '<PACKAGE_NAME>'
AND type = 'PACKAGE'
ORDER BY line;
For package body:
SELECT text
FROM dba_source AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR)
WHERE name = '<PACKAGE_NAME>'
AND type = 'PACKAGE BODY'
ORDER BY line;
Step 2 — Copy the output & recreate
CREATE OR REPLACE PACKAGE package_name AS
...
END;
/
CREATE OR REPLACE PACKAGE BODY package_name AS
...
END;
/
✔ Works if the package existed in the past and UNDO retention still keeps its source.
✔ No DBA involvement needed.
Tags:
Oracle