美文网首页
ORACLE (Edition Based Redefiniti

ORACLE (Edition Based Redefiniti

作者: 轻飘飘D | 来源:发表于2019-08-22 18:47 被阅读0次
    1. 使用DATABASE_PROPERTIES視圖顯示默認版本
    [oracle@DB01 XAG]$ sql sys/MPCDBMPCDB@127.0.0.1:1521/MPTEST as sysdba;
    
    SQL> set sqlformat ansiconsole;
    
    SQL> SELECT property_value FROM database_properties WHERE property_name='DEFAULT_EDITION';
    PROPERTY_VALUE  
    ORA$BASE 
    
    #设置数据库的缺省edition (可選項)
    #alter database DEFAULT EDITION = release_v1;
    
    1. 使用CREATE EDITIO 創建版本
    CREATE EDITION release_v1 AS CHILD OF ORA$BASE;
    CREATE EDITION release_v2 AS CHILD OF release_v1;
    CREATE EDITION release_v3 AS CHILD OF release_v2;
    CREATE EDITION release_v4 AS CHILD OF release_v3;
    
    SQL> SELECT * FROM dba_editions;
    EDITION_NAME  PARENT_EDITION_NAME  USABLE  
    ORA$BASE                           YES     
    RELEASE_V1    ORA$BASE             YES     
    RELEASE_V2    RELEASE_V1           YES     
    RELEASE_V3    RELEASE_V2           YES     
    RELEASE_V4    RELEASE_V3           YES 
    
    SQL> DROP EDITION release_v4;
    
    SQL> SELECT * FROM dba_editions;
    EDITION_NAME  PARENT_EDITION_NAME  USABLE  
    ORA$BASE                           YES     
    RELEASE_V1    ORA$BASE             YES     
    RELEASE_V2    RELEASE_V1           YES     
    RELEASE_V3    RELEASE_V2           YES 
    
    SQL>  DROP EDITION release_v1;
    Error starting at line : 1 in command -
     DROP EDITION release_v1
    Error report -
    ORA-38810: Implementation restriction: cannot drop edition that has a parent and a child
    38810. 00000 -  "Implementation restriction: cannot drop edition that has a parent and a child"
    *Cause:    This error occurred because an attempt was made to drop an edition
               that has a parent and a child edition.
    *Action:   Retry after dropping the parent or child edition.
    

    3.創建 edition_test 用戶並為此用戶啟用版本

    CREATE USER edition_test IDENTIFIED BY 123456 DEFAULT TABLESPACE XAG_UD QUOTA UNLIMITED ON XAG_UD temporary tablespace XAG_GP;
    GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER TO edition_test;
    
    ALTER USER edition_test ENABLE EDITIONS;
    
    SQL> SELECT username,editions_enabled FROM   dba_users WHERE  username = 'EDITION_TEST';
    USERNAME      EDITIONS_ENABLED  
    EDITION_TEST  Y 
    
    1. 授權使用剛創建的版本
    GRANT USE ON EDITION release_v1 TO edition_test;
    GRANT USE ON EDITION release_v2 TO edition_test;
    GRANT USE ON EDITION release_v3 TO edition_test;
    
    1. 通過 alter session 設置當前會話使用的版本
    [oracle@DB01 XAG]$ sql edition_test/123456@127.0.0.1:1521/MPTEST;
    
    SQL> set sqlformat ansiconsole;
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION   
    ORA$BASE  
    
    #切換當前會話到 v1 版本
    SQL> ALTER SESSION SET EDITION = release_v1;
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION     
    RELEASE_V1  
    
    #切換到 ora$base 版本
    SQL> ALTER SESSION SET EDITION = ora$base;
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION   
    ORA$BASE  
    
    1. 測試案例1(無數據轉換)
    [oracle@DB01 XAG]$ sql edition_test/123456@127.0.0.1:1521/MPTEST;
    
    SQL> set sqlformat ansiconsole;
    
    SQL> ALTER SESSION SET EDITION = release_v1;
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION     
    RELEASE_V1  
    
    
    SQL> CREATE TABLE employees_tab (
          employee_id   NUMBER(5)    NOT NULL,
          name          VARCHAR2(40) NOT NULL,
          date_of_birth DATE         NOT NULL,
          CONSTRAINT employees_pk PRIMARY KEY (employee_id)
        );
    
    SQL> CREATE SEQUENCE employees_seq;
    
    SQL> CREATE OR REPLACE EDITIONING VIEW employees AS
        SELECT employee_id,
               name,
               date_of_birth
        FROM   employees_tab;
    
    SQL> CREATE OR REPLACE PROCEDURE create_employee (p_name          IN employees.name%TYPE,
                                                     p_date_of_birth IN employees.date_of_birth%TYPE) AS
        BEGIN
          INSERT INTO employees (employee_id, name, date_of_birth)
          VALUES (employees_seq.NEXTVAL, p_name, p_date_of_birth);
        END create_employee;
        /
    
    SQL> BEGIN
          create_employee('x ag', sysdate-10);
          COMMIT;
        END;
        /
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  
    1            x ag  12-AUG-19      
    
    SQL> SELECT object_name, object_type, edition_name,status FROM user_objects_ae ORDER BY object_name;
    OBJECT_NAME      OBJECT_TYPE  EDITION_NAME  STATUS  
    CREATE_EMPLOYEE  PROCEDURE    RELEASE_V1    VALID   
    EMPLOYEES        VIEW         RELEASE_V1    VALID   
    EMPLOYEES_PK     INDEX                      VALID   
    EMPLOYEES_SEQ    SEQUENCE                   VALID   
    EMPLOYEES_TAB    TABLE                      VALID  
    
    SQL> ALTER SESSION SET EDITION = release_v2;
    
    SQL> BEGIN
          create_employee('x rj', sysdate-10);
          COMMIT;
        END;
        /
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  
    1            x ag  12-AUG-19      
    2            x rj  12-AUG-19      
    
    # 版本v2下 增加字段
    SQL> ALTER TABLE employees_tab ADD (
          postcode   VARCHAR2(20)
        );
    
    SQL> SELECT object_name, object_type, edition_name,status FROM user_objects_ae ORDER BY object_name;
    OBJECT_NAME      OBJECT_TYPE  EDITION_NAME  STATUS  
    CREATE_EMPLOYEE  PROCEDURE    RELEASE_V1    VALID   
    EMPLOYEES        VIEW         RELEASE_V1    VALID   
    EMPLOYEES_PK     INDEX                      VALID   
    EMPLOYEES_SEQ    SEQUENCE                   VALID   
    EMPLOYEES_TAB    TABLE                      VALID   
    
    
    SQL> ALTER SESSION SET EDITION = release_v1;
    
    SQL> BEGIN
          create_employee('y yc', sysdate-5);
          COMMIT;
        END;
        /
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  
    1            x ag  12-AUG-19      
    2            x rj  12-AUG-19      
    3            y yc  17-AUG-19  
    
    # 版本2下按升級後的邏輯修改
    SQL> ALTER SESSION SET EDITION = release_v2;
    
    SQL> CREATE OR REPLACE EDITIONING VIEW employees AS
        SELECT employee_id,name,date_of_birth,postcode from employees_tab;
    
    SQL> CREATE OR REPLACE PROCEDURE create_employee (p_name          IN employees.name%TYPE,
                                                     p_date_of_birth IN employees.date_of_birth%TYPE,
                                                     p_postcode      IN employees.postcode%TYPE) AS
        BEGIN
          INSERT INTO employees (employee_id, name, date_of_birth, postcode)
          VALUES (employees_seq.NEXTVAL, p_name, p_date_of_birth, p_postcode);
        END create_employee;
        /
    
    SQL> BEGIN
          create_employee('x ah', sysdate-1, '555555');
          COMMIT;
        END;
        /
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  POSTCODE  
    1            x ag  12-AUG-19                
    2            x rj  12-AUG-19                
    3            y yc  17-AUG-19                
    4            x ah  21-AUG-19      555555  
    
    SQL> SELECT object_name, object_type, edition_name,status FROM user_objects_ae ORDER BY object_name;
    OBJECT_NAME      OBJECT_TYPE  EDITION_NAME  STATUS  
    CREATE_EMPLOYEE  PROCEDURE    RELEASE_V1    VALID   
    CREATE_EMPLOYEE  PROCEDURE    RELEASE_V2    VALID   
    EMPLOYEES        VIEW         RELEASE_V1    VALID   
    EMPLOYEES        VIEW         RELEASE_V2    VALID   
    EMPLOYEES_PK     INDEX                      VALID   
    EMPLOYEES_SEQ    SEQUENCE                   VALID   
    EMPLOYEES_TAB    TABLE                      VALID   
    
    1. 案例2(雙向數據轉換)
    [oracle@DB01 XAG]$ sql edition_test/123456@127.0.0.1:1521/MPTEST;
                                                                            
    SQL> set sqlformat ansiconsole;
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION   
    ORA$BASE  
    
    SQL> ALTER TABLE employees_tab ADD (
          first_name VARCHAR2(20),
          last_name  VARCHAR2(20)
        );
    
    SQL> UPDATE employees_tab SET first_name = SUBSTR(name, 1, INSTR(name, ' ')-1),
         last_name  = SUBSTR(name, INSTR(name, ' ')+1) WHERE  first_name IS NULL;
    
    SQL> commit;
    #此處會導致版本1和版本2 的insert 報錯
    SQL> ALTER TABLE employees_tab MODIFY (
          first_name VARCHAR2(20) NOT NULL,
          last_name  VARCHAR2(20) NOT NULL
        );
    
    SQL> SELECT object_name, object_type, edition_name,status FROM user_objects_ae ORDER BY object_name;
    OBJECT_NAME      OBJECT_TYPE  EDITION_NAME  STATUS  
    CREATE_EMPLOYEE  PROCEDURE    RELEASE_V2    VALID   
    CREATE_EMPLOYEE  PROCEDURE    RELEASE_V1    VALID   
    EMPLOYEES        VIEW         RELEASE_V2    VALID   
    EMPLOYEES        VIEW         RELEASE_V1    VALID   
    EMPLOYEES_PK     INDEX                      VALID   
    EMPLOYEES_SEQ    SEQUENCE                   VALID   
    EMPLOYEES_TAB    TABLE                      VALID
    
    SQL> ALTER SESSION SET EDITION = release_v1;
    
    SQL> BEGIN
      2   create_employee('z 3', sysdate-5);
      3   COMMIT;
      4  END;
      5  /
    
    Error starting at line : 1 in command -
    BEGIN
     create_employee('z 3', sysdate-5);
     COMMIT;
    END;
    Error report -
    ORA-01400: cannot insert NULL into ("EDITION_TEST"."EMPLOYEES_TAB"."FIRST_NAME")
    ORA-06512: at "EDITION_TEST.CREATE_EMPLOYEE", line 4
    ORA-06512: at line 2
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    An attempt was made to insert NULL into previously listed objects.
    *Action:   These objects cannot accept NULL values.
    
    SQL> ALTER TABLE employees_tab MODIFY 
        (
          first_name VARCHAR2(20) NULL,
          last_name  VARCHAR2(20) NULL
        );
    
    SQL> BEGIN
         create_employee('z 3', sysdate-5);
         COMMIT;
        END;
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  
    1            x ag  12-AUG-19      
    2            x rj  12-AUG-19      
    3            y yc  17-AUG-19      
    4            x ah  21-AUG-19      
    6            z 3   17-AUG-19      
    
    SQL> SELECT * FROM employees_tab;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  POSTCODE  FIRST_NAME  LAST_NAME  
    1            x ag  12-AUG-19                x           ag         
    2            x rj  12-AUG-19                x           rj         
    3            y yc  17-AUG-19                y           yc         
    4            x ah  21-AUG-19      555555    x           ah         
    6            z 3   17-AUG-19                                       
    
    #切換到版本3
    SQL> ALTER SESSION SET EDITION = release_v3;
    
    SQL> CREATE OR REPLACE EDITIONING VIEW employees AS
        SELECT employee_id,
               first_name,
               last_name,
               date_of_birth,
               postcode
        FROM   employees_tab;
    
    SQL> CREATE OR REPLACE PROCEDURE create_employee (p_first_name    IN employees.first_name%TYPE,
                                                     p_last_name     IN employees.last_name%TYPE,
                                                     p_date_of_birth IN employees.date_of_birth%TYPE,
                                                     p_postcode      IN employees.postcode%TYPE) AS
        BEGIN
          INSERT INTO employees (employee_id, first_name, last_name, date_of_birth, postcode)
          VALUES (employees_seq.NEXTVAL, p_first_name, p_last_name, p_date_of_birth, p_postcode);
        END create_employee;
        /
    
    #前向交叉觸發器從舊版本中獲取數據並對其進行轉換以供新版本使用。在這種情況下,涉及將舊NAME列拆分為新列FIRST_NAME和LAST_NAME列
    SQL> CREATE OR REPLACE TRIGGER employees_fwd_xed_trg
          BEFORE INSERT OR UPDATE ON employees_tab
          FOR EACH ROW
          FORWARD CROSSEDITION
          DISABLE
        BEGIN
          :NEW.first_name := SUBSTR(:NEW.name, 1, INSTR(:NEW.name, ' ')-1);
          :NEW.last_name  := SUBSTR(:NEW.name, INSTR(:NEW.name, ' ')+1);
        END employees_fwd_xed_trg;
       /
    
    #反向交叉觸發通過將新值連接在一起以更新舊列值來執行相反的操作(供舊版使用)
    SQL> CREATE OR REPLACE TRIGGER employees_rvrs_xed_trg
          BEFORE INSERT OR UPDATE ON employees_tab
          FOR EACH ROW
          REVERSE CROSSEDITION
          DISABLE
        BEGIN
          :NEW.name := :NEW.first_name || ' ' || :NEW.last_name;
        END employees_rvrs_xed_trg;
        /
    
    #一旦兩個觸發器都到位,我們就可以啟用它們。
    ALTER TRIGGER employees_fwd_xed_trg ENABLE;
    ALTER TRIGGER employees_rvrs_xed_trg ENABLE;
    
    SQL> SELECT object_name, object_type, edition_name,status FROM user_objects_ae ORDER BY object_name;
    OBJECT_NAME             OBJECT_TYPE  EDITION_NAME  STATUS  
    CREATE_EMPLOYEE         PROCEDURE    RELEASE_V2    VALID   
    CREATE_EMPLOYEE         PROCEDURE    RELEASE_V1    VALID   
    CREATE_EMPLOYEE         PROCEDURE    RELEASE_V3    VALID   
    EMPLOYEES               VIEW         RELEASE_V2    VALID   
    EMPLOYEES               VIEW         RELEASE_V3    VALID   
    EMPLOYEES               VIEW         RELEASE_V1    VALID   
    EMPLOYEES_FWD_XED_TRG   TRIGGER      RELEASE_V3    VALID   
    EMPLOYEES_PK            INDEX                      VALID   
    EMPLOYEES_RVRS_XED_TRG  TRIGGER      RELEASE_V3    VALID   
    EMPLOYEES_SEQ           SEQUENCE                   VALID   
    EMPLOYEES_TAB           TABLE                      VALID 
    
    #切換到版本測試插入操作
    SQL> ALTER SESSION SET EDITION = release_v1;
    
    SQL> BEGIN
         create_employee('l 3', sysdate-5);
         COMMIT;
        END;
        /
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  
    1            x ag  12-AUG-19      
    2            x rj  12-AUG-19      
    3            y yc  17-AUG-19      
    4            x ah  21-AUG-19      
    6            z 3   17-AUG-19      
    7            l 3   17-AUG-19      
    
    SQL> SELECT * FROM employees_tab;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  POSTCODE  FIRST_NAME  LAST_NAME  
    1            x ag  12-AUG-19                x           ag         
    2            x rj  12-AUG-19                x           rj         
    3            y yc  17-AUG-19                y           yc         
    4            x ah  21-AUG-19      555555    x           ah         
    6            z 3   17-AUG-19                                       
    7            l 3   17-AUG-19                l           3          
    
    #切換到版本3,繼續新版的操作
    SQL> ALTER SESSION SET EDITION = release_v3;
    
    #檢查基表上沒有未完成的DML操作。
    SQL> DECLARE
          l_scn              NUMBER  := NULL;
          l_timeout CONSTANT INTEGER := NULL;
        BEGIN
          IF NOT DBMS_UTILITY.wait_on_pending_dml(tables=>'employees_tab',timeout =>l_timeout,scn=>l_scn)
          THEN
            RAISE_APPLICATION_ERROR(-20000, 'Wait_On_Pending_DML() timed out. CETs were enabled before SCN: ' || l_scn);
         END IF;
       END;
       /
    #然後我們使用DBMS_SQL包來更新基表,指定用於轉換數據的crossedition觸發器
    DECLARE
      l_cursor NUMBER := DBMS_SQL.open_cursor();
      l_return NUMBER;
    BEGIN
      DBMS_SQL.PARSE(
        c                          => l_cursor,
        Language_Flag              => DBMS_SQL.NATIVE,
        Statement                  => 'UPDATE employees_tab SET name = name',
        apply_crossedition_trigger => 'employees_fwd_xed_trg'
      );
      l_return := DBMS_SQL.execute(l_cursor);
      DBMS_SQL.close_cursor(l_cursor);
      COMMIT;
    END;
    /
    
    SQL> SELECT * FROM employees_tab;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  POSTCODE  FIRST_NAME  LAST_NAME  
    1            x ag  12-AUG-19                x           ag         
    2            x rj  12-AUG-19                x           rj         
    3            y yc  17-AUG-19                y           yc         
    4            x ah  21-AUG-19      555555    x           ah         
    6            z 3   17-AUG-19                z           3          
    7            l 3   17-AUG-19                l           3 
    
    #啟用了交叉觸發器並且數據是最新的,這時可以使新列NOT NULL
    ALTER TABLE employees_tab MODIFY (
      first_name VARCHAR2(20) NOT NULL,
      last_name  VARCHAR2(20) NOT NULL
    );
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION     
    RELEASE_V3  
    
    SQL> BEGIN
          create_employee('L', '4',sysdate-3, '123400');
          COMMIT;
        END;
        /
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  FIRST_NAME  LAST_NAME  DATE_OF_BIRTH  POSTCODE  
    1            x           ag         12-AUG-19                
    2            x           rj         12-AUG-19                
    3            y           yc         17-AUG-19                
    4            x           ah         21-AUG-19      555555    
    6            z           3          17-AUG-19                
    7            l           3          17-AUG-19                
    8            L           4          19-AUG-19      123400    
    
    SQL> SELECT * FROM employees_tab;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  POSTCODE  FIRST_NAME  LAST_NAME  
    1            x ag  12-AUG-19                x           ag         
    2            x rj  12-AUG-19                x           rj         
    3            y yc  17-AUG-19                y           yc         
    4            x ah  21-AUG-19      555555    x           ah         
    6            z 3   17-AUG-19                z           3          
    7            l 3   17-AUG-19                l           3          
    8            L 4   19-AUG-19      123400    L           4   
    
    #切換到舊版本v2 測試舊版插入動作
    SQL> ALTER SESSION SET EDITION = release_v2;
    
    SQL> BEGIN
          create_employee('W 5', sysdate-1, '550000');
          COMMIT;
        END;
        /
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  POSTCODE  
    1            x ag  12-AUG-19                
    2            x rj  12-AUG-19                
    3            y yc  17-AUG-19                
    4            x ah  21-AUG-19      555555    
    6            z 3   17-AUG-19                
    7            l 3   17-AUG-19                
    8            L 4   19-AUG-19      123400    
    9            W 5   21-AUG-19      550000    
    
    SQL> SELECT * FROM employees_tab;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  POSTCODE  FIRST_NAME  LAST_NAME  
    1            x ag  12-AUG-19                x           ag         
    2            x rj  12-AUG-19                x           rj         
    3            y yc  17-AUG-19                y           yc         
    4            x ah  21-AUG-19      555555    x           ah         
    6            z 3   17-AUG-19                z           3          
    7            l 3   17-AUG-19                l           3          
    8            L 4   19-AUG-19      123400    L           4          
    9            W 5   21-AUG-19      550000    W           5          
    
    #切換到舊版本v1 測試舊版插入動作
    SQL> ALTER SESSION SET EDITION = release_v1;
    
    SQL> BEGIN
         create_employee('Z 6', sysdate);
         COMMIT;
        END;
    
    SQL> SELECT * FROM employees;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  
    1            x ag  12-AUG-19      
    2            x rj  12-AUG-19      
    3            y yc  17-AUG-19      
    4            x ah  21-AUG-19      
    6            z 3   17-AUG-19      
    7            l 3   17-AUG-19      
    8            L 4   19-AUG-19      
    9            W 5   21-AUG-19      
    10           Z 6   22-AUG-19      
    
    SQL> SELECT * FROM employees_tab;
    EMPLOYEE_ID  NAME  DATE_OF_BIRTH  POSTCODE  FIRST_NAME  LAST_NAME  
    1            x ag  12-AUG-19                x           ag         
    2            x rj  12-AUG-19                x           rj         
    3            y yc  17-AUG-19                y           yc         
    4            x ah  21-AUG-19      555555    x           ah         
    6            z 3   17-AUG-19                z           3          
    7            l 3   17-AUG-19                l           3          
    8            L 4   19-AUG-19      123400    L           4          
    9            W 5   21-AUG-19      550000    W           5          
    10           Z 6   22-AUG-19                Z           6 
    
    說明: 如計劃將版本2更新成和版本3一致,則在版本2中更新完視圖、存儲過程 等對象後及前向和反向觸發器後必須刪除版本3中的前向和反向觸發器後方可啟用版本2.
    1. 將EDITION參數添加到SQL * Plus命令來指定所需的版本
    [oracle@DB01 XAG]$ sqlplus edition_test/123456@127.0.0.1:1521/MPTEST edition=release_v1;
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    
    EDITION
    --------------------------------------------------------------------------------
    RELEASE_V1
    
    SQL> conn edition_test/123456@127.0.0.1/MPTEST edition=release_v2;                                     
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    
    EDITION
    --------------------------------------------------------------------------------
    RELEASE_V2
    
    
    1. 服務和版本
    [oracle@DB01 XAG]$ sql sys/MPCDBMPCDB@127.0.0.1:1521/MPTEST as sysdba;
    
    SQL> set sqlformat ansiconsole;
    
    SQL> SELECT name, edition from dba_services;
    NAME    EDITION  
    mptest 
    
    BEGIN
      DBMS_SERVICE.modify_service(
        service_name   => 'MPTEST',
        edition        => 'RELEASE_V3',
        modify_edition => TRUE);
    END;
    /
    
    SQL> SELECT name, edition from dba_services;
    NAME    EDITION     
    mptest  RELEASE_V3  
    
    [oracle@DB01 XAG]$ sqlplus edition_test/123456@127.0.0.1:1521/MPTEST edition=release_v1;
    
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION
    --------------------------------------------------------------------------------
    RELEASE_V1
    
    [oracle@DB01 XAG]$ sqlplus edition_test/123456@127.0.0.1:1521/MPTEST;
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION
    --------------------------------------------------------------------------------
    RELEASE_V3
    
    [oracle@DB01 XAG]$ sql edition_test/123456@127.0.0.1:1521/MPTEST;
    SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;
    EDITION                                                                                                                                      
    ------------------------------------------------------------------------------------
    RELEASE_V3                                                                                                                                   
    
    
    #改回默認版本
    BEGIN
      DBMS_SERVICE.modify_service(
        service_name   => 'MPTEST',
        edition        => NULL,
        modify_edition => TRUE);
    END;
    以上改回默認版本後,客戶端程序可通過 使用环境变量 ORA_EDITION=RELEASE_V2 
    (window 和 linux 都可以配置),配置後客戶端程序訪問的就是 RELEASE_V2
    linux:【export ORA_EDITION=RELEASE_V2;】
    

    相关文章

      网友评论

          本文标题:ORACLE (Edition Based Redefiniti

          本文链接:https://www.haomeiwen.com/subject/iqpnsctx.html