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