檢視資料庫錯誤的原始碼

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;