--查询版本：select * from v$version
--查询目录：select * from dba_directories;
使用数据泵遇到的问题：
操作步骤：（在20.168.1.57做测试）
第一：创建导出空间目录
1、创建导出空间目录.bat 内容如下：
echo off
set oracle_sid=orcl
sqlplus sys/sys as sysdba @createdir.sql
2、createdir.sql 文件内容如下
CREATE DIRECTORY dump_dir2 AS 'J:\dump';
GRANT read,write ON DIRECTORY dump_dir2 TO hnxc;
第二：导出oracle中的表空间
导出oracle中的表空间.bat文件内容如下：
set /p reamid=请输入县级导出空间区域代码:
echo %reamid%
C:\oracle\product\10.1.0\Db_1\BIN\expdp hnxc/hnxchnxc@orcl DIRECTORY=dump_dir2
DUMPFILE=FC%reamid%.dmp TABLESPACES=FC%reamid%
LOGFILE=FC%reamid%.log
pause
第三：导入oracle中的表空间
导入oracle中的表空间.bat内容如下：
set /p reamid=请输入县级导出空间区域代码:
echo %reamid%
C:\oracle\product\10.1.0\Db_1\BIN\impdp hnxc/hnxchnxc@orcl
DIRECTORY=dump_dir2 DUMPFILE=FC%reamid%.dmp
TABLESPACES=FC%reamid% EXCLUDE=STA TISTICS
pause
注意：
1、如果要导出或者导入的oracle 存在多个实例时一定要指定要导出或导入的表空间所在的实例名称
例如：hnxc/hnxchnxc@orcl，如果不指定@orcl负责会报错如下图：
错误代码：
UDE-00008：0RA-01017：
2、上面的导入语句：
必须新建一个空的同名表空间，如果在同一个数据库导必须删掉原来的那个表空间；
-- 新建表空间
create tablespace FC431007 datafile'I:\data\FC431007.dbf'size1000m autoextend on next 50m maxsize unlimited;
-- 删除表空间
drop tablespace FC431011 INCLUDING CONTENTS AND DATAFILES;
如果没新建表空间会报错：
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "HNXC"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "HNXC"."SYS_IMPORT_TABLESPACE_01": hnxc/********@orcl DIRECTORY=dump_dir2 DUMPFILE=FC431007.dmp TABLESPACES=FC431007 EXCLUDE=STA TISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'FC431007' does not exist
Failing sql is:
3、导出时注意导出工具的版本是否正确：
如果不正确回报以下错误：UDE-00018:
4、导入时注意问题：
低版本导出的可以导入高版本的数据库。
高版本导出的不能导入低版本的数据库：该问题的解决办法还有待考察。
错误如下：ORA-39001：ORA-39000：ORA-31619
[root@localhost bin]# impdp hnxc/hnxchnxc@WFORCL DIRECTORY=dump_dir1 DUMPFILE=TJXXPT.dmp TABLESPACES=TJXXPT LOGFILE=TJXXPT.log EXCLUDE=STATISTICS PARALLEL=5
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 20 September, 2011 13:57:49
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_DIR1 is invalid
终于发现原来数据库中的directory数据库对象所指向的目录为“/dpump_dir1”，而在该操作系统中根本没有这个目录，因目录不存在，日志文件也就理所当然的无处可写。
修改代码
[root@localhost bin]# impdp hnxc/hnxchnxc@WFORCL DIRECTORY=dump_dir DUMPFILE=TJXXPT.dmp TABLESPACES=TJXXPT LOGFILE=TJXXPT.log EXCLUDE=STATISTICS PARALLEL=5
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 20 September, 2011 14:04:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
再导入
[root@localhost bin]# impdp hnxc/hnxchnxc@WFORCL DIRECTORY=DUMP_DIR DUMPFILE=TJXXPT.dmp TABLESPACES=TJXXPT LOGFILE=TJXXPT.log EXCLUDE=STA TISTICS PARALLEL=5
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 20 September, 2011 14:52:09
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/opt/tmp/TJXXPT.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[root@localhost bin]# impdp hnxc/hnxchnxc@WFORCL DIRECTORY=DUMP_DIR DUMPFILE=TJXXPT.dmp TABLESPACES=TJXXPT LOGFILE=TJXXPT.log EXCLUDE=STA TISTICS PARALLEL=5
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 20 September, 2011 14:42:40
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/opt/hnxc/dump/TJXXPT.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--------------------------------------------------------------------------------------------------------------------------------- [root@localhost tmp]# chmod 777 *.*
-------------------------------------------------------------------------------------------------------------------------------------
[root@localhost bin]# impdp hnxc/hnxchnxc@WFORCL DIRECTORY=DUMP_DIR DUMPFILE=TJXXPT.dmp TABLESPACES=TJXXPT LOGFILE=TJXXPT.log EXCLUDE=STA TISTICS PARALLEL=5
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 20 September, 2011 15:11:57
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/opt/tmp/TJXXPT.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
更换导入方法
[oracle@localhost ~]$ export ORACLE_SID=WFORCL
[oracle@localhost ~]$ export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
[oracle@localhost ~]$ cd /home/oracle/oracle/product/10.2.0/db_1
[oracle@localhost db_1]$ cd bin
------------------------------------------------------------------------------------------------------------------------------------- [oracle@localhost bin]$ impdp hnxc/hnxchnxc@WFORCL DIRECTORY=dump_dir DUMPFILE=FC430807.dmp TABLESPACES=FC430807 EXCLUDE=STATISTICS
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 15:08:13
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39168: Object path STA TISTICS was not found.
----------------------------------------------------------------------------------------------------------------------------------------- 发下是参数STATISTICS报错了
修改导入语句
impdp hnxc/hnxchnxc@WFORCL DIRECTORY=dump_dir DUMPFILE=FC430807.dmp TABLESPACES=FC430807
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 15:13:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "HNXC"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "HNXC"."SYS_IMPORT_TABLESPACE_01": hnxc/********@WFORCL DIRECTORY=dump_dir DUMPFILE=FC430807.dmp TABLESPACES=FC430807
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HNXC"."CZBT_BATCH_430807" 6.320 KB 18 rows
. . imported "HNXC"."CZBT_BTDJ_430807_2005" 0 KB 0 rows
Job "HNXC"."SYS_IMPORT_TABLESPACE_01" successfully completed at 15:13:46
导出报错：
如果dump_dir所指的目录里有要导出的文件，再重复导出时则会报错》
[oracle@localhost bin]$ expdp hnxc/hnxchnxc@WFORCL DIRECTORY=dump_dir DUMPFILE=FC430807.dmp TABLESPACES=FC430807 LOGFILE=FC430807.log
Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 15:22:59
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/opt/tmp/FC430807.dmp"
ORA-27038: created file already exists
Additional information: 1
-- 要解决此问题必须把/opt/tmp/FC430807.dmp文件删掉后再导出
LINUX/Unix 系统大小写敏感
把TJXXPT.dmp更改为TJXXPT.DMP 后导入可以进行
[oracle@localhost bin]$ impdp hnxc/hnxchnxc@WFORCL DIRECTORY=dump_dir DUMPFILE=TJXXPT.DMP TABLESPACES=TJXXPT LOGFILE=TJXXPT.log
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 15:35:33
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "HNXC"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "HNXC"."SYS_IMPORT_TABLESPACE_01": hnxc/********@WFORCL DIRECTORY=dump_dir DUMPFILE=TJXXPT.DMP TABLESPACES=TJXXPT LOGFILE=TJXXPT.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
. . imported "HNXC"."ERRORPHXX" 554.4 MB 10035360 rows
. . imported "HNXC"."CZBT_BTDJ_430806_2010" 186.7 MB 1244346 rows
. . imported "HNXC"."TEMP_ERROR_ZHXX" 47.75 MB 1112637 rows
. . imported "HNXC"."HZ_BTDJHZ" 1.333 MB 19524 rows
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table HNXC.FILE_RECORD by 128 in tablespace TJXXPT
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table HNXC.FILE_RECORD by 128 in tablespace TJXXPT
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table HNXC.FILE_RECORD by 128 in tablespace TJXXPT
ORA-39171: Job is experiencing a resumable wait.
后面怎么又报错了……
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table HNXC.FILE_RECORD by 128 in tablespace TJXXPT
报这个错是由于tjxxpt的空间设置太小了
把tjxxpt的空间重建把空间改成2G再也没报错。
问题解决了
--------------------------------------------------------------------------------------------------------------------------------------
如果写成DUMPFILE=TJXXPT.dmp就会报错
[oracle@localhost bin]$ impdp hnxc/hnxchnxc@WFORCL DIRECTORY=dump_dir DUMPFILE=TJXXPT.dmp TABLESPACES=TJXXPT LOGFILE=TJXXPT.log
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 15:28:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/opt/tmp/TJXXPT.dmp" for read ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3