[20130727]ORACLE 12C使用expdp匯出view資料.txt

[20130727]ORACLE 12C使用expdp匯出view資料.txt

12C新特性裡面可以定義試圖，然後透過view當作表一樣匯出資料，然後匯入資料庫，自己做一個測試：

1.建立測試環境:

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create view v_emp_dept as select emp.*,dept.dname from dept,emp where dept.deptno=emp.deptno;

--system使用者執行
CREATE OR REPLACE DIRECTORY tmp_expdp AS 'D:\tmp\expdp\';

SQL> GRANT READ, WRITE ON DIRECTORY tmp_expdp TO scott;
Grant succeeded.

SQL> grant  EXP_FULL_DATABASE to scott;
Grant succeeded.

SQL> grant  IMP_FULL_DATABASE to scott;
Grant succeeded.


2.expdp匯出檢視資訊以及資料：
d:\tmp>expdp scott/tiger@test01p views_as_tables=v_emp_dept directory=tmp_expdp dumpfile=emp_dept.dmp

Export: Release 12.1.0.1.0 - Production on Sun Jul 28 21:50:26 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@test01p views_as_tables=v_emp_dept directory=tmp_expdp dumpfile=emp_dept.dmp */
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SCOTT"."V_EMP_DEPT"                        9.296 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:\TMP\EXPDP\EMP_DEPT.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jul 28 21:50:41 2013 elapsed 0 00:00:13


3.impdp匯入:
d:\tmp>impdp scott/tiger@test01p  directory=tmp_expdp dumpfile=emp_dept.dmp

Import: Release 12.1.0.1.0 - Production on Sun Jul 28 21:54:57 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/********@test01p directory=tmp_expdp dumpfile=emp_dept.dmp */
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
ORA-39325: TABLE_EXISTS_ACTION cannot be applied to "SCOTT"."V_EMP_DEPT".
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sun Jul 28 21:55:07 2013 elapsed 0 00:00:07

--檢視存在，無法匯入，改名檢視，或者使用remap_table引數。選擇修改檢視看看。
SQL> rename V_EMP_DEPT to V_EMP_DEPT1;
Table renamed.

d:\tmp>impdp scott/tiger@test01p  directory=tmp_expdp dumpfile=emp_dept.dmp
Import: Release 12.1.0.1.0 - Production on Sun Jul 28 22:00:19 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/********@test01p directory=tmp_expdp dumpfile=emp_dept.dmp */
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SCOTT"."V_EMP_DEPT"                        9.296 KB      14 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Sun Jul 28 22:00:24 2013 elapsed 0 00:00:03

SQL> column object_name format a20

SQL> select object_id,data_object_id,object_name,object_type  from  dba_objects where wner=user and object_name in ('V_EMP_DEPT','V_EMP_DEPT1');

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME          OBJECT_TYPE

---------- -------------- -------------------- -----------------------

     92488                V_EMP_DEPT1          VIEW

     92776          92776 V_EMP_DEPT           TABLE

--可以發現匯入後，建立了新表v_emp_dept.