ORA-01536: space quota exceeded for tablespace
当Oracle数据库用户的权限不是dba时,对其默认表空间的“限额”可能为无,这时需要把限额改为“无”即可。
今天在插入数据的时候碰到了ORA-01536,这个是因为一个用户在使用表空间时候受到了权限的限制.修改这个用户的使用这个表空间的空间的权限就可以了.
方法一
ALTER USER <username> QUOTA <integer> [K/M] ON <tablespacename>
方法二
ALTER USER <username> QUOTA UNLIMITED ON <tablespacename>
查找原因
select username, tablespace_name, bytes, max_bytes
from dba_ts_quotas
where username = '<the_user_getting_the_error>'
and tablespace_name = '<the_tablespace_the_error_is_occuring_on>';
O:\>sqlplus greatfinish/finish@ora8
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 5月 31 19:32:25 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
greatfinish@ORA8> insert into NEWSDAT.T_ART_IMAGES select * from NEWSDAT.T_ART_IMAGES@qa.world;
insert into NEWSDAT.T_ART_IMAGES select * from NEWSDAT.T_ART_IMAGES@qa.world
*
ERROR 位于第 1 行:
ORA-01536: space quota exceeded for tablespace 'RSMOTR'
greatfinish@ORA8> insert into NEWSDAT.T_ARTICLES select * from NEWSDAT.T_ARTICLES@qa.world;
insert into NEWSDAT.T_ARTICLES select * from NEWSDAT.T_ARTICLES@qa.world
*
ERROR 位于第 1 行:
ORA-01536: space quota exceeded for tablespace 'RSMOTR'
greatfinish@ORA8> alter user NEWSDAT quota unlimited on RSMOTR;
用户已更改。
greatfinish@ORA8> insert into NEWSDAT.T_ART_IMAGES select * from NEWSDAT.T_ART_IMAGES@qa.world;
已创建1296行。
greatfinish@ORA8> insert into NEWSDAT.T_ARTICLES select * from NEWSDAT.T_ARTICLES@qa.world;
已创建3246行。
greatfinish@ORA8> commit;
提交完成。
greatfinish@ORA8>