利用 rowid 分片导出导入 lob 大表及数据泵最佳实践

SQL> select count(*) from PROD.T_CA_TX_HIS;

COUNT(*)
----------
1525536

SQL> set line 120
SQL> col OWNER for a30
SQL> col SEGMENT_NAME for a30
SQL> select OWNER,SEGMENT_NAME,BYTES/1024/1024/1024 from dba_segments where SEGMENT_NAME='T_CA_TX_HIS' and OWNER='PROD';

OWNER SEGMENT_NAME BYTES/1024/1024/1024
------------------------------ ------------------------------ --------------------
PROD T_CA_TX_HIS 1.83007813

SQL> desc PROD.T_CA_TX_HIS
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
SEQUENCE_NO NOT NULL NUMBER(18)
PK_TX_HIS NOT NULL VARCHAR2(36)
FK_USER_CCBSCF VARCHAR2(36)
FK_PERSON_CCBSCF VARCHAR2(36)
SOFT_CA_BRAND NOT NULL VARCHAR2(20)
SOFT_CA_USER_ID VARCHAR2(50)
TARGET_URL VARCHAR2(300)
TX_CODE VARCHAR2(20)
TX_REQUEST CLOB
TX_RESPONSE CLOB
TX_ERROR_CODE VARCHAR2(50)
TX_ERROR_MESSAGE VARCHAR2(500)
CREATE_TIME NOT NULL DATE
FK_USER_CREATE NOT NULL VARCHAR2(36)
USER_NAME_CREATE NOT NULL VARCHAR2(60)

SQL> col SEGMENT_NAME for a30
SQL> select segment_name,BYTES/1024/1024/1024 from dba_segments where OWNER='PROD' group by segment_name,BYTES/1024/1024/1024 order by 2 asc;
.........省略........
SEGMENT_NAME BYTES/1024/1024/1024
------------------------------ --------------------
T_OPEN_MESSAGE_SMS_RECORD 2.8125
SYS_LOB0000167075C00010$$ 13.7119141

--查看某用户下 Lob 字段大小
SET LINE 345 PAGES 456
COL OWNER FOR a20
COL TABLE_NAME FOR A40
COL SEGMENT_TYPE FOR A20
col COLUMN_NAME FOR A35
SELECT
A.OWNER,
B.TABLE_NAME,
B.COLUMN_NAME,
a.SEGMENT_TYPE,
ROUND(SUM(A.BYTES/1024/1024/1024),2) G
FROM DBA_SEGMENTS A
LEFT JOIN DBA_LOBS B
ON A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
WHERE A.SEGMENT_TYPE='LOBSEGMENT'
AND A.OWNER in('&OWNER')
GROUP BY A.OWNER,B.TABLE_NAME,B.COLUMN_NAME,a.SEGMENT_TYPE
ORDER BY 5 DESC;

SQL> create or replace directory PUBLIC_DUMP as '/data/ora-share';
SQL> grant read,write on directory PUBLIC_DUMP to public;

$ expdp PROD/proD_#31 directory=PUBLIC_DUMP LOGFILE=PROD.T_CA_TX_HIS.log dumpfile=PROD.T_CA_TX_HIS.dmp tables=T_CA_TX_HIS COMPRESSION=ALL CLUSTER=N

