公众号：杨建荣的学习笔记 · 作者：笔记41天 · 发布：2014-04-13 09:21:09 · 原文链接

今天尝试了一下用exp导出外部表，碰到了一些问题。

—-导出

今天导出的时候发现一个严重的问题，导出一个很小的外部表花了很长时间，最后还是报错，而且生成的dump文件有好几个G

[oracle@oel1 ~]$ exp hr/hr file=hr.dmp log=hr.log

Export: Release 10.2.0.1.0 – Production on Fri May 31 21:23:49 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user HR

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user HR

About to export HR’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export HR’s tables via Conventional Path …

. . exporting table ALERT_LOG

Last login: Fri May 31 21:20:56 2013 from 192.168.3.1

[oracle@oel1 ~]$ ll

-rw-r–r– 1 oracle dba 2551078912 May 31 21:26 hr.dmp

-rw-r–r– 1 oracle dba 0 May 31 21:23 hr.log

想了一下数据泵需要用到directory,尝试使用expdp导出。这次成功了。

[oracle@oel1 ~]$ export ORACLE_SID=PROD

[oracle@oel1 ~]$ expdp hr/hr dumpfile=hr_dp.dmp DIRECTORY=alert_dir tables=alert_log

Export: Release 10.2.0.1.0 – Production on Friday, 31 May, 2013 21:31:16

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

Starting “HR”.”SYS_EXPORT_TABLE_01″: hr/******** dumpfile=hr_dp.dmp DIRECTORY=alert_dir tables=alert_log

Estimate in progress using BLOCKS method…

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Master table “HR”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for HR.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/admin/PROD/bdump/hr_dp.dmp

Job “HR”.”SYS_EXPORT_TABLE_01″ successfully completed at 21:31:37

–导入

导入的时候，导入另外一个用户

[oracle@oel1 ~]$ impdp jeanron/jeanron dumpfile=hr_dp.dmp DIRECTORY=alert_dir tables=alert_log

Import: Release 10.2.0.1.0 – Production on Friday, 31 May, 2013 21:39:27

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 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 ALERT_DIR is invalid

导入的时候提示找不到diretory，我查了下，没输错啊。

最后给directory赋予了read,write权限给目标用户

SQL> grant read,write on directory alert_dir to jeanron;

Grant succeeded.

SQL> exit

[oracle@oel1 ~]$ impdp jeanron/jeanron dumpfile=hr_dp.dmp DIRECTORY=alert_dir tables=alert_log

Import: Release 10.2.0.1.0 – Production on Friday, 31 May, 2013 21:40:04

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

ORA-31655: no data or metadata objects selected for job

ORA-39154: Objects from foreign schemas have been removed from import

Master table “JEANRON”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded

Starting “JEANRON”.”SYS_IMPORT_TABLE_01″: jeanron/******** dumpfile=hr_dp.dmp DIRECTORY=alert_dir tables=alert_log

Job “JEANRON”.”SYS_IMPORT_TABLE_01″ successfully completed at 21:40:07

看来新特性还得用新特性提供的方法来做。