ITPub博客

首页 > 数据库 > Oracle > [20200327]ORA-46267 Insufficient space in 'USERS' tablespace.txt

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

原创 Oracle 作者:lfree 时间:2020-03-27 10:35:11 0 删除 编辑

[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.



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2682836/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2669
  • 访问量
    6427906