How To Reload the SYS.DBMS_STATS Package (文档 ID 1310365.1)

发布时间:2014-10-23 23:26:22
来源:分享查询网

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

返回顶部
查看电脑版