外部表的导入导出问题 (41天)

今天尝试了一下用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

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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-04-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏tiane12

记一次由内存不足引起的MYSQL停止故障

1092
来自专栏杨建荣的学习笔记

停止数据库没有响应的问题分析(r9笔记第51天)

昨天写了一篇停库没有响应的问题分析,其实对于我来说,还是有些不太踏实,里面有几点需要改进。 因为是测试环境,所以操作的时候就随意了一些,如果是生产环境,直接ki...

3114
来自专栏码匠的流水账

nginx lua重置请求参数及常量备忘

641
来自专栏乐沙弥的世界

ORA-31623: a job is not attached to this session via the specified handle

    在使用Oracel Datapump API时碰到ORA-31623(a job is not attached to this session via...

633
来自专栏乐沙弥的世界

Shell 脚本中执行mysql语句

    对于自动化运维,诸如备份恢复之类的,DBA经常需要将SQL语句封装到shell脚本。本文描述了在Linux环境下mysql数据库中,shell脚本下调用...

652
来自专栏deed博客

Oracle在Linux下安装

1252
来自专栏杨建荣的学习笔记

mysqlimport导入报错的排查(r10笔记第58天)

今天有个同事问我一个mysqlimport导入的问题,看起来还是蛮奇怪的。同事在客户端导入一个文件。文件大小是2.8G,然后报错mysqlimport: Er...

3127
来自专栏乐沙弥的世界

ORA-01157: cannot identify/lock data file n 故障一例

    最近在使用swingbench的时候碰到了ORA-01157故障,下面是其具体描述与解决。

674
来自专栏Hadoop实操

CM启动报InnoDB engine not found分析

将/tmp目录权限修改为777,重启mysql和cloudera-scm-server服务

3705
来自专栏杂烩

LVS高并发负载均衡实现 原

    LVS:LVS是Linux Virtual Server的简写,意即Linux虚拟服务器,是一个虚拟的服务器集群系统。本项目在1998年5月由章文嵩博士...

382

扫描关注云+社区