首页 > 代码库 > IT忍者神龟之 配额不足的解决方法ORA-01536: space quota exceeded for tablespace
IT忍者神龟之 配额不足的解决方法ORA-01536: space quota exceeded for tablespace
明显的表空间配额不足问题,检查oracle:
SQL> select * from dba_ts_quotas where username=‘USERNAME‘;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---
TBSLOG1 USERNAME 6835470336 -1 834408 -1 NO
这里只能查到用户USERNAME在表空间TBSLOG1上的配额是unlimited,并没有显示TBSLOG的配额。
更改配额:
SQL> alter user USERNAME quota unlimited on tbslog;
User altered.
SQL> select * from dba_ts_quotas where username=‘USERNAME‘;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---
TBSLOG USERNAME 6835470336 -1 834408 -1 NO
TBSLOG1 USERNAME 6835470336 -1 834408 -1 NO
这时看到了max_bytes=-1,说明已经将username在tbslog上的配额改成unlimited。检查tt的同步已经恢复正常,错误日志也不再报配额不足的错误。
11g sqlplus报错ORA-28002: the password will expire within 5 days 的解决方法
11g增加了登录时间的限制,在DEFAULT Profile里能查到,如下所示:
SQL> select * from dba_profiles where profile=‘DEFAULT‘ AND RESOURCE_NAME=‘PASSWORD_LIFE_TIME‘;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
LIMIT字段显示180天。
看一下10g的值:
SQL> select * from dba_profiles where profile=‘DEFAULT‘ AND RESOURCE_NAME=‘PASSWORD_LIFE_TIME‘;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
解决方法:
1、将LIMIT值改为UNLIMITED即可,修改马上生效,不需要重启数据库
SQL> alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
SQL> select * from dba_profiles where profile=‘DEFAULT‘ AND RESOURCE_NAME=‘PASSWORD_LIFE_TIME‘;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
2、已经报错的用户则必须修改一次密码,密码可以不变。
SQL> alter user user1 identified by user1;
User altered.
IT忍者神龟之 配额不足的解决方法ORA-01536: space quota exceeded for tablespace