【錯誤提示】
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下一個存儲過程。
【解決問題】
調出存儲過程，修改執行。