数据泵的view

数据泵的view"/>

数据泵的view

--测试19c(RDBMS19.21)的expdp的参数VIEWS_AS_TABLES  （这个参数是12c开始的参数）

1 在test库下，导出视图bb.v_obj1,bb.v_obj5
2 在bak库下，导入视图

结论：
1 在bak库下，不会导入视图对应的基表
2 在bak库下，导入的视图，是以表的形式存在的 

该参数的帮助
VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

-- 在test库下，导出视图bb.v_obj1,bb.v_obj5

[oracle@19ctest dump]$ expdp \'/ as sysdba\' directory=DUMP dumpfile=exp_v.dmp logfile=exp_v.log  VIEWS_AS_TABLES=bb.v_obj1,bb.v_obj5Export: Release 19.0.0.0.0 - Production on Sat Nov 11 09:53:37 2023
Version 19.21.0.0.0Copyright (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 "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DUMP dumpfile=exp_v.dmp logfile=exp_v.log VIEWS_AS_TABLES=bb.v_obj1,bb.v_obj5
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "BB"."V_OBJ1"                               6.564 MB   51830 rows
. . exported "BB"."V_OBJ5"                               9.985 MB   80326 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:/u01/dump/exp_v.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Nov 11 09:54:03 2023 elapsed 0 00:00:18[oracle@19ctest dump]$

-- 在bak库下导入，导入报错，因为没有bb这个用户，从这里也可以看出，导入的时候，是建立表，以表的方式导入的

[oracle@19ctest ~]$ impdp \'/ as sysdba\' directory=DUMP dumpfile=exp_v.dmp logfile=imp_v.logImport: Release 19.0.0.0.0 - Production on Sat Nov 11 10:01:16 2023
Version 19.21.0.0.0Copyright (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 "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in US7ASCII character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=DUMP dumpfile=exp_v.dmp logfile=imp_v.log
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
ORA-39083: Object type TABLE:"BB"."V_OBJ5" failed to create with error:
ORA-01918: user 'BB' does not existFailing sql is:
CREATE TABLE "BB"."V_OBJ5" ("OWNER" VARCHAR2(128 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(128 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128 BYTE), "SHARING" VARCHAR2(18 BYTE), "EDITIONABLE" VARCHAR2(1 BYTE), "ORACLE_MAINTAINED" VARCHAR2(1 BYTE), "APPLICATION" VARCHAR2(1 BYTE), "DEFAULT_COLLATION" VARCHAR2(100 BYTE), "DUPLICATED" VARCHAR2(1 BYTE), "SHARDED" VARCHAR2(1 BYTE), "CREATED_APPID" NUMBER, "CREATED_VSNID" NUMBER, "MODIFIED_APPID" NUMBER, "MODIFIED_VSNID" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING TABLESPACE "USERS"ORA-39083: Object type TABLE:"BB"."V_OBJ1" failed to create with error:
ORA-01918: user 'BB' does not existFailing sql is:
CREATE TABLE "BB"."V_OBJ1" ("OWNER" VARCHAR2(128 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(128 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128 BYTE), "SHARING" VARCHAR2(18 BYTE), "EDITIONABLE" VARCHAR2(1 BYTE), "ORACLE_MAINTAINED" VARCHAR2(1 BYTE), "APPLICATION" VARCHAR2(1 BYTE), "DEFAULT_COLLATION" VARCHAR2(100 BYTE), "DUPLICATED" VARCHAR2(1 BYTE), "SHARDED" VARCHAR2(1 BYTE), "CREATED_APPID" NUMBER, "CREATED_VSNID" NUMBER, "MODIFIED_APPID" NUMBER, "MODIFIED_VSNID" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING TABLESPACE "USERS"Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sat Nov 11 10:01:19 2023 elapsed 0 00:00:03[oracle@19ctest ~]$

-- bak库下，创建用户后，再次导入，成功 

[oracle@19ctest ~]$ impdp \'/ as sysdba\' directory=DUMP dumpfile=exp_v.dmp logfile=imp_v.logImport: Release 19.0.0.0.0 - Production on Sat Nov 11 10:02:26 2023
Version 19.21.0.0.0Copyright (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 "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in US7ASCII character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=DUMP dumpfile=exp_v.dmp logfile=imp_v.log
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "BB"."V_OBJ1"                               6.564 MB   51830 rows
. . imported "BB"."V_OBJ5"                               9.985 MB   80326 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sat Nov 11 10:02:30 2023 elapsed 0 00:00:03[oracle@19ctest ~]$

-- 导入的仅仅是视图，还是连基表也导入了？在bak库下查看视图，无视图，查看表，有两个表，刚导入的，是以表的形式存在的 

SYS@bak SQL> select text_vc from dba_views where owner='BB';no rows selectedSYS@bak SQL> select count(*) from dba_views where owner='BB';COUNT(*)
----------0SYS@bak SQL>SYS@bak SQL> select table_name from dba_tables where owner='BB';TABLE_NAME
--------------------------------------------------------------------------------
V_OBJ5
V_OBJ1SYS@bak SQL>

-- 生成sql脚本，看看内容，导入的时候，以表的方式导入

[oracle@19ctest ~]$ impdp \'/ as sysdba\' directory=DUMP dumpfile=exp_v.dmp logfile=imp_v_sql.log sqlfile=imp_v.sqlImport: Release 19.0.0.0.0 - Production on Sat Nov 11 10:16:52 2023
Version 19.21.0.0.0Copyright (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 "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
import done in US7ASCII character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_SQL_FILE_FULL_01":  "/******** AS SYSDBA" directory=DUMP dumpfile=exp_v.dmp logfile=imp_v_sql.log sqlfile=imp_v.sql
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Sat Nov 11 10:16:54 2023 elapsed 0 00:00:01[oracle@19ctest ~]$[oracle@19ctest dump]$ cat imp_v.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
CREATE TABLE "BB"."V_OBJ5"(    "OWNER" VARCHAR2(128 BYTE),"OBJECT_NAME" VARCHAR2(128 BYTE),"SUBOBJECT_NAME" VARCHAR2(128 BYTE),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT_TYPE" VARCHAR2(23 BYTE),"CREATED" DATE,"LAST_DDL_TIME" DATE,

END