当前位置:  数据库>oracle

重建DBMS_STATS解决ORA-31626 ORA-21633以及ORA-04063 ORA-06508 错误

    来源: 互联网  发布时间：2017-06-27

    本文导语: 在PL/SQL中无意间修改了package body-DBMS_STATS 的内容导致在系统多处功能异常。 如下 [Oracle@mhxy01 ~]$ expdp scott/tiger directory=DMP dumpfile=emp.dump tables=emp Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:35:59 2016 Copyright (c) 1982, 2011, Oracle and/or its a...

在PL/SQL中无意间修改了package body-DBMS_STATS 的内容导致在系统多处功能异常。 如下

[Oracle@mhxy01 ~]$ expdp scott/tiger directory=DMP dumpfile=emp.dump tables=emp

Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:35:59 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01647: tablespace 'USERS' is read-only, cannot allocate space in it

SQL> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'emp');
BEGIN dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'emp'); END;

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 1

重建DBMS_STATS包，解决以上问题， 当然也有个ORA错误不是它引起来的，以下是重建过程

[oracle@mhxy01 ~]$ sqlplus / as sysdba
SQL> set linesize 400
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> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'emp');
BEGIN dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'emp'); END;

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 1


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.
SQL> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'emp');

PL/SQL procedure successfully completed.

SQL> exit
重建完成后再次执行 dbms_stats.gather_table_stats 是成功的
但是在 expdp的时候还是同样的错误提示，提示表空间USERS 是read only 状态，需要修改为online

[oracle@mhxy01 ~]$ expdp scott/tiger directory=DMP dumpfile=emp.dump tables=emp

Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:35:59 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01647: tablespace 'USERS' is read-only, cannot allocate space in it
修改表空间USERS为online


SQL> select name,file#,checkpoint_change# from v$datafile ;

NAME                                                                                  FILE# CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ---------- ------------------
+DATA/mhxy/datafile/system.256.922979845                                                  1            1678924
+DATA/mhxy/datafile/sysaux.257.922979845                                                  2            1678924
+DATA/mhxy/datafile/undotbs1.258.922979845                                                3            1678924
+DATA/mhxy/datafile/users.259.922979847                                                  4            1387796
+DATA/mhxy/datafile/example.264.922980153                                                5            1678924
+DATA/mhxy/datafile/undotbs2.265.922980651                                                6            1678924

6 rows selected

SQL> alter system checkpoint;

System altered

SQL> select name,file#,checkpoint_change# from v$datafile;

SQL> select name,file#,checkpoint_change# from v$datafile;
 
NAME                                                                                  FILE# CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ---------- ------------------
+DATA/mhxy/datafile/system.256.922979845                                                  1            1690156
+DATA/mhxy/datafile/sysaux.257.922979845                                                  2            1690156
+DATA/mhxy/datafile/undotbs1.258.922979845                                                3            1690156
+DATA/mhxy/datafile/users.259.922979847                                                  4            1387796
+DATA/mhxy/datafile/example.264.922980153                                                5            1690156
+DATA/mhxy/datafile/undotbs2.265.922980651                                                6            1690156

6 rows selected

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                        ONLINE
SYSAUX                        ONLINE
UNDOTBS1                      ONLINE
TEMP                          ONLINE
USERS                          READ ONLY
UNDOTBS2                      ONLINE
EXAMPLE                        ONLINE

7 rows selected

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1690156

SQL> alter tablespace users read write;

Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                        ONLINE
SYSAUX                        ONLINE
UNDOTBS1                      ONLINE
TEMP                          ONLINE
USERS                          ONLINE
UNDOTBS2                      ONLINE
EXAMPLE                        ONLINE

7 rows selected

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1690156

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
1690156
1690156
1690156
1690448
1690156
1690156

6 rows selected

SQL> alter system checkpoint;

System altered

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
1690485
1690485
1690485
1690485
1690485
1690485

6 rows selected

SQL>
再次expdp 正常导出数据备份

[oracle@mhxy01 ~]$ expdp scott/tiger directory=DMP dumpfile=emp.dump tables=emp

Export: Release 11.2.0.4.0 - Production on Tue Oct 11 19:43:57 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=DMP dumpfile=emp.dump tables=emp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/oracledmp/emp.dump
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 11 19:44:29 2016 elapsed 0 00:00:22

：


    
 
 

您可能感兴趣的文章:

 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • 如何重建/dev/cdrom 这个设备？
  • Linux平台下Oracle 密码文件重建
  • 软链接不能修改？只能删除，然后重建？
  • Sql Server 删除主键与重建主键的例子
  • make如果没有makefile文件将如何重建？
  • 红旗+winxp,winxp重装，然后红旗重建引导时说我原来的swap分区未格式化，然后就不让我建grub了，怎么弄呢？
  • 修改内核代码问题:如何编译修改的单个文件而不用重建整个内核?
  • SQL SEVER数据库重建索引的方法
  • 如何对SQL Server 重建索引
  • Android开发 旋转屏幕导致Activity重建解决方法
  • 提高SQL Server性能-重建索引
  • oracle 11g em重建报唯一约束错误解决方法
  • 网络技术 iis7站长之家
  • MSSQL自动重建出现碎片的索引的方法分享


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3