IT忍者神龜之 配額不足的解決方案ORA-01536: space quota exceeded for tablespace,tablespace

來源:互聯網
上載者:User

IT忍者神龜之 配額不足的解決方案ORA-01536: space quota exceeded for tablespace,tablespace
今天有同事反映最近幾天的資料在oracle中查不到。檢查TT的錯誤記錄檔顯示:TT5211: TT5211: Oracle out of resource error in OCIStmtExecute(): ORA-01536: space quota exceeded for tablespace 'TBSLOG' rc = -1 -- file "bdbTblH.c", lineno 2452, procedure "ttBDbStmtForce()"。
明顯的資料表空間配額不足問題,檢查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.




相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.