VIEWS_AS_TABLES parameter has been introduced in datapump of 12c. With this we can export a view at source database and import as table in target database.
Create a view:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> create view dba_view( emp_number) as select emp from dbatool;
View created.
SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW';
OWNER OBJECT_NAM OBJECT_TYPE
------------------ ---------- -----------------------
SYS DBA_VIEW VIEW
|
Take export of this view as table using views_as_tables parameter:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
[oracle@localhost ~]$ expdp dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW
Export: Release 12.1.0.2.0 - Production on Sat Aug 22 07:07:54 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: sys/oracle@ORCL as sysdba
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 "SYS"."SYS_EXPORT_TABLE_01": sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SYS"."DBA_VIEW" 5.117 KB 8 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/DUMP/view.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 22 07:08:17 2015 elapsed 0 00:00:11
|
Now drop the view and import the same from the export dump which we have taken.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SQL> drop view dba_view;
View dropped.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@localhost ~]$ expdp dumpfile=view.dmp logfile=view.log directory=DUMP view_as_tables=D^C
[oracle@localhost ~]$
[oracle@localhost ~]$ impdp dumpfile=view.dmp logfile=view.log directory=DUMP tables=DBA_VIEW
Import: Release 12.1.0.2.0 - Production on Sat Aug 22 07:09:09 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: sys/oracle@ORCL as sysdba
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 "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP tables=DBA_VIEW
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."DBA_VIEW" 5.117 KB 8 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Aug 22 07:10:21 2015 elapsed 0 00:00:04
|
Now check the object_type:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
[oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 22 07:10:25 2015
Copyright (c) 1982, 2014, Oracle. 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
SQL> set pagesize 200
SQL> set lines 200
SQL> set long 999
SQL> col owner for a9
SQL> col object_name for a10
SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW';
OWNER OBJECT_NAM OBJECT_TYPE
--------- ---------- -----------------------
SYS DBA_VIEW TABLE
|
We can see DBA_VIEW which we exported as view has imported as table.
No comments:
Post a Comment