遭遇 ORA-03232: unable to allocate an extent of 40 blocks from tablespace 5

對Oracle database 進行 exp imp操作的時候，跑到最後發現報錯了

…

MP-00017: following statement failed with ORACLE error 604:
"ALTER TABLE "abc_USER_AUTOPAY_PLAN" ENABLE CONSTRAINT "FK_AUTOP_PLAN_abc""
IMP-00003: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-03232: unable to allocate an extent of 40 blocks from tablespace 5

…

解決步驟：

1 tablespace 5是什麼

SQL> select * from v$tablespace;
       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 TOOLS
         2 OEM_REPOSITORY
         3 RBS
         4 TEMP
        5 APP_TEMP
         6 USERS
         7 ABC_DATA
         8 INDX
         9 ABC_INDX
        10 DRSYS
11 rows selected.

增大 tablespace size 無效

alter database datafile '/u04/oradata/crprod/apptemp01.dbf' resize 2000m;

google一下發現可能是 dba_tablespace的 next_extent的問題

SQL> select next_extent from dba_tablespaces where tablespace_name = 'APP_TEMP';
NEXT_EXTENT
-----------
      65536

 

SQL> alter tablespace app_temp default storage (next 1m);
Tablespace altered.
SQL> select next_extent from dba_tablespaces where tablespace_name = 'APP_TEMP';
NEXT_EXTENT
-----------
    1048576

手工運行-成功：

SQL> ALTER TABLE ABC_USER_AUTOPAY_PLAN ENABLE CONSTRAINT FK_AUTOP_PLAN_UPROF_ABC;
Table altered.

記錄一下備忘！