【原】ORA-31626问题小计

作者:david_zhang@sh 【转载时请以超链接形式标明文章】

链接:http://www.cnblogs.com/david-zhang-index/archive/2012/08/04/2622442.html

首先看数据库版本

 1 SQL> select * from v$version;
 2 
 3 BANNER -------------------------------------------------------------------------------- 
 4 
 5 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 
 6 
 7 PL/SQL Release 11.2.0.1.0 - Production 
 8 
 9 CORE    11.2.0.1.0      Production 
10 
11 TNS for Linux: Version 11.2.0.1.0 - Production 
12 
13 NLSRTL Version 11.2.0.1.0 - Production

先说一下背景,目的是将A库中的bond schema用户数据导入到B库中,采用expdp和impdp方式进行导出和导入,数据库版本一直,当在B库进行导入的时候出现如下报错,这个问题比较有意思,注意看下面代码第2行

猜测1.B库system表空间没有自动扩展,空间不足导致,导致问题?

猜测2.从下面代码第2行猜测,导出的数据中包含A库system空间的数据(操作人可能用system用户将bond数据导出,而且忘记加上参数sechems=bond),当在B库用bond用户导入的时候就出现2行问题?

猜测3.expdp和impdp相关的数据字典出了问题

1 ORA-31626: job does not exist
2 ORA-31637: cannot create job SYS_IMPORT_SCHEMA_01 for user SYSTEM
3 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
4 ORA-06512: at "SYS.KUPV$FT_INT", line 798
5 ORA-39244: Event to disable dropping null bit image header during relational select
6 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
7 ORA-06512: at "SYS.KUPC$QUE_INT", line 1825
8 ORA-01658: unable to create INITIAL extent for

先看问题1.查询下系统表空间是否自动扩展,这里提示一下,表空间是个逻辑概念,并不真正存在,我们要看的是里面的数据文件是否自动扩展

 1 SQL> desc dba_data_files
 2  Name                                      Null?    Type
 3  ----------------------------------------- -------- ----------------------------
 4  FILE_NAME                                          VARCHAR2(513)
 5  FILE_ID                                            NUMBER
 6  TABLESPACE_NAME                                    VARCHAR2(30)
 7  BYTES                                              NUMBER
 8  BLOCKS                                             NUMBER
 9  STATUS                                             VARCHAR2(9)
10  RELATIVE_FNO                                       NUMBER
11  AUTOEXTENSIBLE                                     VARCHAR2(3)
12  MAXBYTES                                           NUMBER
13  MAXBLOCKS                                          NUMBER
14  INCREMENT_BY                                       NUMBER
15  USER_BYTES                                         NUMBER
16  USER_BLOCKS                                        NUMBER
17  ONLINE_STATUS                                      VARCHAR2(7)
 1 SQL> select TABLESPACE_NAME ,FILE_NAME ,AUTOEXTENSIBLE from dba_data_files;
 2 
 3 TABLESPACE FILE_NAME                                          AUT
 4 ---------- -------------------------------------------------- ---
 5 USERS      /u01/app/oracle/oradata/oracle/users01.dbf         YES
 6 UNDOTBS1   /u01/app/oracle/oradata/oracle/undotbs01.dbf       YES
 7 SYSAUX     /u01/app/oracle/oradata/oracle/sysaux01.dbf        YES
 8 SYSTEM     /u01/app/oracle/oradata/oracle/system01.dbf        YES
 9 DATA01     /u01/app/oracle/oradata/oracle/data01.dbf          NO
10 SP         /u01/app/oracle/oradata/oracle/sp01.dbf            NO

可以看出系统表空间为自动扩展
如果不为自动扩展,可以采用如下语句将表空间改为自动扩展,这里拿DATA01表空间为例:

1 SQL> alter database datafile '/u01/app/oracle/oradata/oracle/data01.dbf' autoextend on maxsize unlimited;
2 
3 Database altered.
 1 SQL> select TABLESPACE_NAME ,FILE_NAME ,AUTOEXTENSIBLE from dba_data_files;
 2 
 3 TABLESPACE FILE_NAME                                          AUT
 4 ---------- -------------------------------------------------- ---
 5 USERS      /u01/app/oracle/oradata/oracle/users01.dbf         YES
 6 UNDOTBS1   /u01/app/oracle/oradata/oracle/undotbs01.dbf       YES
 7 SYSAUX     /u01/app/oracle/oradata/oracle/sysaux01.dbf        YES
 8 SYSTEM     /u01/app/oracle/oradata/oracle/system01.dbf        YES
 9 DATA01     /u01/app/oracle/oradata/oracle/data01.dbf          YES
10 SP         /u01/app/oracle/oradata/oracle/sp01.dbf            NO

题外话,增加数据文件方法:

 1 SQL> alter tablespace DATA01 add datafile '/u01/app/oracle/oradata/oracle/data02.dbf' size 50m;
 2 
 3 Tablespace altered.
 4 
 5 SQL> select TABLESPACE_NAME ,FILE_NAME ,AUTOEXTENSIBLE from dba_data_files;
 6 
 7 TABLESPACE FILE_NAME                                          AUT
 8 ---------- -------------------------------------------------- ---
 9 USERS      /u01/app/oracle/oradata/oracle/users01.dbf         YES
10 UNDOTBS1   /u01/app/oracle/oradata/oracle/undotbs01.dbf       YES
11 SYSAUX     /u01/app/oracle/oradata/oracle/sysaux01.dbf        YES
12 SYSTEM     /u01/app/oracle/oradata/oracle/system01.dbf        YES
13 DATA01     /u01/app/oracle/oradata/oracle/data01.dbf          YES
14 SP         /u01/app/oracle/oradata/oracle/sp01.dbf            NO
15 DATA01     /u01/app/oracle/oradata/oracle/data02.dbf          NO

再看问题2.问题2个人认为人为原因很多,要与操作人确认,当时的操作语言是否正确,甚至查看到当时操作的语句进行核对。

再看问题3.重建数据字典,执行catalog.sql and catproc.sql来配置后台数据字典,system用户登录执行:

1 sql>@?/rdbms/admin/catalog.sql
2 sql>@?/rdbms/admin/catproc.sql

总结:数据库改动前先要先backup,另提供根据错误号定位问题方法: oerr ora 31637

上一篇:网站安装打包 概述[一]


下一篇:同余方程