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>
-------------------其实可以仔细看下执行的包里面的内容就知道，干了些什么了
