資料庫錯誤

Q: 錯誤訊息如下

  2007-07-31 08:27:37 - Error running query: ORA-01653: unable to extend table BB_BB60.ACTIVITY_ACCUMULATOR by 16 in tablespace BB_BB60_DATA

ORA-06512: at "BB_BB60.ACTIVITY_ACCUMULATOR_CR", line 53 ORA-06512: at line 1 - blackboard.persist.PersistenceException: Error running query: ORA-01653: unable to extend table BB_BB60.ACTIVITY_ACCUMULATOR by 16 in tablespace BB_BB60_DATA ORA-06512: at "BB_BB60.ACTIVITY_ACCUMULATOR_CR", line 53 ORA-06512: at line 1

at blackboard.persist.BbPersistenceManager.runDbQuery(BbPersistenceManager.java:543) at blackboard.platform.tracking.TrackingManagerTask.run(TrackingManagerTask.java:136) at blackboard.platform.impl.services.task.TaskManagerService$TimerTaskProxy.run(TaskManagerService.java:312) at java.util.TimerThread.mainLoop(Timer.java:512) at java.util.TimerThread.run(Timer.java:462) Root Cause: java.sql.SQLException: ORA-01653: unable to extend table BB_BB60.ACTIVITY_ACCUMULATOR by 16 in tablespace BB_BB60_DATA ORA-06512: at "BB_BB60.ACTIVITY_ACCUMULATOR_CR", line 53 ORA-06512: at line 1

A: 是因為 Oracle 的 bb_bb60_data_file 已經到達 2G 了，需要新增加一個 Data file ，處理的方式如下：

set linesize 120 SQL> SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB, ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL FROM ( SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB, 0 TOTAL_MB, 0 MAX_MB FROM dba_free_space GROUP BY tablespace_name UNION SELECT tablespace_name, 0 CURRENT_MB, SUM(bytes)/1024/1024 TOTAL_MB, SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB FROM dba_data_files GROUP BY tablespace_name) GROUP BY tablespace_name;

TABLESPACE_NAME CUR_USE_MB CUR_SZ_MB CUR_PCT_FULL FREE_SPACE_MB MAX_SZ_MB PCT_FULL

---

---------- ---------- ------------ ------------- ---------- ----------

SYSTEM 499 500 100 32269 32768 2 USERS 0 5 9 32768 32768 0 BB_BB60_INDX 1702 1702 100 298 2000 85 BB_BB60_DATA 2000 2000 100 0 2000 100 BBADMIN_INDX 2 20 11 1998 2000 0 BB_BB60_STATS_INDX 865 865 100 1135 2000 43 SYSAUX 398 420 95 32370 32768 1 BBADMIN_DATA 2 50 4 1998 2000 0 BB_BB60_STATS_DATA 637 637 100 1363 2000 32 UNDOTBS1 489 3855 13 32279 32768 1

SQL> SELECT file_name, tablespace_name, ROUND(bytes/1045876) MB FROM dba_data_files ORDER BY file_name;

FILE_NAME TABLESPACE_NAME MB

---

-------------------- ----------

/export/home/oracle/oradata/bb71/bb_bb60_data_file1.dbf BB_BB60_DATA 2005 /export/home/oracle/oradata/bb71/bb_bb60_indx_file1.dbf BB_BB60_INDX 1707 /export/home/oracle/oradata/bb71/bb_bb60_stats_data_file1.dbf BB_BB60_STATS_DATA 639 /export/home/oracle/oradata/bb71/bb_bb60_stats_indx_file1.dbf BB_BB60_STATS_INDX 867 /export/home/oracle/oradata/bb71/bbadmin_data_file1.dbf BBADMIN_DATA 50 /export/home/oracle/oradata/bb71/bbadmin_indx_file1.dbf BBADMIN_INDX 20 /export/home/oracle/oradata/bb71/sysaux01.dbf SYSAUX 421 /export/home/oracle/oradata/bb71/system01.dbf SYSTEM 501 /export/home/oracle/oradata/bb71/undotbs01.dbf UNDOTBS1 3865 /export/home/oracle/oradata/bb71/users01.dbf USERS 5

SQL> alter tablespace bb_bb60_data add datafile '/export/home/oracle/oradata/bb71/bb_bb60_data_file2.dbf' size 2000M;