*
ERROR at line 1:
ORA-06550: line 1, column 24:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
current delete exists prior
SQL> exec :temp:=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk40b860bcf2',verify=>'no',commit=>'yes');
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE YES
YES
select object_id,object_name from t2 where object_id between 103 and 108
SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE YES
YES
select object_id,object_name from t2 where object_id between 103 and 108
SQL_HANDLE PLAN_NAME ORIGIN ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'enabled',attribute_value=>'no');
PL/SQL procedure successfully completed.
查看修改结果
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE NO
YES
select object_id,object_name from t2 where object_id between 103 and 108
SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE YES
YES
select object_id,object_name from t2 where object_id between 103 and 108
SQL_HANDLE PLAN_NAME ORIGIN ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
实验结果
SQL> select object_id,object_name from t2 where object_id between 103 and 108;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
103
MIGRATE$
104
DEPENDENCY$
105
ACCESS$
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
106
I_DEPENDENCY1
107
I_DEPENDENCY2
108
I_ACCESS1
6 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8vtdn0kgytfxr, child number 2
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
NOTE: cannot fetch plan for SQL_ID: 8vtdn0kgytfxr, CHILD_NUMBER: 2
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
8 rows selected.
原因同上，
SQL> select object_id,object_name from t2 where object_id between 103 and 108;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
103
MIGRATE$
104
DEPENDENCY$
105
ACCESS$