To find out the User name or ID who updated the address of the other staff

Question

This is my first post on this forum and hope I will get an answer.

I have very limited info with me about my database.

The query is like:

I wanted to know who has updated the address of the other staff, surely it is updated from the Java based application, but I came to know that in my database I have audit schema and in that I can find out the user name who updated the address.

But I don't know in which table this information will be available as we have around 1000+ tables in my database.

Could you please assist me to find out the exact table where this info will be available.

Answer 1

Aija, this is a difficult question to answer as there are so many possibilities, however we maybe able to help you narrow it down. Tables like this often start with audit, history, change, etc. or the reverse and have that appended to the end of the file they are tracking. E.g. audit_personnel or personnel_change. You say you have 1,000+ tables. That is a lot, but I have worked with bigger. It is still feasible to go through the list by the name of the table one by one. When databases get this big, naming standards come into play. Have a look at the way the table names are put together, and you will be able to narrow down your search a lot.

Answer 2

Another option then is the single table audit control. In this style, the table has 4 major components. First the data being changed which will be something like the table and field, maybe recid. Second is the original data. Third is the new data. Fourth is the who and when of the change. If this is the style, then you will need to know which table it is that you want to track. Then you will need something like "select * from [audit table] where [audit table].[monitored table] = [target table]".

Answer 3

Thanks for your input i have gone throgh all the tables by name starting/ending with name audit .i found one table audit trail in that there are multiple tables but i could not able to find the info which is expected. even iam not sure these tables are coming under my prebvillage or its in under sys or any other user.