Exporting views as tables Oracle Database 12C release 1 (12.1)

This feature extends functionality of Data Pump 12C. It allows to export views as tables.

expdp .. VIEWS_AS_TABLES=[schema_name.]view_name ...

Features

  • new parameter for expdp VIEWS_AS_TABLES
  • it exports view as table definition plus data
  • it exports dependent objects like constraints(on the view) and grants(on the view)
  • very useful to export subset of a data from table(s) via complex view
  • during import as first step table is created then data is imported
  • it can be exported in PL/SQL using DBMS_DATAPUMP.METADATA_FILTER
  • doesn’t support views that reference object type or functions
  • unloads data as unencrypted format

Examples

Export of view emp_view”as table and table departments

expdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view TABLES=departments

Export of two views as tables emp_view and oe.orders_v

expdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view,oe.orders_v

Import of exported view as table emp_view and rename the table as emp_new_table

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view REMAP_TABLE=emp_view:emp_new_table

Import through data base link db_link via view emp_v and loading it directly to existing table employees

impdp hr/hr VIEWS_AS_TABLE=emp_v NETWORK_LINK=db_link
REMAP_TABLE=emp_v:employees TABLE_EXISTS_ACTION=append

Have a fun šŸ™‚

Tomasz

 

 

5 thoughts on “Exporting views as tables Oracle Database 12C release 1 (12.1)

    • Just create external table as CREATE TABLE AS SELECT, yet it’s not the same. Anyway you will be able to attach such table to other database.

      Regards
      Tomasz

      • yes, it’s working but here, in my case I need to load dump file which is associated with external table and created by “create external table as CREATE TABLE AS SELECT” into another database using sql loader.

        Is there a way to achieve it?

        Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *