Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

133

积分

0

好友

17

主题
1#
发表于 2012-12-6 15:35:55 | 查看: 10814| 回复: 6
本帖最后由 saup007 于 2012-12-29 09:13 编辑

更新:

今天晚上22点,又跑失败了,肿么回事?

在MOS没找到对应的文章。

同样的错误ORA-01461: can bind a LONG value only for insert into a LONG column,也可以做实验再现,只是不知道scheduler job怎么也报这个,难道是bug?

linux 5.6 x86_64
oracle 11.2.0.2.0

alter 日志:
  1. Wed Dec 05 22:17:32 2012
  2. SYS_AUTO_SQL_TUNING_TASK exiting with error "1461" for execution "EXEC_61608".  See DBA_ADVISOR_EXECUTIONS for more details.
  3. End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
  4. Errors in file /u01/app/oracle/diag/rdbms/yongbing/YONGBING/trace/YONGBING_j004_18678.trc:
  5. ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_14823"
  6. ORA-01461: can bind a LONG value only for insert into a LONG column
  7. ORA-06512: at "SYS.PRVT_ADVISOR", line 2735
  8. ORA-06512: at "SYS.DBMS_ADVISOR", line 241
  9. ORA-06512: at "SYS.DBMS_SQLTUNE", line 821
  10. ORA-06512: at line 4
复制代码
scheduler也有报错:
  1. SQL> select * from Dba_Scheduler_Job_Run_Details WHERE status <> 'SUCCEEDED';

  2.     LOG_ID LOG_DATE                                OWNER  JOB_NAME               STATUS    RUN_DURATION  ADDITIONAL_INFO
  3. ---------- --------------------------------------- ------ ---------------------- -------   ------------- -------------------------------------------------------------------
  4.     211333 05-12月-12 10.17.32.347700 下午 +08:00  SYS    ORA$AT_SQ_SQL_SW_14823 FAILED    +000 00:17:30 ORA-01461: can bind a LONG value only for insert into a LONG column
复制代码
alter日志建议查看DBA_ADVISOR_EXECUTIONS
  1. SQL> select * from DBA_ADVISOR_EXECUTIONS t where task_name='SYS_AUTO_SQL_TUNING_TASK';

  2. OWNER     TASK_ID TASK_NAME                     EXECUTION_ID  EXECUTION_TYPE  EXECUTION_TYPE# EXECUTION_START      ADVISOR_NAME       ADVISOR_ID STATUS         STATUS#  ERROR_MESSAGE
  3. ------ ---------- ------------------------------------------  --------------- --------------- ---------------      ------------------ ---------- ----------- ----------  --------------------------------------------
  4. SYS             1 SYS_AUTO_SQL_TUNING_TASK             61608  TUNE SQL                      1 2012/12/5 22:00:02  SQL Tuning Advisor          4 FATAL ERROR          6  ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值

  5. 30 rows selected
