FW: 12c 如何移動aud$ table 到特定tablespace

12c 如何移動aud$ table 到特定tablespace
主要是運用了DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION 這個套件來做調整

或是可參考我以前寫的文章
https://jaychu649.blogspot.tw/2012/02/oracle-11g-auditing-sysaud-disable-db.html


ref:
http://oracle-help.com/oracle-12c/oracle-12cr2/move-aud-table-another-tablespace-using-dbms_audit_mgmt/?lipi=urn%3Ali%3Apage%3Ad_flagship3_feed%3BSMvvrWj8Rxa5bKThzjrryg%3D%3D

Use steps to move AUD$.

1 2 3 4 5

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.

1 2 3 4 5 6 7

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.

1 2 3 4 5

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

Oracle檢測帳號是否仍使用(not expired)

col username for a25

col account_status for a8

col default_tablespace for a10

select username , account_status , created , default_tablespace from dba_users where account_status not like 'EXPIRED%';

#鎖定帳號

SQL> alter user TEST account lock;

#使密碼失效 , 若下次解鎖登入後 , 會要求更改新密碼

SQL> alter user TEST password expire;

#解開帳號

SQL> alter user TEST account unlock;



#List All User Roles with dba 權限
select granted_role , grantee from DBA_ROLE_PRIVS where grantee in (select username from dba_users)  and granted_role in ('DBA','SYSDBA') order by GRANTEE;