how to know which columns are updated in a table in Oracle 11g with out using triggers and auditing

Question

Table name is employees

ID NAME SALARY  

1 K1 2000  
2 K2 3000  
3 K3 4000

Now,i updated the 2nd row( 3000 with 9000)

ID NAME SALARY

1 K1 2000  
2 K2 9000  
3 K3 4000

Now,i want oracle command to know which columns are updated,with out using triggers.
For Example: if i give table name " employees " then the command should say " SALARY " column is updated.
If i give column name " SALARY " then it must say the Column " SALARY " is changed. If i give column name as " NAME " then it must say column " Name " is not changed.Thank you friends.

Answer 1

You could use Fine grained auditing. DBMS_FGA is an in-built package provided by Oracle to add policies. See documentation for more insight.

For example,

SELECT * FROM emp WHERE empno = 9998;
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1);
UPDATE emp SET sal = 10 WHERE empno = 9998;
DELETE emp WHERE empno = 9998;
ROLLBACK;

-- Check the audit trail.

CONN sys/password AS SYSDBA
SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------
SELECT * FROM emp WHERE empno = 9998
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)
UPDATE emp SET sal = 10 WHERE empno = 9998
DELETE emp WHERE empno = 9998

4 rows selected.

This is just an example, where all DML actions were audited. There are other audit options:

• DDL (CREATE, ALTER & DROP of objects)
• DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
• SYSTEM EVENTS (LOGON, LOGOFF etc.)

Have a look at this article by Tim Hall.