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"，共同學習，共同成長！！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2675085/，如需轉載，請註明出處，否則將追究法律責任。