ORA-20000: Incompatible version of Workspace Manager Installed
失败的 sql 为:
BEGIN
declare ver varchar2(100) ; cnt integer ; dummy integer; vdummy varchar2(30) ; compile_exception EXCEPTION; PRAGMA EXCEPTION_INIT(compile_exception, -06550); invalid_table EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_table, -00942); procedure createErrorProc is begin execute immediate 'create or replace function system.wm$_check_install return boolean is begin return true ; end;' ; end ; begin select status into vdummy from dba_registry where comp_id = 'OWM' ; if (vdummy not in ('VALID', 'UPGRADED', 'LOADED')) then raise compile_exception ; end if ; select 1 into dummy from dual where exists (select 1 from all_users where username = 'WMSYS') ; execute immediate 'create or replace function wmsys.wm$_old_version return varchar2 is begin return ''11.2.0.4.0'' ; end;' ; select count(*) into cnt from dba_procedures where object_name='LT_EXPORT_PKG' and procedure_name='IMPORTEXPORTSUPPORT'; if (cnt=0) then raise_application_error(-20000, 'Incompatible version of Workspace Manager Installed'); end if ; execute immediate 'select value from wmsys.wm$env_vars where name=''OWM_VERSION''' into ver ; if (0>0 and ver != '11.2.0.4.0') then raise_application_error(-20000, 'Unable to import due to one or more versioned tables not having a ''VERSIONED'' status'); end if ; execute immediate 'declare cnt integer ; begin select count(*) into cnt from (select workspace from wmsys.wm$version_hierarchy_table where version!=0 union all select table_name from wmsys.wm$versioned_tables) ; if (cnt>0)
ORA-39083: 对象类型 PROCACT_SYSTEM 创建失败, 出现错误:
ORA-20000: Incompatible version of Workspace Manager Installed
失败的 sql 为:
BEGIN
declare ver varchar2(100) ; cnt integer ; dummy integer; vdummy varchar2(30) ; compile_exception EXCEPTION; PRAGMA EXCEPTION_INIT(compile_exception, -06550); invalid_table EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_table, -00942); procedure createErrorProc is begin execute immediate 'create or replace function system.wm$_check_install return boolean is begin return true ; end;' ; end ; begin select status into vdummy from dba_registry where comp_id = 'OWM' ; if (vdummy not in ('VALID', 'UPGRADED', 'LOADED')) then raise compile_exception ; end if ; select 1 into dummy from dual where exists (select 1 from all_users where username = 'WMSYS') ; execute immediate 'create or replace function wmsys.wm$_old_version return varchar2 is begin return ''11.2.0.4.0'' ; end;' ; select count(*) into cnt from dba_procedures where object_name='LT_EXPORT_PKG' and procedure_name='IMPORTEXPORTSUPPORT'; if (cnt=0) then raise_application_error(-20000, 'Incompatible version of Workspace Manager Installed'); end if ; execute immediate 'select value from wmsys.wm$env_vars where name=''OWM_VERSION''' into ver ; if (0>0 and ver != '11.2.0.4.0') then raise_application_error(-20000, 'Unable to import due to one or more versioned tables not having a ''VERSIONED'' status'); end if ; execute immediate 'declare cnt integer ; begin select count(*) into cnt from (select workspace from wmsys.wm$version_hierarchy_table where version!=0 union all select table_name from wmsys.wm$versioned_tables) ; if (cnt>0)
ORA-20000: Incompatible version of Workspace Manager Installed
失败的 sql 为:
BEGIN
declare ver varchar2(100) ; cnt integer ; dummy integer; vdummy varchar2(30) ; compile_exception EXCEPTION; PRAGMA EXCEPTION_INIT(compile_exception, -06550); invalid_table EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_table, -00942); procedure createErrorProc is begin execute immediate 'create or replace function system.wm$_check_install return boolean is begin return true ; end;' ; end ; begin select status into vdummy from dba_registry where comp_id = 'OWM' ; if (vdummy not in ('VALID', 'UPGRADED', 'LOADED')) then raise compile_exception ; end if ; select 1 into dummy from dual where exists (select 1 from all_users where username = 'WMSYS') ; execute immediate 'create or replace function wmsys.wm$_old_version return varchar2 is begin return ''11.2.0.4.0'' ; end;' ; select count(*) into cnt from dba_procedures where object_name='LT_EXPORT_PKG' and procedure_name='IMPORTEXPORTSUPPORT'; if (cnt=0) then raise_application_error(-20000, 'Incompatible version of Workspace Manager Installed'); end if ; execute immediate 'select value from wmsys.wm$env_vars where name=''OWM_VERSION''' into ver ; if (0>0 and ver != '11.2.0.4.0') then raise_application_error(-20000, 'Unable to import due to one or more versioned tables not having a ''VERSIONED'' status'); end if ; execute immediate 'declare cnt integer ; begin select count(*) into cnt from (select workspace from wmsys.wm$version_hierarchy_table where version!=0 union all select table_name from wmsys.wm$versioned_tables) ; if (cnt>0)
] 中 Worker 发生意外致命错误
PROCACT_SYSTEM
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: 在 "SYS.KUPW$WORKER", line 12620
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: 在 "SYS.KUPW$WORKER", line 34278
----- PL/SQL Call Stack -----
object line object
handle number name
00007FF70BE8F840 33476 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
00007FF70BE8F840 12641 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
00007FF70BE8F840 34341 package body SYS.KUPW$WORKER.SEND_TRACE_MSG
00007FF70BE8F840 28767 package body SYS.KUPW$WORKER.SEND_MSG
00007FF70BE8F840 5418 package body SYS.KUPW$WORKER.LOAD_METADATA
00007FF70BE8F840 13781 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
00007FF70BE8F840 2429 package body SYS.KUPW$WORKER.MAIN
00007FF70BD34800 2 anonymous block
DBMS_LOB.TRIM
DBMS_LOB.FREETEMPORARY
DBMS_LOB.FREETEMPORARY
KUPW: In procedure UPDATE_TYPE_COMPLETION_ROW
KUPW: Old Seqno: 108 New Path: PO Num: -6 New Seqno: 0 error count: 1
KUPW: Primary row is: FALSE
KUPW: Working on old seqno with count of: 4
KUPW: In procedure SEND_MSG. Fatal=0
KUPW: Error count: 1
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
[oracle@source~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@source~]$ exp PARFILE=export_sqlset_201906.par
Export: Release11.2.0.4.0- Production on Tue Jun 1800:17:572019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options
Export done in ZHS16GBK charactersetand AL16UTF16 NCHARcharacterset
About to export specified tables via Direct Path ...
Table SOL_STSTAB_201906 will be exported in conventional path.
. . exporting table SOL_STSTAB_201906 183rows exported
Export terminated successfully without warnings.
Type of the action to perform by the function. If NULL it will default to the value of the DEFAULT_EXECUTION_TYPE parameter. Possible values are:
1）[TEST] EXECUTE – test-execute every SQL statement and collect its execution plans and execution statistics. The resulting plans and statistics will be stored in the advisor framework. This is default.
2）EXPLAIN PLAN – generate explain plan for every statement in the SQL workload. This is similar to the EXPLAIN PLAN command. The resulting plans will be stored in the advisor framework in association with the task.
3）COMPARE [PERFORMANCE] – analyze and compare two versions of SQL performance data. The performance data is generated by test-executing or generating explain plan of the SQL statements. Use this option when two executions of type EXPLAIN_PLAN or TEST_EXECUTE already exist in the task
4）CONVERT SQLSET – used to read the statistics captured in a SQL Tuning Set and model them as a task execution. This can be used when you wish to avoid executing the SQL statements because valid data for the experiment already exists in the SQL Tuning Set.
Bug 10428438 : WITH EXECUTE_FULLDML ROWS IS ALWAYS SET TO 0 11.2.0.1
Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3
By default, only the query portion of DMLs is executed. Using APIs, you can execute the full DML by using the EXECUTE_FULLDML task parameter.EXECUTE_FULLDML when set to TRUE executes DML statement fully, including acquiring row locks and modifying rows; When EXECUTE_FULLDML is set to FALSE (the default value is false) to execute only the query part of the DML without modifying data. When TRUE, SQL Performance Analyzer will issue a rollback following DML execution to prevent persistent changes from being made by the DML. So SPA does not make make any change to the data in the tables.
2）对比两次Trail中的SQL执行的CPU时间
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
EXECUTION_NAME => 'COMPARE_CT_201906', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'CPU_TIME', -
'EXECUTION_NAME1','EXEC_11G_201906', -
'EXECUTION_NAME2','EXEC_19C_201906'), -
EXECUTION_DESC => 'Compare SQLs between 11g and 19c at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
3）对比两次Trail中的SQL执行的逻辑读
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
EXECUTION_NAME => 'COMPARE_BG_201906', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'BUFFER_GETS', -
'EXECUTION_NAME1','EXEC_11G_201906', -
'EXECUTION_NAME2','EXEC_19C_201906'), -
EXECUTION_DESC => 'Compare SQLs between 11g and 19c at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL elapsed_all.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','ALL','ALL',NULL,3000,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
2）获取执行时间下降报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL elapsed_regressed.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201903','HTML','REGRESSED','ALL',NULL,NULL,'COMPARE_ET_201903')).GETCLOBVAL(0,0) FROM DUAL;
spool off
3）获取逻辑读全部报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL buffer_all.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','ALL','ALL',NULL,3000,'COMPARE_BG_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
4）获取逻辑读下降报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL buffer_regressed.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','REGRESSED','ALL',NULL,NULL,'COMPARE_BG_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
5）获取错误报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL error.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','ERRORS','ALL',NULL,NULL,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
6）获取不支持报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL unsupported.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','UNSUPPORTED','ALL',NULL,NULL,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
7）获取执行计划变化报告（已完成，耗时12小时）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL changed_plans.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','CHANGED_PLANS','ALL',NULL,3000,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
8）获取执行超时报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL timeout.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','TIMEOUT','ALL',NULL,NULL,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
计算
安全
数据库
网络和加速
企业服务
