ORA-12012 ORA-12008 ORA-01555

【錯誤提示】

Errors in file /u/oracle/product/diag/rdbms/wsj/wsj/trace/wsj_j001_23086.trc:
ORA-12012: 自動執行工作124時發生錯                                                                         ORA-12008: 具體化視觀表重新整理路徑發生錯誤
ORA-01555: 瞬間拷貝太久: ?到回區段編號3 與名稱 "_SYSSMU3$" 過小
ORA-02063: ? line ぇ玡, ? L_HRM
ORA-06512: ? "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: ? "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: ? "SYS.DBMS_IREFRESH", line 685
ORA-06512: ? "SYS.DBMS_REFRESH", line 195
ORA-06512: ? line 1
Mon Dec 26 18:54:02 2011
SMON: Parallel transaction recovery tried
Mon Dec 26 18:55:51 2011
Thread 1 cannot allocate new log, sequence 5843

【問題分析】

SQL> select * from dba_objects t where t.object_type='MATERIALIZED VIEW';
 
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
SYSMAN                         MGMT_ECM_MD_ALL_TBL_COLUMNS                                                                                          67367                MATERIALIZED VIEW   2009/8/15 上 2009/8/15 上午  2009-08-15:00:40:30 VALID   N         N         N                 19
INOUT                          MV_BAS_DEPT                                                                                                         100899                MATERIALIZED VIEW   2011/12/20  2011/12/20 下午 2011-12-20:13:08:42 VALID   N         N         N                 19
INOUT                          MV_ECPY_FILE                                                                                                         78309                MATERIALIZED VIEW   2011/5/26 上 2011/5/26 上午  2011-05-26:08:50:43 VALID   N         N         N                 19
INOUT                          MV_ECPD_FILE                                                                                                         78314                MATERIALIZED VIEW   2011/5/26 上 2011/5/26 上午  2011-05-26:08:55:48 VALID   N         N         N                 19
INOUT                          MV_BAS_EMP                                                                                                           76866                MATERIALIZED VIEW   2011/5/12 上 2011/5/12 上午  2011-05-12:10:45:26 VALID   N         N         N                 19
INOUT                          MV_BAS_BU                                                                                                            76876                MATERIALIZED VIEW   2011/5/12 上 2011/5/12 上午  2011-05-12:10:50:14 VALID   N         N         N                 19
INOUT                          MV_ATT_RECORD                                                                                                        81426                MATERIALIZED VIEW   2011/6/24 上 2011/6/24 上午  2011-06-24:08:38:35 VALID   N         N         N                 19
INOUT                          MV_BAS_EMPLOYEE                                                                                                     101612                MATERIALIZED VIEW   2011/12/27  2011/12/27 下午 2011-12-27:13:53:49 VALID   N         N         N                 19
 
8 rows selected
 
SQL> select * from dba_jobs t where t.BROKEN='Y';
 
       JOB LOG_USER                       PRIV_USER                      SCHEMA_USER                    LAST_DATE   LAST_SEC         THIS_DATE   THIS_SEC         NEXT_DATE   NEXT_SEC         TOTAL_TIME BROKEN INTERVAL                                                                           FAILURES WHAT                                                                             NLS_ENV                                                                          MISC_ENV                                                           INSTANCE
---------- ------------------------------ ------------------------------ ------------------------------ ----------- ---------------- ----------- ---------------- ----------- ---------------- ---------- ------ -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ----------
         3 INOUT                          INOUT                          INOUT                          2011/8/15 上 08:29:02                                      2011/8/24 上 07:14:52               5841 Y      sysdate+1                                                                                16 PRO_EDATA_TOTAL;                                                                 NLS_LANGUAGE='TRADITIONAL CHINESE' NLS_TERRITORY='TAIWAN' NLS_CURRENCY='NT$' NLS 0102000200000000                                                          0
 
可以看出這個PRO_EDATA_TOTAL任務8月15日執行報錯就一直沒有成功。

SQL> select * from dba_objects t where t.OBJECT_name='PRO_EDATA_TOTAL';
 
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
INOUT                          PRO_EDATA_TOTAL                                                                                                      76035                PROCEDURE           2011/5/4 下午 2011/12/27 下午 2011-12-27:14:54:39 VALID   N         N         N                  1
可以看出PRO_EDATA_TOTAL為INOUT下一個存儲過程。

【解決問題】

調出存儲過程，修改執行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16381228/viewspace-714006/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16381228/viewspace-714006/