Oracle Database 12c: Data pump views_as_tables parameter (New feature)

Hello everyone!!
How many times, you would like to converted a view into a table during an export or import process.
Well, now on Oracle 12c this is can be possible with the views_as_tables parameter, with this parameter allows you to convert a view into a table during either an export to a dump file or a import from a dump file.
During the process of export dump file, the views listed into the parameter views_as_tables are converted during the process.NOTE: IF the source objects are encrypted, the data exported will not be encrypted, so you will have encrypt the Data Pump export itself.
You don’t need to have a dump file exported with the views_as_tables parameter, to use the views_as_tables in the import, you can make the load from directly dump file.

Let’s start:

  • I will use this example on my schema test. Of course to achieve this you will need the necessary permissions on users to accomplish this activity.

[oracle@srvdb12c ~]$ expdp usrapp/oracle@salespdb directory=dump_dir \ dumpfile=usrappdp.dmp  views_as_tables=PRICE_VW, PRODUCTS_VW

Export: Release 12.1.0.2.0 – Production on Wed Aug 19 21:31:28 2015

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 “USRAPP”.”SYS_EXPORT_TABLE_01″: usrapp/********@salespdb directory=dump_dir dumpfile=usrappdp.dmp views_as_tables=PRICE_VW, PRODUCTS_VW
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported “USRAPP”.”PRICE_VW” 10.20 MB 89434 rows
. . exported “USRAPP”.”PRODUCTS_VW” 10.38 MB 91041 rows
Master table “USRAPP”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for USRAPP.SYS_EXPORT_TABLE_01 is:
/home/oracle/usrappdp.dmp
Job “USRAPP”.”SYS_EXPORT_TABLE_01″ successfully completed at Wed Aug 19 21:31:46 2015 elapsed 0 00:00:15

  • How you can see I listed the views witch I want on the views_as_tables parameters.
  • Now I will import the dump file into my schema TESTAPP user.

[oracle@srvdb12c ~]$ impdp testapp/oracle@salespdb views_as_tables=usrapp.price_vw, usrapp.products_vw directory=dump_dir dumpfile=usrappdp.dmp remap_schema=usrapp:testapp remap_table=price_vw:price_tb, products_vw:products_tb

Import: Release 12.1.0.2.0 – Production on Wed Aug 19 21:58:57 2015

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 “TESTAPP”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “TESTAPP”.”SYS_IMPORT_TABLE_01″: testapp/********@salespdb views_as_tables=usrapp.price_vw, usrapp.products_vw directory=dump_dir dumpfile=usrappdp.dmp remap_schema=usrapp:testapp remap_table=price_vw:price_tb, products_vw:products_tb
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported “TESTAPP”.”PRICE_TB” 10.20 MB 89434 rows
. . imported “TESTAPP”.”PRODUCTS_TB” 10.38 MB 91041 rows
Job “TESTAPP”.”SYS_IMPORT_TABLE_01″ successfully completed at Wed Aug 19 21:59:18 2015 elapsed 0 00:00:16

Now after the import if you query the user_objects from testapp you will see the old views PRICE_VW and PRODUCTS_VW as tables PRICE_TB and PRODUCTS_TB.

SQL> select object_name, object_type from user_objects;

OBJECT_NAME    OBJECT_TYPE
———————-   ———————–
PRICE_TB              TABLE
PRODUCTS_TB     TABLE

I hope that you enjoy!

Cheers, Felipe!

The command below shows you how to run with the export command:

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s