重建DBMS_STATS包

发表于 2012 年 10 月 22 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：重建DBMS_STATS包

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库版本

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

执行DBMS_STATS报错

SQL> exec dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');
 
begin sys.dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');; end;
 
ORA-04063: package body "SYS.DBMS_STATS" 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_STATS" 的程序单元
ORA-06512: 在 line 2

重建DBMS_STATS包

SQL> drop package DBMS_STATS;

Package dropped.

SQL> @?/rdbms/admin/dbmsstat.sql

Package created.

No errors.

Synonym created.


Grant succeeded.

create role gather_system_statistics
            *
ERROR at line 1:
ORA-01921: role name 'GATHER_SYSTEM_STATISTICS' conflicts with another user or
role name



Grant succeeded.


Grant succeeded.


Library created.

SQL> @?/rdbms/admin/prvtstas.plb

Package created.

No errors.
SQL> @?/rdbms/admin/prvtstai.plb

Package body created.

No errors.
SQL> @?/rdbms/admin/prvtstat.plb

Package body created.

No errors.

重新执行DBMS_STATS

SQL> exec dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

补充说明
1.建议数据库在restricted模式下执行重建DBMS_STATS相关脚本
2.对于11g以前版本,具体参考1310365.1

SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plb
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

《重建DBMS_STATS包》有 1 条评论

  1. 惜分飞 说：
    2012 年 10 月 22 日21:57

    How To Reload the SYS.DBMS_STATS Package [ID 1310365.1]

    Applies to
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Goal
How to reload the SYS.DBMS_STATS package into the database ?

Solution
Recreate the SYS.DBMS_STATS package by running the following scripts.
SQL> connect / as sysdba

Drop the SYS.DBMS_STATS package:
SQL> drop package DBMS_STATS;

Recreate the SYS.DBMS_STATS package:

1) Release 9.2, 10.1 and 10.2:
SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plb

2) Release 11.1 and 11.2:
SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstai.plb
SQL> @?/rdbms/admin/prvtstat.plb

Remark: We recommend running these scripts during maintenance window 
while database in restricted mode to avoid problems that can be caused 
when other sessions access the same objects.