Export: Release 19.0.0.0.0 - Production on Wed Nov 29 15:00:36 2023
Version 19.15.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
FLASHBACK automatically enabled to preserve database integrity.
Starting "PROD"."SYS_EXPORT_TABLE_01": PROD/******** directory=PUBLIC_DUMP LOGFILE=PROD.T_CA_TX_HIS.log dumpfile=PROD.T_CA_TX_HIS.dmp tables=T_CA_TX_HIS COMPRESSION=ALL CLUSTER=N
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "PROD"."T_CA_TX_HIS" 3.710 GB 1524771 rows
Master table "PROD"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PROD.SYS_EXPORT_TABLE_01 is:
/data/ora-share/PROD.T_CA_TX_HIS.dmp
Job "PROD"."SYS_EXPORT_TABLE_01" successfully completed at Wed Nov 29 16:16:40 2023 elapsed 0 01:16:03

vi tableid.par

userid='/ as sysdba'
directory=PUBLIC_DUMP
content=ALL
compression=ALL
cluster=no
tables=PROD.T_CA_TX_HIS

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
;;; **************************************************************************
;;; Parfile values:
;;; parfile: tables=PROD.T_CA_TX_HIS
;;; parfile: compression=ALL
;;; parfile: content=ALL
;;; parfile: directory=PUBLIC_DUMP
;;; parfile: userid=/******** AS SYSDBA
;;; **************************************************************************
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_08": /******** AS SYSDBA parfile=tableid.par dumpfile=T_CA_TX_HIS_08.dmp logfile=tableid_08.log query="where mod(dbms_rowid.rowid_block_number(rowid),8)=7"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "PROD"."T_CA_TX_HIS" 471.9 MB 190827 rows
Master table "SYS"."SYS_EXPORT_TABLE_08" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_08 is:
/data/ora-share/T_CA_TX_HIS_08.dmp
Job "SYS"."SYS_EXPORT_TABLE_08" successfully completed at Wed Nov 29 17:15:40 2023 elapsed 0 00:21:50

--查看原用户创建语句及权限
set long 9999
select dbms_metadata.get_ddl('USER',username) from dba_users where username='PROD';

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
Master table "SYS"."SYS_IMPORT_FULL_04" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_04": "/******** AS SYSDBA" directory=public_dump dumpfile=T_CA_TX_HIS_02.dmp logfile=T_CA_TX_HIS_02.log cluster=no REMAP_TABLESPACE=PROD
_SCFOP_TBS:CC_OP_DATA REMAP_SCHEMA=PROD:CC_OP TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y TABLE_EXISTS_ACTION=append
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "CC_OP"."T_CA_TX_HIS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CC_OP"."T_CA_TX_HIS" 475.4 MB 189956 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_04" successfully completed at Wed Nov 29 18:45:39 2023 elapsed 0 00:54:44

DIRECTORY：用于转储文件和日志文件的目录对象。
DUMPFILE：指定导出备份文件的命名。
LOGFILE：指定导出备份日志的命名。里面记录了备份中的信息。
FULL：导出整个数据库 （默认是N，就是默认只会导出登录用户的所有数据）。
SCHEMAS：要导出的方案的列表 （指定想要导出哪个用户下的数据）。
EXCLUDE：排除特定对象类型。(表名要大写)
SAMPLE：要导出的数据的百分比。
TABLESPACES：标识要导出的表空间的列表。
VERSION：指定导出数据库的版本，一般用于高版本数据库的数据要导入到低版本数据库中时用到。
PARALLEL：更改当前作业的活动 worker 的数量。
REUSE_DUMPFILES：覆盖目标转储文件 (如果文件存在) [N]。
TABLES：标识要导出的表的列表。例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
QUERY：用于导出表的子集的谓词子句。例如, QUERY=employees:"WHERE department_id > 10"。
JOB_NAME：要创建的导出作业的名称。

impdp参数
impdp help=y

--仅导出元数据表结构
nohup expdp \'/ as sysdba\' directory=PUBLIC_DUMP SCHEMAS=DT_CC CONTENT=METADATA_ONLY exclude=STATISTICS LOGFILE=DT_CC0916.log dumpfile=expdp_METADATA_ONLY_DT_CC-2023-09-16_%U.dmp COMPRESSION=ALL PARALLEL=4 &

--SQL语句
SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
14898415798

--导出某 schema 的数据
nohup expdp \'/ as sysdba\' directory=PUBLIC_DUMP SCHEMAS=PROD FLASHBACK_SCN=14898415798 exclude=STATISTICS LOGFILE=expdp_PROD0420.log dumpfile=expdp_PROD_2022-04-20_%U.dmp COMPRESSION=ALL CLUSTER=N PARALLEL=8 &

--导出某用户下以 T_PRICE 开头的表。
nohup expdp PROD/'rop_P98#' directory=PUBLIC_DUMP LOGFILE=prod_t_price63Tables.log dumpfile=expdp_prod_t_price63Tables.dmp tables=t_price% flashback_scn=9759642727 COMPRESSION=all exclude=statistics parallel=4 cluster=no &

--按条件导出大表的一部分数据
expdp PROD/'rop_P98#' directory=PUBLIC_DUMP LOGFILE=prod.T_AUTH_ORIGINAL.log dumpfile=expdp_prod.T_AUTH_ORIGINAL.dmp tables=T_AUTH_ORIGINAL COMPRESSION=ALL query="where DIGEST_TIME '>=' to_date\('2023-01-01','yyyy-mm-dd'\)"

--使用 parfile 导出表的一部分数据
expdp scott/tiger directory=exp_dir parfile=emp_main.par

vim emp_main.par
tables=emp_main
dumpfile=emp_main.dmp
logfile=emp_main.log
query="where sendtime between to_date('20220101','yyyymmdd') and to_date('20220401','yyyymmdd')"

--利用 SQLfile 参数生成创建索引，触发器，约束的 SQL 语句,该参数可以用于 impdp,主要作用是未真实在目标端执行导入的情况下,生成 sql 文件包含该 dmp 文件的所有 ddl 语句,使用语法为
impdp \'/ as sysdba \' directory=PUBLIC_DIR dumpfile=expdp_FULL_T2_CC_2022-10_17_%U.dmp logfile=T2_index.log sqlfile=t2_cre_index.sql include=INDEX include=TRIGGER include=CONSTRAINT

nohup impdp \'/ as sysdba\' directory=EXPDP_DIR dumpfile=expdp_prod-2021-08-17_%U.dmp parallel=4 logfile=impdp_PROD_081720.log logtime=ALL TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y &
注意：在不管是非归档还是归档情况下使用 DISABLE_ARCHIVE_LOGGING 都会减小导入时间，减少归档量，但是需要注意如果数据库是 force logging 情况下，DISABLE_ARCHIVE_LOGGING 参数会无效。

--导入到其他用户
nohup impdp \'/ as sysdba\' directory=PUBLIC_DUMP LOGFILE=impdp_D2_CC0826.log dumpfile=expdp_prod_cc-2021-08-25_%U.dmp REMAP_SCHEMA=prod_cc:D2_CC REMAP_TABLESPACE=CC_DATA:D2_CC_DATA,CC_INDEX:D2_CC_INDEX PARALLEL=4 &

--查看表的统计信息
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name like 'ACT_GE_%' and owner='OP_DEMO';

--收集用户统计信息
SQL> exec dbms_stats.gather_schema_stats('OP_DEMO')
SQL> exec dbms_stats.gather_schema_stats(ownname => 'D5_CC',options => 'GATHER AUTO',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size repeat',degree => 5)

--只导出表结构和数据，排除索引和统计信息
nohup expdp \'/ as sysdba\' directory=PUBLIC_DUMP SCHEMAS=prod exclude=STATISTICS,INDEX LOGFILE=prod0712.log dumpfile=onlydata_expdp_prod-2021-07-12_%U.dmp COMPRESSION=ALL PARALLEL=4 CLUSTER=N &

--导入用户元数据
impdp \'/ as sysdba\' directory=DUMP_DIR LOGFILE=New_imp_T2_APP.log dumpfile=T2_APP-2021-05-31_NEW.dmp REMAP_SCHEMA=T2_APP:T1_APP REMAP_TABLESPACE=T2_APP_TBS:T1_APP_TBS

--直接导入
nohup impdp \'/ as sysdba\' directory=EXPDP_DIR dumpfile=expdp_prod-2021-08-16_%U.dmp parallel=4 logfile=impdp_PROD_0817.log logtime=ALL TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y &

--使用 dblink 不落地导入
nohup impdp system/Oracle_19C@TEST directory=EXPDP_DIR NETWORK_LINK=PROD_LINK flashback_scn=9010004930 exclude=statistics parallel=4 cluster=no schemas=PROD,PROD_CC logfile=impdp_PROD.log logtime=ALL TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y &
注意：logtime=ALL，Oracle 12c 以后的新参数，记录导入导出的时间，将时间信息输出到控制台和日志里。

--导出序列
SQL> SELECT ' CREATE SEQUENCE '||SEQUENCE_NAME|| ' INCREMENT BY '|| INCREMENT_BY ||' START WITH '||LAST_NUMBER||' MAXVALUE '|| MAX_VALUE ||' CACHE '||CACHE_SIZE||' ORDER NOCYCLE ;'
FROM user_SEQUENCES;

--导出序列
SQL> select dbms_metadata.get_ddl('SEQUENCE',u.object_name) from user_objects u where object_type='SEQUENCE';

--导出序列
nohup expdp \'/ as sysdba\' directory=PUBLIC_DUMP LOGFILE=prod.log dumpfile=expdp_prod-2021-05-21_%U.dmp SCHEMAS=prod COMPRESSION=ALL PARALLEL=4 CLUSTER=N &

nohup impdp \'/ as sysdba\' directory=PUBLIC_DUMP include=sequence LOGFILE=imp_D4_CC_SEQ.log dumpfile=expdp_prod_scfop-2021-07-09.dmp REMAP_SCHEMA=prod:D4_CC REMAP_TABLESPACE=CC_DATA:D4_CC_DATA PARALLEL=2 &