Datapump en 12c, incorpora un nuevo parámetro "VIEWS_AS_TABLES". Con este parámetro, podemos exportar una vista desde una base de datos origen e importarla como tabla en una base destino.
Veamos como funciona esto:
Creamos primero una vista:
SQL> create view dba_view( emp_number) as select emp from dbatool;
View created.
Verificamos:
SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW';
OWNER OBJECT_NAM OBJECT_TYPE
------------------ ---------- -----------------------
SYS DBA_VIEW VIEW
Realizamos un export de la vista, como tabla, utilizando el parámetro: views_as_tables
[oracle@localhost ~]$ expdp dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW
Export: Release 12.1.0.2.0 - Production on Sat Aug 22 07:07:54 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: sys/oracle@ORCL as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SYS"."DBA_VIEW" 5.117 KB 8 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/DUMP/view.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 22 07:08:17 2015 elapsed 0 00:00:11
Ahora hacemos un drop de la vista y la importamos desde el dump (export) que tomamos previamente.
SQL> drop view dba_view;
View dropped.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Import:
[oracle@localhost ~]$ impdp dumpfile=view.dmp logfile=view.log directory=DUMP tables=DBA_VIEW
Import: Release 12.1.0.2.0 - Production on Sat Aug 22 07:09:09 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: sys/oracle@ORCL as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP tables=DBA_VIEW
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."DBA_VIEW" 5.117 KB 8 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Aug 22 07:10:21 2015 elapsed 0 00:00:04
Verificamos ahora que tipo de objeto es DBA_VIEW:
[oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba
SQL> set pagesize 200
SQL> set lines 200
SQL> set long 999
SQL> col owner for a9
SQL> col object_name for a10
SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW';
OWNER OBJECT_NAM OBJECT_TYPE
--------- ---------- -----------------------
SYS DBA_VIEW TABLE
Como podemos ver, DBA_VIEW fue exportada como vista y luego el import es una tabla. Esto lo logramos utilizando esta nueva funcionalidad de datapump.
Fuente: http://oracle-help.com/oracle-dbaas/views_as_tables-parameter-datapump-oracle-12c/ (Skant Gupta)
No hay comentarios:
Publicar un comentario