本文目录

• 背景
• 演示不拆分的导出导入
• 导出
• 删除原表
• 导入原库
• 检查测试表的数据

背景

生产的数据泵，一般要根据数据量在大小决定是否拆分成元数据与纯数据的导出导入。
这样就会有一个注意点:只读表的问题.
只读表是否能正常导出与导入?
如果是元数据与数据一起导入,到遇到上面的问题吗?

结果:

1. 由于元数据已经在目标库导入,就会导入只读表的只读属性也附带上面.

2. 这会导入数据在导入时,sql无法insert进入.

3. 元数据与纯数据一起导出导入时,会没有这个问题.

演示不拆分的导出导入

构建数据

CZQTEST@192.168.6.11:1521/gdsndb 2021-11-04 14:55:19 >create table test1 (name varchar2(20),age number,address varchar2(55));

Table created.

CZQTEST@192.168.6.11:1521/gdsndb 2021-11-04 14:55:56 >insert into test1 values ('czq','22','guangdong province maoming city');

1 row created.
CZQTEST@192.168.6.11:1521/gdsndb 2021-11-04 15:02:23 >select * from test1;

NAME | AGE|ADDRESS
----------------------------------------|----------|-------------------------------------------------------
czq | 22|guangdong province maoming city

复制

导出

[oracle@cdb1:/home/oracle/datadump czqcdb1]$ expdp 'czqtest/czqtest@192.168.6.11:1521/gdsndb' tables=test1 dumpfile=test1_%U.dmp directory=datapumpdir

Export: Release 19.0.0.0.0 - Production on Thu Nov 4 15:02:02 2021
Version 19.12.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 "CZQTEST"."SYS_EXPORT_TABLE_01": czqtest/********@192.168.6.11:1521/gdsndb tables=test1 dumpfile=test1_%U.dmp directory=datapumpdir
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "CZQTEST"."TEST1" 5.929 KB 1 rows
Master table "CZQTEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CZQTEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/datadump/test1_01.dmp
Job "CZQTEST"."SYS_EXPORT_TABLE_01" successfully completed at Thu Nov 4 15:02:37 2021 elapsed 0 00:00:30


复制

删除原表

CZQTEST@192.168.6.11:1521/gdsndb 2021-11-04 15:02:33 >drop table test1 purge;

Table dropped.

复制

导入原库

[oracle@cdb1:/home/oracle/datadump czqcdb1]$ impdp 'czqtest/czqtest@192.168.6.11:1521/gdsndb' dumpfile=test1_%U.dmp directory=datapumpdir

Import: Release 19.0.0.0.0 - Production on Thu Nov 4 15:03:10 2021
Version 19.12.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
Master table "CZQTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CZQTEST"."SYS_IMPORT_FULL_01": czqtest/********@192.168.6.11:1521/gdsndb dumpfile=test1_%U.dmp directory=datapumpdir
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CZQTEST"."TEST1" 5.929 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "CZQTEST"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 4 15:03:56 2021 elapsed 0 00:00:46


复制

检查测试表的数据

CZQTEST@192.168.6.11:1521/gdsndb 2021-11-04 15:02:47 >select * from test1;

NAME | AGE|ADDRESS
----------------------------------------|----------|-------------------------------------------------------
czq | 22|guangdong province maoming city

CZQTEST@192.168.6.11:1521/gdsndb 2021-11-04 15:03:48 >select read_only from dba_tables where table_name='TEST1';

REA
---
YES

复制