ORA-01555 caused by SQL

alert报错如下：

Thu Dec 31 14:36:38 2015

ORA-01555 caused by SQL statement below (SQL ID: 51hmywh9jz8qg, Query Duration=34461 sec, SCN: 0x0000.26f53fdc):

整理后：

SQL> select owner,OBJECT_NAME from dba_objects where object_name like 'AGENT_WORKING_RATE_TEMP2';OWNER       OBJECT_NAME------------------------------ ------------------------------STAT_HOLLYCRMAPP       AGENT_WORKING_RATE_TEMP2STAT_TEST       AGENT_WORKING_RATE_TEMP2SQL> SQL> SQL> SQL> explain plan for      INSERT INTO STAT_HOLLYCRMAPP.AGENT_WORKING_RATE_TEMP2       (ROW_DATE, LOGID, ACDRING_TIME, ACDIVR_TIME, ACDTALK_TIME, CALLTALK_SUM)  SELECT SUBSTR(T.ON_TIME_C, 1, 13) ROW_DATE,         TRIM(T.ANSLOGIN) LOGID,         SUM(T.RINGTIME) ACDRING_TIME,         SUM(T.DURATION - T.QUEUETIME - T.RINGTIME - T.TALKTIME) ACDIVR_TIME,         SUM(T.TALKTIME) ACDTALK_TIME,         SUM(CASE               WHEN INSTR(T.DISPOSITION, '2') > 0 AND T.DIRECTION = '0' THEN                1               ELSE                0             END) CALLTALK_SUM    FROM STAT_HOLLYCRMAPP.CALL_REC_DETAIL T   WHERE 1 = 1     AND T.ANSLOGIN IS NOT NULL     AND SUBSTR(T.ON_TIME_C, 1, 13) = :B1   GROUP BY SUBSTR(T.ON_TIME_C, 1, 13), TRIM(T.ANSLOGIN)   ;Explained.SQL> set linesize 1000SQL> set pagesize 9999SQL> select *from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2426201691----------------------------------------------------------------------------------------------------------  | Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |  ----------------------------------------------------------------------------------------------------------  |   0 | INSERT STATEMENT              |                          |    31 |  1364 |  2824   (1)| 00:00:34 |  |   1 |  LOAD TABLE CONVENTIONAL      | AGENT_WORKING_RATE_TEMP2 |       |       |            |          |  |   2 |   HASH GROUP BY               |                          |    31 |  1364 |  2824   (1)| 00:00:34 |  |*  3 |    TABLE ACCESS BY INDEX ROWID| CALL_REC_DETAIL          |    31 |  1364 |  2823   (1)| 00:00:34 |  |*  4 |     INDEX FULL SCAN           | IDX_ANSLOGIN_REC         |  3058 |       |    82   (0)| 00:00:01 |  ----------------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------     3 - filter(SUBSTR("T"."ON_TIME_C",1,13)=:B1)   4 - filter("T"."ANSLOGIN" IS NOT NULL)17 rows selected.SQL> 

