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!