ORACLE (Edition Based Redefiniti
2019-08-22 本文已影响0人
轻飘飘D
- 使用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;
- 使用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
- 授權使用剛創建的版本
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;
- 通過 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（無數據轉換）
[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
- 案例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.
- 將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
- 服務和版本
[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;】
