重建DBMS_STATS包

联系:手机/微信(+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. 惜分飞 说:

    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.