advertisements
_____________________________________________________________________________________________________________________
In
Oracle 11gR2 you can create and keep different versions or editions of objects.
The objects are limited to some set of objects which is not having the storage.
You can see more details here with examples.Oracle Object Editions
Now
let’s discuss about the data pump parameters expdp source_edition and impdp target_edition.
Oracle provides the provision for different versions of same object under different
editions. Suppose if you wanted to backup and restore these versions into
across different editions using these options.
During
the application development you can create multiple versions of objects for
different releases. So in case if you wanted to migrate code from one version
to different version within database or across database you can use this
option.
See
some examples.
I
have 2 editions of one view called ED_EMP_VIEW_ORABASE in my schema. One is
with default edition called ORA$BASE and another in NEW_EDITION edition with
different columns from emp table. In below example I am trying to export views
from scott schema from NEW_EDITION to ORA$BASE edition.
SQL>
ALTER SESSION SET EDITION =ORA$BASE;
Session
altered.
SQL>
desc ed_emp_view_ORABASE
Name
Null? Type
-----------------------------------------
-------- --------------
EMPNO NOT NULL
NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
SQL>
ALTER SESSION SET EDITION =NEW_EDITION;
Session
altered.
SQL>
desc ED_EMP_VIEW_ORABASE
Name
Null? Type
-----------------------------------------
-------- ---------------
EMPNO NOT NULL
NUMBER(4)
ENAME
VARCHAR2(10)
SAL
NUMBER(7,2)
COMM NUMBER(7,2)
Exporting
view
expdp
job_name=exp1 schemas=scott dumpfile=exp_edition.dmp logfile=exp_edition.log include=view directory=EXP_DIR compression=all
source_edition=NEW_EDITION
Export:
Release 11.2.0.2.0 - Production on Mon Nov 19 04:54:34 2012
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
Username:
/ as sysdba
Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With
the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data
Mining and Real Application Testing options
Starting
"SYS"."EXP1":
/******** AS SYSDBA job_name=exp1 schemas=scott dumpfile=exp_edition.dmp
logfile=exp_edition.log include=view directory=EXP_DIR compression=all
source_edition=NEW_EDITION
Estimate
in progress using BLOCKS method...
Processing
object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 0 KB
Processing object
type SCHEMA_EXPORT/VIEW/VIEW
Master
table "SYS"."EXP1" successfully loaded/unloaded
******************************************************************************
Dump
file set for SYS.EXP1 is:
/u01/zfssa/dev/backup1/export/exp_edition.dmp
Job
"SYS"."EXP1" successfully completed at 04:54:41
Importing
the dumpfile / views to ORA$BASE edition. You should remove the objects from
the target edition before importing the dump. Otherwise, if the object present
in the target edition you will get “ORA-31684: Object type VIEW:"SCOTT"."ED_EMP_VIEW_ORABASE"
already exists” error.
SQL>
ALTER SESSION SET EDITION =ORA$BASE;
Session
altered.
SQL>
drop view ed_emp_view_ORABASE;
View
dropped.
impdp
job_name=schemaexp full=y dumpfile=exp_edition.dmp logfile=impexp_edition.log
directory=exp_dir TARGET_EDITION=ORA\$BASE
Import:
Release 11.2.0.2.0 - Production on Mon Nov 19 05:04:26 2012
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
Username:
/ as sysdba
Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With
the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data
Mining and Real Application Testing options
Master
table "SYS"."SCHEMAEXP" successfully loaded/unloaded
Starting
"SYS"."SCHEMAEXP":
/******** AS SYSDBA job_name=schemaexp full=y dumpfile=exp_edition.dmp
logfile=impexp_edition.log directory=exp_dir TARGET_EDITION=ORA$BASE
Processing object
type SCHEMA_EXPORT/VIEW/VIEW
Job
"SYS"."SCHEMAEXP" successfully completed at 05:04:31
After
the import you can verify the import after setting corresponding edition.
SQL>
ALTER SESSION SET EDITION =ORA$BASE;
Session
altered.
SQL>
SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE
SQL>
desc ed_emp_view_ORABASE
Name
Null? Type
-----------------------------------------
-------- ---------------
EMPNO NOT NULL
NUMBER(4)
ENAME
VARCHAR2(10)
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)_____________________________________________________________________________________________________________________
0 comments:
Post a Comment