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
Post a Comment