Simple Steps to Enable Extended Auditing in Oracle
- Get link
- X
- Other Apps
| Enabling auditing in db | sqlplus / as sysdba |
| ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE; | |
| shutdown immediate | |
| startup | |
| table for audit trails | DBA_AUDIT_TRAIL |
| view the size of audit | select round(bytes/1024/1024/1024) from dba_segments where segment_name ='AUD$'; |
| see the current tablespace of audit table | select tablespace_name from dba_tables where table_name='AUD$'; |
| Audit notes | Master Note For Oracle Database Auditing (Doc ID 1299033.1) |
| To audit a specific schema | AUDIT ALL STATEMENTS BY <username> ; |
| Moving $AUD table from SYSTEM TS to a dedicated Table Space | If your AUD$ table is in SYSTEM and SYSTEM tablespace, Then it is advised to move the AUD$to a dedicated tablespace. |
| Use steps to move AUD$. | select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; |
| OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024 | |
| ------- ------------- ------------ ---------------- ---------------- | |
| SYS AUD$ TABLE SYSTEM 16 | |
| Use the dbms_audit_mgmt to move the tablespace. | BEGIN |
| DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, | |
| audit_trail_location_value => 'AUDIT_DATA'); | |
| END; | |
| / | |
| PL/SQL procedure successfully completed. | |
| check whether tablespace has been moved from system to AUDIT_DATA or not. | select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; |
| OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024 | |
| ------- ------------- ------------------ ------------------------------ --------------- | |
| SYS AUD$ TABLE AUDIT_DATA 16 | |
| REFERENCE: |
- Get link
- X
- Other Apps

(Y)
ReplyDelete