ORACLE 12C可以通过expdp导出view数据

发表于 2012 年 12 月 20 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORACLE 12C可以通过expdp导出view数据

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有时候,多么的希望ORACLE能够导出某个视图中的数据,然后通过这个视图来迁移需要的数据,现在ORACLE 12C通过expdp的views_as_tables来实现了该功能,把视图当作一个普通表从而导出数据,导入的时候直接和一个正常表一样,通过视图的导出,表的导入来实现相关需求
准备测试环境

SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                

SQL> create table t_lx as select * from v$log; 

Table created.

SQL> create table t_xl as select * from v$logfile;

Table created.

SQL> create view v_xifenfei  as
  2   SELECT thread#,
  3           a.sequence#,
  4           a.group#,
  5           TO_CHAR (first_change#, '9999999999999999') "SCN",
  6           a.status,
  7           MEMBER
  8      FROM t_lx a, t_xl b
  9     WHERE a.group# = B.GROUP#
 10  ORDER BY a.sequence# DESC;

View created.

SQL> col member for a50    
SQL> set lines 134
SQL> select * from v_xifenfei;

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER
---------- ---------- ---------- ----------------- ---------------- -------------------------------------------
         1         30          3            391892 CURRENT          /u01/app/oracle/oradata/xifenfei/redo03.log
         1         29          2            377363 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo02.log
         1         28          1            374892 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo01.log

expdp结合VIEWS_AS_TABLES导出视图

[oracle@xifenfei ~]$ expdp xff/xifenfei views_as_tables=v_xifenfei directory=data_pump_dir dumpfile=xifenfei.dmp

Export: Release 12.1.0.0.2        on Sun Dec 16 07:56:48 2012

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XFF"."SYS_EXPORT_TABLE_01":xff/******** views_as_tables=v_xifenfei directory=data_pump_dir
 dumpfile=xifenfei.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 "XFF"."V_XIFENFEI"                          7.390 KB       3 rows
Master table "XFF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/xifenfei/dpdump/xifenfei.dmp
Job "XFF"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 16 07:58:17 2012 elapsed 0 00:00:56

impdp导入数据

[oracle@xifenfei ~]$ impdp xff/xifenfei remap_table=v_xifenfei:v_xff directory=data_pump_dir dumpfile=xifenfei.dmp

Import: Release 12.1.0.0.2        on Sun Dec 16 08:06:06 2012

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":xff/******** remap_table=v_xifenfei:v_xff directory=data_pump_dir
 dumpfile=xifenfei.dmp 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "XFF"."V_XFF"                               7.390 KB       3 rows
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sun Dec 16 08:06:20 2012 elapsed 0 00:00:10

验证数据

SQL> COL OBJECT_NAME FOR A20
SQL> select object_type,object_name from user_objectS where object_name like 'V_X%';

OBJECT_TYPE             OBJECT_NAME
----------------------- --------------------
VIEW                    V_XIFENFEI
TABLE                   V_XFF

SQL> col member for a50
SQL> set lines 134
SQL> select * from v_XFF;

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------
         1         30          3            391892 CURRENT          /u01/app/oracle/oradata/xifenfei/redo03.log
         1         29          2            377363 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo02.log
         1         28          1            374892 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo01.log

通过测试证明在12C中ORACLE的expdp/impdp可以实现导出视图数据,进入导入到[其他库]其他表中

此条目发表在 ORACLE 12C 分类目录，贴了 标签。将固定链接加入收藏夹。

《ORACLE 12C可以通过expdp导出view数据》有 3 条评论

  1. 惜分飞 说：
    2012 年 12 月 20 日22:58

    views_as_tables含table_name参数执行失败

    [oracle@Lunar trace]$ expdp xff/xifenfei views_as_tables=XFF.V_XIFENFEI:T_XIFENFEI directory=data_pump_dir
  \ dumpfile=xff.dmp  

Export: Release 12.1.0.0.2 - Beta on Sun Dec 16 09:26:45 2012

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XFF"."SYS_EXPORT_TABLE_01":  xff/******** views_as_tables=XFF.V_XIFENFEI:T_XIFENFEI 
directory=data_pump_dir dumpfile=xff.dmp
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object XFF.V_XIFENFEI was not found.
ORA-31655: no data or metadata objects selected for job
Job "XFF"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Sun Dec 16 09:26:55 2012 elapsed 0 00:00:05

    出现这个问题有几种情况：
    1.Utilities文档错误,因为expdp help=y中确实没有该用法
    2.beta版本不成熟,存在bug

  2. 惜分飞 说：
    2012 年 12 月 20 日22:07

    VIEWS_AS_TABLES参数具体说明

    VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...
Data Pump exports a table with the same columns as the view and with row data fetched from the view. 
Data Pump also exports objects dependent on the view, such as grants and constraints. 
Dependent objects that do not apply to tables (for example, grants of the UNDER object privilege) 
are not exported.The VIEWS_AS_TABLES parameter can be used by itself or along with the TABLES parameter. 
If either is used, Data Pump performs a table-mode export.
The syntax elements are defined as follows:
schema_name--The name of the schema in which the view resides. 
If a schema name is not supplied, it defaults to the user performing the export.

view_name--The name of the view to be exported as a table. 
The view must exist and it must be a relational view with only scalar, non-LOB columns. 
If you specify an invalid or non-existent view, the view is skipped and an error message is returned.

table_name--The name of a table to serve as the source of the metadata for the exported view. 
By default Data Pump automatically creates a temporary "template table" with the same columns 
and data types as the view, but no rows. If the database is read-only, 
then this default creation of a template table will fail. In such a case, 
you can specify a table name. The table must be in the same schema as the view. 
It must be a non-partitioned relational table with heap organization. It cannot be a nested table.

If the export job contains multiple views with explicitly specified template tables, 
the template tables must all be different. 

For example, in the following job (in which two views use the same template table) one of the views is skipped:
expdp scott/tiger directory=dpump_dir dumpfile=a.dmp views_as_tables=v1:emp,v2:emp
An error message is returned reporting the omitted object.
Template tables are automatically dropped after the export operation is completed. 
While they exist, you can perform the following query to view their names (which all begin with KU$VAT):
SQL> SELECT * FROM user_tab_comments WHERE table_name LIKE 'KU$VAT%';
TABLE_NAME TABLE_TYPE
------------------------------ -----------
COMMENTS
-----------------------------------------------------
KU$VAT_63629 TABLE
Data Pump metadata template table for view SCOTT.EMPV
  3. 惜分飞 说：
    2012 年 12 月 20 日22:05

    存在相同的视图或者表报错

    [oracle@xifenfei ~]$ impdp xff/xifenfei tables=v_xifenfei directory=data_pump_dir dumpfile=xifenfei.dmp

Import: Release 12.1.0.0.2 - Beta on Sun Dec 16 07:59:05 2012

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "XFF"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_TABLE_01":  xff/******** tables=v_xifenfei directory=data_pump_dir
 dumpfile=xifenfei.dmp 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
ORA-39325: TABLE_EXISTS_ACTION cannot be applied to "XFF"."V_XIFENFEI".
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Job "XFF"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sun Dec 16 07:59:13 2012 elapsed 0 00:00:05