在11.2.0.2的环境中,碰到了这个问题,使用REVOKE UNLIMITED TABLESPACE命令后,发现设置的用户QUOTA也消失了。
这是一个实际的案例,在客户的环境中新建了一个用户,准备导入一些数据,结果碰到了问题:
SQL> create user thams account unlock identified by thams default tablespace LOB_AU2M quota unlimited on LOB_AU2M;
User created.
SQL> grant connect,resource to thams;
Grant succeeded.
SQL> grant create view,create synonym,create database link to thams;
Grant succeeded.
SQL> revoke unlimited tablespace from thams;
Revoke succeeded.
SQL> grant read,write on directory expdir to thams;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 -
64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@dbserver1 oracle-export]$ impdp thams/thams
dumpfile=AMS-ORA9_20110815_2.DP logfile=AMS-ORA9_20110815_2_imp.log
directory=expdir remap_tablespace=THAMS:LOB_AU2M
remap_tablespace=users:lob_au2m
Import: Release 11.2.0.2.0 - Production on Tue Aug 16 12:36:41 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g
Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39006: internal error
ORA-39068: invalid master table data in row with PROCESS_ORDER=-4
ORA-01536: space quota exceeded for tablespace 'LOB_AU2M'
ORA-39097: Data Pump job encountered unexpected error -1536
刚看到这个错误的时候十分疑惑,明明在创建用户的时候指定了LOB_AU2M表空间上的无限制的QUOTA,怎会仍然会出现这个错误呢。
但是检查数据库,确实没有发现无限QUOTA的存在:
[oracle@dbserver1 oracle-export]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 16 12:38:29 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> set pages 100 lines 120
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/196700/viewspace-705219/,如需转载,请注明出处,否则将追究法律责任。