[20191203]enq: ZA - add std audit table partition.txt

[20191203]enq: ZA - add std audit table partition.txt

--//上午想將審計表移動別的表空間遇到問題.有點奇怪:

1.環境:
SYS@192.168.xx.zzz:1521/orcl> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
IBMPC/WIN_NT64-9.1.0           11.2.0.1.0     Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SYS@192.168.xx.zzz:1521/orcl> @ tpt/ash/ash_wait_chains event2 1=1 trunc(sysdate)+9/24  trunc(sysdate)+10/24
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This  SECONDS AAS WAIT_CHAIN
------ ------- --- -----------------------------------------------------------------------------------------------------------------------
  88%      896  .2 -> ON CPU
   3%       35   0 -> db file sequential read
   2%       24   0 -> enq: ZA - add std audit table partition  -> log file switch (checkpoint incomplete)
   1%       12   0 -> control file sequential read
   1%       11   0 -> control file parallel write
   1%        6   0 -> enq: ZA - add std audit table partition  -> log file switch (checkpoint incomplete)  -> control file sequential read
   1%        6   0 -> log file switch (checkpoint incomplete)
   1%        6   0 -> enq: ZA - add std audit table partition  -> ON CPU
   0%        5   0 -> log file parallel write
   0%        5   0 -> db file parallel write
   0%        3   0 -> log file sync  -> log file parallel write
   0%        3   0 -> log file sync
   0%        2   0 -> enq: ZA - add std audit table partition
   0%        2   0 -> log file switch (checkpoint incomplete)  -> control file sequential read
   0%        1   0 -> log file sync  -> ON CPU
   0%        1   0 -> db file scattered read
16 rows selected.
--//出現enq: ZA - add std audit table partition.

SYS@192.168.xx.zzz:1521/orcl> @ enq "ZA"
EQ_NAME         EQ REQ_REASON                    TOTAL_REQ#  TOTAL_WAIT#    SUCC_REQ#  FAILED_REQ# CUM_WAIT_TIME REQ_DESCRIPTION                               EVENT#
--------------- -- ----------------------------- ---------- ------------ ------------ ------------ ------------- --------------------------------------------- ------
Audit Partition ZA add std audit table partition         96            5           96            0         39265 lock held to add partition to std audit table    809

2.我的操作過程記錄如下:

SYS@192.168.xx.zzz:1521/orcl> SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
---------- ---------------
AUD$       SYSTEM
FGA_LOG$   SYSTEM

--//以sys使用者執行:
SYS@192.168.xx.zzz:1521/orcl> ALTER USER SYS QUOTA UNLIMITED ON users;
User altered.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => 'users');
END;
/

--//遇到如下錯誤:
BEGIN
*
ERROR at line 1:
ORA-46267: Insufficient space in 'USERS' tablespace, cannot complete operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1585
ORA-06512: at line 2

--//不知道為什麼?我已經給SYS使用者無限使用users表空間的許可權.我執行如下:
alter database datafile 'f:\oradata\orcl\users01.dbf' resize 301m;

--//然後再執行前面的語句ok.

SYS@192.168.xx.zzz:1521/orcl> SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
---------- ------------------------------
AUD$       USERS
FGA_LOG$   USERS

--//不知道為什麼要先要滿足表空間能容納sys.aud$才可以操作,否者不行.
--//另外個人建議安裝好oracle馬上關閉執行如下,關閉成功登入審計.有一些應用系統登入會暴漲.
NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;

--//如果再想系統,system表空間可能無法在回縮了.