复制代码
trace 文件:
  1. $ cat  /u01/app/oracle/diag/rdbms/yongbing/YONGBING/trace/YONGBING_j004_18678.trc
  2. Trace file /u01/app/oracle/diag/rdbms/yongbing/YONGBING/trace/YONGBING_j004_18678.trc
  3. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  4. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  5. ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
  6. System name:     Linux
  7. Node name:     dbserver2
  8. Release:     2.6.18-238.19.1.el5xen
  9. Version:     #1 SMP Fri Jul 15 08:16:59 EDT 2011
  10. Machine:     x86_64
  11. VM name:     Xen Version: 3.1 (PVM)
  12. Instance name: YONGBING
  13. Redo thread mounted by this instance: 1
  14. Oracle process number: 95
  15. Unix process pid: 18678, image: oracle@dbserver2 (J004)


  16. *** 2012-12-05 22:00:31.151
  17. *** SESSION ID:(1397.63227) 2012-12-05 22:00:31.151
  18. *** CLIENT ID:() 2012-12-05 22:00:31.151
  19. *** SERVICE NAME:(SYS$USERS) 2012-12-05 22:00:31.151
  20. *** MODULE NAME:(DBMS_SCHEDULER) 2012-12-05 22:00:31.151
  21. *** ACTION NAME:(ORA$AT_SQ_SQL_SW_14823) 2012-12-05 22:00:31.151

  22.   status=(pst=ERROR)
  23.     error: pos=0, message follows:
  24. kestsDumpMsg(): dump message
  25. msg=(@=0x2ad7126cc3d8, fac=ORA, cod=16960, p1=ORA-16960: SQL Analyze could not reproduce the desired plan.)

  26. *** 2012-12-05 22:14:23.599
  27.   status=(pst=SUCCESS)
  28. **************************************************************
  29. Execution Statistics
  30.   Exec count:         1
  31.   CR gets:            477607
  32.   CU gets:            0
  33.   Disk Reads:         409893
  34.   Disk Writes:        0
  35.   IO Read Requests:   35590
  36.   IO Write Requests:  0
  37.   Bytes Read:         3357843456
  38.   Bytes Written:      0
  39.   Bytes Exchanged with Storage:  3357843456
  40.   Bytes Exchanged with Disk:  3357843456
  41.   Bytes Simulated Read:  0
  42.   Bytes Simulated Returned:  0
  43.   Elapsed Time: 13 (s)
  44.   CPU Time: 3236 (ms)
  45.   User I/O Time: 9327 (ms)
  46.   status=(pst=ERROR)
  47.   status=(pst=SUCCESS)
  48. **************************************************************
  49. Execution Statistics
  50.   Exec count:         1
  51.   CR gets:            393109
  52.   CU gets:            0
  53.   Disk Reads:         8368
  54.   Disk Writes:        0
  55.   IO Read Requests:   8351
  56.   IO Write Requests:  0
  57.   Bytes Read:         68550656
  58.   Bytes Written:      0
  59.   Bytes Exchanged with Storage:  68550656
  60.   Bytes Exchanged with Disk:  68550656
  61.   Bytes Simulated Read:  0
  62.   Bytes Simulated Returned:  0
  63.   Elapsed Time: 7245 (ms)
  64.   CPU Time: 2116 (ms)
  65.   User I/O Time: 4847 (ms)

  66. *** 2012-12-05 22:14:43.598
  67.   status=(pst=SUCCESS)
  68. **************************************************************
  69. Execution Statistics
  70.   Exec count:         1
  71.   CR gets:            2305
  72.   CU gets:            0
  73.   Disk Reads:         0
  74.   Disk Writes:        0
  75.   IO Read Requests:   0
  76.   IO Write Requests:  0
  77.   Bytes Read:         0
  78.   Bytes Written:      0
  79.   Bytes Exchanged with Storage:  0
  80.   Bytes Exchanged with Disk:  0
  81.   Bytes Simulated Read:  0
  82.   Bytes Simulated Returned:  0
  83.   Elapsed Time: 8831 (us)
  84.   CPU Time: 6800 (us)
  85.   User I/O Time: 0 (us)
  86.   status=(pst=SUCCESS)

  87. *** 2012-12-05 22:16:08.786
  88. **************************************************************
  89. Execution Statistics
  90.   Exec count:         1
  91.   CR gets:            76936
  92.   CU gets:            2
  93.   Disk Reads:         587
  94.   Disk Writes:        0
  95.   IO Read Requests:   587
  96.   IO Write Requests:  0
  97.   Bytes Read:         4808704
  98.   Bytes Written:      0
  99.   Bytes Exchanged with Storage:  4808704
  100.   Bytes Exchanged with Disk:  4808704
  101.   Bytes Simulated Read:  0
  102.   Bytes Simulated Returned:  0
  103.   Elapsed Time: 3047 (ms)
  104.   CPU Time: 224 (ms)
  105.   User I/O Time: 2678 (ms)
  106.   status=(pst=ERROR)
  107.   status=(pst=SUCCESS)
  108. **************************************************************
  109. Execution Statistics
  110.   Exec count:         1
  111.   CR gets:            10984
  112.   CU gets:            2
  113.   Disk Reads:         509
  114.   Disk Writes:        0
  115.   IO Read Requests:   504
  116.   IO Write Requests:  0
  117.   Bytes Read:         4169728
  118.   Bytes Written:      0
  119.   Bytes Exchanged with Storage:  4169728
  120.   Bytes Exchanged with Disk:  4169728
  121.   Bytes Simulated Read:  0
  122.   Bytes Simulated Returned:  0
  123.   Elapsed Time: 154 (ms)
  124.   CPU Time: 139 (ms)
  125.   User I/O Time: 14 (ms)

  126. *** 2012-12-05 22:17:24.011
  127.   status=(pst=SUCCESS)
  128. **************************************************************
  129. Execution Statistics
  130.   Exec count:         1
  131.   CR gets:            100734
  132.   CU gets:            0
  133.   Disk Reads:         90330
  134.   Disk Writes:        0
  135.   IO Read Requests:   1838
  136.   IO Write Requests:  0
  137.   Bytes Read:         739983360
  138.   Bytes Written:      0
  139.   Bytes Exchanged with Storage:  739983360
  140.   Bytes Exchanged with Disk:  739983360
  141.   Bytes Simulated Read:  0
  142.   Bytes Simulated Returned:  0
  143.   Elapsed Time: 6862 (ms)
  144.   CPU Time: 1068 (ms)
  145.   User I/O Time: 5197 (ms)

  146. *** 2012-12-05 22:17:32.235
  147. ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_14823"
  148. ORA-01461: can bind a LONG value only for insert into a LONG column
  149. ORA-06512: at "SYS.PRVT_ADVISOR", line 2735
  150. ORA-06512: at "SYS.DBMS_ADVISOR", line 241
  151. ORA-06512: at "SYS.DBMS_SQLTUNE", line 821
  152. ORA-06512: at line 4
