expdp 如何导出某用户下一部分表

JiekeXu之路

发布于 2022-01-04 17:13:03

1.1K0

发布于 2022-01-04 17:13:03

很多时候，作为 DBA 运维人员都会遇到一些奇葩的需求和奇怪的问题，最近一段时间也遇到了几个奇葩的导出数据的需求，这里做一个简单的归纳总结，以防后期遇到同样的需求时不知所措。

**一、比较规则的一部分表，例如 T_PRICE* 开头的表**

T_PRICE 开头繁荣表，可以从 dba_tables 视图或者 user_tables 视图中 like ‘T_PRICE%’ 查询到，这类 expdp 导出比较方便, 如下的一个项目中需要在 oracle 数据库某个用户下，以 T_PRICE 开头的表约有 90 多个表做备份，在 11.2.0.4 下 rman 没办法做单表备份，CTAS 90 多张表也是个事，只能通过 expdp 导出了，不过很少遇到需要导出这么多表的情况，通常都是按 schema 导出，或者整库导出。考虑到 expdp 中 include 参数可以附带查询语句，本次遇到的需要导出大量具体表的建议思路：

1）利用 expdp 导出命令的 include 参数附带 select 语句查询 dba_tables 表，获得需要导出的表名;当然要是使用普通用户导出则需查询 user_tables。

select TABLE_NAME from dba_tables where owner='T3_CCBSCF' and table_name like 'T_PRICE%';

2）编辑 exptable.par 文件，开始导出

具体过程如下：

首先查看 SCN

select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
3523577018

然后查看导出目录

set linesize 9999
col OWNER for a10
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a60
select * from dba_directories;

--如果目录过小或者不存在,则
create directory expdp_dir as '/u01/app/backup/expdp_dir';
grant read,write on directory expdp_dir to public;

编辑 par 文件

more exptable.par
dumpfile=T3_CC_93Tables_20210908_%U.dmp
logfile=T3_CC_93Tables_20210908.log
schemas=T3_CC
directory=DUMP_DIR
#exclude=statistics
flashback_scn=3523577018
PARALLEL=4
COMPRESSION=all
include=TABLE:"IN (select TABLE_NAME from dba_tables where owner='T3_CC' and table_name like 'T_PRICE%')"

--注意：exclude 和 include 不能同时使用，否则报错 
--UDE-00011: parameter include is incompatible with parameter exclude 。
直到 21c 新特性中这两个参数才可以同时使用,最佳实践中数据泵导出参数exclude=statistics 为不可或缺的参数。

expdp 后台导出数据

nohup expdp \'/ as sysdba\' parfile=exptable.par &

查看日志

more T3_CCBSCF_93Tables_20210908.log
;;;
Export: Release 11.2.0.4.0 - Production on Wed Sep 8 17:29:51 2021