SQL> explain plan for      SELECT SUBSTR(T.ON_TIME_C, 1, 13) ROW_DATE,                TRIM(T.ANSLOGIN) LOGID,           SUM(T.RINGTIME) ACDRING_TIME,           SUM(T.DURATION - T.QUEUETIME - T.RINGTIME - T.TALKTIME) ACDIVR_TIME,           SUM(T.TALKTIME) ACDTALK_TIME,           SUM(CASE                 WHEN INSTR(T.DISPOSITION, '2') > 0 AND T.DIRECTION = '0' THEN                  1                 ELSE                  0               END) CALLTALK_SUM      FROM STAT_HOLLYCRMAPP.CALL_REC_DETAIL T     WHERE 1 = 1       AND T.ANSLOGIN IS NOT NULL       AND SUBSTR(T.ON_TIME_C, 1, 13) = :B1     GROUP BY SUBSTR(T.ON_TIME_C, 1, 13), TRIM(T.ANSLOGIN) ;Explained.SQL>  set linesize 1000  SQL>  set pagesize 9999 SQL> SQL> select * from table (dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 538663538----------------------------------------------------------------------------------------------| Id  | Operation   | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |     | 49658 |2182K|     | 60409   (1)| 00:12:05 ||   1 |  HASH GROUP BY   |     | 49658 |2182K|3320K| 60409   (1)| 00:12:05 ||*  2 |   TABLE ACCESS FULL| CALL_REC_DETAIL | 49658 |2182K|     | 59842   (1)| 00:11:59 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("T"."ANSLOGIN" IS NOT NULL AND SUBSTR("T"."ON_TIME_C",1,13)=:B1)14 rows selected.SQL> 

SQL> alter session set events '1555 trace name errorstack forever, level 3';

Session altered.

问题再现确实有点难：

SQL> alter session set events '1555 trace name errorstack forever, level 3';Session altered.SQL> show parameter eventsNAME     TYPE VALUE------------------------------------ ----------- ------------------------------xml_db_events     string enableSQL> SELECT SUBSTR(T.ON_TIME_C, 1, 13) ROW_DATE,    2                TRIM(T.ANSLOGIN) LOGID,           SUM(T.RINGTIME) ACDRING_TIME,           SUM(T.DURATION - T.QUEUETIME - T.RINGTIME - T.TALKTIME) ACDIVR_TIME,           SUM(T.TALKTIME) ACDTALK_TIME,           SUM(CASE                 WHEN INSTR(T.DISPOSITION, '2') > 0 AND T.DIRECTION = '0' THEN                  1                 ELSE                  0               END) CALLTALK_SUM      FROM STAT_HOLLYCRMAPP.CALL_REC_DETAIL T     WHERE 1 = 1       AND T.ANSLOGIN IS NOT NULL       AND SUBSTR(T.ON_TIME_C, 1, 13) = :B1     GROUP BY SUBSTR(T.ON_TIME_C, 1, 13), TRIM(T.ANSLOGIN) ;  3    4    5    6    7    8    9   10   11   12   13   14   15   16  SP2-0552: Bind variable "B1" not declared.SQL>  SELECT SUBSTR(T.ON_TIME_C, 1, 13) ROW_DATE,    2                TRIM(T.ANSLOGIN) LOGID,           SUM(T.RINGTIME) ACDRING_TIME,           SUM(T.DURATION - T.QUEUETIME - T.RINGTIME - T.TALKTIME) ACDIVR_TIME,           SUM(T.TALKTIME) ACDTALK_TIME,           SUM(CASE                 WHEN INSTR(T.DISPOSITION, '2') > 0 AND T.DIRECTION = '0' THEN                  1                 ELSE                  0               END) CALLTALK_SUM      FROM STAT_HOLLYCRMAPP.CALL_REC_DETAIL T     WHERE 1 = 1       AND T.ANSLOGIN IS NOT NULL       AND SUBSTR(T.ON_TIME_C, 1, 13) = 3     GROUP BY SUBSTR(T.ON_TIME_C, 1, 13), TRIM(T.ANSLOGIN) ;  3    4    5    6    7    8    9   10   11   12   13   14   15   16       AND SUBSTR(T.ON_TIME_C, 1, 13) = 3         *ERROR at line 15:ORA-01722: invalid numberSQL> select ON_TIME_C from STAT_HOLLYCRMAPP.CALL_REC_DETAIL WHERE ROWNUM <10;ON_TIME_C--------------------2015-01-28 10:59:032015-01-28 10:59:142015-01-28 10:58:532015-01-28 11:00:182015-01-28 10:53:592015-01-28 11:00:242015-01-28 11:01:462015-01-28 10:56:172015-01-28 10:58:229 rows selected.SQL>  SELECT SUBSTR(T.ON_TIME_C, 1, 13) ROW_DATE,    2                TRIM(T.ANSLOGIN) LOGID,           SUM(T.RINGTIME) ACDRING_TIME,           SUM(T.DURATION - T.QUEUETIME - T.RINGTIME - T.TALKTIME) ACDIVR_TIME,           SUM(T.TALKTIME) ACDTALK_TIME,           SUM(CASE                 WHEN INSTR(T.DISPOSITION, '2') > 0 AND T.DIRECTION = '0' THEN                  1                 ELSE                  0               END) CALLTALK_SUM      FROM STAT_HOLLYCRMAPP.CALL_REC_DETAIL T     WHERE 1 = 1       AND T.ANSLOGIN IS NOT NULL       AND SUBSTR(T.ON_TIME_C, 1, 13) =   3    4    5    6    7    8    9   10   11   12   13   14   15  '2015-01-28 11:00:24' 16  GROUP BY SUBSTR(T.ON_TIME_C, 1, 13), TRIM(T.ANSLOGIN) ;no rows selectedSQL>