记一次 数据泵的表逻辑备份、迁移

授权

grant dba to user;

导出

expdp user/passwd dumpfile=data_pump_dir:hs_tables_%U.dat tables=T_QYHSJC_CHECK_RESULT1 nologfile = y
                   --设置导出文件名、格式                    泵出的表（多表用,分隔）
 #导出                  
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "LUAN_ZXK"."SYS_EXPORT_TABLE_01":  luan_zxk/******** dumpfile=data_pump_dir:hs_tables_%U.dat tables=T_QYHSJC_CHECK_RESULT1 nologfile=y
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/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "LUAN_ZXK"."T_QYHSJC_CHECK_RESULT1"         55.79 GB 127884398 rows
Master table "LUAN_ZXK"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for LUAN_ZXK.SYS_EXPORT_TABLE_01 is:
  /inspur/app/oracle/admin/orcl/dpdump/hs_tables_01.dat
Job "LUAN_ZXK"."SYS_EXPORT_TABLE_01" successfully completed at Fri Nov 25 14:36:22 2022 elapsed 0 00:09:44

#单表55.79 GB 127884398 rows  25字段、单列索引,近10分钟导出

导入

impdp user/paaawd dumpfile=data_pump_dir:hs_tables_%U.dat nologfile = y tables=T_QYHSJC_CHECK_RESULT1

#报错
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "LUAN_ZXK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "LUAN_ZXK"."SYS_IMPORT_FULL_01":  LUAN_ZXK/******** dumpfile=data_pump_dir:hs_tables_%U.dat
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01653: unable to extend table LUAN_ZXK.T_QYHSJC_CHECK_RESULT1 by 8192 in tablespace LUAN_ZXK
Resumable stmt:  BEGIN    SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_01', 'LUAN_ZXK', 0, 0);  END;
Resumable stmt status: SUSPENDED

#扩容下表空间即可

查看执行job

#dump job执行
select * from dba_datapump_jobs