重建DBMS_STATS包

Posted on by

联系：手机(13429648788)  QQ(107644445)

链接：https://www.orasos.com/%e9%87%8d%e5%bb%badbms_stats%e5%8c%85.html

标题：重建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

One thought on “重建DBMS_STATS包”

  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.

Comments are closed.