不同edition间的切换
alter session set
edition=newedition;
使用环境变量ORA_EDITION
export
ORA_EDITION=ora\$base / set
ORA_EDITION=ora$base
通过sqlplu客户端指定edition登录
$ sqlplus
username/password@service edition=ora\$base
sqlplud中使用connect关键字连接
SQL> CONNECT
username/password@service EDITION=ora$base
数据库服务和editions
使用modify_service修改指定service的默认edition
BEGIN
DBMS_SERVICE.modify_service(
service_name
=> 'DB11G.WORLD',
edition
=> 'RELEASE_V1',
modify_edition =>
TRUE);
END;
/
SQL>SELECT name, edition FROM dba_services;
NAME EDITION
--------------------
------------------------------
SYS$BACKGROUND
SYS$USERS
DB11GXDB
DB11G.WORLD
RELEASE_V1
登录时，即选择了指定的edition。
$SQLPLUS
edition_test/edition_test@//localhost:1521/DB11G.WORLD
SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS
edition FROM dual;
EDITION
--------------------
RELEASE_V1
实战1： 升降级没有数据传输
本部分，将在基表上添加一列，老系统将忽略更改继续工作，新系统将应用新列。
整体思路，是在两个edition中v1,v2分别基于基表创建不同列的ev,从而实现通过editioning
view达到新老系统的兼容。
SQL> conn sun/sun
Connected.
SQL> alter session set edition=release_v1;
Session altered.
SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME')
AS edition FROM dual;
EDITION
--------------------------------------------------------------------------------
RELEASE_V1
SQL> CREATE TABLE employees_tab (
2 employee_id NUMBER(5)
NOT NULL,
3 name
VARCHAR2(40) NOT NULL,
4 date_of_birth DATE NOT
NULL,
5 CONSTRAINT employees_pk PRIMARY KEY
(employee_id)
6 );
Table created.
SQL> CREATE SEQUENCE employees_seq;
Sequence created.
SQL> CREATE OR REPLACE EDITIONING VIEW employees AS
2 SELECT
employee_id,
3 name,
4 date_of_birth
5 FROM employees_tab;
View created.
SQL> CREATE OR REPLACE PROCEDURE
create_employee (p_name IN
employees.name%TYPE,
2 p_date_of_birth IN employees.date_of_birth%TYPE) AS
3 BEGIN
4 INSERT INTO employees (employee_id, name,
date_of_birth)
5 VALUES (employees_seq.NEXTVAL, p_name,
p_date_of_birth);
6 END
create_employee;
7 /
Procedure created.
SQL> BEGIN
2 create_employee('Peter Parker',
TO_DATE('01-JAN-2010', 'DD-MON-YYYY'));
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT * FROM employees;
EMPLOYEE_ID NAME DATE_OF_B
----------- ----------------------------------------
---------
1 Peter Parker
01-JAN-10
COLUMN object_name FORMAT A20
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY object_name;
OBJECT_NAME OBJECT_TYPE EDITION_NAME
-------------------- -------------------
------------------------------
EMPLOYEES_PK
INDEX
EMPLOYEES_TAB
TABLE
EMPLOYEES_SEQ
SEQUENCE
EMPLOYEES
VIEW
RELEASE_V1
CREATE_EMPLOYEE
PROCEDURE
RELEASE_V1
##可以看到table,index,sequence是没有editon属性的
SQL> ALTER SESSION SET EDITION = release_v2;
Session altered.
SQL> BEGIN
2 create_employee('Clark Kent',
TO_DATE('02-JAN-2010', 'DD-MON-YYYY'));
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM employees;
EMPLOYEE_ID NAME DATE_OF_B
----------- ----------------------------------------
---------
2 Peter Parker
01-JAN-10
3 Clark Kent
02-JAN-10
SQL> ALTER TABLE employees_tab ADD (
2 postcode VARCHAR2(20)
3 );
Table altered.
SQL> ALTER SESSION SET EDITION = release_v1;
Session altered.
SQL>
SQL> BEGIN
2 create_employee('Flash Gordon',
TO_DATE('03-JAN-2010', 'DD-MON-YYYY'));
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT * FROM employees;
EMPLOYEE_ID NAME DATE_OF_B
----------- ----------------------------------------
---------
2 Peter Parker
01-JAN-10
3 Clark Kent
02-JAN-10
4 Flash Gordon
03-JAN-10
SQL> ALTER SESSION SET EDITION = release_v2;
Session altered.
SQL> CREATE OR REPLACE EDITIONING VIEW employees AS
2 SELECT
employee_id,
3 name,
4 date_of_birth,
5 postcode
6 FROM employees_tab;
View created.
SQL> CREATE OR REPLACE PROCEDURE create_employee (p_name
IN employees.name%TYPE,
2 p_date_of_birth IN employees.date_of_birth%TYPE,
3 p_postcode
IN
employees.postcode%TYPE) AS
4 BEGIN
5 INSERT INTO employees (employee_id, name,
date_of_birth, postcode)
6 VALUES (employees_seq.NEXTVAL, p_name,
p_date_of_birth, p_postcode);
7 END
create_employee;
8 /
Procedure created.
SQL> BEGIN
2 create_employee('Mighty Mouse',
TO_DATE('04-JAN-2010', 'DD-MON-YYYY'), 'AA1 2BB');
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SET LINESIZE 100
SQL> SELECT * FROM employees;
EMPLOYEE_ID NAME DATE_OF_B POSTCODE
----------- ---------------------------------------- ---------
--------------------
2 Peter Parker
01-JAN-10
3 Clark Kent
02-JAN-10
4 Flash Gordon
03-JAN-10
5 Mighty Mouse
04-JAN-10 AA1 2BB
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY object_name, edition_name;
OBJECT_NAME OBJECT_TYPE
EDITION_NAME
-------------------- -------------------
------------------------------
EMPLOYEES_TAB TABLE
EMPLOYEES_PK INDEX
EMPLOYEES_SEQ SEQUENCE
EMPLOYEES VIEW RELEASE_V2
EMPLOYEES VIEW RELEASE_V1
CREATE_EMPLOYEE PROCEDURE RELEASE_V2
CREATE_EMPLOYEE PROCEDURE RELEASE_V1
##可以看到table,index,sequence是没有editon属性的
SQL> ALTER SESSION SET EDITION =
release_v1;
Session altered.
SQL> SELECT * FROM employees;
EMPLOYEE_ID NAME DATE_OF_B
----------- ----------------------------------------
---------
2 Peter Parker
01-JAN-10
3 Clark Kent
02-JAN-10
4 Flash Gordon
03-JAN-10
5 Mighty Mouse
04-JAN-10
实战2：
升降级需要互相进行数据传输
实验将name列分为first_name和last_name,我们知道三列之间的关系，从而通过cross
trigger实现数据的互相同步。
在新系统通过创建forward crossediton trigger
，reverse crossedition trigger 实现三列的转换，
新老系统操作ev触发对基表的三列的不同更新；
SQL> conn sun/sun
Connected.
SQL>
SQL>
SQL> ALTER TABLE employees_tab ADD (
2 first_name VARCHAR2(20),
3 last_name VARCHAR2(20)
4 );
Table altered.
SQL> UPDATE employees_tab
2 SET first_name = SUBSTR(name, 1, INSTR(name, '
')-1),
3 last_name =
SUBSTR(name, INSTR(name, ' ')+1)
4 WHERE
first_name IS NULL;
4 rows updated.
SQL>
SQL> ALTER TABLE employees_tab MODIFY (
2 first_name VARCHAR2(20) NOT NULL,
3 last_name VARCHAR2(20) NOT
NULL
4 );
Table altered.
SQL> SELECT * FROM employees_tab ;
EMPLOYEE_ID NAME DATE_OF_B POSTCODE FIRST_NAME LAST_NAME
----------- --------------- --------- --------------------
-------------------- --------------------
2 Peter Parker
01-JAN-10 Peter Parker
3 Clark Kent
02-JAN-10
Clark Kent
4 Flash Gordon
03-JAN-10 Flash Gordon
5 Mighty Mouse
04-JAN-10 AA1 2BB
Mighty Mouse
SQL> CREATE OR REPLACE EDITIONING VIEW employees AS
2 SELECT
employee_id,
3 first_name,
4 last_name,
5 date_of_birth,
6 postcode
7 FROM employees_tab;
View created.
SQL> SQL> CREATE OR REPLACE PROCEDURE create_employee
(p_first_name IN
employees.first_name%TYPE,
2 p_last_name IN
employees.last_name%TYPE,
3 p_date_of_birth IN employees.date_of_birth%TYPE,
4 p_postcode
IN
employees.postcode%TYPE) AS
5 BEGIN
6 INSERT INTO employees (employee_id, first_name,
last_name, date_of_birth, postcode)
7 VALUES (employees_seq.NEXTVAL, p_first_name,
p_last_name, p_date_of_birth, p_postcode);
8 END
create_employee;
9 /
Procedure created.
SQL> CREATE OR REPLACE TRIGGER employees_fwd_xed_trg
2 BEFORE INSERT OR UPDATE ON employees_tab
3 FOR
EACH ROW
4 FORWARD CROSSEDITION
5 DISABLE
6 BEGIN
7 :NEW.first_name := SUBSTR(:NEW.name, 1,
INSTR(:NEW.name, ' ')-1);
8 :NEW.last_name :=
SUBSTR(:NEW.name, INSTR(:NEW.name, ' ')+1);
9 END
employees_fwd_xed_trg;
10 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER employees_rvrs_xed_trg
2 BEFORE INSERT OR UPDATE ON employees_tab
3 FOR
EACH ROW
4 REVERSE CROSSEDITION
5 DISABLE
6 BEGIN
7 :NEW.name := :NEW.first_name || ' ' ||
:NEW.last_name;
8 END
employees_rvrs_xed_trg;
9 /
Trigger created.
SQL> ALTER TRIGGER employees_fwd_xed_trg ENABLE;
Trigger altered.
SQL> ALTER TRIGGER employees_rvrs_xed_trg ENABLE;
Trigger altered.
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY object_name, edition_name;
OBJECT_NAME OBJECT_TYPE
EDITION_NAME
------------------------- -------------------
------------------------------
EMPLOYEES_TAB TABLE
EMPLOYEES_PK INDEX
EMPLOYEES_SEQ SEQUENCE
EMPLOYEES VIEW
RELEASE_V2
EMPLOYEES VIEW
RELEASE_V3
EMPLOYEES VIEW
RELEASE_V1
CREATE_EMPLOYEE PROCEDURE
RELEASE_V2
CREATE_EMPLOYEE PROCEDURE
RELEASE_V1
CREATE_EMPLOYEE PROCEDURE
RELEASE_V3
EMPLOYEES_FWD_XED_TRG TRIGGER
RELEASE_V3
EMPLOYEES_RVRS_XED_TRG TRIGGER RELEASE_V3
## 在对表first_name和last_name列进行not
null操作前，先确保老系统没有对基表的DML操作，pending后再使用employees_fwd_xed_trg对name做更新。
SQL> DECLARE
2 l_scn NUMBER :=
NULL;
3 l_timeout CONSTANT INTEGER := NULL;
4 BEGIN
5 IF
NOT DBMS_UTILITY.wait_on_pending_dml(tables =>
'employees_tab',
6 timeout => l_timeout,
7 scn => l_scn)
8 THEN
9 RAISE_APPLICATION_ERROR(-20000,
'Wait_On_Pending_DML() timed out. CETs were enabled before SCN: '
|| l_scn);
10 END
IF;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_cursor NUMBER := DBMS_SQL.open_cursor();
3 l_return NUMBER;
4 BEGIN
5 DBMS_SQL.PARSE(
6 c => l_cursor,
7 Language_Flag =>
DBMS_SQL.NATIVE,
8 Statement => 'UPDATE employees_tab SET name =
name',
9 apply_crossedition_trigger =>
'employees_fwd_xed_trg'
10 );
11 l_return := DBMS_SQL.execute(l_cursor);
12 DBMS_SQL.close_cursor(l_cursor);
13 COMMIT;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL> ALTER TABLE employees_tab MODIFY (
2 first_name VARCHAR2(20) NOT NULL,
3 last_name VARCHAR2(20) NOT
NULL
4 );
SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME')
AS edition FROM dual;
EDITION
-------------------------
RELEASE_V3
SQL> BEGIN
2 create_employee('Wonder', 'Woman',
TO_DATE('01-JAN-2010', 'DD-MON-YYYY'), 'A11 2BB');
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
-- Check the editioning
view.
SQL> SET LINESIZE 100
SQL> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME
DATE_OF_B
POSTCODE
----------- -------------------- --------------------
--------- --------------------
2 Peter Parker 01-JAN-10
3 Clark Kent 02-JAN-10
4 Flash Gordon 03-JAN-10
5 Mighty Mouse
04-JAN-10 AA1 2BB
6 Wonder Woman
01-JAN-10 A11 2BB
-- Check the base table.
EMPLOYEE_ID NAME DATE_OF_B
POSTCODE FIRST_NAME LAST_NAME
----------- -------------------- --------- ----------
-------------------- --------------------
2 Peter Parker
01-JAN-10 Peter Parker
3 Clark Kent
02-JAN-10 Clark Kent
4 Flash Gordon
03-JAN-10 Flash Gordon
5 Mighty Mouse
04-JAN-10 AA1 2BB Mighty Mouse
6 Wonder Woman
01-JAN-10 A11 2BB Wonder Woman
SQL> ALTER SESSION SET EDITION = release_v2;
Session altered.
SQL> BEGIN
2 create_employee('Inspector Gadget',
TO_DATE('01-JAN-2010', 'DD-MON-YYYY'), 'A12 2BB');
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> -- Check editioning
view.
SQL> COLUMN name FORMAT A25
SQL>
SQL> SELECT * FROM employees;
EMPLOYEE_ID NAME DATE_OF_B
POSTCODE
----------- ------------------------- ---------
----------
2 Peter Parker
01-JAN-10
3 Clark Kent
02-JAN-10
4 Flash Gordon
03-JAN-10
5 Mighty Mouse
04-JAN-10 AA1 2BB
6 Wonder Woman
01-JAN-10 A11 2BB
7 Inspector Gadget
01-JAN-10 A12 2BB
6 rows selected.
SQL>
SQL> -- Check base
table.
SQL> SELECT * FROM employees_tab;
EMPLOYEE_ID NAME DATE_OF_B
POSTCODE FIRST_NAME
LAST_NAME
----------- ------------------------- --------- ----------
-------------------- --------------------
2 Peter Parker
01-JAN-10 Peter Parker
3 Clark Kent
02-JAN-10 Clark Kent
4 Flash Gordon
03-JAN-10 Flash Gordon
5 Mighty Mouse
04-JAN-10 AA1 2BB Mighty Mouse
6 Wonder Woman
01-JAN-10 A11 2BB Wonder Woman
7 Inspector Gadget
01-JAN-10 A12 2BB
Inspector Gadget
695
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
