EXPDP/IMPDP
文章目录
1常用参数和命令
1.1 数据泵参数
数据泵作为 Oracle 数据库的主要功能，每个版本都在迭代更新，功能和参数越来越多。不同于原始的 exp/imp工具，数据泵需要在服务器端运行，使用服务器进程创建 Job 来执行导出导入任务。
1.1.1 expdp 常用参数
## expdp 语法格式
expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
## 最简单示例
expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
## attach（连接到作业，进入交互模式）
attach=[schema_name.]job_name
## cluster（RAC 导出需要设置为 no，否则将会报ORA-39.等错误，原因是其他节点对导出目录无权限）
cluster=[yes|no]
## compression（压缩）
compression=[all|data_only|metadata_only|none]
## content（需要导出的数据）
content=[all|data_only|metadata_only]
## directory（导出路径）
directory=directory_object
## dumpfile（导出的文件名）
dumpfile=[directory_object:]file_name[,...]
## exclude/include（排除/包括特定的对象类型）
exclude=object_type[:name_clause][,...]
include=object_type[:name_clause][,...]
## filesize（导出每个 dump 文件的最大限制）
filesize=integer[b|kb|mb|gb|tb]
## full（导出所有数据和元数据，要执行完全导出，需要具有datapump_exp_full_database角色）
full=[yes|no]
## logfile（导出的日志文件名）
logfile=[directory_object:]file_name
## network_link（连接到源数据库进行导出）
network_link=source_database_link
## parallel（并行度，默认为1，该值应小于等于 dump 文件数量）
parallel=integer
## parfile（导出参数文件名称）
parfile=[directory_path]file_name
## query（按查询条件导出）
query=[schema.][table_name:] query_clause
## schemas（导出用户）
schemas=schema_name[,...]
## tables（导出表）
tables=[schema_name.]table_name[:partition_name][,...]
## views_as_tables（将视图导出成表）
views_as_tables=[schema_name.]view_name.
1.1.2 impdp 常用参数
## impdp 语法格式
impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
## 最简单示例
impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
## attach（连接到作业，进入交互模式）
attach=[schema_name.]job_name
## cluster（RAC 导入需要设置为 no，否则将会报ORA-39.等错误，原因是其他节点对导出目录无权限）
cluster=[yes|no]
## content（需要导入的数据）
content=[all|data_only|metadata_only]
## directory（导入路径）
directory=directory_object
## dumpfile（导入的文件名）
dumpfile=[directory_object:]file_name[,...]
## exclude/include（排除/包括特定的对象类型）
exclude=object_type[:name_clause][,...]
include=object_type[:name_clause][,...]
## full（导入所有数据和元数据，要执行完全导入，需要具有datapump_exp_full_database角色）
full=[yes|no]
## logfile（导入的日志文件名）
logfile=[directory_object:]file_name
## network_link（连接到源数据库进行导入）
network_link=source_database_link
## parallel（并行度，默认为1，该值应小于等于 dump 文件数量）
parallel=integer
## parfile（导入参数文件名称）
parfile=[directory_path]file_name
## query（按查询条件导入）
query=[schema.][table_name:] query_clause
## remap_schema（允许导入期间重命名schema名）
remap_schema=source_schema:target_schema
## remap_table（允许导入期间重命名表名）
remap_table=[schema.]old_tablename[.partition]:new_tablename
## remap_tablespace（允许导入期间重命名表空间名）
remap_tablespace=source_tablespace:target_tablespace
## schemas（导入用户）
schemas=schema_name[,...]
## table_exists_action（导入数据处理方式）
table_exists_action=[SKIP | APPEND | TRUNCATE | REPLACE]
## tables（导入表）
tables=[schema_name.]table_name[:partition_name][,...]
## views_as_tables（将视图导入成表）
views_as_tables=[schema_name.]view_name.
1.2 常用命令
下面我们以 scott 用户为例，如果没有 scott 用户，可以执行以下命令创建：
sqlplus / as sysdba @?/rdbms/admin/utlsampl.sql
1.2.1 expdp 命令
## 导出一张表
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables=scott.emp
## 导出多张表
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables=\(scott.emp,scott.dept\)
## 导出一个用户(导出这个用户的所有对象)
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log schemas=scott
## 导出多个用户
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log schemas=\(scott,hr\)
## 导出整个数据库（sys、ordsys、mdsys的用户数据不会被导出）
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log full=yes
## 并行导出
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp%U.dmp logfile=expdp.log schemas=scott parallel=5
## 导出用户元数据(包含表定义、存储过程、函数等等)
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log schemas=scott content=metadata_only
## 导出用户存储过程
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log schemas=scott include=procedure
## 导出用户函数和视图
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log schemas=scott include=\(function,view\)
## 导出一个用户，但不包括索引
expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log schemas=scott exclude=index
1.2.2 impdp 命令
## 导入dmp文件中的所有数据
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log full=yes
## 导入一张表
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log tables=scott.emp
## 导入多张表
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log tables=\(scott.emp,scott.dept\)
## 导入一个用户
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log schemas=scott
## 导入多个用户
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log schemas=\(scott,hr\)
## 并行导入
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp%U.dmp logfile=impdp.log parallel=5
## 导入元数据(包含表定义、存储过程、函数等等)
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log content=metadata_only
## 导入存储过程
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log include=procedure
## 导入函数和视图
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log include=\(function,view\)
## 导入数据，但不包括索引
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log exclude=index
## 重命名表名导入
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log remap_table=scott.emp:emp1
## 重命名schema名导入
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log remap_schema=scott:tim
## 重命名表空间名导入
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log remap_tablespace=users:apptbs
## 导入时，忽略所有对象的段属性，这样导入时对象都创建在目标数据库用户默认的表空间上
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log transform=segment_attributes:n
## 将dmp文件的ddl语句导入到一个文件，不导入数据库
impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=impdp.log sqlfile=import.sql
#数据泵相比 exp/imp 参数和功能均改变很多，可以通过执行 expdp help=y 和 impdp help=y 来查看！
2 全库导出导入
在工作中正常只需要导应用用户，全库导出导入的方式一般不太常用
通过设置参数 full=y 可以执行全库导出导入操作，必须使用拥有 DBA 权限或者 DATAPUMP_EXP_FULL_DATABASE 角色的用户才能执行该操作：
expdp system/oracle directory=expdp_dir dumpfile=full.dmp logfile=full_exp.log full=y
impdp system/oracle directory=expdp_dir dumpfile=full.dmp logfile=full_imp.log full=y
全库导出一般很少用于数据库迁移/升级/备份，不可控且容易造成时间空间的浪费，更适用于小容量、重要等级低的数据库。
2.1 创建directory
有条件的话，可以nfs在目标端和源端挂载同一个盘，这样可以省去拷贝文件的时间。
## root 下
mkdir /expdp
chown oracle:oinstall /expdp
su - oracle
sqlplus / as sysdba
select * from dba_directories;
create directory expdp_dir as '/expdp';
grant read,write on directory expdp_dir to system;
2.2 全库导出
全库导出会导出以下信息：
- 用于重建表空间，用户，表，索引，约束，触发器，序列，以及存储的 PL/SQL 脚本等元素的所有 DDL 语句；
- 除去 SYS 用户下的所有表中的数据
拼接系统用户：
select listagg('\''' || username || '\''',',') within group(order by username) from dba_users where created < (select created from v$database);
----------------------------------------
\'ANONYMOUS\',\'APPQOSSYS\',\'AUDSYS\',\'CTXSYS\',\'DBSFWUSER\',\'DBSNMP\',\'DIP\',\'DVF\',\'DVSYS\',\'GGSYS\',\'GSMADMIN_INTERNAL\',\'GSMCATUSER\',\'GSMROOTUSER\',\'GSMUSER\',\'LBACSYS\',\'MDDATA\',\
'MDSYS\',\'OJVMSYS\',\'OLAPSYS\',\'ORACLE_OCM\',\'ORDDATA\',\'ORDPLUGINS\',\'ORDSYS\',\'OUTLN\',\'REMOTE_SCHEDULER_AGENT\',\'SI_INFORMTN_SCHEMA\',\'SYS\',\'SYS$UMF\',\'SYSBACKUP\',\'SYSDG\',\'SYSKM\',
\'SYSRAC\',\'SYSTEM\',\'WMSYS\',\'XDB\',\'XS$NULL\'
正常使用全库导出时，可以排除掉数据库的原始用户数据，只导出应用用户数据（使用exclude参数）：
– 排除系统用户
expdp system/oracle directory=expdp_dir full=y exclude=schema:\"IN \(\'ANONYMOUS\',\'APEX_030200\',\'APEX_PUBLIC_USER\',\'APPQOSSYS\',\'CTXSYS\',\'DBSNMP\',\'DIP\',\'EXFSYS\',\'FLOWS_FILES\',\'MDDATA\',\'MDSYS\',\'MGMT_VIEW\',\'OLAPSYS\',\'ORACLE_OCM\',\'ORDDATA\',\'ORDPLUGINS\',\'ORDSYS\',\'OUTLN\',\'OWBSYS\',\'OWBSYS_AUDIT\',\'SCOTT\',\'SI_INFORMTN_SCHEMA\',\'SPATIAL_CSW_ADMIN_USR\',\'SPATIAL_WFS_ADMIN_USR\',\'SYS\',\'SYSMAN\',\'SYSTEM\',\'WMSYS\',\'XDB\',\'XS$NULL\'\)\" dumpfile=full.dmp logfile=full_exp.log
不要使用 include 参数。
执行导出前，为了保持数据静止，建议关闭job，关闭监听，重启数据库（如果是rac，建议只保留一个节点开启）：
lsnrctl stop
sqlplus / as sysdba
alter system set job_queue_processes=0 sid='*';
shu immediate
startup
记录当前数据库的无效对象：
select owner,object_name,object_type from dba_objects where status='IVALID';
在 oracle 用户下执行导出命令，可以根据实际情况开启并行度以及压缩等参数（如果是RAC，加上cluster=N）：
expdp system/oracle directory=expdp_dir full=y exclude=schema:\"IN \(\'ANONYMOUS\',\'APEX_030200\',\'APEX_PUBLIC_USER\',\'APPQOSSYS\',\'CTXSYS\',\'DBSNMP\',\'DIP\',\'EXFSYS\',\'FLOWS_FILES\',\'MDDATA\',\'MDSYS\',\'MGMT_VIEW\',\'OLAPSYS\',\'ORACLE_OCM\',\'ORDDATA\',\'ORDPLUGINS\',\'ORDSYS\',\'OUTLN\',\'OWBSYS\',\'OWBSYS_AUDIT\',\'SCOTT\',\'SI_INFORMTN_SCHEMA\',\'SPATIAL_CSW_ADMIN_USR\',\'SPATIAL_WFS_ADMIN_USR\',\'SYS\',\'SYSMAN\',\'SYSTEM\',\'WMSYS\',\'XDB\',\'XS$NULL\'\)\" dumpfile=full.dmp logfile=full_exp.log compression=all parallel=8
导出时间依赖于数据库的大小以及磁盘的IO性能，导出完成后查看日志是否存在错误。
2.3 全库导入
需要注意的点：
- 确保目标端字符集与源端保持一致，否则可能导致乱码。
- 确保存放数据文件的磁盘空间足够。
- 确保源端数据文件路径在目标端存在或者目标端配置OMF参数（手工创建表空间也可以）。
- 目标端存在数据的话，需要加上table_exists_action参数。
执行导入前，同样建议关闭job，关闭监听，重启数据库（如果是rac，建议只保留一个节点开启）：
lsnrctl stop
sqlplus / as sysdba
-- 设置OMF
alter system set db_create_file_dest='/oradata' sid='*';
-- 关闭job
alter system set job_queue_processes=0 sid='*';
-- 重启数据库
shu immediate
startup
在 oracle 用户下执行导入（如果是RAC，加上cluster=N，导入时可以选择全库导入，也可以导出指定用户）：
– 全库导入
impdp system/oracle directory=expdp_dir dumpfile=full.dmp logfile=full_imp.log full=y parallel=8 table_exists_action=truncate
如果导入过程中出现 ORA-38. 错误，代表目标库已存在，可以忽略这个错误。
2.4 收尾
当导入完成后，目标端执行无效对象重编译后，与源端进行比较：
sqlplus / as sysdba @?/rdbms/admin/utlrp.sql
打开监听，重启数据库，打开job：
lsnrctl start
sqlplus / as sysdba
shu immediate
startup
alter system set job_queue_processes=1000 sid='*';
至此，全库导出导入完成
3 用户导出导入
3.1 导出前准备
对于用户的导出导入，需要做一些导出前的准备工作，比如导出目录、表空间的创建等等。
3.1.1 检查被导出用户(源端)
select sum(bytes) from dba_segments where owner='TEST';
SUM(BYTES)
----------
7.648
– 查看是否有对象存在 SYSTEM/USERS 表空间（如果存在，移走在 system 表空间中的对象）
select * from dba_segments where owner='TEST' and tablespace_name in ('SYSTEM','USERS');
select * from dba_tables where owner='TEST' and tablespace_name in ('SYSTEM','USERS');
select * from dba_indexes where owner='TEST' and tablespace_name in ('SYSTEM','USERS');
-- 移动表: alter table TEST.tables move tablespace TEST;
– 查看用户对象的角色权限（如果存在，在目标端创建该角色）
select distinct grantee from dba_tab_privs where owner='TEST';
3.1.2 创建导出目录(源端和目标端)
set line200
col owner for a10
col directory_path for a100
select owner,directory_name,directory_path from dba_directories;
create directory EXPDP_DIR as '/expdp';
grant read,write on directory EXPDP_DIR to system;
3.1.3 无效对象记录(源端)
记录当前数据库的无效对象：
select owner,object_name,object_type from dba_objects where status='IVALID' and owner='TEST';
3.1.4 创建表空间(目标端)
数据泵的导出会包含用户和权限信息，因此不需要人工在目标端创建用户以及权限（除非有强迫症）。
– 查看需要创建的表空间
select distinct tablespace_name from dba_segments where owner='TEST';
– 获取所需创建表空间的ddl语句（此方法用于创建很多表空间时会很方便）
select distinct 'select dbms_metadata.get_ddl(''TABLESPACE'',' || chr(39) ||
tablespace_name || chr(39) || ') ddl FROM dual;' sql
from dba_tablespaces
where tablespace_name in (select distinct tablespace_name
from dba_segments
where owner = 'TEST')
order by 1;
---------------------------------------------------------------------------------------
select dbms_metadata.get_ddl('TABLESPACE','TEST') ddl FROM dual;
– 获取创建表空间的ddl语句
set long 2000000000 echo off feedback off heading off pagesize 0 linesize 30000 trimout on wrap on trimspool on termout off serveroutput off SQLPROMPT "SQL>"
col ddl for a3000 wrapped word
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
spool ctbs_create.sql
select dbms_metadata.get_ddl('TABLESPACE','TEST') ddl FROM dual;
spool off
– 获取脚本后修改对应的数据库文件目录（如果有数据文件目录的，需要修改成目标端的路径）
SQL>select dbms_metadata.get_ddl('TABLESPACE','TEST') ddl FROM dual;
CREATE TABLESPACE "TEST" DATAFILE
SIZE 104857600
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
SQL>spool off
注意：如果创建表空间时遇到 ORA-02494: invalid or missing maximum file size in MAXSIZE clause 则说明数据文件大小比 MAXSIZE 要大，只需要把对应的 MAXSIZE 改为 32767M。
3.2 导出导入
这里默认是源端和目标端不停库的情况下进行导出，如果是做迁移一类的，需要保持源端数据静止的，那就需要在源端和目标端都做以下操作：
alter system set job_queue_processes=0 sid='*';
保证数据静止
----------------------for single--------------------
lsnrctl stop
sqlplus / as sysdba
shutdown immediate
startup
----------------------------------------------------
----------------------for rac-----------------------
srvctl stop database -d TEST
srvctl start instance -d TEST -i TEST1 -o mount
srvctl stop listener
srvctl stop scan_listener
sqlplus / as sysdba
alter database open;
-----------------------------------------------------
3.2.1 导出(源端)
使用 parfile 的方式进行导出，每一行代表一个参数，可以省去很多转义的麻烦，如果是rac需要加上参数 cluster=n。
## 创建 parfile for TEST 导出
cat<<-\EOF>/home/oracle/TEST_EXP.par
dumpfile=TEST_EXP%U.DMP
directory=EXPDP_DIR
logfile=TEST_EXP.log
schemas=TEST
parallel=8
EOF
## 执行导出
expdp system/oracle parfile=/home/oracle/TEST_EXP.par
3.2.2 导出过程
[oracle@TEST:/expdp]$ expdp system/oracle parfile=/home/oracle/TEST_EXP.par
Export: Release 11.2.0.4.0 - Production on Wed Sep 21 11:04:20 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=/home/oracle/TEST_EXP.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9600 KB
. . exported "TEST"."TABLES" 6979 KB 27950 rows
. . exported "TEST"."TEST" 5 KB 1 rows
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/expdp/TEST_EXP01.DMP
/expdp/TEST_EXP02.DMP
/expdp/TEST_EXP03.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Sep 21 11:04:30 2022 elapsed 0 00:00:08
导出成功后，在源端 /expdp 目录下生成对应的 dmp 文件，将其拷贝至目标端即可。
3.2.3 导入(目标端)
并行度根据实际情况进行填写，如果是rac需要加上参数 cluster=n，参数 remap_schema 是为了修改目标端用户的名称：
## 创建 parfile for TEST 导入
cat<<-\EOF>/home/oracle/TEST_IMP.par
dumpfile=TEST_EXP%U.DMP
directory=EXPDP_DIR
logfile=TEST_IMP.log
remap_schema=TEST:oliver
parallel=8
EOF
## 执行导入
impdp system/oracle parfile=/home/oracle/TEST_IMP.par
3.2.4 导入过程
[oracle@orcl:/expdp]$ impdp system/oracle parfile=/home/oracle/TEST_IMP.par
Import: Release 11.2.0.4.0 - Production on Wed Sep 21 11:22:04 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** parfile=/home/oracle/TEST_IMP.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "OLIVER"."TABLES" 697.9 KB 2795 rows
. . imported "OLIVER"."TEST" 5 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Sep 21 11:22:08 2022 elapsed 0 00:00:03
对应数据是否准确，可以比对导出导入的对象行数。
3.3 导入后操作(目标端)
当导入完成后，目标端执行无效对象重编译后，与源端进行比较：
sqlplus / as sysdba @?/rdbms/admin/utlrp.sql
如果导出前关闭了JOB，现在需要打开JOB：
alter system set job_queue_processes=1000 sid='*';
至此，数据泵用户的导出导入完成。
4 表导出导入
数据泵按表导出，通常用于数据恢复、分区表数据归档以及坏块修复等等。
4.1 数据恢复
如果数据库未开启归档模式或者没有RMAN备份时，通常是使用数据泵进行备份。此时，如果表被误删，并且回收站无法找回，可以使用数据泵进行导入，找回部分数据（前提是存在数据泵导出备份）。
数据泵导出用户备份：
[oracle@TEST:/expdp]$ expdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXP.DMP logfile=TEST_EXP.log schemas=TEST
Export: Release 11.2.0.4.0 - Production on Sat Oct 8 10:56:14 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXP.DMP logfile=TEST_EXP.log schemas=TEST
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 960 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TEST"."TABLES" 697.9 KB 2795 rows
. . exported "TEST"."TEST" 5 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/expdp/TEST_EXP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Oct 8 10:56:22 2022 elapsed 0 00:00:07
模拟表删除：
sys@TEST> select count(0) from TEST.tables;
COUNT(0)
---------
2795
sys@TEST> drop table TEST.tables purge;
Table dropped.
sys@TEST> select count(0) from TEST.tables;
select count(0) from TEST.tables
*
ERROR at line 1:
ORA-00942: table or view does not exist
导入误删表：
[oracle@TEST:/expdp]$ impdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXP.DMP logfile=TEST_IMP.log tables=TEST.tables
Import: Release 11.2.0.4.0 - Production on Sat Oct 8 10:59:43 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXP.DMP logfile=TEST_IMP.log tables=TEST.tables
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TABLES" 697.9 KB 2795 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sat Oct 8 10:59:44 2022 elapsed 0 00:00:01
查询数据：
sys@TEST> select count(0) from TEST.tables;
COUNT(0)
----------
2795
误删表已恢复，真实环境可能会丢失部分数据。
4.2 分区表归档
分区表的数据通常比较大，需要定时进行数据归档。假设分区表按月分区，需要归档近1年的数据。
## 创建 parfile for TEST 表导出
cat<<-\EOF>/home/oracle/TEST_EXP.par
dumpfile=TEST_EXP%U.DMP
directory=EXPDP_DIR
logfile=TEST_EXP.log
tables=
TEST:TEST_P202101,
TEST:TEST_P202102,
TEST:TEST_P202103,
TEST:TEST_P202104,
TEST:TEST_P202105,
TEST:TEST_P202106,
TEST:TEST_P2027.
TEST:TEST_P2028.
TEST:TEST_P2029.
TEST:TEST_P202110,
TEST:TEST_P202111,
TEST:TEST_P202112
EOF
## 执行导出
[oracle@TEST:/expdp]$ expdp par/par parfile=/home/oracle/TEST_EXP.par
Export: Release 11.2.0.4.0 - Production on Sat Oct 8 11:15:00 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "PAR"."SYS_EXPORT_TABLE_01": par/******** parfile=/home/oracle/TEST_EXP.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 768 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "PAR"."TEST":"TEST_P202101" 5.859 KB 1 rows
. . exported "PAR"."TEST":"TEST_P202102" 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P202103" 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P202104" 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P202105" 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P202106" 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P2027. 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P2028. 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P2029. 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P202110" 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P202111" 5.867 KB 1 rows
. . exported "PAR"."TEST":"TEST_P202112" 5.867 KB 1 rows
Master table "PAR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PAR.SYS_EXPORT_TABLE_01 is:
/expdp/TEST_EXP01.DMP
Job "PAR"."SYS_EXPORT_TABLE_01" successfully completed at Sat Oct 8 11:15:08 2022 elapsed 0 00:00:07
分区归档数据导出后，可以导入到专门的历史数据归档库，并且删除生产库的归档数据：
alter table PAR.TEST truncate partition TEST_P202101;
alter table PAR.TEST truncate partition TEST_P202102;
alter table PAR.TEST truncate partition TEST_P202103;
alter table PAR.TEST truncate partition TEST_P202104;
alter table PAR.TEST truncate partition TEST_P202105;
alter table PAR.TEST truncate partition TEST_P202106;
alter table PAR.TEST truncate partition TEST_P2027.
alter table PAR.TEST truncate partition TEST_P2028.
alter table PAR.TEST truncate partition TEST_P2029.
alter table PAR.TEST truncate partition TEST_P202110;
alter table PAR.TEST truncate partition TEST_P202111;
alter table PAR.TEST truncate partition TEST_P202112;
检查索引是否需要重建（本地索引无需重建，全局索引需要重建）：
set line222 pages1000
SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index
FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index
FROM dba_ind_partitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status = 'UNUSABLE'
UNION ALL
SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index
FROM dba_ind_subpartitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status = 'UNUSABLE';
分区表数据归档完成。
4.3 坏块修复
如果数据库产生坏块，并且不幸的坏在表上，对应的表将无法访问，此时可以通过数据泵导出导入进行修复，并且跳过坏块。
模拟坏块：
eason@ORADB> !dd if=/dev/zero of=/oracle/oradata/ORADB/datafile/o1_mf_eason_kn3.2._.dbf bs=8192 conv=notrunc seek=1437 count=1
eason@ORADB> alter system flush buffer_cache;
System altered.
eason@ORADB> select count(0) from eason.hyj;
select count(0) from eason.hyj
*
ERROR at line 1:
ORA-07.: ORACLE data block corrupted (file # 8, block # 1437)
ORA-01110: data file 8: '/oracle/oradata/ORADB/datafile/o1_mf_eason_kn3.2._.dbf'
先将表设置为只读：
sys@TEST> alter table eason.hyj read only;
Table altered.
导出坏块表：
[oracle@TEST:/expdp]$ expdp system/oracle directory=EXPDP_DIR dumpfile=HYJ_EXP.DMP logfile=HYJ_EXP.log tables=eason.hyj
Export: Release 11.2.0.4.0 - Production on Sat Oct 8 11:28:12 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=EXPDP_DIR dumpfile=HYJ_EXP.DMP logfile=HYJ_EXP.log tables=eason.hyj
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "EASON"."HYJ" 8.862 MB 91204 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/expdp/HYJ_EXP.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sat Oct 8 11:28:18 2022 elapsed 0 00:00:06
导入坏块表为新表：
[oracle@TEST:/expdp]$ impdp system/oracle directory=EXPDP_DIR dumpfile=HYJ_EXP.DMP logfile=HYJ_IMP.log remap_table=eason.hyj:hyj20221004
Import: Release 11.2.0.4.0 - Production on Sat Oct 8 11:30:06 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=EXPDP_DIR dumpfile=HYJ_EXP.DMP logfile=HYJ_IMP.log remap_table=eason.hyj:hyj20221004
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "EASON"."HYJ20221004" 8.862 MB 91204 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-38.: Object type INDEX:"EASON"."I_HYJ" already exists
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"EASON"."I_HYJ" already exists
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sat Oct 8 11:30:07 2022 elapsed 0 00:00:01
此时，再次新表数据，存在部分数据丢失:
sys@TEST> select count(0) from dba_objects;
COUNT(0)
----------
97.
sys@TEST> select count(0) from eason.hyj20221004;
COUNT(0)
----------
91204
通过新表数据，确认丢失数据是否重要，如果可以忽略则可以直接导入覆盖坏块表：
[oracle@TEST:/expdp]$ impdp system/oracle directory=EXPDP_DIR dumpfile=HYJ_EXP.DMP logfile=HYJ_IMP.log tables=eason.hyj table_exists_action=replace
Import: Release 11.2.0.4.0 - Production on Sat Oct 8 11:37:59 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=EXPDP_DIR dumpfile=HYJ_EXP.DMP logfile=HYJ_IMP.log tables=eason.hyj table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "EASON"."HYJ" 8.862 MB 91204 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sat Oct 8 11:38:00 2022 elapsed 0 00:00:01
再次查询坏块表：
sys@TEST> select count(0) from eason.hyj;
COUNT(0)
----------
91204
sys@TEST> select READ_ONLY from dba_tables where owner='EASON' and table_name='HYJ';
REA
---
NO
sys@TEST> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
8 1437 1 0 ALL ZERO
此时已经可以访问表，但是坏块还是存在的，可以通过其他方式进行修复，这里不做介绍。
5 并行和压缩
数据泵提供了并行和压缩功能，用于提升导出效率以及减少空间占用，配合使用效果更佳。
## compression（压缩）
compression=[all|data_only|metadata_only|none]
## parallel（并行度，默认为1，该值应小于等于 dump 文件数量）
parallel=integer
## dumpfile（导出的文件名）
dumpfile=[directory_object:]file_name[,...]
## filesize（导出每个 dump 文件的最大限制）
filesize=integer[b|kb|mb|gb|tb]
下面通过一些实验来演示如何使用参数以及小技巧。
5.1 并行参数
使用并行度参数时，需要注意的是 dumpfile 参数必须得使用 % 自动分片，最简单的格式就是 %U，也可以配合 filesize 参数来控制每个dump文件大小的上限。
dumpfile=TEST_EXP%U.DMP parallel=4
很多人认为，在数据泵使用过程中，为了提升速度使用并行，不就是加个 parallel 参数嘛，但实际导入过程中创建索引依然很慢，该如何利用该参数做到真正提升速度呢？
准备测试数据：
create table TEST.tabs as select * from dba_objects;
create index TEST.idx_tabs_objectid on TEST.tabs(object_id);
导出测试用户：
[oracle@TEST:/expdp]$ time expdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_EXP.log schemas=TEST parallel=4
Export: Release 11.2.0.4.0 - Production on Wed Oct 12 12:57:31 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_EXP.log schemas=TEST parallel=4
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 19. MB
. . exported "TEST"."TABS" 8.868 MB 97. rows
. . exported "TEST"."TABLES" 697.9 KB 2795 rows
. . exported "TEST"."TEST" 5 KB 1 rows
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/expdp/TEST_EXP01.DMP
/expdp/TEST_EXP02.DMP
/expdp/TEST_EXP03.DMP
/expdp/TEST_EXP04.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Oct 12 12:57:36 2022 elapsed 0 00:00:05
real 0m5.788s
user 0m0.015s
sys 0m0.011s
导入测试用户：
[oracle@TEST:/expdp]$ time impdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_IMP.log schemas=TEST parallel=4 sqlfile=imppara.sql
Import: Release 11.2.0.4.0 - Production on Wed Oct 12 12:57:42 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_IMP.log schemas=TEST parallel=4 sqlfile=imppara.sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Wed Oct 12 12:57:43 2022 elapsed 0 00:00:01
real 0m1.408s
user 0m0.014s
sys 0m0.006s
查看导入sqlfile中创建索引的并行度为 1：
-- CONNECT TEST
CREATE INDEX "TEST"."IDX_TEST_ID" ON "TEST"."TEST" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 27.83645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST" PARALLEL 1 ;
ALTER INDEX "TEST"."IDX_TEST_ID" NOPARALLEL;
CREATE INDEX "TEST"."IDX_TABLES_OWNER" ON "TEST"."TABLES" ("OWNER")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 27.83645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST" PARALLEL 1 ;
ALTER INDEX "TEST"."IDX_TABLES_OWNER" NOPARALLEL;
CREATE INDEX "TEST"."IDX_TABS_OBJECTID" ON "TEST"."TABS" ("OBJECT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 27.83645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "TEST"."IDX_TABS_OBJECTID" NOPARALLEL;
如果导入对象有几百G，不开并行度创建索引将会很慢。
正确姿势：排除索引单独导入大对象，导入后再并行创建索引：
time impdp TEST/oracle directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_IMP.log tables=tabs parallel=4 exclude=index
## 导入成功后，再开并行创建索引，并行度可以根据CPU来调整
create index TEST.idx_tabs_objectid on TEST.tabs(object_id) parallel 40;
alter index TEST.idx_tabs_objectid noparallel;
5.2 压缩参数
数据泵提供的压缩参数分为 4 个选项：
COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
- ALL: 元数据和数据都会被压缩；
- DATA_ONLY: 只压缩数据；
- METADATA_ONLY: 只压缩元数据（默认）；
- NONE: 不压缩任何数据；
不考虑其他情况，下面通过导出同一个用户来测试导出的速度以及压缩比！
5.2.1 NONE
[oracle@TEST:/expdp]$ time expdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXP.DMP logfile=TEST_EXP.log schemas=TEST
Export: Release 11.2.0.4.0 - Production on Wed Oct 12 13:31:53 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXP.DMP logfile=TEST_EXP.log schemas=TEST
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 19. MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TEST"."TABS" 8.868 MB 97. rows
. . exported "TEST"."TABLES" 697.9 KB 2795 rows
. . exported "TEST"."TEST" 5 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/expdp/TEST_EXP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Oct 12 13:31:56 2022 elapsed 0 00:00:02
real 0m3.221s
user 0m0.013s
sys 0m0.009s
5.2.2 ALL
[oracle@TEST:/expdp]$ time expdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXPALL.DMP logfile=TEST_EXPALL.log schemas=TEST compression=all
Export: Release 11.2.0.4.0 - Production on Wed Oct 12 13:25:35 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXPALL.DMP logfile=TEST_EXPALL.log schemas=TEST compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 19. MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TEST"."TABS" 1019. KB 97. rows
. . exported "TEST"."TABLES" 78.56 KB 2795 rows
. . exported "TEST"."TEST" 4.671 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/expdp/TEST_EXPALL.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Oct 12 13:25:38 2022 elapsed 0 00:00:02
real 0m3.9.
user 0m0.015s
sys 0m0.009s
5.2.3 DATA_ONLY
[oracle@TEST:/expdp]$ time expdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXPDATA.DMP logfile=TEST_EXPDATA.log schemas=TEST compression=data_only
Export: Release 11.2.0.4.0 - Production on Wed Oct 12 13:26:23 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXPDATA.DMP logfile=TEST_EXPDATA.log schemas=TEST compression=data_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 19. MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TEST"."TABS" 1019. KB 97. rows
. . exported "TEST"."TABLES" 78.56 KB 2795 rows
. . exported "TEST"."TEST" 4.671 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/expdp/TEST_EXPDATA.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Oct 12 13:26:26 2022 elapsed 0 00:00:02
real 0m3.9.
user 0m0.009s
sys 0m0.011s
5.2.4 METADATA_ONLY
[oracle@TEST:/expdp]$ time expdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXPMETADATA.DMP logfile=TEST_EXPMETADATA.log schemas=TEST compression=metadata_only
Export: Release 11.2.0.4.0 - Production on Wed Oct 12 13:27:33 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXPMETADATA.DMP logfile=TEST_EXPMETADATA.log schemas=TEST compression=metadata_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 19. MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TEST"."TABS" 8.868 MB 97. rows
. . exported "TEST"."TABLES" 697.9 KB 2795 rows
. . exported "TEST"."TEST" 5 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/expdp/TEST_EXPMETADATA.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Oct 12 13:27:36 2022 elapsed 0 00:00:02
real 0m2.928s
user 0m0.012s
sys 0m0.010s
[oracle@TEST:/expdp]$ du -sh *
1.2M TEST_EXPALL.DMP
1.2M TEST_EXPDATA.DMP
9.9M TEST_EXP.DMP
9.9M TEST_EXPMETADATA.DMP
虽然导出的数据量比较小，但是通过以上测试结果还是比较明显，ALL 和 DATA_ONLY 的选项导出速度以及压缩比相差不多；METADATA_ONLY 的选项导出的时间较快，导出的文件相比不开压缩来说相差却不大。只用压缩参数无法保证时间和空间的同时提升，
正确姿势：配合并行参数一起使用，将会大大提升。
[oracle@TEST:/expdp]$ time expdp system/oracle directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_EXP.log schemas=TEST parallel=4 compression=data_only
Export: Release 11.2.0.4.0 - Production on Wed Oct 12 13:35:55 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_EXP.log schemas=TEST parallel=4 compression=data_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 19. MB
Processing object type SCHEMA_EXPORT/USER
. . exported "TEST"."TABLES" 78.56 KB 2795 rows
. . exported "TEST"."TEST" 4.671 KB 1 rows
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
. . exported "TEST"."TABS" 1019. KB 97. rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/expdp/TEST_EXP01.DMP
/expdp/TEST_EXP02.DMP
/expdp/TEST_EXP03.DMP
/expdp/TEST_EXP04.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Oct 12 13:35:58 2022 elapsed 0 00:00:02
real 0m2.894s
user 0m0.011s
sys 0m0.011s
[oracle@TEST:/expdp]$ ll -lrth TEST_EXP0*
-rw-r----- 1 oracle oinstall 92K Oct 12 13:35 TEST_EXP02.DMP
-rw-r----- 1 oracle oinstall 36K Oct 12 13:35 TEST_EXP04.DMP
-rw-r----- 1 oracle oinstall 44K Oct 12 13:35 TEST_EXP03.DMP
-rw-r----- 1 oracle oinstall 1.0M Oct 12 13:35 TEST_EXP01.DMP
导出的数据量越大，效果越明显
6 table_exists_action
关于 Oracle 数据泵导入时目标端已存在表时，数据如何处理的问题，可以使用 table_exists_action 参数：
## table_exists_action（导入数据处理方式）
table_exists_action=[SKIP | APPEND | TRUNCATE | REPLACE]
- SKIP：跳过当前表进行下一个。如果 CONTENT 参数设置为 DATA_ONLY，不生效。
- APPEND：从源加载数据并保持现有数据不变。
- TRUNCATE：删除现有表数据，然后从源加载数据。
- REPLACE：删除现有表，然后从源创建并加载数据。如果 CONTENT 参数设置为 DATA_ONLY，不生效。
在使用 SKIP 和 APPEND 参数时，导入时不会对导入对象做任何 DDL 操作，不会修改现有的表相关对象，例如索引、授权、触发器和约束；而 TRUNCATE 参数会先清空表数据，然后再导入数据；至于 REPLACE 参数，则会先 drop table *** cascade constraints purge 后再重新创建该对象。
7 数据泵转换分区表
在Oracle数据库中，分区表的使用是很常见的，使用数据泵也可以进行普通表到分区表的转换，虽然实际场景应用的不多。
7.1 创建测试表
sys@TEST> create table TEST.tabs as select * from dba_objects;
Table created.
sys@TEST> create index TEST.idx_tabs_objectid on TEST.tabs(object_id);
Index created.
sys@TEST> select count(0) from TEST.tabs;
COUNT(0)
----------
98.
7.2 记录无效对象
当做一些表结构的改变时，会引起对象无效，一般都需要在开始前记录无效对象情况。
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects t
where t.status = 'INVALID' order by 1;
7.3 表空间使用率
由于转分区需要重命名原始表和索引进行回退保留方案，再新建新分区表以及索引，意味着需要双倍的表和索引空间，因此提前增加数据文件是必要的。
set line200 pagesize10000
SELECT v.tbsname,
v.tbstype,
round(v.total_bytes / 1024 / 1024 / 1024,
2) total_gb,
round(v.left_bytes / 1024 / 1024 / 1024,
2) left_gb,
round(v.used_percent,
2) used_percent,
round(v.extent_total / 1024 / 1024 / 1024,
2) max_extent_gb,
v.used_extent_able_percent,
v.count_file
FROM (SELECT a.tablespace_name tbsname,
'DATAFILE' tbstype,
a.count_file,
(1 - b.free / a.total) * 100 used_percent,
round((1 - (b.free + a.extent_total) / a.maxtotal) * 100,
2) used_extent_able_percent,
a.total total_bytes,
b.free left_bytes,
a.extent_total,
CASE
WHEN a.extent_able = 1 THEN
'YES'
WHEN a.extent_able = 0 THEN
'NO'
ELSE
'MIXED'
END extent_able,
max_fragment_bytes
FROM (SELECT tablespace_name,
SUM(bytes) total,
SUM(decode(autoextensible,
'NO',
bytes,
'YES',
(CASE
WHEN maxbytes > bytes THEN
maxbytes
ELSE
bytes
END))) maxtotal,
SUM(decode(autoextensible,
'YES',
(CASE
WHEN maxbytes > bytes THEN
maxbytes - bytes
ELSE
0
END),
0)) extent_total,
AVG(decode(autoextensible,
'YES',
1,
'NO',
0)) extent_able,
COUNT(file_name) count_file
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
SUM(nvl(bytes,
0)) free,
MAX(bytes) max_fragment_bytes
FROM dba_free_space
GROUP BY tablespace_name) b,
dba_tablespaces c
WHERE a.tablespace_name = c.tablespace_name
AND c.tablespace_name = b.tablespace_name(+)
ORDER BY used_extent_able_percent DESC) v;
7.4 设置表只读
为了保持表数据静止，可以设置表只读，更极端的可以停机进行作业（与业务沟通好之后执行）：
alter table TEST.tabs read only;
7.5 导出原表数据
为了新分区表可以并行创建索引，还有本地索引的需求，建议只导出表数据即可：
[oracle@TEST:/expdp]$ expdp TEST/oracle directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_EXP.log tables=tabs parallel=4 content=data_only
Export: Release 11.2.0.4.0 - Production on Thu Oct 13 11:55:26 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": TEST/******** directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_EXP.log tables=tabs parallel=4 content=data_only
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11 MB
. . exported "TEST"."TABS" 8.869 MB 98. rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/expdp/TEST_EXP01.DMP
/expdp/TEST_EXP02.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Thu Oct 13 11:55:27 2022 elapsed 0 00:00:01
7.6 重命名原表和索引
导出完成后，由于需要创建新的分区表结构，并且为了备份原表，因此重命名原表以及相关对象（索引、约束、触发器等）：
sys@ORADB 2022-10-13 11:55:46> alter table TEST.tabs rename to tabs_bak;
Table altered.
sys@ORADB 2022-10-13 11:55:47> alter index TEST.idx_tabs_objectid rename to idx_tabs_objectid_bak;
Index altered.
7.7 创建分区表结构
根据原表结构编写新分区表的表结构并提前创建好，这里为了方便实验，使用的是 dba_objects 的表结构。：
sys@TEST 2023-8-13 11:55:47> create table TEST.tabs
partition by range (object_id)
(partition p1 values less than (1000),
partition p2 values less than (5000),
partition p3 values less than (10000),
partition p4 values less than (15000),
partition p5 values less than (20000),
partition p6 values less than (25000),
partition p7 values less than (maxvalue))
as select * from dba_objects where 1=0;
Table created.
7.8 导入分区表数据
使用导出的DUMP，将表数据导入到新的分区表中，由于分区表结构已创建，所以只需追加数据即可（APPEND）：
[oracle@TEST:/expdp]$ impdp TEST/oracle directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_EXP.log tables=tabs parallel=4 table_exists_action=append
Import: Release 11.2.0.4.0 - Production on Thu Oct 13 11:56:55 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": TEST/******** directory=EXPDP_DIR dumpfile=TEST_EXP%U.DMP logfile=TEST_EXP.log tables=tabs parallel=4 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TABS" 8.869 MB 98. rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at Thu Oct 13 11:56:58 2022 elapsed 0 00:00:03
7.9 创建新分区表索引
这里因为没有导入索引，所以可以开并行度创建本地索引，如果是主键或者唯一索引则只能创建为全局索引：
sys@TEST 2023-8-13 11:57:12> create index TEST.idx_TEST_objectid on TEST.tabs(object_id) local parallel 8;
Index created.
sys@TEST 2023-8-13 11:57:13> alter index TEST.idx_TEST_objectid noparallel;
Index altered.
7.10 核对新分区表数据
数据已经成功导入到新分区表，并且按照规则进行分布：
sys@TEST 2023-8-13 11:57:14> SELECT D.TABLE_OWNER,D.TABLE_NAME,D.PARTITION_NAME FROM DBA_TAB_PARTITIONS d WHERE d.table_name='TABS';
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
TEST TABS P1
TEST TABS P2
TEST TABS P3
TEST TABS P4
TEST TABS P5
TEST TABS P6
TEST TABS P7
7 rows selected.
sys@TEST 2023-8-13 11:57:50> select count(*) from TEST.TABS partition(P1);
COUNT(*)
----------
997
sys@TEST 2023-8-13 11:59:15> select count(*) from TEST.TABS partition(P3);
COUNT(*)
----------
4814
sys@TEST 2023-8-13 11:59:17> select count(*) from TEST.TABS partition(P4);
COUNT(*)
----------
4618
sys@TEST 2023-8-13 11:59:19> select count(*) from TEST.TABS partition(P7);
COUNT(*)
----------
67916
7.11 检查索引是否需要重建
set line200 pages1000
SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index
FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index
FROM dba_ind_partitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status = 'UNUSABLE'
UNION ALL
SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index
FROM dba_ind_subpartitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status = 'UNUSABLE';
7.12 检查并行度
set line200 pages1000
col table_name format a35
col index_name format a35
select substr(t.owner,1,15) Owner ,
t.table_name ,
substr(trim(t.degree),7. Degree ,
substr(trim(t.instances),9. Instances,
i.index_name ,
substr(trim(i.degree),7. Degree ,
substr(trim(i.instances),9. Instances
from all_indexes i,
all_tables t
where ( trim(i.degree) != trim(t.degree) or
trim(i.instances) != trim(t.instances) ) and
i.owner = t.owner and
i.table_name = t.table_name;
7.13 核对无效对象
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects t
where t.status = 'INVALID' order by 1;
7.14 收集统计值信息
sys@TEST > exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'TABS',method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '40');
PL/SQL procedure successfully completed.
至此，转分区表已完成。
8 数据泵传输表空间
随着现在数据库使用越来越多，客户对于数据库的规范要求越来越高。目前基本都是一个业务对应一个或多个表空间，这样做的好处在于可以数据隔离，便于管理，升级迁移时也可以避免导出一大堆用户，只需要使用参数 transport_tablespaces 即可，达到一次性迁移整个表空间的目的，也即是传输表空间。
传输表空间技术始于 Oracle 9i，不论是数据字典管理的表空间还是本地管理的表空间，都可以使用传输表空间技术；传输表空间不需要在源数据库和目标数据库之间具有同样的DB_BLOCK_SIZE 块大小，使用传输表空间迁移数据比使用数据导入导出工具迁移数据的速度要快，这是因为传输表空间只是复制包含实际数据的数据文件到目标数据库的指定位置，而使用数据导入导出工具则是传输表空间对象的元数据到目标数据库。
传输表空间支持使用 SQL*PLUS，RMAN，Data Pump工具实现。
8.1 测试准备
本文演示的是同平台迁移，如果是跨平台需要注意字节序，可以通过以下 sql 查询：
col platform_name for a30
select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
Linux x86 64-bit Little
– 查看支持的平台转换
select * from v$transportable_platform order by platform_name;
– 在源库在数据导出后使用 rman 进行转换
convert tablespace tran_TEST to platform 'Microsoft Windows x86 64-bit' format '/tmp/%N_%f';
创建测试数据：
sys@TEST 2023-8-26 14:08:13> create tablespace tran_TEST datafile size 1M autoextend on;
Tablespace created
sys@TEST 2023-8-26 14:08:27> create user tran_TEST identified by oracle;
User created.
sys@TEST 2023-8-26 14:08:31> grant dba to tran_TEST;
Grant succeeded.
sys@TEST 2023-8-26 14:08:34> create table tran_TEST.tabs tablespace tran_TEST as select * from dba_objects;
Table created.
sys@TEST 2023-8-26 14:08:38> create index tran_TEST.idx_tabs_objectid on tran_TEST.tabs(object_id) tablespace tran_TEST;
Index created.
sys@TEST 2023-8-26 14:08:51> select count(0) from tran_TEST.tabs;
COUNT(0)
----------
91301
这里需要注意：传输表空间导入不会创建用户，所以目标端需要自行创建用户。
一键获取源端用户创建语句：
select 'create user ' || t.username || ' identified by values ' || chr(39) ||
u.password || chr(39) || ' default tablespace ' ||
t.default_tablespace || ' profile ' || p.name || ' Temporary TABLESPACE '|| TEMPORARY_TABLESPACE ||';' create_user_withoutpass
from dba_users t, sys.user$ u, sys.profname$ p, sys.user_astatus_map m
where t.user_id = u.user#
and u.resource$ = p.profile#
and u.astatus = m.status#
and t. username = 'TRAN_TEST';
– 目标端执行
create user TRAN_TEST identified by values 'C3B4B21CC5FF337D' default tablespace USERS profile DEFAULT Temporary TABLESPACE TEMP;
8.2 传输表空间
8.2.1 设置表空间只读
在执行导出前，为保证数据静止，设置表空间只读(为了安全，可以把所有用户也设置只读)：
– 检查是否违反自包含
sys@TEST 2023-8-26 14:26:50> exec sys.dbms_tts.transport_set_check('TRAN_TEST',true);
PL/SQL procedure successfully completed.
sys@TEST 2023-8-26 14:27:02> select * from sys.transport_set_violations;
no rows selected
sys@TEST 2023-8-26 14:12:16> alter tablespace tran_TEST read only;
Tablespace altered.
sys@TEST 2023-8-26 14:27:03> select tablespace_name,status from dba_tablespaces where tablespace_name='TRAN_TEST';
TABLESPACE_NAME STATUS
------------------------------ ---------
TRAN_TEST READ ONLY
sys@TEST 2023-8-26 14:12:24> select name,file#,checkpoint_change#,status,enabled from v$datafile where enabled like '%ONLY%';
NAME FILE# CHECKPOINT_CHANGE# STATUS ENABLED
-------------------------------------------------------------------------------- --------
/oracle/oradata/ORADB/datafile/o1_mf_tran_tes_kokmpvpb_.dbf 9 2963465 ONLINE READ ONLY
8.2.2 导出表空间元数据
[oracle@TEST:~]$ expdp system/oracle dumpfile=TRAN_TEST_EXP.DMP directory=EXPDP_DIR transport_tablespaces=tran_TEST transport_full_check=y logfile=TRAN_TEST_EXP.log
Export: Release 11.2.0.4.0 - Production on Wed Oct 26 14:29:39 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=TRAN_TEST_EXP.DMP directory=EXPDP_DIR transport_tablespaces=tran_TEST transport_full_check=y logfile=TRAN_TEST_EXP.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/expdp/TRAN_TEST_EXP.DMP
******************************************************************************
Datafiles required for transportable tablespace TRAN_TEST:
/oracle/oradata/ORADB/datafile/o1_mf_tran_luc_kokmpvpb_.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Oct 26 14:29:57 2022 elapsed 0 00:00:17
8.2.3 拷贝文件
将导入的dmp文件以及所需的数据文件拷贝至目标端：
[oracle@TEST:/expdp]$ scp /expdp/TRAN_TEST_EXP.DMP 10.211.55.101:/expdp
oracle@10.211.55.101's password:
TRAN_TEST_EXP.DMP 100% 8.B 15.6MB/s 00:00
[oracle@TEST:/expdp]$ scp /oracle/oradata/ORADB/datafile/o1_mf_tran_luc_kokmpvpb_.dbf 10.211.55.101:/oradata/orcl
oracle@10.211.55.101's password:
o1_mf_tran_luc_kokmpvpb_.dbf 100% 14MB 91.5MB/s 00:00
8.2.4 导入表空间
目标端创建用户：
SQL> create user TRAN_TEST identified by values 'C3B4B21CC5FF337D' default tablespace USERS profile DEFAULT Temporary TABLESPACE TEMP;
User created.
目标端执行导入：
[oracle@orcl:~]$ impdp system/oracle dumpfile=TRAN_TEST_EXP.DMP directory=EXPDP_DIR transport_datafiles=/oradata/orcl/o1_mf_tran_luc_kokmpvpb_.dbf
Import: Release 11.2.0.4.0 - Production on Wed Oct 26 14:44:03 2022
Copyright (c) 8., 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TRAN_TEST_EXP.DMP directory=EXPDP_DIR transport_datafiles=/oradata/orcl/o1_mf_tran_luc_kokmpvpb_.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Oct 26 14:44:05 2022 elapsed 0 00:00:01
8.2.5 验证数据
SQL> set line200
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'TRAN_TEST';
TABLESPACE_NAME STATUS
------------------------------ ---------
TRAN_TEST READ ONLY
SQL> select owner,table_name,tablespace_name from dba_tables where owner = 'TRAN_TEST';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TRAN_TEST TABS TRAN_TEST
SQL> select count(0) from tran_TEST.tabs;
COUNT(0)
----------
91301
8.2.6 恢复读写
确认没有问题之后，将源端和目标端的表空间都改为读写：
SQL> alter tablespace TRAN_TEST read write;
Tablespace altered.
至此，传输表空间迁移完成。
9 EXPDP/IMPDP生产导出导入案例
9.1 检查
9.1.1 检查对象信息
检查对象情况,并记录，导入后和源库比较
select owner,object_type,count(*) from dba_objects where owner='&ownername' group by owner,object_type order by owner,object_type;
9.1.2 检查是否存在使用SYSTEM,USERS表空间的情况
select * from dba_segments where owner='&ownername' and tablespace_name in ('SYSTEM','USERS');
如果存在有存储在system和users表空间的情况需要移除命令如下
移动表: alter table 账户.表名 move tablespace 表空间名;
9.1.3 检查无效对象记录
select owner,object_name,object_type from dba_objects where status='IVALID' and owner='&ownername';
9.1.4 检查表空间分布情况
select tablespace_name,sum(bytes)/1024/1024 MB from dba_segments WHERE owner='&ownername' group by tablespace_name;
9.2 获取源库建表空间语句
spool itpux_tbs_create_ddl.sql set long 200000 pagesize 0 head off verify off feedback off linesize 200 select dbms_metadata.get_ddl('TABLESPACE','RADAR') from dual; spool off;
如果目标库已经有同名表空间可以省略这一步，需要注意空间大小。
如果目标库没有同名表空间，也不想在把数据存在原来名字的表空间，可以在impdp导入时添加参数remap_tablespace指定源表空间名和新表空间名的对应关系
9.3 在目标库创建表空间
由上面获得的sql语句创建
9.4 源端执行导出dmp文件，并传送到目标端
9.5 目标库导入
9.5.1 禁止自动维护任务
execute dbms_auto_task_admin.disable;
9.5.2 建立目录并授权
9.5.3 导入数据之前先把索引语句、约束语句导出来，统计信息不用导
impdp system/oracle directory=dump_dir schemas=USER01,USER02 dumpfile=expdp_full_xxx.01.dmp,expdp_full_xxx.02.dmp,expdp_full_xxx.03.dmp logfile=impxxx.log full=y parallel=2 include=index,constraint sqlfile=indexddl.sql
此条语句执行后会生成indexddl.sql文件，导出后修改这个文件里面的parallel 值，根据实际情况设并行度，不要超过CPU核数，等数据导完后建索引和约束
9.5.4 导入数据（索引、约束、统计信息不导入）
nohup impdp system/oracle directory=dump_dir dumpfile=expdp_full_xxx.01.dmp,expdp_full_xxx.02.dmp,expdp_full_xxx.03.dmp logfile=impxxx.log full=y parallel=2 exclude=index,constraint,statistics &
9.5.5 创建索引和约束
indexddl.sql 利用之前生成的这个文件创建索引和约束
9.5.6 收集统计信息
vi stats.sql
begin
dbms_stats.gather_database_stats;
end;
/
nohup sqlplus "/as sysdba"@stats.sql &
9.5.7 无效对象编译
@?/rdbms/admin/utlrp.sql
select * from dba_objects where status <> 'VALID' and owner in ('','')
9.5.8 验证数据
select owner,object_type,count(*) from dba_objects where owner in ('USER01','USER02') group by owner,object_type;
9.5.9 收尾：开启自动维护任务
execute dbms_auto_task_admin.enable;
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
