重建DBMS_STATS解决ORA-31626 ORA-21633以及ORA-04063 ORA-06508 错误
在PL/SQL中无意间修改了package body-DBMS_STATS 的内容导致在系统多处功能异常。 如下
重建DBMS_STATS包，解决以上问题， 当然也有个ORA错误不是它引起来的，以下是重建过程
点击(此处)折叠或打开
重建完成后再次执行 dbms_stats.gather_table_stats 是成功的
但是在 expdp的时候还是同样的错误提示，提示表空间USERS 是read only 状态，需要修改为online
修改表空间USERS为online
再次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: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
点击(此处)折叠或打开
- [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
但是在 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
点击(此处)折叠或打开
-
- 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>
点击(此处)折叠或打开
- [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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27039319/viewspace-2126134/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 闪回技术-闪回查询
- 闪回技术-闪回数据库01
- 配置ADG 产生ORA-10458错误
- oracle RAC 迁移votedisk
- ORACL RAC 环境下添加磁盘组提示ORA-15032 ORA-15201
- create database提示ORA-01501 ORA-00200 ORA-00202 ORA-27038错误提示
- 手动创建实例时提示ORA-00845的解决方法
- RAC环境中ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
- 在RAC环境中recreate controlfile 提示ORA-01503和ORA-12720 错误
- RMAN-Configuring an Archived Redo Log Deletion Policy
