SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--1,用户准备
SQL> create user edtest identified by edtest;
User created.
SQL> grant connect,resource to edtest;
Grant succeeded.
SQL> grant create any edition,drop any edition to edtest;
Grant succeeded.
SQL> grant create view to edtest;
Grant succeeded.
SQL> alter user edtest enable editions;
User altered.
SQL> select editions_enabled from dba_users where username='EDTEST';
E
-
Y
-
Y
--2,使用edtest用户登录,创建测试表
SQL> create table emp(x int,y int);
Table created.
SQL> insert into emp select rownum,rownum from dual connect by level<21;
20 rows created.
SQL> commit;
Commit complete.
--3,使用edtest用户登录,创建editioning view
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE
--------------------------------------------------------------------------------
ORA$BASE
SQL> create editioning view v_emp as select * from emp;
View created.
SQL> select view_name,editioning_view from user_views;
VIEW_NAME E
------------------------------ -
V_EMP Y
------------------------------ -
V_EMP Y
SQL> select view_name,editioning_view,edition_name from user_views_ae;
VIEW_NAME E EDITION_NAME
------------------------------ - ------------------------------
V_EMP Y ORA$BASE
------------------------------ - ------------------------------
V_EMP Y ORA$BASE
SQL> select * from user_EDITIONING_VIEWS_AE;
VIEW_NAME TABLE_NAME
------------------------------ ------------------------------
EDITION_NAME
------------------------------
V_EMP EMP
ORA$BASE
------------------------------ ------------------------------
EDITION_NAME
------------------------------
V_EMP EMP
ORA$BASE
SQL> select * from USER_EDITIONING_VIEW_COLS_AE;
VIEW_NAME VIEW_COLUMN_ID VIEW_COLUMN_NAME
------------------------------ -------------- ------------------------------
TABLE_COLUMN_ID TABLE_COLUMN_NAME EDITION_NAME
--------------- ------------------------------ ------------------------------
V_EMP 1 X
1 X ORA$BASE
------------------------------ -------------- ------------------------------
TABLE_COLUMN_ID TABLE_COLUMN_NAME EDITION_NAME
--------------- ------------------------------ ------------------------------
V_EMP 1 X
1 X ORA$BASE
V_EMP 2 Y
2 Y ORA$BASE
2 Y ORA$BASE
--4,使用edtest用户登录,创建editioning view
SQL> create edition ed_test;
Edition created.
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE
--------------------------------------------------------------------------------
ORA$BASE
SQL> alter session set edition = ed_test;
Session altered.
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ED_TEST
--------------------------------------------------------------------------------
ED_TEST
SQL> create editioning view v_emp as select x from emp;
create editioning view v_emp as select x from emp
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create or replace editioning view v_emp as select x from emp;
View created.
SQL> desc v_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
SQL> alter session set edition = ora$base;
Session altered.
SQL> desc v_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y NUMBER(38)
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y NUMBER(38)
SQL> select view_name,editioning_view from user_views;
VIEW_NAME E
------------------------------ -
V_EMP Y
------------------------------ -
V_EMP Y
SQL> select view_name,editioning_view,edition_name from user_views_ae;
VIEW_NAME E EDITION_NAME
------------------------------ - ------------------------------
V_EMP Y ORA$BASE
V_EMP Y ED_TEST
------------------------------ - ------------------------------
V_EMP Y ORA$BASE
V_EMP Y ED_TEST
SQL> select * from user_EDITIONING_VIEWS;
VIEW_NAME TABLE_NAME
------------------------------ ------------------------------
V_EMP EMP
==>return only session edition
------------------------------ ------------------------------
V_EMP EMP
==>return only session edition
SQL> select * from user_EDITIONING_VIEWS_AE;
VIEW_NAME TABLE_NAME
------------------------------ ------------------------------
EDITION_NAME
------------------------------
V_EMP EMP
ORA$BASE
------------------------------ ------------------------------
EDITION_NAME
------------------------------
V_EMP EMP
ORA$BASE
V_EMP EMP
ED_TEST
ED_TEST
SQL> select * from USER_EDITIONING_VIEW_COLS_AE;
VIEW_NAME VIEW_COLUMN_ID VIEW_COLUMN_NAME
------------------------------ -------------- ------------------------------
TABLE_COLUMN_ID TABLE_COLUMN_NAME EDITION_NAME
--------------- ------------------------------ ------------------------------
V_EMP 1 X
1 X ORA$BASE
------------------------------ -------------- ------------------------------
TABLE_COLUMN_ID TABLE_COLUMN_NAME EDITION_NAME
--------------- ------------------------------ ------------------------------
V_EMP 1 X
1 X ORA$BASE
V_EMP 2 Y
2 Y ORA$BASE
2 Y ORA$BASE
V_EMP 1 X
1 X ED_TEST
1 X ED_TEST
select * from all_editions
EDITION_NAME PARENT_EDITION_NAME USABLE
------------------------------ ------------------------------ ------
ORA$BASE YES
ED_TEST ORA$BASE YES
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-773503/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-773503/