12c datapump new features - part 3 - VIEWS_AS_TABLES

Nice new feature this one - allows extracting of a view definition and it's data as if it is a table.

First we connect and create a simple view

[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# export TWO_TASK=//localhost:1525/test.e-ssi.net
[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 15:00:45 2013

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

Last Successful login time: Tue Jul 23 2013 14:59:13 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create view example as select * from session_roles;

View created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Now we extract that using the new parameter

[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# expdp test/test views_as_tables=example directory=test dumpfile=example.dmp

Export: Release 12.1.0.1.0 - Production on Tue Jul 23 15:01:43 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** views_as_tables=example directory=test dumpfile=example.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "TEST"."EXAMPLE"                            5.523 KB      24 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /tmp/example.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 23 15:01:51 2013 elapsed 0 00:00:06

Now we drop the view and import it back in as a table

[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 15:02:23 2013

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

Last Successful login time: Tue Jul 23 2013 15:01:43 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> drop view example;

View dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# impdp test/test directory=test dumpfile=example.dmp

Import: Release 12.1.0.1.0 - Production on Tue Jul 23 15:02:46 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** directory=test dumpfile=example.dmp
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "TEST"."EXAMPLE"                            5.523 KB      24 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 23 15:02:50 2013 elapsed 0 00:00:02

Now check all looks OK

[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 15:02:56 2013

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

Last Successful login time: Tue Jul 23 2013 15:02:46 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> desc example
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(128)


SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
EXAMPLE
A


SQL>

Comments