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

--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

--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

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

SQL> select view_name,editioning_view,edition_name from user_views_ae;

VIEW_NAME E EDITION_NAME
------------------------------ - ------------------------------
V_EMP Y ORA$BASE

SQL> select * from user_EDITIONING_VIEWS_AE;

VIEW_NAME TABLE_NAME
------------------------------ ------------------------------
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

V_EMP 2 Y
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

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

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)

SQL> select view_name,editioning_view from user_views;

VIEW_NAME E
------------------------------ -
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

SQL> select * from user_EDITIONING_VIEWS;

VIEW_NAME TABLE_NAME
------------------------------ ------------------------------
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

V_EMP EMP
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

V_EMP 2 Y
2 Y ORA$BASE

V_EMP 1 X
1 X ED_TEST

select * from all_editions
EDITION_NAME                   PARENT_EDITION_NAME            USABLE
------------------------------ ------------------------------ ------
ORA$BASE                                                      YES
ED_TEST                        ORA$BASE                       YES

Oracle11g新特性之edition

相關文章