Home » SQL & PL/SQL » SQL & PL/SQL » Table Audit - INSERT with Particular Value on Past date
Table Audit - INSERT with Particular Value on Past date [message #202341] Thu, 09 November 2006 04:00 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Hi,

I have got 2 tables

TabA (S number, t number,r number)
TabA_history (S number, t number)

Now TabA_history table is populated with data every other week based on TabA,

now how can i find out the insert statement , which actually insert data into TabA_histroy where S = 1122 ????

TabA_history is based on TabA

TabA.S = 1122 then TabA.t = 999
TabA_history.S = 1122 then TabA_history.t = 999 (expected) becoz its history table of A.

But somehow different TabA_history.t value is inserted for TabA_history.S = 1122, and there is no document so i cant find out which script is inserting data .

I have tried to search INSERT statement using v$sql and sql_text but didnt get any success.

Any other way to find out which script or Insert statement inserted into TabA_history table with TabA_history.S = 1122

Thanks
Re: Table Audit - INSERT with Particular Value on Past date [message #202348 is a reply to message #202341] Thu, 09 November 2006 04:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The query will only be in v$sql until the session ends, (unless someone else reuses the cursor) so that's not likely to help.

If you've looked in DBA_SOURCE for the script and not found it then it must (presumably) be an SQL Script run by a process outside the database.

You'll need to have a look at what jobs are scheduled to run from the OS.

Bear in mind, that this could be done from any computer that can connect to your database.....
Re: Table Audit - INSERT with Particular Value on Past date [message #202351 is a reply to message #202348] Thu, 09 November 2006 04:32 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
i thought , oracle would record every transaction, and we could get detail of each transaction, but from your reply, its not the case.

Thanks
Re: Table Audit - INSERT with Particular Value on Past date [message #202354 is a reply to message #202351] Thu, 09 November 2006 04:48 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It records all the changes made to the data in the Redo logs, but unless you're set up to use LogMiner, then that isn't going to be a great deal of use to you.

Oracle does not record (other than as transient data in tables like v$sql) the DML performed on the database.
Previous Topic: subset
Next Topic: Cursor advice
Goto Forum:
  


Current Time: Sat Dec 10 07:10:24 CST 2016

Total time taken to generate the page: 0.08030 seconds