复制代码
7#
发表于 2012-12-29 09:09:53
本帖最后由 saup007 于 2012-12-29 09:12 编辑
Liu Maclean(刘相兵 发表于 2012-12-7 21:01
SQL 过长确实可能是 引起该问题的一种原因, 还是建议 禁止 该自动tuning job
  1. SQL> select object_id from dba_advisor_objects where execution_name='EXEC_61608'
  2.   2  minus
  3.   3  select object_id FROM  dba_advisor_findings where execution_name='EXEC_61608'
  4.   4  ;

  5. OBJECT_ID
  6. ----------
  7.      62611
  8.      62724

  9. 查找这两个对象对应的SQL:

  10. SQL> col ATTR1 for a30
  11. SQL> col ATTR3 for a30
  12. SQL>
  13. SQL>
  14. SQL>
  15. SQL> select object_id,type,task_name,ATTR1,ATTR3 from dba_advisor_objects where object_id in (62611,62724);

  16. OBJECT_ID TYPE                           TASK_NAME                      ATTR1                          ATTR3
  17. ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
  18.      62611 AUTOMATIC SQL WORKLOAD         SYS_AUTO_SQL_TUNING_TASK       124                           
  19.      62724 SQL                            SYS_AUTO_SQL_TUNING_TASK       ct94ypw24bjtn                  zhongyl

  20. SQL> select sql_id,to_char(sql_text) from dba_hist_sqltext where sql_id='ct94ypw24bjtn';

  21. select sql_id,to_char(sql_text) from dba_hist_sqltext where sql_id='ct94ypw24bjtn'

  22. ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 12044, 最大: 4000)

