Expdp using VIEWS_AS_TABLES in Oracle 19c

I got a chance to test views_as_tables recently. My friend wanted to take backup of few views which will have content in it. Since database version for her was Oracle 12c, I suggested for VIEWS_AS_TABLES. But here is the catch –

She wanted to export specific rows from the view.

Traditional way of doing this will be –

create table <table> select * from <view> where ....

Then taking the export backup of it. After Oracle 12c, oracle expdp has an parameter called – VIEWS_AS_TABLES This parameter will take care of all the manual work here.

Limitation

1. Compatible parameter of the database should be 12c or above –

Export: Release 19.0.0.0.0 - Production on Tue Oct 18 20:15:08 2022
Version 19.15.0.0.0

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

Username: / as sysdba

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
- Production
ORA-39005: inconsistent arguments
ORA-39055: The VIEWS_AS_TABLES feature is not supported in version
11.2.0.4.0.

2. The VIEWS_AS_TABLES parameter cannot be used with the TRANSPORTABLE=ALWAYS parameter.
3. Tables created using the VIEWS_AS_TABLES parameter do not contain any hidden columns that were part of the specified view.
4. The VIEWS_AS_TABLES parameter does not support tables that have columns with a data type of LONG.

How to Use VIEWS_AS_TABLES

Syntax –

VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...

schema_name The name of the schema in which the view resides. If a schema name is not supplied, it defaults to the user performing the export.
view_name The name of the view to be exported as a table. The view must exist and it must be a relational view with only scalar, non-LOB columns. If you specify an invalid or non-existent view, the view is skipped and an error message is returned.
table_name The name of a table to serve as the source of the metadata for the exported view. By default Data Pump automatically creates a temporary “template table” with the same columns and data types as the view, but no rows. If the database is read-only, then this default creation of a template table will fail. In such a case, you can specify a table name. The table must be in the same schema as the view. It must be a non-partitioned relational table with heap organization. It cannot be a nested table.

https://docs.oracle.com

Here is the test case –

To begin with the test case I have created following for this example.

1. I have created a table using dba_objects
2. I have created a view using that table
3. I have create a view using view created in step 2.

$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 18 20:18:01 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> create table dbsnmp.oracledbworld as select * from dba_objects;

Table created.

SQL> create view dbsnmp.oracledbworld_view as select * from dbsnmp.oracledbworld where object_name not like 'BIN%';

View created.

SQL> create view dbsnmp.oracledbworld_view_2 as select * from dbsnmp.oracledbworld_view where owner='DBSNMP';

View created.

SQL> select count(1) from dbsnmp.oracledbworld_view;

   COUNT(1)
----------
      97530

SQL> select count(1) from dbsnmp.oracledbworld_view_2
   2  ;

   COUNT(1)
----------
        162


SQL> create directory oracledbworld2 as '/orahome/oracle';

Directory created.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.15.0.0.0

Now it’s time to take a export backup –

$ expdp directory=oracledbworld2 dumpfile=test_oracledbworld_dump.dmp logfile=test_oracledbworld_dump.log VIEWS_AS_TABLES=dbsnmp.oracledbworld_view

With above statement, it will create a table object in dumpfile which will contain all the rows which are result of select * from dbsnmp.oracledbworld_view;

I have created a new view – oracledbworld_view_2, which was pointing to an view – oracledbworld_view. which was also successful for me.

$ expdp directory=oracledbworld2 dumpfile=test_oracledbworld_dump.dmp logfile=test_oracledbworld_dump.log VIEWS_AS_TABLES=dbsnmp.oracledbworld_view_2

Export: Release 19.0.0.0.0 - Production on Tue Oct 18 20:19:16 2022
Version 19.15.0.0.0

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

Username: / as sysdba

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
- Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_02":  /******** AS SYSDBA
directory=oracledbworld2 dumpfile=test_oracledbworld_dump.dmp
logfile=test_oracledbworld_dump.log VIEWS_AS_TABLES=dbsnmp.oracledbworld_view_2
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "DBSNMP"."oracledbworld_VIEW_2"                    35.19 KB 162 rows
Master table "SYS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_02 is:
   /orahome/oracle/test_oracledbworld_dump.dmp
Job "SYS"."SYS_EXPORT_TABLE_02" successfully completed at Tue Oct 18 20:19:50 2022 elapsed 0 00:00:28

Certainly Helpful over Query Parameter?

Not really!

But yes if you ask me for data specific then you can avoid syntax errors while taking export backup of a table using query option. That is the only advantage I can see here.

Query Parameter where loads and unloads based on actual table where as VIEWS_AS_TABLES create a new object with content of view hence VIEWS_AS_TABLES avoid dependent objects of those tables.