首页 » ORACLE 9i-23ai » Troubleshooting dbms_sqltune ORA-04068 ORA-04065 ORA-06508 ORA-06512 在做异常恢复后
Troubleshooting dbms_sqltune ORA-04068 ORA-04065 ORA-06508 ORA-06512 在做异常恢复后
前几日有个库sysaux和部分业务表空间数据文件损坏，在数据库强制异常恢复后， 提示dbms_sqltune使用sql profile无法使用，这个问题与对象的先后创建顺序或部分重建导致，错误信息如下，这里我还原一下问题和分享一下思路。
ORA-04068: existing state of packages has been discarded ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_SQLTUNE_INTERNAL" ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE_INTERNAL" ORA-06512: at "SYS.DBMS_SQLTUNE", line 6759 ORA-06512: at "SYS.DBMS_SQLTUNE", line 6729 ORA-06512: at line 6
1, 还原问题
SQL> @o dbms_sqltune owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME ------------------------- ------------------------------ -------------------- --------- ---------- ---------- ------------------- ------------------- SYS DBMS_SQLTUNE PACKAGE VALID 13804 2019-04-17 01:03:55 2020-03-20 05:50:42 SYS DBMS_SQLTUNE PACKAGE BODY VALID 19191 2019-04-17 01:11:27 2019-04-17 01:11:27 SYS DBMS_SQLTUNE_INTERNAL PACKAGE VALID 17064 2019-04-17 01:07:16 2019-04-17 01:07:16 SYS DBMS_SQLTUNE_INTERNAL PACKAGE BODY VALID 19188 2019-04-17 01:11:25 2019-04-17 01:11:25 字典部分字段说明 obj$ ctime date not null, /* object creation time */ mtime date not null, /* DDL modification time */ stime date not null, /* specification timestamp (version) */ status number not null, /* status of object (see KQD.H): */ dependency$ /* dependency table */ d_obj# number not null, /* dependent object number */ d_timestamp date not null, /* dependent object specification timestamp */ order# number not null, /* order number */ p_obj# number not null, /* parent object number */ p_timestamp date not null, /* parent object specification timestamp */ d_owner# number, /* dependent owner number */ property number not null, /* 0x01 = HARD dependency */ /* 0x02 = REF dependency */ /* 0x04 = FINER GRAINED dependency */ d_attrs raw("M_CSIZ"), /* Finer grain attr. numbers if finer grained */ d_reason raw("M_CSIZ")) /* Reason mask of attrs causing invalidation */ SQL> SELECT do.obj# d_obj, do.name d_name, do.type# d_type, po.obj# p_obj, po.name p_name, to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp", to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME", po.ctime,po.mtime, decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po WHERE P_OBJ#=po.obj#(+) AND D_OBJ#=do.obj# and p_obj# in(select obj# from obj$ where name like 'DBMS_SQLTUNE_INTERNAL%' and type# in(9,11) ) AND do.status=1 /*dependent is valid*/ AND po.status=1 /*parent is valid*/ --AND po.stime!=p_timestamp /*parent timestamp not match*/ ORDER BY 2,1; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 D_OBJ D_NAME D_TYPE P_OBJ P_NAME P_Timestamp STIME CTIME MTIME X ---------- ------------------------- ---------- ---------- ------------------------- ----------------------- ----------------------- ----------------- ----------------- -------- 12431 DBMS_AUTO_SQLTUNE 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12457 DBMS_SMB 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12456 DBMS_SMB_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12455 DBMS_SPM 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12454 DBMS_SPM_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12733 DBMS_SQLDIAG 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12730 DBMS_SQLDIAG_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12452 DBMS_SQLPA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12732 DBMS_SQLTCB_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12429 DBMS_SQLTUNE 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12426 DBMS_SQLTUNE_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12448 DBMS_SQLTUNE_UTIL1 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12449 DBMS_SQLTUNE_UTIL2 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12727 DBMS_STATS 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12728 DBMS_STATS_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12723 DBMS_XPLAN 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12422 PRVT_SQLADV_INFRA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12450 PRVT_SQLPA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12424 PRVT_SQLPROF_INFRA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12423 PRVT_SQLSET_INFRA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12438 WRI$_ADV_HDM_T 14 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12427 WRI$_ADV_SQLTUNE 14 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 12428 WRI$_REPT_SQLT 14 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 25-JUN-2020 07:59:54 20130822 04:08:04 20200625 07:59:54 SAME 23 rows selected. 破坏，更新obj$.stime 模拟可能重建DBMS_SQLTUNE_INTERNAL SQL> update obj$ set STIME=sysdate where obj#=11256; SQL> commit;
2， 验证
SQL> create table anbob.t10000 as select object_id,object_name from dba_objects where rownum<=10000; Table created. SQL> select object_name from anbob.t10000 where object_id=10;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
C_USER#
SQL> @sqlt anbob.t1000
HASH_VALUE SQL_ID CHLD# OPT_MODE SQL_TEXT
---------- ------------- ---------- ---------- ----------------------------------------------------------------------------------------------------
413536917 0jar8zncac4np 0 ALL_ROWS select object_name from anbob.t10000 where object_id=10
2422626187 dh2vr3a86cpwb 0 ALL_ROWS select hash_value, sql_id, -- old_hash_value, child_number chld#, -- plan_hash_value
plan_hash, optimizer_mode opt_mode, sql_text sqlt_sql_text from v$sql where lower(sql_text) like
lower('%anbob.t1000%') --and hash_value != (select sql_hash_value from v$session where sid = (select
sid from v$mystat where rownum = 1))
SQL> SELECT operation,options,object_name,object_alias
FROM v$sql_plan
WHERE sql_id='&sqlid'
AND child_number='&cn' 2 3 4
5 ;
Enter value for sqlid: 0jar8zncac4np
Enter value for cn: 0
OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ -----------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS FULL T10000 T10000@SEL$1
SQL> DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '0jar8zncac4np';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T10000"@"SEL$1" "IDX_NOT_EXISTS")'),
NAME => 'PROFILE_0jar8zncac4np',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_SQLTUNE_INTERNAL"
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE_INTERNAL"
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6759
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6729
ORA-06512: at line 6
前提目前对象都还是VALID状态，只是不能执行。
3, 尝试方法一
可以在SESSION或system动态配置_disable_fast_validate 参数。
SQL> @pd fast_val
Show all parameters and session values from x$ksppi/x$ksppcv...
INDX I_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------- ------------------------------ ----------------------------------------------------------------------
1757 6DD _disable_fast_validate FALSE disable PL/SQL fast validation
SQL> alter session set "_disable_fast_validate"=true;
Session altered.
SQL> DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '0jar8zncac4np';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T10000"@"SEL$1" "IDX_NOT_EXISTS")'),
NAME => 'PROFILE_0jar8zncac4np',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_SQLTUNE_INTERNAL"
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE_INTERNAL"
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6759
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6729
ORA-06512: at line 6
有时该方法生效，或尝试重启后配置该参数；
4, 尝试方法2
所有对象 编译
1) Run this query to find the objects with timestamp issue
set pagesize 10000
column d_name format a20
column p_name format a20
SELECT
do.obj# d_obj,
do.name d_name,
do.type# d_type,
po.obj# p_obj,
po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po
WHERE P_OBJ#=po.obj#(+)
AND D_OBJ#=do.obj#
AND do.status=1 /*dependent is valid*/
AND po.status=1 /*parent is valid*/
AND po.stime!=p_timestamp /*parent timestamp not match*/
ORDER BY 2,1;
2) d_type = 1 INDEX alter index rebuild;
d_type = 2 TABLE alter table upgrade;
d_type = 4 VIEW alter view compile;
d_type = 5 SYNONYM alter synonym compile;
d_type = 7 PROCEDUR alter procedure compile;
d_type = 8 FUNCTION alter function compile;
d_type = 9 PACKAGE alter package compile;
d_type = 11 PACKAGE BODY alter package compile body;
d_type = 12 TRIGGER alter trigger compile;
d_type = 13 TYPE alter session set events '10826 trace name context forever, level 1';
alter type name compile
如
SQL> alter package SYS.DBMS_SQLTUNE_INTERNAL compile body;
Package body altered.
SQL> alter package SYS.DBMS_SQLTUNE_INTERNAL compile body;
Package body altered.
SQL> r
SELECT
do.obj# d_obj,
do.name d_name,
do.type# d_type,
po.obj# p_obj,
po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME", po.ctime,po.mtime,
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po
WHERE P_OBJ#=po.obj#(+)
AND D_OBJ#=do.obj#
and p_obj# in(select obj# from obj$ where name like 'DBMS_SQLTUNE_INTERNAL%' and type# in(9,11) )
AND do.status=1 /*dependent is valid*/
AND po.status=1 /*parent is valid*/
--AND po.stime!=p_timestamp /*parent timestamp not match*/
D_OBJ D_NAME D_TYPE P_OBJ P_NAME P_Timestamp STIME CTIME MTIME X
---------- ------------------------- ---------- ---------- ------------------------------ ----------------------- ----------------------- ----------------- ----------------- --------
12733 DBMS_SQLDIAG 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12429 DBMS_SQLTUNE 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12730 DBMS_SQLDIAG_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12456 DBMS_SMB_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12422 PRVT_SQLADV_INFRA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12423 PRVT_SQLSET_INFRA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12424 PRVT_SQLPROF_INFRA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12427 WRI$_ADV_SQLTUNE 14 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12428 WRI$_REPT_SQLT 14 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12431 DBMS_AUTO_SQLTUNE 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12448 DBMS_SQLTUNE_UTIL1 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12449 DBMS_SQLTUNE_UTIL2 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12450 PRVT_SQLPA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12452 DBMS_SQLPA 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12454 DBMS_SPM_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12455 DBMS_SPM 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12457 DBMS_SMB 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12426 DBMS_SQLTUNE_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 30-JUL-2020 08:42:22 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 SAME
12438 WRI$_ADV_HDM_T 14 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12723 DBMS_XPLAN 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12728 DBMS_STATS_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12727 DBMS_STATS 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
12732 DBMS_SQLTCB_INTERNAL 11 11256 DBMS_SQLTUNE_INTERNAL 25-JUN-2020 07:59:54 30-JUL-2020 08:42:22 20130822 04:08:04 20200625 07:59:54 *DIFFER*
23 rows selected.
SQL> DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '0jar8zncac4np';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T10000"@"SEL$1" "IDX_NOT_EXISTS")'),
NAME => 'PROFILE_0jar8zncac4np',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_SQLTUNE_INTERNAL"
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE_INTERNAL"
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6759
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6729
ORA-06512: at line 6
如果报错，同样可以在编译完后尝试重启；
5， 尝试方法3
更新基表数据，操作需谨慎。
update sys.dependency$ set P_Timestamp=(select P_Timestamp from sys.dependency$ where D_OBJ#=12429 and p_obj#=11256) where D_OBJ#=12426 and p_obj#=11256;
update sys.obj$ set stime=(select P_Timestamp from sys.dependency$ where D_OBJ#=12429 and p_obj#=11256) where obj#=11256;
SQL> commit;
Commit complete.
SQL> DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '0jar8zncac4np';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T10000"@"SEL$1" "IDX_NOT_EXISTS")'),
NAME => 'PROFILE_0jar8zncac4np',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_SQLTUNE_INTERNAL"
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE_INTERNAL"
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6759
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6729
ORA-06512: at line 6
SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2253456 bytes
Variable Size 218107248 bytes
Database Buffers 402653184 bytes
Redo Buffers 3313664 bytes
Database mounted.
Database opened.
SQL> select object_name from anbob.t10000 where object_id=10;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
C_USER#
SQL> DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '0jar8zncac4np';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T10000"@"SEL$1" "IDX_NOT_EXISTS")'),
NAME => 'PROFILE_0jar8zncac4np',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14
PL/SQL procedure successfully completed.
如何知道是上面的对象呢？ 有该问题时首先用hcheck.sql去检查数据库对象，答案在其中。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。