共计 7158 个字符，预计需要花费 18 分钟才能阅读完成。
在 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
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-02/140648.htm
