重建DBMS_STATS包【转】

最新推荐文章于 2021-04-16 04:00:12 发布

cuisheng6388

最新推荐文章于 2021-04-16 04:00:12 发布

阅读量199

点赞数

文章标签：数据库

链接： http://www.xifenfei.com/3928.html

数据库版本

 
           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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7191998/viewspace-756948/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7191998/viewspace-756948/