本文目录
背景
生产的数据泵,一般要根据数据量在大小决定是否拆分成元数据与纯数据的导出导入。
这样就会有一个注意点:只读表的问题.
只读表是否能正常导出与导入?
如果是元数据与数据一起导入,到遇到上面的问题吗?
结果:
-
由于元数据已经在目标库导入,就会导入只读表的只读属性也附带上面.
-
这会导入数据在导入时,sql无法insert进入.
-
元数据与纯数据一起导出导入时,会没有这个问题.
演示不拆分的导出导入
构建数据
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
复制
最后修改时间:2021-11-17 14:00:32
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。