CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Freakyuser
Freakyuser

Reputation: 2814

history of a specific table in oracle

Can we find the history of values, updates, deletes or inserts of a specific table?

I don't have the access to create a stored procedure. Can you please provide me a query in the answer or some other way to find the history?

Upvotes: 4

Views: 30523

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

It is possible if FLASHBACK has been enabled over the schema or table. Most critical tables could have that enabled. Please check with DBA on this. If you have DBA access, then select the table name in SQL Developer , press Shift+F4 and move to Flashback tab to find details.

if enabled, you can use the below query ( just a sample)

SELECT * FROM employee AS OF TIMESTAMP 
   TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE name = 'JOHN';

if not enabled, you may have to write TRIGGERS for evry DML over that table. I agree the history of data before TRIGGERS are gone for ever, unless DBA is able to do some magic with redo logs..!

Upvotes: 3

Mark J. Bobak
Mark J. Bobak

Reputation: 14403

Two possibilities come to mind.

1.) If you have auditing enabled, you are all set. But, I'm guessing if that was the case, you wouldn't be asking the question. If you think this request will come up again, you should investigate setting up auditing for future requests.

2.) If auditing isn't set up, there's LogMiner, which allows you to examine the contents of the archived and online redo logs. This is probably your only solution, if you need the details of inserts, updates, deletes to a specific table.

Hope that helps.

Upvotes: 6

Siva Tumma
Siva Tumma

Reputation: 1701

Is this what you are looking for ? http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2103.htm

Upvotes: 0

Related Questions