博客

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

  • Post author:
  • Post category:其他



8c27feb02c436cb638b9644a76ffd734.gif

作者 | JiekeXu

来源 | JiekeXu DBA之路（ID: JiekeXu_IT）

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 expdp 如何导出某用户下一部分表，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！

很多时候，作为 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

76b70d1e8b621baf69e57400313851c3.png



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 &

c8cdd8980c1d665d491c0b3b8268e5a9.png


本次分享到此结束啦，2021 也就马上要过去啦，祝小伙伴们元旦快乐，新的一年工作顺利，升值加薪，万事顺遂，我们 2022 在相见！

今天是今年，

明天是明年，

后天是将来！

祝小伙伴们：

今年快乐，

明年健康，

将来幸福!

2021.12.31

❤️ 欢迎关注我的公众号，来一起玩耍吧！！！


——————————————————————–—–————


公众号：JiekeXu DBA之路

墨天轮：https://www.modb.pro/u/4347

CSDN ：https://blog.csdn.net/JiekeXu

腾讯云：https://cloud.tencent.com/developer/user/5645107


—————————————————————————-———

ddb930c61d836398152c66b1432cacef.gif



VMWARE16 Oracle Linux7.9 安装 Oracle19c RAC 详细配置方案




使用 VMware 16 RHEL7.7 虚拟机静默安装 Oracle 19c RAC




爆肝一万字终于把 Oracle Data Guard 核心参数搞明白了





Oracle 12c 及以上版本补丁更新说明及下载方法（收藏版）





Oracle 19c 19.10DBRU 最新补丁升级看这一篇就够了




Redhat 7.7 安装最新版 MongoDB 5.0.1 手册




ASM 管理的内部工具：KFED、KFOD、AMDU





性能优化|关于数据库历史性能问题的一道面试题







一线运维 DBA 五年经验常用 SQL 大全（二）





ORA-00349|激活 ADG 备库时遇到的问题




OGG-01004|OGG 初始化数据问题处理




Oracle 轻量级实时监控工具 oratop




Linux 7.7 源码安装 MySQL 8.0.26




MySQL OCP 认证考试你知道吗？





Oracle 19C RAC 安装遇到的坑




国产数据库|TiDB 5.0 快速体验




Oracle 19C MAA 搭建指南




Oracle 参数文件三两事儿





Oracle 每日一题系列合集




百花齐放的国产数据库


923a289274b9d0a59da73ba46402c13b.png


版权声明：本文为JiekeXu原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。