EXPORT VIEWS USING DATAPUMP

In this article, we will see how to export a view using datapump from database using datapump

[oracle@orcl19x ~]$ expdp -help|egrep '^VIEWS|view'

VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.

Create a view for exporting from database

SQL> create view test_vu as select * from test;

View created.

You can also get the metadata of view using dbms_metadata package and get the script to create the view on another database. But this will be more manual way to create if there are multiple views to be exported

SQL> set numwidth 50
SQL> set long 10000000
SQL> select dbms_metadata.get_ddl('VIEW','TEST_VU') from dual;

DBMS_METADATA.GET_DDL('VIEW','TEST_VU')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."TEST_VU" ("NAME", "NUM") AS

  select "NAME","NUM" from test

In oracle database 12c version, a new feature was introduced to export and import views called ‘views_as_tables‘. Export the views using expdp like a table. By default if you dont specify any schema name, ‘sys’ user will be taken

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu

Export: Release 12.1.0.2.0 - Production on Mon Jun 28 23:28:09 2021

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

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":  "/******** AS SYSDBA" directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu
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"."TEST_VU"                             5.656 KB      13 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /data01/view_exp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 28 23:29:00 2021 elapsed 0 00:00:09

You can also import it directly to the database

[oracle@orcl19x /]$ impdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=imp_view_exp.log views_as_tables=test_vu

Import: Release 12.1.0.2.0 - Production on Mon Jun 28 23:50:12 2021

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

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":  "/******** AS SYSDBA" directory=DATAPUMP dumpfile=view_exp.dmp logfile=imp_view_exp.log views_as_tables=test_vu
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."TEST_VU"                             5.656 KB      13 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jun 28 23:50:54 2021 elapsed 0 00:00:01

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s