Oracle数据库迁移，使用expdp/impdp数据泵

大宇进阶之路

已于 2023-03-22 15:32:17 修改

阅读量2.4k

点赞数

文章标签： oracle 数据库

于 2022-08-17 09:20:05 首次发布

本文链接：https://blog.csdn.net/wjzholmes/article/details/125798558

版权

按表空间导出，只会导出表空间相关的表，只有按用户导出，会把用户相关的表、存过、package、job等都导出

查看数据量：

select owner,tablespace_name,SEGMENT_NAME ,segment_type,sum(BYTES/1024/1024) "sizes(MB)" 
from dba_segments where  owner='WJZ' 
group by owner,segment_name,segment_type, tablespace_name 
order by "sizes(MB)"  desc;

有的表因为有lob字段导致占用空间大：

查看用户下所有lob字段占用空间大小：
SELECT B.TABLE_NAME,
   B.TABLESPACE_NAME,
   B.COLUMN_NAME,
   A.SEGMENT_NAME,
   a.SEGMENT_TYPE,
   ROUND(SUM(A.BYTES / 1024 / 1024 ), 2) MB
FROM DBA_SEGMENTS A
JOIN DBA_LOBS B
ON A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
WHERE A.owner='ZTEAPP'
GROUP BY B.TABLE_NAME,B.TABLESPACE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE
ORDER BY MB DESC ;

1.查看/创建导入导出的目录

dmp文件放到机器的Oracle定义的directory目录(select * from dba_directories)，这是关键，先登录Oracle看你这个参数：

select * from dba_directories;

默认导出目录为D:\app\Administrator/admin/orcl/dpdump/（其中前面为Oracle安装目录）。也可以重新创建导入导出目录：

create directory dump_dir as '/data/app/database_new/admin/dpdump'

注意：目录路径必须是已经存在的目录，如果目录不存在，也会显示目录创建成功（语句不会自动去创建目录），在执行expdp时候会报错误

2.为创建的目录赋权限

需要以其他用户运行，建议在sys用户下执行。

grant read,write on directory 目录名 to 需要赋值的用户名

grant read,write on directory dump_dir to WJZ;

grant exp_full_database,imp_full_database to WJZ;

3.导出数据

使用命令行和文件两种方法可导出，可以根据用户、表空间、表等方法。

Exclude要写的表多，字符串太长，不使用命令形式，用参数文件方式：

expdp WJZ/CRM schemas=WJZ directory=dump_dir dumpfile=wjz%U.dmp logfile=wjz.log  parallel=4 EXCLUDE=TABLE:\"IN \(\'TABLE_A\',\'TABLE_B\',\'TABLE_C\'\)\"

参数文件：

userid='/ as sysdba'

directory=CRM_DMP_NAME

schemas=WJZ

dumpfile=wjz%U.dmp

job_name=wjz_exp

logfile=wjz.log

parallel=4

EXCLUDE=TABLE:"IN('TABLE_A','TABLE_B','TABLE_C')"

参数文件的方法执行：expdp parfile=expdp.par

4.导入数据

impdp wjz/password directory=dump_dir remap_schema=user_a:wjz remap_tablespace=TBS_A:BASE_DATA,TBS_B:BASE_DATA,TBS_C:BASE_DATA table_exists_action=replace dumpfile=EXPORT.dmp logfile=export.log

5.其他

rac expdp导出时报错：
ORA-31693: Table data object "testuser"."test" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "
/home/oracle/exp_dir/full05.dmp" for write
ORA-19505: failed to identify file "
/home/oracle/exp_dir/full05.dmp"
ORA-27037: unable to obtain file status
错误原因：
在RAC环境下如果PARALLEL参数设置大于1，进程会在RAC的各个节点上进行导出。如果导出目录不共享的话就会报错
解决方法：
1、设置为共享的directory；
2、导出语句添加cluster=n选项，指定只在本地导出；

impdp导入覆盖只是覆盖表，序列、存过、包若存在则不会替换

解决方法：

序列若冲突，则调整所有用户序列的增长步长为10000：
将库中所有非默认用户的序列值+10000（加大最大值），再插入数据时则不会再出现唯一约束；
SELECT ‘Alter Sequence ‘||sequence_owner||’.’|| SEQUENCE_NAME || ’ Increment By ’ || to_char(10000)||’;’ from DBA_SEQUENCES
WHERE SEQUENCE_OWNER in
(select b.username from dba_users b
where b.account_status = ‘OPEN’ and b.username not in(‘SYS’, ‘SYSTEM’, ‘SYSMAN’, ‘MGMT_VIEW’, ‘DBSNMP’, ‘SAMPLE_DBA’))

或者导入时把数据清空再导入

可以使用命令

DROP USER 用户名 CASCADE;

使用drop user cascade; 可以删除用户及所相关的所有对象，可以通过重新创建一个同名对象，然后使用imp 命令从dmp文件导入新的对象

expdp数据迁移序列值导入失败，导致新增数据违反唯一约束，调整所有序列值_范斯浪学的博客-CSDN博客_expdp sequence

报错UDE-00014 invalid value for parameter,

EXCLUDE=TABLE:"IN ('参数中的值太多时，就会出现问题。根据这一现象，搜索到文章：UDE-00014 invalid value for parameter, 'tables'. (Doc ID 758848.1)。 当tables参数中的参数值太大，超过3400字节时，就可能会出现无效的参数值。

终止导入导出任务

在expdp和impdp导出导入时由于命令错误想终止操作，于是就按ctr + c 中进入export交互界面，然后exit退出然后查看操作系统进程发现没有expdp进程了以为就是停掉了，但发现导出的dmp文件还在一直增加，其实任务并没有停止。
正确方法
1.查看视图dba_datapump_jobs
select job_name,state from dba_datapump_jobs;
发现任务确认还在执行
2.正确停止expdp导出任务使用stop_job
expdp system/oracle@orcl attach=SYS_EXPORT_SCHEMA_01
或者
impdp system/oracle@orcl attach=SYS_EXPORT_SCHEMA_01
3.执行关闭job
stop_job=immediate

expdp impdp中 exclude/include 的使用_主要是钱不够呀的博客-CSDN博客_expdp include

EXPDP之INCLUDE ---导出存储过程_lwei_998的博客-CSDN博客_expdp 导出存储过程