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