CODEWITHSUNDEEP
sqloracle-database
sagi
sagi

Reputation: 40481

ORACLE - retrieve deleted data

I recently truncated a table by mistake in the wrong environment. It's a huge table saving history 5 years back.

I used TRUNCATE, so I can't undo / rollback.

I tried using flashback:

SELECT * FROM STG_SCD_SUB_ACCOUNTS_KEY AS OF TIMESTAMP 
 TO_TIMESTAMP('2016-03-09 09:30:00', 'YYYY-MM-DD HH:MI:SS')

But no luck, produced the error:

unable to read data - table definition has changed flashback

Any ideas?

Upvotes: 0

Views: 434

Answers (1)

Amit Naik
Amit Naik

Reputation: 1073

Since Oracle-10g and beyond Table level flashback feature is enabled by default. Hence the reason we can recover table/index from last DDL time.

For ORA-01466 : Unable to read data -- Table definition has changed
cause - the query was executed with a snapshot older than the time the object was changed.

since truncate is a DDL one can not be rolled back or recovered using flashback. Only options are to restore full backup or recover tablespace.

Upvotes: 2

Related Questions