oracle报错00801,升级 – 搜索结果 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - ...

数据库从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)