关于ORA-01536 报错的几种场景:
我们都知道unix 或linux下都可以限制某个用户使用磁盘空间,避免造成空间占用100%引发系统事故,
oracle也可以实现该功能,就是通过指定用户对某些表空间的配额quota来实现的。一旦超过配额上限,
就会报ORA-01536,下面的几个场景就来介绍一下。
场景1:用户quota不足
sqlplus / as sysdba
drop user test cascade;
create user test identified by abcdefg;
alter user test quota 1m on users ;
grant create session to test;
grant create table to test;
查看配额情况:
select username, tablespace_name,bytes, max_bytes from dba_ts_quotas where username = 'TEST';
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
------------------------------------------------------------
TEST USERS 1048576 1048576 <<1M
插入数据:
create table test.test_t as select * from dba_objects ;
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
上述结果可以看到,因为我们只给了test用户使用users表空间1m的配额,
所以插入大量数据就会报错,因为错误明显,检查dba_ts_quotas就可以解决。
场景2:递归cursor
sqlplus / as sysdba
grant dba to maob;
SQL> select username, tablespace_name,bytes, max_bytes from dba_ts_quotas where username = 'MAOB';
no rows selected
上述可以看到这个视图只能查询到显式赋予配额的用户和大小,我们给maob用户赋予dba之后,在配额表里面是没有任何记录的,这是因为dba的role在起作用,
SQL> select * from session_privs WHERE PRIVILEGE LIKE '%UNLIMIT%' ;
PRIVILEGE
--------------------------------------------------------------------------------
UNLIMITED TABLESPACE
查看当前session 的确是有UNLIMITED的quota的权限的
sqlplus maob/abcdefg
create table maob.test_t2 as select * from dba_objects where 1=2;
create table test.test_t2 as select * from dba_objects where 1=2;
grant all on test.test_t2 to public ;
CREATE OR REPLACE TRIGGER maob.test_trg
BEFORE INSERT OR DELETE OR UPDATE
ON maob.test_t2 FOR EACH ROW
BEGIN
INSERT INTO test.test_t2(object_id,object_name) values(:new.object_id,:new.object_name);
END;
/
SQL> insert into maob.test_T2 select * from dba_objects;
insert into maob.test_T2 select * from dba_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at "MAOB.TEST_TRG", line 3
ORA-04088: error during execution of trigger 'MAOB.TEST_TRG'
上述可以看到,虽然maob用户是对任何表空间不限制quota的,但是因为trigger的原因,
导致操作了test用户下的表,而test用户是有quota限制的,所以会报错,不过这个根据报错
信息trigger也很容易定位原因。
场景3:错误的使用索引,在其他用户下创建索引
sqlplus maob/abcdefg
create table test_t3 as select * from dba_objects where 1=2;
==在用户test下的索引却指向本用户的表
sqlplus test/abcdefg
create index test.i3 on maob.test_t3( object_name,owner,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED ) tablespace users;
sql>insert into maob.test_T3 select * from dba_objects;
SQL> SQL> insert into maob.test_T3 select * from dba_objects;
insert into maob.test_T3 select * from dba_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
这个报错就会让人很奇怪,因为通常情况下在生产系统上类似test_t3业务表,owner是不会被限制quota的,而且
dba通常进一步检查dba_ts_quotas和系统权限就能很快排除可能性,但是却很容易忽略对象上的索引造成问题,
因为的确很少有人在B用户下建索引却指向A用户的表,往往都是有人错误操作造成的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25462274/viewspace-2141798/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25462274/viewspace-2141798/