☘️博主介绍☘️：
✨又是一天没白过，我是奈斯，DBA一名✨
✌✌️擅长Oracle、MySQL、SQLserver、Linux，也在扩展大数据方向的知识面✌✌️
❣️❣️❣️大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注❣️❣️❣️
今天作为expdp/impdp数据泵的最后一篇文章，这篇文章所讲的技能也是非常重要的。在Oracle数据库中，新建实例往往伴随着一系列系统用户的自动生成，如sys、system、scott等。然而，在进行 全库full=y导入 时，若这些系统用户也被 一并导入 ，往往会引发大量的ORA-错误，这样通过日志排查问题时就会有许多干扰。因此，在进行全库迁移时，我们必须排除这些系统用户的导入。接下来，就让我们深入今天的主题，探讨如何通过expdp/impdp实现高效且精准的全库迁移。
关于逻辑迁移工具全部的篇幅介绍，四篇的内容分别如下：
- 第一篇：一文搞清exp/imp逻辑迁移工具的用法
- 第二篇：一文搞清expdp/impdp逻辑迁移工具的用法
- 第三篇：expdp/impdp轻松完成某个生产用户从GBK到UTF8编码的迁移
- 第四篇：expdp/impdp高效完成全部生产用户的全库迁移（当前篇）
目录
expdp/impdp全库导入的应用场景：
1、迁移字符集。比如将全库用户从GBK字符集迁移到UTF8编码。
2、大版本升级。可以通过全库导入完成大版本升级。
3、测试环境恢复。通过全量备份完成测试环境的数据导入。
expdp/impdp全库导入注意事项：
1）通过impdp导入时，指定sqlfile参数可以显示执行的DDL语句。加上这个参数后就不会进行数据导入了，只会根据导入的dmp文件，将dmp文件涉及到的DDL语句输出到sqlfile指定的文件中，然后筛选出来业务表空间进行手动创建。
2）排除导入的系统用户，因为系统用户是按照实例默认创建的所以导入时需要排除。在每个大版本中系统用户都不一样，所以需要根据具体的大版本而排除。
Oracle11g和19c环境下的默认系统用户：
Oracle 11g单机环境下的系统用户：新建了一个11g的实例，可以看到默认11g的实例有30个系统用户，所以在导入时就要排除它们。
SQL> select username,to_char(CREATED,'yyyy-mm-dd hh24:mi:ss') from dba_users order by CREATED desc; USERNAME TO_CHAR(CREATED,'YYYY-MM-DDHH24:MI:SS') ------------------------------- ------------------------------------------------------------- SCOTT 2013-08-24 12:04:21 OWBSYS_AUDIT 2013-08-24 12:03:41 OWBSYS 2013-08-24 12:03:40 APEX_PUBLIC_USER 2013-08-24 11:58:27 FLOWS_FILES 2013-08-24 11:58:27 APEX_030200 2013-08-24 11:58:27 MGMT_VIEW 2013-08-24 11:57:53 SYSMAN 2013-08-24 11:56:19 SPATIAL_CSW_ADMIN_USR 2013-08-24 11:56:11 SPATIAL_WFS_ADMIN_USR 2013-08-24 11:56:08 MDDATA 2013-08-24 11:53:28 OLAPSYS 2013-08-24 11:52:40 ORDDATA 2013-08-24 11:49:40 ORDSYS 2013-08-24 11:49:40 MDSYS 2013-08-24 11:49:40 ORDPLUGINS 2013-08-24 11:49:40 SI_INFORMTN_SCHEMA 2013-08-24 11:49:40 XS$NULL 2013-08-24 11:49:29 ANONYMOUS 2013-08-24 11:47:55 XDB 2013-08-24 11:47:55 CTXSYS 2013-08-24 11:47:37 EXFSYS 2013-08-24 11:47:27 WMSYS 2013-08-24 11:43:26 APPQOSSYS 2013-08-24 11:43:00 DBSNMP 2013-08-24 11:42:59 ORACLE_OCM 2013-08-24 11:39:34 DIP 2013-08-24 11:38:58 OUTLN 2013-08-24 11:37:43 SYS 2013-08-24 11:37:40 SYSTEM 2013-08-24 11:37:40 30 rows selected.
Oracle 19c单机环境下的系统用户：新建了一个19c的实例，可以看到默认19c的实例有36个系统用户，所以在导入时就要排除它们。
SQL> select username,to_char(CREATED,'yyyy-mm-dd hh24:mi:ss') from dba_users order by CREATED desc; USERNAME TO_CHAR(CREATED,'YY --------------------------------- ---------------------------------------------------------------------------------- DVF 2019-04-17 02:02:47 DVSYS 2019-04-17 02:02:47 LBACSYS 2019-04-17 02:02:15 MDDATA 2019-04-17 01:53:39 OLAPSYS 2019-04-17 01:52:56 SI_INFORMTN_SCHEMA 2019-04-17 01:48:37 ORDDATA 2019-04-17 01:48:37 ORDPLUGINS 2019-04-17 01:48:37 MDSYS 2019-04-17 01:48:37 ORDSYS 2019-04-17 01:48:36 CTXSYS 2019-04-17 01:46:56 OJVMSYS 2019-04-17 01:42:57 WMSYS 2019-04-17 01:40:04 ANONYMOUS 2019-04-17 01:16:53 XDB 2019-04-17 01:16:53 GGSYS 2019-04-17 01:15:01 GSMCATUSER 2019-04-17 01:14:57 APPQOSSYS 2019-04-17 01:14:37 DBSNMP 2019-04-17 01:14:35 SYS$UMF 2019-04-17 01:10:05 ORACLE_OCM 2019-04-17 01:04:04 DBSFWUSER 2019-04-17 01:02:55 REMOTE_SCHEDULER_AGENT 2019-04-17 01:02:54 XS$NULL 2019-04-17 01:02:44 DIP 2019-04-17 01:02:17 GSMROOTUSER 2019-04-17 01:02:03 GSMADMIN_INTERNAL 2019-04-17 01:02:02 GSMUSER 2019-04-17 01:02:02 OUTLN 2019-04-17 00:56:39 SYSKM 2019-04-17 00:56:33 AUDSYS 2019-04-17 00:56:33 SYSRAC 2019-04-17 00:56:33 SYSBACKUP 2019-04-17 00:56:33 SYSTEM 2019-04-17 00:56:33 SYSDG 2019-04-17 00:56:33 SYS 2019-04-17 00:56:32 36 rows selected.
ps注意：单机环境和rac环境是不同的，所以rac的实例系统用户和单机的实例系统用户是有可能不一样的，这里我就不演示了。rac环境搭建后之后，通过上面的SQL去查看默认的系统用户有哪些，那么就要在导入的时候排除。
expdp全库导出（实例orcl，单机环境）部分
1、创建数据泵的dmp文件存放目录
[root@11g ~]# mkdir /liu
[root@11g ~]# chown oracle:oinstall /liu
###文件liu（路径/liu）在/dev/sdb3下挂载，将文件的所属用户和目录改为oracle：oinstall
SYS@orcl> create directory BACKUP20200328 as '/liu';
SYS@orcl> grant all on directory BACKUP20200328 to system ;
###创建数据泵的转储路径（在使用expdp时，指定到liu目录时，数据文件就会生成在/liu路径下）。赋予给所有用户目录liu的所有执行权限，为了以后普通用户使用expdp时有权限将dmp数据文件导入到/liu下。2、全库full=y导出数据
[oracle@11g ~]# expdp \"/ as sysdba\" directory=BACKUP20200328 dumpfile=expdp_orcl_full_%U.dmp logfile=expdp_full.log full=y parallel=2
impdp全库导入（实例test，单机环境）部分
1、全库用户导入前需要注意的事情
1）关注归档目录，定时进行删除，避免空间耗尽。可以考虑先关闭归档
2）全部数据量有500G以上，导入过程中undo和temp占用很多，适当扩容。
Undo表空间给7个，总大小210G，并且减少undo_retention为300秒
Temp表空间给3个，总大小90G
3）数据文件看情况增加
4）expdp/impdp导入导出优化。👉【Oracle篇】一文搞清expdp/impdp逻辑迁移工具的用法（第二篇，总共四篇）-CSDN博客👈
2、在服务器上创建一个新实例test
实例静默安装：
[oracle@11g ~]$ vi /home/db/oracle/product/12.2/db_1/response/dbca.rsp sid=实例名 gdbName=全局数据库名 sysPassword=密码 systemPassword=密码 dbsnmpPassword=密码 datafileDestination=/home/db/oracle/oradata ---数据库文件路径 recoveryAreaDestination=/home/db/oracle/fast_recovery_area characterSet= AL32UTF8 totalMemory=AMM内存大小 ---单位M automaticMemoryManagement=TRUE ---是否启用自动内存管理 templateName=General_Purpose.dbc ---数据库模板，必填 storageType=FS ---指定要创建的数据库的存储类型，有FS和ASM，默认FS文件系统 [oracle@rac1 ~]$ dbca -silent -createDatabase -responseFile /home/db/oracle/product/12.2/db_1/response/dbca.rsp调整新实例的数据文件、undo、temp、redo初始大小
数据库文件和undo： SQL> set linesize 500 set pagesize 99 col file_name for a70 col file_id for 9999999 col status for a10 col ts_name for a25 col cur_mb for 99999 col max_mb for 99999 SQL> select status, file_id, file_name, tablespace_name ts_name,blocks/128 tolal_mb, maxblocks/128 max_mb,AUTOEXTENSIBLE from dba_data_files order by file_name; SQL> alter database datafile 2 resize 20g; SQL> alter database datafile 1 resize 20g; SQL> alter database datafile 3 resize 20g; SQL> alter database datafile 5 resize 20g; SQL> alter database datafile 4 resize 10g; temp临时表空间: SQL> select username,temporary_tablespace from dba_users; SQL> set linesize 230 col file_name for a65 SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,bytes/1024/1024 tolal_mb,status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 max_mb from dba_temp_files; SQL> alter database tempfile 1 resize 20g; redo重做日志:生产建议3到6组，每组大小为1G左右 SQL> set linesize 230 col member for a50 SQL> select * from v$logfile; SQL> select * from v$log; SQL> alter database add logfile thread 1 group 10 ('+ARCHDG') size 1024m; SQL> alter database add logfile thread 1 group 11 ('+ARCHDG') size 1024m; SQL> alter database add logfile thread 1 group 12 ('+ARCHDG') size 1024m; SQL> alter database add logfile thread 1 group 13 ('+ARCHDG') size 1024m; SQL> alter database add logfile thread 2 group 14 ('+ARCHDG') size 1024m; SQL> alter database add logfile thread 2 group 15 ('+ARCHDG') size 1024m; SQL> alter database add logfile thread 2 group 16 ('+ARCHDG') size 1024m; SQL> alter system switch logfile; SQL> alter system checkpoint; SQL> alter database drop logfile group 1; SQL> alter database drop logfile group 2; SQL> alter database drop logfile group 3; SQL> alter database drop logfile group 4;关闭密码过期（默认180天过期）:
SQL> select * from dba_profiles where profile='DEFAULT'; SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited; SQL> alter profile default limit PASSWORD_LOCK_TIME unlimited; SQL> alter profile default limit PASSWORD_GRACE_TIME unlimited;关闭审计：长时间开启审计，审计占用空间较大
SQL> alter system set audit_trail=none scope=spfile;设置归档路径：
SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter system set log_archive_dest_1='location=+ASM磁盘'; SQL> alter database open; SQL> archive log list; SQL> show parameter recover;db实例参数优化：
SQL> alter system set sga_max_size=物理内存x0.5 scope=spfile sid='*'; alter system set sga_target=物理内存x0.5 scope=spfile sid='*'; alter system set pga_aggregate_target=物理内存x0.2 scope=spfile sid='*'; ---设置为ASMM alter system set "_cursor_obsolete_threshold"=400 scope=spfile sid='*'; alter system set "_rollback_segment_count" =2000 scope=spfile sid='*'; alter system set "_ktb_debug_flags"=8 sid='*'; alter system set "_datafile_write_errors_crash_instance"=false scope=spfile sid='*'; alter system set events '43822 trace name context forever,level 1'; alter system set enable_ddl_logging=true sid='*' scope=spfile; alter system set fast_start_parallel_rollback=low sid='*'; alter system set recovery_parallelism=10 scope=spfile sid='*'; alter system set "_cleanup_rollback_entries"=10000 scope=spfile sid='*'; alter system set "_gc_bypass_readers" = false sid = '*' scope=spfile; alter system set "_gc_override_force_cr" = false sid = '*' scope=spfile; alter system set "_gc_read_mostly_locking" = false sid = '*' scope=spfile; alter system set "_serial_direct_read" = false sid = '*' ; alter system set "_high_priority_processes" = "LGWR|LMS*|VKTM" sid='*' scope=spfile; alter system set "_use_adaptive_log_file_sync"=false sid='*' scope=spfile; alter system set "_px_use_large_pool"=true sid ='*' scope=spfile; alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1','60025 trace name context forever,level 1', '10503 trace name context forever,level 128' sid='*' scope=spfile; alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile; alter system set "_optimizer_adaptive_cursor_sharing"=false sid='*' scope=spfile; alter system set "_optimizer_extended_cursor_sharing"=none sid='*' scope=spfile; alter system set "_optimizer_extended_cursor_sharing_rel"=none sid='*' scope=spfile; alter system set "_gc_defer_time"=3 scope=spfile sid='*'; alter system set result_cache_max_size=0 scope=spfile sid='*'; alter system set "_gc_policy_time"=0 scope=spfile sid='*'; alter system set "_gc_undo_affinity"=false scope=spfile sid='*'; alter system set control_file_record_keep_time=31 scope=spfile sid='*'; alter system set parallel_max_servers=128 scope=spfile sid='*'; alter system set parallel_adaptive_multi_user=false sid='*'; alter system set "_undo_autotune"=false scope=spfile sid='*'; alter system set undo_retention=10800 scope=spfile sid='*'; alter system set "_partition_large_extents"=false scope=spfile sid='*'; alter system set parallel_force_local=true scope=spfile sid='*'; alter system set processes=2000 scope=spfile sid='*'; ---进程数。设置完成后，session和transactions参数会自动调整（session=1.5*processes+22 ； transactions=1.1*sessions） alter system set open_cursors=2000 scope=spfile sid='*'; ---打开游标数。一个sql语句就会对应到一个游标（简单理解游标就是一个sql语句） alter system set session_cached_cursors=300 scope=spfile sid='*'; ---会话缓存游标数（sql语句）。一个会话缓存多个游标（sql语句），一个sql对应一个游标，缓存游标是为了避免软解析，相同的sql再次执行就不需要打开游标了（执行sql就会打开游标，避免打开游标就是避免软解析），绑定变量解决硬解析。 alter system set db_files=8192 scope=spfile sid='*'; ---允许创建的数据文件总数
3、确认字符集调整undo保留时间
[oracle@11g ~]# export ORACLE_SID=test
[oracle@11g ~]# echo $ORACLE_SID
SQL> alter system set undo_retention=300 scope=both sid='*'; --导入完成之后修改回来4、创建存放DMP文件夹的引用地址
SQL> create or replace directory baj_dir as '/data3/data4/backup/db160';
SQL> grant all on directory baj_dir to system;5、通过备份的dmp文件，生成表空间创建的DDL语句
sqlfile=：导入时可以显示执行的DDL语句。加上这个参数后就不会进行数据导入了，只会根据导入的dmp文件，将dmp文件涉及到的DDL语句输出到sqlfile指定的文件中，然后筛选出来业务表空间进行手动创建。
[oracle@11g ~]# impdp \"/ as sysdba\" directory=baj_dir dumpfile=expdp_orcl_full_01.dmp,expdp_orcl_full_02.dmp logfile=imp.txt sqlfile=ddl_dump.txt
[oracle@11g ~]# more ddl_dump.txt注：将ddl_dump.txt文件中创建表空间的DDL语句整理到其他文本中，然后在sqlplus中执行，排查掉SYSAUX、SYSTEM、TEMP、UNDOTBS1、USERS这些系统表空间的创建。
6、执行创建表空间语句
总共涉及到43个表空间的创建，其中42个业务表空间，还包括了一个临时表空间CARRECOVER_TEMP的创建，将表空间的原路径+DATA/core/datafile修改为/data3/oradata/recover/recover/路径：
SQL>
CREATE TABLESPACE "TBLSPACE_PRP" DATAFILE
'/data3/oradata/recover/recover/prp.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_VISA" DATAFILE
'/data3/oradata/recover/recover/visa.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_CLAIM" DATAFILE
'/data3/oradata/recover/recover/claim.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_PRPIDX" DATAFILE
'/data3/oradata/recover/recover/prpidx.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_PAYMENTIDX" DATAFILE
'/data3/oradata/recover/recover/paymentidx.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_CLAIMIDX" DATAFILE
'/data3/oradata/recover/recover/claimidx.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "WLREPORT_DATA" DATAFILE
'/data3/oradata/recover/recover/data.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBS_DEFAULT" DATAFILE
'/data3/oradata/recover/recover/default.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBS_ALLIDX" DATAFILE
'/data3/oradata/recover/recover/allidx.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_CTUSER" DATAFILE
'/data3/oradata/recover/recover/ctuser.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_VISAIDX" DATAFILE
'/data3/oradata/recover/recover/visaidx.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "WLREPORT_IDX" DATAFILE
'/data3/oradata/recover/recover/idx.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_MGR" DATAFILE
'/data3/oradata/recover/recover/mgr.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "JY" DATAFILE
'/data3/oradata/recover/recover/jy.dbf' SIZE 2G,
'/data3/oradata/recover/recover/jy_01.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "INTFTBS" DATAFILE
'/data3/oradata/recover/recover/intftbs.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_REINS" DATAFILE
'/data3/oradata/recover/recover/reins.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_CW" DATAFILE
'/data3/oradata/recover/recover/cw.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "OPENCMS" DATAFILE
'/data3/oradata/recover/recover/opencms.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_JY" DATAFILE
'/data3/oradata/recover/recover/jy2.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "UMTBS" DATAFILE
'/data3/oradata/recover/recover/umtbs.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "INTRAWEB" DATAFILE
'/data3/oradata/recover/recover/intraweb.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "OPRTBS" DATAFILE
'/data3/oradata/recover/recover/oprtbs.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_CIRC" DATAFILE
'/data3/oradata/recover/recover/circ.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_OTHIDX" DATAFILE
'/data3/oradata/recover/recover/othidx.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_OTH" DATAFILE
'/data1/ora_pmon_recover_oradata/oth.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth_01.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth_02.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth3.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth4.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth5.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth6.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth7.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth8.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth14.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth9.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth10.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth11.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth12.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth13.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth15.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth16.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth17.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth18.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth19.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth20.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth21.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth22.dbf' SIZE 1M autoextend on,
'/data1/ora_pmon_recover_oradata/oth23.dbf' SIZE 1M autoextend on;
CREATE TABLESPACE "TBLSPACE_RESERVE" DATAFILE
'/data3/oradata/recover/recover/reserve.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "ZCDATA" DATAFILE
'/data3/oradata/recover/recover/zcdata.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_REINSIDX" DATAFILE
'/data3/oradata/recover/recover/reinsidx.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_PAYMENT" DATAFILE
'/data3/oradata/recover/recover/payment.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_OTH02" DATAFILE
'/data3/oradata/recover/recover/TBLSPACE_OTH02.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TABLESPACE_GB" DATAFILE
'/data3/oradata/recover/recover/gb01.dbf' SIZE 2G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "CEDET_BAOXIAN" DATAFILE
'/data3/oradata/recover/recover/cedet_baoxian.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "FXQ" DATAFILE
'/data3/oradata/recover/recover/fxq.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "HZ_UCIA" DATAFILE
'/data3/oradata/recover/recover/ucia.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "OGG" DATAFILE
'/data3/oradata/recover/recover/ogg.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "HZ_SJZL" DATAFILE
'/data3/oradata/recover/recover/sjzl.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "CEDET_BAOXIAN_NEW" DATAFILE
'/data3/oradata/recover/recover/cedet_baoxian_new.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "TABLESPACE_HZHIC" DATAFILE
'/data3/oradata/recover/recover/hzhic.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "HZ_LITIGATION" DATAFILE
'/data3/oradata/recover/recover/litigation.dbf' SIZE 2G
AUTOEXTEND ON NEXT 8192 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBS_DBBAK" DATAFILE
'/data3/oradata/recover/recover/TBS_DBBAK.dbf' SIZE 2G
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "GGATE" DATAFILE
'/data3/oradata/recover/recover/GGATE.dbf' SIZE 2G
AUTOEXTEND ON NEXT 104857600 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "TBLSPACE_CARRECOVER" DATAFILE
'/data3/oradata/recover/recover/TBLSPACE_CARRECOVER.dbf' SIZE 2G
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE "CARRECOVER_TEMP" TEMPFILE
'/data3/oradata/recover/recover/CARRECOVER_TEMP.dbf' SIZE 2G
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;7、执行impdp导入命令
排除导入的系统用户，因为系统用户是按照实例默认创建的所以导入时需要排除。在每个大版本中系统用户都不一样，所以需要根据具体的大版本而排除。
排除导入的系统用户：11g的系统用户涉及到30个，将所有的用户编辑到一个文件中，在导入时指定这个文件，那么就是排除这些用户的导入了
[oracle@11g ~]$ vi /data3/data4/backup/db160/exclude.par
EXCLUDE=SCHEMA:"='SYS'"
EXCLUDE=SCHEMA:"='SYSTEM'"
EXCLUDE=SCHEMA:"='OUTLN'"
EXCLUDE=SCHEMA:"='MGMT_VIEW'"
EXCLUDE=SCHEMA:"='FLOWS_FILES'"
EXCLUDE=SCHEMA:"='MDSYS'"
EXCLUDE=SCHEMA:"='ORDSYS'"
EXCLUDE=SCHEMA:"='EXFSYS'"
EXCLUDE=SCHEMA:"='DBSNMP'"
EXCLUDE=SCHEMA:"='WMSYS'"
EXCLUDE=SCHEMA:"='APPQOSSYS'"
EXCLUDE=SCHEMA:"='APEX_030200'"
EXCLUDE=SCHEMA:"='OWBSYS_AUDIT'"
EXCLUDE=SCHEMA:"='ORDDATA'"
EXCLUDE=SCHEMA:"='CTXSYS'"
EXCLUDE=SCHEMA:"='ANONYMOUS'"
EXCLUDE=SCHEMA:"='SYSMAN'"
EXCLUDE=SCHEMA:"='XDB'"
EXCLUDE=SCHEMA:"='ORDPLUGINS'"
EXCLUDE=SCHEMA:"='OWBSYS'"
EXCLUDE=SCHEMA:"='SI_INFORMTN_SCHEMA'"
EXCLUDE=SCHEMA:"='OLAPSYS'"
EXCLUDE=SCHEMA:"='SCOTT'"
EXCLUDE=SCHEMA:"='ORACLE_OCM'"
EXCLUDE=SCHEMA:"='XS$NULL'"
EXCLUDE=SCHEMA:"='MDDATA'"
EXCLUDE=SCHEMA:"='DIP'"
EXCLUDE=SCHEMA:"='APEX_PUBLIC_USER'"
EXCLUDE=SCHEMA:"='SPATIAL_CSW_ADMIN_USR'"
EXCLUDE=SCHEMA:"='SPATIAL_WFS_ADMIN_USR'"
进行impdp导入：
[oracle@11g ~]$ impdp \"/ as sysdba\" directory=baj_dir dumpfile=expdp_orcl_full_01.dmp,expdp_orcl_full_02.dmp logfile=impdp_full.log full=y parallel=4 table_exists_action=append PARFILE=exclude.par
####impdp导入之前，需要在目标数据库上创建相应表空间对象即可；而对于imp导入时需要在目标数据库上创建相应的用户、权限、表空间等对象。
验证数据部分
第一步：验证数据和用户
SQL> select sum(bytes) / 1024 / 1024 / 1024 || 'G' sum from dba_segments;
SQL> select username,created from dba_users order by created desc;注：总共123个用户，除去系统的30个用户，总共导入了93个用户
第二步：验证有无失效的对象（目标数据库上执行）
SQL> select * from dba_objects where status!='VALID' and owner in('用户');
SQL> @?/rdbms/admin/utlrp.sql ---有无效对象的话，进行无效对象的编译（最大可能自动修复无效对象）。其他重新编译失效对象的方法参考“PLSQL工具使用详解（1）、对象重定义（部分）”文档第三步：收集统计信息（目标数据库上执行）
[oracle@rac1 ~]# vi status.sql
begin
dbms_stats.gather_database_stats;
end;
/
[oracle@rac1 ~]# nohup sqlplus / as sysdba @status.sql & ---因为收集统计信息时间长，所以写个sh后台运行第四步：查看哪些表的统计信息被锁定（stattype_locked字段为ALL的表示锁定了表的统计信息，默认stattype_locked字段为空表示可以收集统计信息）：
SQL> select * from dba_ind_statistics where stattype_locked='ALL' AND OWNER='用户';
SQL> select * from dba_tab_statistics where stattype_locked='ALL' AND OWNER='用户';
SQL> begin
dbms_stats.unlock_schema_stats(ownname => '用户');
end;
/第五步：验证对象（目标数据库上执行）
SQL> select object_type t_object_type, count(*) t_count
from dba_objects
where owner in('用户')
group by object_type
###注：oracle的对象类型可以分的很详细，表、表分区、表子分区是不同的类型。
第六步：对比导入和导出日志
第七步：将undo时间修改回最佳值
SQL> alter system set undo_retention=10800 scope=both sid='*';关于Oracle逻辑迁移工具的用户和案例今天就全部讲完了，4篇文章从构思、排版、整理花了2星期的时间，创作不易坚持亦难，希望各个小伙伴 点赞、收藏、加关注 。希望这4篇文章能够在大家以后的工作和学习中带来帮助。下篇文章将带来MySQL的内容，尽请关注。
![洛谷：P8671 [蓝桥杯 2018 国 AC] 约瑟夫环](https://img-blog.csdnimg.cn/direct/50561718cef344e19b3dfcd239b2fb92.jpeg)
![[计算机知识] 各种小问题思考](https://img-blog.csdnimg.cn/direct/9e0d7351c3c9427291ebd7b73c2be0f4.png)
