标签归档：SYS.DBMS_SQLTUNE

数据库从11.2.0.2升级到11.2.0.4,遇到一下错误

Oracle Database 11.2 Post-Upgrade Status Tool           08-21-2020 20:43:36
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.   ORA-04063: package body "SYS.DBMS_SQLTUNE" 有错误
.   ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_SQLTUNE" 的程序单元
.   ORA-06512: 在 line 8
.                                       INVALID      11.2.0.4.0  00:07:28
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:01:45
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:20
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:35
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:57
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:12
Oracle Enterprise Manager
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 6
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.   ORA-06512: 在 line 5
.   SP2-1506: START, @ 或 @@ 命令没有参数
.   ORA-01422: 实际返回的行数超出请求的行数
.                                         VALID      11.2.0.4.0  00:00:24
Oracle XDK
.                                         VALID      11.2.0.4.0  00:00:16
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:33
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:01:21
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:05
Oracle Multimedia
.   ORA-04063: package body "SYS.DBMS_SQLTUNE" 有错误
.   ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_SQLTUNE" 的程序单元
.   ORA-06512: 在 "SYS.UTL_RECOMP", line 865
.   ORA-06512: 在 "SYS.UTL_RECOMP", line 878
.   ORA-06512: 在 line 1
.                                         VALID      11.2.0.4.0  00:00:53
Spatial
.                                         VALID      11.2.0.4.0  00:03:25
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:08
Oracle Rules Manager
.                                         VALID      11.2.0.4.0  00:00:06
Oracle Application Express
.                                         VALID     3.2.1.00.10
Final Actions
.                                                                00:00:00
Total Upgrade Time: 00:18:38

PL/SQL 过程已成功完成。

查询mos发现类似解决方案

SQL> select owner, object_name, object_type, status from dba_objects  where object_name = 'DBMS_STATS';

OWNER                          OBJECT_NAME
                                        OBJECT_TYPE         STATUS
------------------------------ -----------------------------------------------------------------------------------------
--------------------------------------- ------------------- -------
SYS                            DBMS_STATS
                                        PACKAGE             VALID
SYS                            DBMS_STATS
                                        PACKAGE BODY        VALID
PUBLIC                         DBMS_STATS
                                        SYNONYM             VALID

SQL> select owner, object_name, object_type, status from dba_objects  where object_name = 'DBMS_SQLTUNE'
  2  /

OWNER                          OBJECT_NAME
                                        OBJECT_TYPE         STATUS
------------------------------ -----------------------------------------------------------------------------------------
--------------------------------------- ------------------- -------
SYS                            DBMS_SQLTUNE
                                        PACKAGE             VALID
SYS                            DBMS_SQLTUNE
                                        PACKAGE BODY        INVALID
PUBLIC                         DBMS_SQLTUNE
                                        SYNONYM             INVALID

SQL> alter package DBMS_SQLTUNE compile body;

警告: 更改的包体带有编译错误。

SQL> show error;
PACKAGE BODY DBMS_SQLTUNE 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: 内部错误 [phdite:node kind]
10210/5  PL/SQL: ORA-01775: 同义词的循环链
10210/5  PL/SQL: SQL Statement ignored
13917/7  PL/SQL: Statement ignored
16995/5  PL/SQL: SQL Statement ignored
17060/12 PL/SQL: ORA-01775: 同义词的循环链
17657/7  PL/SQL: Statement ignored
17660/31 PLS-00231: 函数 'SQLERRM' 不能在 SQL 中使用
19718/5  PL/SQL: SQL Statement ignored
19718/12 PL/SQL: ORA-01775: 同义词的循环链
19782/5  PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
19782/17 PLS-00231: 函数 'BUILD_SQL_DETAIL_XML' 不能在 SQL 中使用
SQL> select owner, object_name, object_type, status from dba_objects  where object_name = 'XMLCONCAT';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
PUBLIC
XMLCONCAT
SYNONYM             VALID


SQL> drop public synonym XMLCONCAT;

同义词已删除。

SQL>  alter package DBMS_SQLTUNE compile body;

程序包体已变更。

重新跑升级脚本catupgrd.sql,一切正常

Oracle Database 11.2 Post-Upgrade Status Tool           08-21-2020 21:08:31
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:06:44
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:00
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:00
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:00
Oracle OLAP API
.                                         VALID      11.2.0.4.0
Oracle Enterprise Manager
.                                         VALID      11.2.0.4.0  00:00:00
Oracle XDK
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:00
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:00:00
Spatial
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Rules Manager
.                                         VALID      11.2.0.4.0
Oracle Application Express
.                                         VALID     3.2.1.00.10
Final Actions
.                                                                00:00:00
Total Upgrade Time: 00:06:51

PL/SQL 过程已成功完成。

参考解决方案:Upgrade From 10G To 11G Fails On Dbms_sqltune : ORA-04063: Package Body “SYS.DBMS_SQLTUNE” Has Errors (Doc ID 1271490.1)
DBMS_STATS , DBMS_SQLTUNE_INTERNAL , DBMS_SQLTUNE Package Can Not be Recompiled during/After Upgrade reporting “ORA-04063″ , “ORA-06508″ . “ORA-06512″ (Doc ID 761961.1)