Can I get the row level information from a table in the database if I go to Audit Trail?
The Audit Trail that Oracle supplies is limited in the type of information that you can get from it. Basically, you can find out who modified a table, but it won't tell you the particulars. In other words, you may find that the user SYSTEM issued an update against the SCOTT.EMP table. But you won't find that SYSTEM updated to change all manager's salary to zero.
Before Oracle 9i, the DBA had to set up a trigger on the table in question to log this information into a completely different table. Alternatively, the DBA could mine the archived redo logs with Log Miner to capture the change information for each row of the table.
Oracle 9i introduced a capability called Fine Grained Auditing (FGA) which does allow this capability. Now, a DBA can set up row-level auditing on a particular table.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Learn how to put row-level security to work in Azure SQL databases
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.