Enable auditing on specific table:
AUDIT ALTER ON SCOTT.T1;Verify that the object is being audited:
SELECT OBJECT_NAME,OBJECT_TYPE,ALT FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER ='SCOTT' ORDER BY 1 ASC;Result:
|------------------------------------| |object_name | object_type | alt | |-------------|--------------|-------| |T1 | TABLE | S/S | |------------------------------------|Object is indeed being audited.
Make a change to the table:
ALTER TABLE SCOTT.T1 ADD (age number(2));Check that the change is captured in the audit-trail:
SELECT TIMESTAMP, OS_USERNAME, OBJ_NAME,USERHOST, SESSIONID, USERNAME,ACTION_NAME, RETURNCODE FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME = 'T1' AND OWNER = 'SCOTT' ORDER BY TIMESTAMP DESC; |-----------------------------------------------------------------------------------------------------------| |TIMESTAMP | OS_USERNAME | OBJ_NAME | USERHOST| SESSIONID | USERNAME | ACTION_NAME | RETURNCODE | |-----------------------------------------------------------------------------------------------------------| |03.11.2013 08:32:28 | SCOTT | T1 | scottpc | 172154 | SCOTT | ALTER TABLE | 0 | |03.11.2013 08:32:18 | SCOTT | T1 | scottpc | 172154 | SCOTT | ALTER TABLE | 1430 | |03.11.2013 08:32:18 | SCOTT | T1 | scottpc | 172154 | SCOTT | ALTER TABLE | 911 | |01.11.2013 16:14:17 | SCOTT | T1 | scottpc | 234766 | SCOTT | ALTER TABLE | 0 | |-----------------------------------------------------------------------------------------------------------|The return codes can be checked by using the oerr utility that comes with the oracle installed binaries, for example:
oerr ora 1430 01430, 00000, "column being added already exists in table" // *Cause: // *Action:
Note that other DDL statements, too, are captured in the audit trail for the table, such as DROP and TRUNCATE.
If you drop the table without purging the recyclebin, the auditing will still be in place, and if you query the DBA_OBJ_AUDIT_OPTS after a DROP without purge, it will show you the recyclebase name in the view.
Awesome Description with exact details and practical enough
ReplyDelete