Recover Lost or Overwritten Code on Oracle Autonomous Database

If you are reading this, I presume you’ve just lost some code. Take a reading of you smartwatch heart rate and post it in the comments later.

This blog is aimed at Autonomous Database; for non-Autonomous Database, click here.

If you don’t have time for a preamble - click to go to the fix

You may have seen other links that talk about this

SELECT line, text
FROM ALL_SOURCE AS OF TIMESTAMP 
     TO_TIMESTAMP('2024-11-25 13:22:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE name = 'MY_CODE'

This is going to give you this

ORA-41900: missing FLASHBACK privilege on "SYS"."ALL_SOURCE"

https://docs.oracle.com/error-help/db/ora-41900/
41900. 00000 - "missing %s privilege on \"%s\".\"%s\""
*Cause: An attempt was made to operate on an object for which the user did
not have the required privilege.
*Action: Perform the action after acquiring the required privileges.
*Params: 1) privilege
2) owner_name: The name of the owner.
3) object_name: The name of the object.
Error at Line: 1 Column: -4

You may have seen solutions that select from sys.source$ or dba_source. This is not going to work either

What doesn’t work on Autonomous Database

• Selecting from all_source as AS OF TIMESTAMP

• Selecting from sys.source$ as AS OF TIMESTAMP

• Selecting from dba_source as AS OF TIMESTAMP

• All combinations of the above with/without sys owner prefix

• Changing the sys password and logging on to to a version of the above - TBH, I didn’t try this; and actually, do not try this, its not a good idea.

• GRANT FLASHBACK ANY TABLE TO ADMIN and then trying a SQL above

• GRANT FLASHBACK ON ALL_SOURCE TO ADMIN or any other user or another table

What does work on Autonomous Database

• OPTION 1: Restore to a Database Backup - Paid ADB only. Always-Free backs up, but does the restore is not available

• OPTION 2: The DBMS_FLASHBACK.ENABLE_AT_TIME procedure in Oracle is used to enable Flashback operations for a specific time, allowing you to view or query the state of the database as it was at a particular point in time.

  1. Run this to set your session to the approximate time of the loss - in my case, 2 hours ago

      EXEC DBMS_FLASHBACK.enable_at_time(SYSTIMESTAMP - INTERVAL '2' HOUR);
     

  2. Optional; if running this in SQLcl, set these parameters

      SET PAGESIZE 5000
      SET TRIMSPOOL ON
      SET SQLFORMAT ANSICONSOLE
      SET TERMOUT OFF
      SET LINESIZE 200
     

  3. Run this to extract your lost code - adjust MY_CODE to the name of your package, procedure, etc

      SELECT text
      FROM all_source
      WHERE name = 'MY_CODE'
      ORDER BY line
     

  4. Run this to put your session back to the present moment.

      EXEC DBMS_FLASHBACK.DISABLE;
     

Recovered your code? fantastic, now take another heart rate test. How does it compare to before? let me know in the comments.

Credits: Thanks to Artur Morawski for his input

ENJOY!

Whats the picture? The Stray Cherry Blossoms in Harrogate during November. Here is what it looks like in Spring. Visit Yorkshire!