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