expdp的 exclude测试

最新推荐文章于 2025-04-21 17:59:58 发布

文档搬运工

最新推荐文章于 2025-04-21 17:59:58 发布

阅读量1.2k

点赞数

分类专栏： EXPDPIMPDP 文章标签： oracle

本文链接：https://blog.csdn.net/xxzhaobb/article/details/130747648

版权

EXPDPIMPDP 专栏收录该内容

28 篇文章

订阅专栏

测试目的：
1 导出schema下的表。排除某一些表，使用通配符
2 已排除的表，表上的索引会不会导出

测试结论：
1 可以通过通配符来排除，注意表的大小写
2 已排除的表，该表上的索引，不会导出

-- 创建测试用户和表，导出app schema下的表。但是排除表atest1 atest2 atest3 。使用通配符%TEST%

create user app identified by oracle ;
alter user app default tablespace users ;
grant dba to app;

conn apps/oracle 
create table t1(id int,name varchar2(10));
create table atest1(id int,name varchar2(10));  
create table atest2(id int,name varchar2(10));
create table atest3(id int,name varchar2(10));
create table t2 (id int,name varchar2(10));

insert into t1 values (1,'a');
insert into atest1 values(2,'a');
insert into atest2 values(3,'a');
insert into atest3 values(4,'a');
insert into t2 values(5,'a');
commit;

-- 在排除的表上创建索引，以测试，排除这些表的时候，这些排除表的索引，是否会被导出

create index idx_at1 on atest1(id);
create index idx_at2 on atest2(id);
create index idx_at3 on atest3(id);

-- 导出,排除掉atest1,atest2,atest3, 使用通配符%TEST% .注意要大写。排除的表上有索引，可以看出导出动作中没有导出索引

[oracle@redhat762100 dmp]$ expdp \'/ as sysdba\' directory = DUMP dumpfile=20230518.dmp logfile=20230518.log schemas=app exclude=TABLE:\"LIKE \'%TEST%\'\"

Export: Release 19.0.0.0.0 - Production on Thu May 18 14:36:56 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DUMP dumpfile=20230518.dmp logfile=20230518.log schemas=app exclude=TABLE:"LIKE '%TEST%'" 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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
. . exported "APP"."T1"                                  5.484 KB       1 rows
. . exported "APP"."T2"                                  5.484 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/dmp/20230518.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu May 18 14:37:07 2023 elapsed 0 00:00:11

[oracle@redhat762100 dmp]$

-- 如果在排除的时候，排除的表，没有使用大写，则不会排除。排除的表会继续被导出，并且有导出索引的动作。

[oracle@redhat762100 dmp]$ expdp \'/ as sysdba\' directory = DUMP dumpfile=20230518.dmp logfile=20230518.log schemas=app exclude=TABLE:\"LIKE \'%test%\'\"

Export: Release 19.0.0.0.0 - Production on Thu May 18 14:35:05 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DUMP dumpfile=20230518.dmp logfile=20230518.log schemas=app exclude=TABLE:"LIKE '%test%'" 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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
. . exported "APP"."ATEST1"                              5.492 KB       1 rows
. . exported "APP"."ATEST2"                              5.492 KB       1 rows
. . exported "APP"."ATEST3"                              5.492 KB       1 rows
. . exported "APP"."T1"                                  5.484 KB       1 rows
. . exported "APP"."T2"                                  5.484 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/dmp/20230518.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu May 18 14:35:28 2023 elapsed 0 00:00:19

[oracle@redhat762100 dmp]$

-- 使用parfile ，使用引号，不需要使用转义符

vi par

directory = DUMP 
dumpfile=20230518-1.dmp 
logfile=20230518-1.log 
schemas=app 
exclude=TABLE:"LIKE '%TEST%'"


expdp \'/ as sysdba\' parfile=/u01/dmp/par

[oracle@redhat762100 dmp]$ expdp \'/ as sysdba\' parfile=/u01/dmp/par 

Export: Release 19.0.0.0.0 - Production on Thu May 18 14:57:56 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" parfile=/u01/dmp/par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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
. . exported "APP"."T1"                                  5.484 KB       1 rows
. . exported "APP"."T2"                                  5.484 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/dmp/20230518-1.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu May 18 14:58:09 2023 elapsed 0 00:00:13

[oracle@redhat762100 dmp]$ cat /u01/dmp/par
directory = DUMP 
dumpfile=20230518-1.dmp 
logfile=20230518-1.log 
schemas=app 
exclude=TABLE:"LIKE '%TEST%'"
[oracle@redhat762100 dmp]$

END