資料庫錯誤 - 修訂歷史

Center17 於 2008年5月13日 (二) 03:20

2008-05-13T03:20:53Z

Center17 於 2008年5月13日 (二) 03:19

2008-05-13T03:19:11Z

Center17 於 2008年5月13日 (二) 03:17

2008-05-13T03:17:33Z

Center17：新頁面: 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....

2008-05-13T03:16:53Z

新頁面: 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....

新頁面

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;

@@ 行 9： / 行 9： @@
 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
@@ 行 18： / 行 19： @@
 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 ，處理的方式如下：
@@ 行 31： / 行 43： @@
 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,
 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;

@@ 行 1： / 行 1： @@
 Q: 錯誤訊息如下
--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