[20200327]ORA-46267 Insufficient space in 'USERS' tablespace.txt

[20200327]ORA-46267 Insufficient space in 'USERS' tablespace, cannot complete operation.txt

--//今天想將一套生產系統sys.aud$表空間移出系統表空間遇到一些問題，做一個記錄：
--//上次做這個操作也遇到類似問題，連結：http://blog.itpub.net/267265/viewspace-2666690/

1.環境:
zzzzzz> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.操作記錄：
zzzzzz> NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;
Noaudit succeeded.
--//主要目的僅僅審計不成功的登入，取消成功的登入審計。

zzzzzz> 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使用者執行:
zzzzzz> 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;
/

*
ERROR at line 1:
ORA-46267: Insufficient space in 'USERS' tablespace, cannot complete operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1659
ORA-06512: at line 2

--//我感覺很奇怪實際users表空間定義如下：
CREATE TABLESPACE USERS DATAFILE
  '/u01/app/oracle/oradata/orcl/users01.dbf' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--//可以自動擴充套件沒有限制，視乎這樣的操作不會擴充套件表空間大小。

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf' RESIZE 100M;

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

zzzzzz> 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
--//OK!!

--//說明:
The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.

    DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.