Oracle表空間切換路徑，解決硬碟滿導致的ORA-01653問題

問題

ORA-1653: unable to extend table CAMS_CORE.BP_E_JOURNAL_DATA by 128 in                 tablespace CAMS_CORE_TAB
ORA-1653: unable to extend table CAMS_CORE.BP_E_JOURNAL_DATA by 1024 in                 tablespace CAMS_CORE_TAB

解決方案

檢視錶空間檔案大小及路徑
select * from dba_data_files;
將部分表空間offline
alter tablespace CAMS_CORE_TAB offline;
alter tablespace CAMS_CORE_IDX offline;
alter tablespace CAMS_CORE_LOB offline;
建立臨時存放表空間檔案的路徑
mkdir -p /app/oracle/tablespace/
mv /home/oracle/cams/tablespace/CAMS_CORE_*.dbf /app/oracle/tablespace/
修改表空間的路徑
alter tablespace CAMS_CORE_IDX rename datafile '/home/oracle/cams/tablespace/CAMS_CORE_IDX_001.dbf' to '/app/oracle/tablespace/CAMS_CORE_IDX_001.dbf';
alter tablespace CAMS_CORE_LOB rename datafile '/home/oracle/cams/tablespace/CAMS_CORE_LOB_001.dbf' to '/app/oracle/tablespace/CAMS_CORE_LOB_001.dbf';
alter tablespace CAMS_CORE_TAB rename datafile '/home/oracle/cams/tablespace/CAMS_CORE_TAB_001.dbf' to '/app/oracle/tablespace/CAMS_CORE_TAB_001.dbf';
將相關表空間online
alter tablespace CAMS_CORE_TAB online;
alter tablespace CAMS_CORE_IDX online;
alter tablespace CAMS_CORE_LOB online;
檢視錶空間最新路徑
select * from dba_data_files;

結果圖片 **

經過驗證，應用程式恢復正常，資料庫恢復正常！