Oracle 12C新特性-数据泵新参数(VIEWS_AS_TABLES)

Oracle 12C 新特性 - 数据泵新参数 (VIEWS_AS_TABLES)   在Oracle 12C 中，数据泵增加了新的参数 VIEWS_AS_TABLES 通过这个参数： expdp: 可以将视图当成表一样导出数据 而不是定义语句 。 VIEWS_AS_TABLES 参数解释： [oracle@cjcos trace]$ expdp help=y VIEWS_AS_TABLES Identifies one or more views to be exported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW. [oracle@cjcos trace]$ impdp help=y VIEWS_AS_TABLES Identifies one or more views to be imported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW. Note that in network import mode, a table name is appended to the view name.   实验如下： 创建测试表和测试视图 SQL> conn cjc/cjc@cjcpdb SQL> create table t1 as select * from dba_objects; SQL> create view v1_t1 as select object_id,object_name from t1; SQL> col tname for a10 SQL> select *from tab; TNAME    TABTYPE   CLUSTERID ---------- ------------- ---------- V1_T1         VIEW T1        TABLE 在12C 之前 , 导出和导入视图方式如下： 导出V1_T1 视图 [root@cjcos ~]# mkdir /dir [root@cjcos ~]# chown oracle.oinstall /dir   SQL> conn sys/oracle@cjcpdb as sysdba SQL> create directory expdp_dir as '/dir'; SQL> grant read,write on directory expdp_dir to cjc; [oracle@cjcos ~]$ expdp cjc/cjc@cjcpdb include=view:"in('V1_T1')"  directory=expdp_dir dumpfile=v1_t1a.dmp logfile=v1_t1a.log Export: Release 19.0.0.0.0 - Production on Mon Feb 10 22:43:23 2020 Version 19.3.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 "CJC"."SYS_EXPORT_SCHEMA_01":  cjc/********@cjcpdb include=view:in('V1_T1') directory=expdp_dir dumpfile=v1_t1a.dmp logfile=v1_t1a.log Processing object type SCHEMA_EXPORT/VIEW/VIEW Master table "CJC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CJC.SYS_EXPORT_SCHEMA_01 is:   /dir/v1_t1a.dmp Job "CJC"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 10 22:45:13 2020 elapsed 0 00:01:37 导入V1_T1 视图 到chen 用户下 SQL> create user chen identified by cjc default tablespace users; SQL> grant connect,resource to chen; [oracle@cjcos ~]$ impdp cjc/cjc@cjcpdb remap_schema=cjc:chen remap_tablespace=cjctbs:users directory=expdp_dir dumpfile=v1_t1a.dmp logfile=v1_t1a_impdp.log Import: Release 19.0.0.0.0 - Production on Tue Feb 11 10:24:35 2020 Version 19.3.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 "CJC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "CJC"."SYS_IMPORT_FULL_01":  cjc/********@cjcpdb remap_schema=cjc:chen remap_tablespace=cjctbs:users directory=expdp_dir dumpfile=v1_t1a.dmp logfile=v1_t1a_impdp.log Processing object type SCHEMA_EXPORT/VIEW/VIEW ORA-39082: Object type VIEW:"CHEN"."V1_T1" created with compilation warnings Job "CJC"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Feb 11 10:24:59 2020 elapsed 0 00:00:13 查看 SQL> conn chen/cjc@cjcpdb SQL> col tname for a10 SQL> select * from tab; TNAME    TABTYPE   CLUSTERID ---------- ------------- ---------- V1_T1    VIEW SQL>   select dbms_metadata.get_ddl('VIEW','V1_T1','CHEN') from dual; 从12C 开始 , 导出视图可以使用 views_as_tables 参数，将视图当做表处理。 expdp: 使用 views_as_tables 参数导出视图数据 [oracle@cjcos ~]$ expdp cjc/cjc@cjcpdb views_as_tables=v1_t1 directory=expdp_dir dumpfile=v1_t1.dmp logfile=v1_t1.log Export: Release 19.0.0.0.0 - Production on Mon Feb 10 22:06:14 2020 Version 19.3.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 "CJC"."SYS_EXPORT_TABLE_01":  cjc/********@cjcpdb views_as_tables=v1_t1 directory=expdp_dir dumpfile=v1_t1.dmp logfile=v1_t1.log Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE . . exported "CJC"."V1_T1"                               2.990 MB   72488 rows Master table "CJC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CJC.SYS_EXPORT_TABLE_01 is:   /dir/v1_t1.dmp Job "CJC"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 10 22:07:45 2020 elapsed 0 00:00:56 impdp 导入数据 [oracle@cjcos ~]$ impdp cjc/cjc@cjcpdb remap_table=v1_t1:v2_t1 directory=expdp_dir dumpfile=v1_t1.dmp   Import: Release 19.0.0.0.0 - Production on Mon Feb 10 22:23:45 2020 Version 19.3.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 "CJC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "CJC"."SYS_IMPORT_FULL_01":  cjc/********@cjcpdb remap_table=v1_t1:v2_t1 directory=expdp_dir dumpfile=v1_t1.dmp Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA . . imported "CJC"."V2_T1"                               2.990 MB   72488 rows Job "CJC"."SYS_IMPORT_FULL_01" successfully completed at Mon Feb 10 22:24:08 2020 elapsed 0 00:00:18 查看 SQL> conn cjc/cjc@cjcpdb SQL> select * from tab; TNAME    TABTYPE   CLUSTERID ---------- ------------- ---------- V1_T1    VIEW T1       TABLE V2_T1    TABLE SQL>   select dbms_metadata.get_ddl('TABLE','V2_T1','CJC') from dual; 欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！