今天尝试了一下用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
看来新特性还得用新特性提供的方法来做。
我来说两句