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.

The following command starts an instance (and mounts and opens the database) in restricted mode:

SQL*Plus	SRVCTL (When Oracle Restart Is In Use)
STARTUP RESTRICT	srvctl start database -d db_unique_name -o restrict

You can use the restrict mode in combination with the mount, nomount, and open modes.

Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

测试：

[oracle@rh64 ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 31 06:55:47 2013


Copyright (c) 1982, 2013, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select * from v$version;


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


SQL> drop package DBMS_STATS;    -----先删除掉包


Package dropped.


SQL> exec dbms_stats.GATHER_TABLE_STATS('SCOTT','EMP');        -------开始报错
BEGIN dbms_stats.GATHER_TABLE_STATS('SCOTT','EMP'); END;


      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_STATS.GATHER_TABLE_STATS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> select count(*) from dba_objects where object_name='DBMS_STATS';    -----查看此时的对象数字有一个，而且是public的


  COUNT(*)
----------
         1


SQL> SELECT OWNER,OBJECT_NAME from dba_objects where object_name='DBMS_STATS'; 


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
PUBLIC
DBMS_STATS


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict;    --------按官档模式以限制方式启动
ORACLE instance started.


Total System Global Area  263049216 bytes
Fixed Size                  2252336 bytes
Variable Size             176161232 bytes
Database Buffers           79691776 bytes
Redo Buffers                4943872 bytes
Database mounted.
Database opened.


SQL> select logins from v$instance;    ----查看得知是以限制模式启动，如果正常的启动这是ALLOW,下文会有
 

LOGINS
----------
RESTRICTED


SQL> select open_mode from v$database;   -----查看模式为读写模式


OPEN_MODE
--------------------
READ WRITE


SQL> exec dbms_stats.GATHER_TABLE_STATS('SCOTT','EMP');
BEGIN dbms_stats.GATHER_TABLE_STATS('SCOTT','EMP'); END;


      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_STATS.GATHER_TABLE_STATS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored




SQL> drop package DBMS_STATS;
drop package DBMS_STATS
*
ERROR at line 1:
ORA-04043: object DBMS_STATS does not exist




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.
SQL> exec dbms_stats.GATHER_TABLE_STATS('SCOTT','EMP');     -------重建后会发现能正常收集统计信息


PL/SQL procedure successfully completed.
SQL> SELECT OWNER,OBJECT_NAME from dba_objects where object_name='DBMS_STATS';   ----查看此时则有三个对象


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SYS
DBMS_STATS


SYS
DBMS_STATS


PUBLIC
DBMS_STATS




SQL> select count(*) from dba_objects where object_name='DBMS_STATS';   


  COUNT(*)
----------
         3
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;


System altered.


SQL>  select logins from v$instance;   -------修改正常打开后，LOGINS则是ALLOWED的模式。正常启动时查出来的值是ALLOWED，restricted模式时，此值是RESTRICTED


LOGINS
----------
ALLOWED


SQL> select open_mode from v$database; -------正常的open时包括restricted和nonrestricted状态，查出来的值是READ WRITE，read-only模式时，此值是READ ONLY


OPEN_MODE
--------------------
READ WRITE


SQL> 

-------------------其实可以仔细看下执行的包里面的内容就知道，干了些什么了