[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表空间可能无法在回缩了.