Copyright (c) 1982, 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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_04": "/******** AS SYSDBA" parfile=exptable.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 338.1 MB
. . exported "T3_CC"."T_PRICE_SHARE_DETAIL" 6.953 MB 63781 rows
. . exported "T3_CC"."T_PRICE_SNAPSHOT_DETAIL" 12.58 MB 27957 rows
. . exported "T3_CC"."T_PRICE_RETRYABLE_TASK" 9.148 MB 74225 rows
. . exported "T3_CC"."T_PRICE_SHARE_PLAN" 4.293 MB 63811 rows
. . exported "T3_CC"."T_PRICE_SNAPSHOT_MPS" 2.339 MB 36888 rows
. . exported "T3_CC"."T_PRICE_FEE_PLAN_MPS" 2.798 MB 36888 rows
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
. . exported "T3_CC"."T_PRICE_BIZ_EXTENSION_ABS" 1.704 MB 36888 rows

…………省略中间部分……………

. . exported "T3_CC"."T_PRICE_SHARE_TIME_POINT" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
/oracle/dump_dir/T3_CC_93Tables_20210908_01.dmp
/oracle/dump_dir/T3_CC_93Tables_20210908_02.dmp
/oracle/dump_dir/T3_CC_93Tables_20210908_03.dmp
/oracle/dump_dir/T3_CC_93Tables_20210908_04.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Wed Sep 8 17:32:23 2021 elapsed 0 00:02:31

注意：查看 SCN 对于的时间和导出表中部分数据时可参考如下语句。

查看 SCN 对应的时间

select to_char(scn_to_timestamp(3523577018), 'yyyy-mm-dd hh24:mi:ss') scndate from dual;
SCNDATE
-------------------
2021-09-08 17:24:03

导出表中的部分数据（query)：

expdp scott/tiger dumpfile=scott_20211230_%U.dmp directory=exp_dir tables =test query=test:'"where show_date <= 20211230"'

注意：query 有单引号包含双引号。

导出多个表(tables)：

expdp scott/tiger dumpfile=scott_20211230_T_%U.dmp directory=exp_dir tables=EMP,DEPT,BONUS,SALGRADE,TEST,T1,T2

二、不规则的一部分表，例如 EMP,DEPT,TEST,T1,T2 等几百张表

如果遇到如上列举的几百张表都是不规则的，那么也就只能是通过上节最后一个示例，导出部分表了。但有时候你可能不知道业务账号密码，无法使用普通用户导出，那就只能使用 SYS 用户导出吧。

expdp scott/tiger dumpfile=scott_20211230_T_%U.dmp directory=exp_dir tables=EMP,DEPT,BONUS,SALGRADE,TEST,T1,T2

但是，使用 SYS 用户导出时，需要注意的点就是表名前需要加用户名，如：
tables=SCOTT.EMP,SCOTT.DEPT,…… 然后将所有的表名列出来。

1、命令行直接导出部分表

expdp \'/ as sysdba\' dumpfile=T4_20211230.dmp directory=PUBLIC_DUMP exclude=statistics PARALLEL=4 COMPRESSION=all cluster=n logfile=T4_100tables.log tables = PROD_CC.T_VCHR_INVOICE_HISTORY, PROD_CC.T_SYS_XZQH, PROD_CC.T_SYS_USER_ROLES, PROD_CC.T_SYSTEM_FILE, PROD_CC.T_PRODUCT_RELATION, PROD_CC.T_PRODUCT_LOGISTICS, PROD_CC.T_PRODUCT_CORP, PROD_CC.T_PRODUCT_CORE_CREDIT, PROD_CC.T_PRODUCT_CC, PROD_CC.T_PRODUCT_CASH_FLOW, PROD_CC.T_PRICE_SNAPSHOT_DETAIL, PROD_CC.T_PRICE_SNAPSHOT, PROD_CC.T_PRICE_SHARE_PLAN, PROD_CC.T_PRICE_SHARE_DETAIL, PROD_CC.T_PRICE_FEE_SNAPSHOT, PROD_CC.T_PRICE_FEE_PLAN, PROD_CC.T_PRICE_CONFIG, PROD_CC.T_POND_DRAWDOWN, PROD_CC.T_ORG_STAFF_PREFERENCE, PROD_CC.T_ORG_STAFF_DEPT_TEMP

使用上面所示将几百张上千张表列出来时，但有网友说有时候会出现参数错误的问题。

但是我刚才测试了一下 exclude 排除了 724 张用户名加表名的 expdp 导出也没有问题，就不知道当时他是怎么写的命令了。

原描述大概如下：使用数据泵导出一个用户下的部分表，但是其中要剔除其中 200 张表，用 exclude 直接写表名会因为剔除的表名太多，报 exclude 参数无效的错：UDE-00014: invalid value for parameter, ‘exclude’.

expdp \'/ as sysdba\' dumpfile=T4_2021123022.dmp schemas=PROD directory=PUBLIC_DUMP exclude=statistics PARALLEL=4 COMPRESSION=all cluster=n logfile=T4_100tables.log exclude = PROD.T_SYS_ROLE,PROD_PERMISSION_TEMP,……

如果有问题，可以尝试使用 parfile 参数文件，将所有导出的内容，表，日志、目录等等

写入一个文件中，然后直接用 parfile=文件名代替，命令行中则可以省略掉很多，看起来比较直观,而且不容易出错，很多导出导入错误都是由于命令行参数太长导致的。

vim exp100table.par

dumpfile=T4_100Tables_20211230_%U.dmp
logfile=T4_100Tables_20211230.log
#schemas=PROD_CC
directory=PUBLIC_DUMP
exclude=statistics
PARALLEL=4
tables=PROD_CC.T_HISTORY,PROD_CC.T_HI，……等一千张表
COMPRESSION=all
cluster=n
#include=TABLE:"IN (select TABLE_NAME from dba_tables where table_name like 'T_PRICE%')"

2、使用 parfile 导出部分表

$ expdp \'/ as sysdba\'  parfile=exp100table.par

Export: Release 11.2.0.4.0 - Production on Thu Dec 30 15:38:05 2021

Copyright (c) 1982, 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" parfile=exp100table.par 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.594 GB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
. . exported "PROD_CC"."T_PRICE_SNAPSHOT_DETAIL"     47.28 MB  113825 rows
Processing object type TABLE_EXPORT/TABLE/TABLE
……………省略部分输出…………………

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /nfs/T4_100Tables_20211230_01.dmp
  /nfs/T4_100Tables_20211230_02.dmp
  /nfs/T4_100Tables_20211230_03.dmp
  /nfs/T4_100Tables_20211230_04.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Dec 30 15:40:24 2021 elapsed 0 00:02:14

3、通过查看表名导出部分表

这个方法，其实和最上面的方法查 dba_tables 是一个道理，新建一张表，将需要导出表的表名写入一张表中，则和前面的导出方案就一样了，这里大概说一句。

使用导出用户创建一张表，我这里使用的是 SYS 创建和导出的。

create table T4_100Tables (TABLE_NAME varchar2(40));

拼接 SQL 语句将其要导出的 94 张表名转换成如下的插入 SQL.

insert into T4_100Tables (table_name) values('T_VCHR_HISTORY');
insert into T4_100Tables (table_name) values('T_SYS_ZQH');
insert into T4_100Tables (table_name) values('T_SYS_ROLE');
insert into T4_100Tables (table_name) values('T_SYS_FILES');
commit;

编辑 par 文件导出融通下的 94 张表.

vim exp94table.par

dumpfile=T4_100Tables_20211202_%U.dmp
logfile=T4_100Tables_20211202.log
schemas=PROD_CC
directory=PUBLIC_DUMP
#exclude=statistics
flashback_scn=10836454986
PARALLEL=4
COMPRESSION=all
cluster=n
#include=TABLE:"IN (select TABLE_NAME from dba_tables where table_name like 'T_PRICE%')"
include=TABLE:"IN (select TABLE_NAME from T4_100Tables)"

后台导出部分表

nohup expdp \'/ as sysdba\' parfile=exp94table.par &

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-12-31，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自 JiekeXu之路微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度