复制代码
手工对这个SQL调用 DBMS_SQLTUNE:

  1. SQL> DECLARE
  2.   2    l_sql_tune_task_id  VARCHAR2(100);
  3.   3    v_sql clob:='select *
  4.   4  from username.table_name t
  5.   5  where t.user_id in
  6.   6  (
  7.   7  1847,
  8. ...............

  9. 1003 1856
  10. 1004 )';
  11. 1005 BEGIN
  12. 1006   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  13. 1007                           sql_text    => v_sql,
  14. 1008                           scope       => DBMS_SQLTUNE.scope_comprehensive,
  15. 1009                           time_limit  => 60,
  16. 1010                           task_name   => 'ct94ypw24bjtn_tuning_task',
  17. 1011                           description => 'Tuning task for statement ct94ypw24bjtn.');
  18. 1012   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
  19. 1013 END;
  20. 1014 /

  21. DECLARE
  22.   l_sql_tune_task_id  VARCHAR2(100);
  23.   v_sql clob:='select *
  24. from username.表 t
  25. where t.user_id in
  26. (
  27. ..........


  28. ORA-13607: 指定的任务或对象 ct94ypw24bjtn_tuning_task 已存在
  29. ORA-06512: 在 "SYS.DBMS_SQLTUNE_INTERNAL", line 7625
  30. ORA-06512: 在 "SYS.PRVT_SQLADV_INFRA", line 40
  31. ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 629
  32. ORA-06512: 在 line 1006

  33. SQL>


  34. 这里报错是因为我之前已经执行过了。

  35. SQL> begin
  36.   2  DBMS_SQLTUNE.execute_tuning_task(task_name => 'ct94ypw24bjtn_tuning_task');
  37.   3  end;
  38.   4  /

  39. begin
  40. DBMS_SQLTUNE.execute_tuning_task(task_name => 'ct94ypw24bjtn_tuning_task');
  41. end;

  42. ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值
  43. ORA-06512: 在 "SYS.PRVT_ADVISOR", line 2735
  44. ORA-06512: 在 "SYS.DBMS_ADVISOR", line 241
  45. ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 837
  46. ORA-06512: 在 line 2
复制代码
  1. 鉴于这是一个偶然调用的SQL,所以我删除其对应的SNAPSHOT,这样在SYS_AUTO_SQL_TUNING_TASK执行的时候,就不会对其进行分析了。




  2. SQL> select to_char(timestamp,'yyyymmdd hh24:mi:ss') from dba_hist_sql_plan where sql_id='ct94ypw24bjtn';

  3. TO_CHAR(TIMESTAMP,'YYYYMMDDHH2
  4. ------------------------------
  5. 20121205 10:13:27
  6. 20121205 10:13:27




  7. 通过以下SQL找到对应的 SNAPSHOT


  8. select */*to_char(begin_interval_time,'yyyymmdd hh24:mi:ss'),to_char(end_interval_time,'yyyymmdd hh24:mi:ss')*/ from dba_hist_snapshot  where begin_interval_time >= to_date('20121205 09:50:00','yyyymmdd hh24:mi:ss') and end_interval_time <= to_date('20121205 10:40:00','yyyymmdd hh24:mi:ss')


  9. 删除SNAPSHOT:


  10. SQL> exec dbms_workload_repository.drop_snapshot_range(23418,23418,2205571097);

  11. PL/SQL procedure successfully completed


  12. 以后正常了。


  13. select * FROM DBA_AUTOTASK_TASK


  14. 这个视图记录了 sql tuning advisor 是否ENABLED.

  15. 其实这个job enable、disable意义并不大,因为默认不接受自动生成的SQL PROFILE,需要更改配置

  16. BEGIN
  17. DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
  18. task_name => 'SYS_AUTO_SQL_TUNING_TASK',
  19. parameter => 'ACCEPT_SQL_PROFILES',
  20. value => 'TRUE');
  21. END;
  22. /

复制代码

回复 只看该作者 道具 举报

6#
发表于 2012-12-7 21:01:36
SQL 过长确实可能是 引起该问题的一种原因, 还是建议 禁止 该自动tuning job

回复 只看该作者 道具 举报

5#
发表于 2012-12-7 15:23:32
本帖最后由 saup007 于 2012-12-7 15:28 编辑
Liu Maclean(刘相兵 发表于 2012-12-6 22:50
在MOS没找到对应的文章。=> 为啥我一下就找到了呢?考虑禁用该 自动tuning job 来绕过该问题吧,运行以下 ...


2012-12-05 job_name='ORA$AT_SQ_SQL_SW_14823'
  1. select * from dba_hist_active_sess_history where action='ORA$AT_SQ_SQL_SW_14823'
  2. order by to_char(sample_time,'yyyymmdd hh24:mi:ss') desc ;

  3. --最后一个运行sql_id='2x7umvydhanuq'

  4. select length(sql_text) from dba_hist_sqltext where sql_id='2x7umvydhanuq'
  5. --长度为89026
复制代码
  1. 2012-12-06 job_name='ORA$AT_SQ_SQL_SW_14843'

  2. select * from dba_hist_active_sess_history where action='ORA$AT_SQ_SQL_SW_14843'
  3. order by to_char(sample_time,'yyyymmdd hh24:mi:ss') desc ;

  4. --最后一个运行sql_id='2x7umvydhanuq'

  5. select length(sql_text) from dba_hist_sqltext where sql_id='2x7umvydhanuq'
  6. --长度为89026
复制代码
sql_id的文本在2x7umvydhanuq.sql里
  1. select sql_id,max(length(sql_text)) from dba_hist_sqltext
  2. group by sql_id order by max(length(sql_text)) desc

  3.            SQL_ID        MAX(LENGTH(SQL_TEXT))
  4. 1        2x7umvydhanuq        89026
  5. 2        byz2v1s7n7bsk        44643
  6. 3        5xxxrg5ckv1k3        15477
  7. 4        1m9jgx6ftdx76        13986
  8. 5        6qjjq18g2f7ur        12843
  9. 6        93fhja4nptvy6        12842
复制代码
2x7umvydhanuq.txt (86.94 KB, 下载次数: 1388)



Desktop.rar (40.91 KB, 下载次数: 1665)





回复 只看该作者 道具 举报

4#
发表于 2012-12-7 00:44:37
Liu Maclean(刘相兵 发表于 2012-12-6 22:50
在MOS没找到对应的文章。=> 为啥我一下就找到了呢?考虑禁用该 自动tuning job 来绕过该问题吧,运行以下 ...

HI,Maclean:
不是没有搜MOS等现成的,我的确搜了好久,挨个看也没找到,除了打不开的《Write Formula Form, Errors: ORA-01461: Can bind a LONG value only for insert into a LONG column(806895.1)》

我记得bug都在知识库有文章记录?

在“搜索源”,为“搜索知识库”,加上bug db或所有源就可以搜到了。真心是杯具。MOS还是用的不熟。谢谢Maclean。

回复 只看该作者 道具 举报

3#
发表于 2012-12-6 22:50:48
saup007 发表于 2012-12-6 16:25
我猜是不是这种可能?

SYS.PRVT_ADVISOR.SET_TASK_PARAMETER因为L_DATA  VARCHAR2( 32767) := DATA; 当VAR ...

在MOS没找到对应的文章。=> 为啥我一下就找到了呢?
  1. dr: 12957646 11.2.0.2 RDBMS 11.2.0.2 SVRMAN SQLTN PRODID-5 PORTID-226 ORA-1461
  2. Abstract: ORA-1461 DURING AUTOTASK RUN

  3. BUG TYPE CHOSEN
  4.   ===============
  5.   Code
  6.   
  7.   SubComponent: Dictionary
  8.   ========================
  9.   DETAILED PROBLEM DESCRIPTION
  10.   ============================
  11.   unable to use Automatic tuning task due to :
  12.   ORA-1461: Ein LONG-Wert kann nur zur Einfügung in eine LONG-Spalte gebunden
  13.   werden
  14.   ----- Current SQL Statement for this session (sql_id=3m8smr0v7v1m6) -----
  15.   INSERT INTO sys.wri$_adv_message_groups
  16.   (task_id,id,seq,message#,fac,hdr,lm,nl,p1,p2,p3,p4,p5)
  17.   VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
  18.   
  19.   DIAGNOSTIC ANALYSIS
  20.   ===================
  21.   failing request via DBMS_SCHEDULER
  22.   same request fails via SQLPLUS
  23.   
  24.   WORKAROUND?
  25.   ===========
  26.   No
  27.   
  28.   TECHNICAL IMPACT
  29.   ================
  30.   Automatic tuning task can not be used
  31.   
  32.   RELATED ISSUES (bugs, forums, RFAs)
  33.   ===================================
  34.   [ORA-1461 wri$_adv_message_groups]
  35.   BUG: 5877381
  36.   RDBMS 11.1.0.0 SVRMAN SQLTN PRODID-5 PORTID-46 5696361
  37.   Abstract: LNX070207 - SYS_AUTO_SQL_TUNING_TASK EXITING WITH ERROR "1461"
  38.   --> Status: 36,Duplicate Bug.
  39.   .
  40.   BUG:  5696361
  41.   RDBMS 11.0.1.3 SVRMAN SQLTN PRODID-5 PORTID-46 ORA-1461
  42.   Abstract: SYS_AUTO_SQL_TUNING_TASK FAILS WITH ORA-1461
  43.   -->Status: 31,Could Not Reproduce
  44.   .
  45.   [kgeselv <- ksesecl0 <- opiobv]
  46.   BUG: 12876003
  47.   RDBMS 11.2.0.2 DICTIONARY PRODID-5 PORTID-226
  48.   Abstract: INTERMITTENT ORA-1461 SIGNALLED ON JOBS AFTER UPGRADE FROM
  49.   11.2.0.1
  50.   Status :11
  51.   
  52.   HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
  53.   ====================================================
  54.   Always
  55.   
  56.   DOES THE ISSUE REPRODUCE INTERNALLY?
  57.   ====================================
  58.   Not attempted
  59.   
  60.   EXPLAIN WHY THE ISSUE WAS NOT TESTED INTERNALLY.
  61.   ================================================
  62.   not having all values for bind variables
  63.   



复制代码
考虑禁用该 自动tuning job 来绕过该问题吧,运行以下脚本禁用  SYS_AUTO_SQL_TUNING_TASK
  1. BEGIN
  2. dbms_auto_task_admin.disable(
  3.     client_name => 'sql tuning advisor',
  4.     operation   => NULL,
  5.     window_name => NULL);
  6. END;
  7. /
  8. select client_name,status from DBA_AUTOTASK_CLIENT where client_name='sql tuning advisor';
复制代码

回复 只看该作者 道具 举报

2#
发表于 2012-12-6 16:25:54
本帖最后由 saup007 于 2012-12-6 16:29 编辑

我猜是不是这种可能?

SYS.PRVT_ADVISOR.SET_TASK_PARAMETER
  1. PROCEDURE SET_TASK_PARAMETER(TASK_NAME     IN VARCHAR2,
  2.                                PARAMETER     IN VARCHAR2,
  3.                                DATA          IN VARCHAR2,
  4.                                PROP          IN BINARY_INTEGER := 0 ,
  5.                                SCOPE         IN BINARY_INTEGER := FUTURE_EXECUTIONS,
  6.                                TASK_OWNER_ID IN BINARY_INTEGER := -1 ) IS
  7.     TASK_ID_NUM      BINARY_INTEGER := 0 ;
  8.     TP               SYS.WRI$_ADV_ABSTRACT_T;
  9.     L_DATA           VARCHAR2( 32767) := DATA;
  10.     L_NAME           VARCHAR2( 32767) := PARAMETER;
  11.     ADV_PROP         BINARY_INTEGER;
  12.     TASK_PROP        BINARY_INTEGER;
  13.     L_NEW_FLAGS      BINARY_INTEGER := 0 ;
  14.     TASK_STATUS      BINARY_INTEGER;
  15.     TASK_STORED_PROP NUMBER;
  16.     L_DEFAULTED      BOOLEAN := FALSE;
  17.     ENAME            VARCHAR2( 30);
  18.   BEGIN

  19.     IF PROP IS NULL OR PROP = 0 THEN
  20.       TASK_PROP := TASK_PROP_TASK;
  21.     ELSE
  22.       TASK_PROP := PROP;
  23.     END IF;

  24.     TASK_ID_NUM := VALIDATE_TASK(TASK_NAME, TASK_PROP, TASK_OWNER_ID);

  25.     HANDLE_LOCK(TASK_ID_NUM, TASK_LOCK_X);

  26.     CHECK_MODIFY(TASK_NAME, TASK_ID_NUM);

  27.     TP := FETCH_ADVISOR(TASK_ID_NUM);

  28.     SELECT PROPERTY
  29.       INTO ADV_PROP
  30.       FROM SYS.WRI$_ADV_DEFINITIONS
  31.      WHERE ID = TP.ADVISOR_ID;

  32.     SELECT NVL(E.STATUS, A.STATUS) STATUS#, A.PROPERTY
  33.       INTO TASK_STATUS, TASK_STORED_PROP
  34.       FROM WRI$_ADV_TASKS A, WRI$_ADV_EXECUTIONS E
  35.      WHERE A.ID = E.TASK_ID(+)
  36.        AND A.ADVISOR_ID = E.ADVISOR_ID(+)
  37.        AND A.LAST_EXEC_NAME = E.NAME(+)
  38.        AND A.ID = TASK_ID_NUM;

  39.     VALIDATE_TASK_PARAMETER(TP,
  40.                             ADV_PROP,
  41.                             TASK_NAME,
  42.                             TASK_ID_NUM,
  43.                             TASK_STATUS,
  44.                             TASK_STORED_PROP,
  45.                             SCOPE,
  46.                             L_NAME,
  47.                             L_DATA,
  48.                             L_NEW_FLAGS);

  49.     IF (SCOPE = FUTURE_EXECUTIONS) THEN

  50.       IF (BITAND(ADV_PROP, ADV_PROP_MULTI_EXEC) <> 0) THEN
  51.         PROPAGATE_TASK_PARAMETER(TASK_ID_NUM, L_NAME);
  52.       END IF;

  53.       UPDATE SYS.WRI$_ADV_PARAMETERS A
  54.          SET A.VALUE = L_DATA, A.FLAGS = L_NEW_FLAGS
  55.        WHERE A.TASK_ID = TASK_ID_NUM
  56.          AND A.NAME = L_NAME
  57.          AND A.VALUE <> L_DATA;

  58.     ELSE

  59.       ENAME := GET_LAST_EXECUTION(TASK_ID_NUM);

  60.       MERGE INTO WRI$_ADV_EXEC_PARAMETERS P
  61.       USING ( SELECT TASK_ID_NUM TASK_ID,
  62.                     ENAME       EXEC_NAME,
  63.                     L_NAME      NAME,
  64.                     L_DATA      VALUE
  65.                FROM DUAL) I
  66.       ON (P.TASK_ID = I.TASK_ID AND P.EXEC_NAME = I.EXEC_NAME AND P.NAME = I.NAME)
  67.       WHEN MATCHED THEN
  68.         UPDATE SET P.VALUE = I.VALUE
  69.       WHEN NOT MATCHED THEN
  70.         INSERT
  71.           (TASK_ID, EXEC_NAME, NAME, VALUE)
  72.         VALUES                                -----2735行
  73.           (I.TASK_ID, I.EXEC_NAME, I.NAME, I.VALUE);

  74.     END IF;

  75.     UPDATE_TASK(TASK_ID_NUM);

  76.     COMMIT;

  77.     HANDLE_LOCK(TASK_ID_NUM, TASK_LOCK_R);

  78.     RETURN;

  79.   EXCEPTION
  80.     WHEN OTHERS THEN
  81.       ROLLBACK;

  82.       HANDLE_LOCK(TASK_ID_NUM, TASK_LOCK_R);

  83.       RAISE;
  84.   END SET_TASK_PARAMETER;
复制代码
因为L_DATA  VARCHAR2( 32767) := DATA; 当VARCHAR2超过4000,估计就被Oracle认为是LONG类型了,在merge时报错。

16:24:41 sys@PRI11G> desc WRI$_ADV_EXEC_PARAMETERS
Name                Null?    Type
------------------------------------------------ --------
TASK_ID        NOT NULL NUMBER
EXEC_NAME        NOT NULL VARCHAR2(30)
NAME                NOT NULL VARCHAR2(30)
VALUE                NOT NULL VARCHAR2(4000)

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 06:25 , Processed in 0.053318 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569