下面的例子将演示联机重定义使用VPD策略的表，并修改表中的一个列名，原始表jy.orders的创建语句如下:
SQL> create table jy.orders(
2 order_id number(12) primary key,
3 order_date timestamp with local time zone constraint order_date_nn not null,
4 order_mode varchar2(8),
5 customer_id number(6) constraint order_customer_id_nn not null,
6 order_status number(2),
7 order_total number(8,2),
8 sales_rep_id number(6),
9 promotion_id number(6),
10 constraint order_mode_lov
11 check (order_mode in ('direct','online')),
12 constraint order_total_min
13 check (order_total >= 0));
Table created.
创建下面的jy.auth_orders函数来创建VPD策略
SQL> create or replace function jy.auth_orders(
2 schema_var in varchar2,
3 table_var in varchar2
4 )
5 return varchar2
6 as
7 return_val varchar2 (400);
8 unm varchar2(30);
9 begin
10 select user into unm from dual;
11 if (unm = 'jy') then
12 return_val := null;
13 else
14 return_val := 'sales_rep_id = 159';
15 end if;
16 return return_val;
17 end auth_orders;
18 /
Function created.
下面执行dbms_rls.add_policy过程来使用jy.auth_orders函数来给原始表jy.orders指定VPD策略:
SQL> begin
2 dbms_rls.add_policy(
3 object_schema => 'jy',
4 object_name => 'orders',
5 policy_name => 'orders_policy',
6 function_schema => 'jy',
7 policy_function => 'auth_orders');
8 end;
9 /
PL/SQL procedure successfully completed.
在这个例子中，被重定义后表中的sales_rep_id列被修改为sale_pid。当在执行重定义过程如可修改一个或多个列或列的数据类型，那么在start_refef_table过程中对于copy_vpd_opt参数必须指定dbms_redefinition.cons_vpd_manual。
1.用要执行联机重定义操作的用户登录数据库
SQL> conn jy/jy@jypdb
Connected.
2.验证原始表是否可以执行联机重定义
SQL> begin
2 dbms_redefinition.can_redef_table(
3 uname => 'jy',
4 tname => 'orders',
5 options_flag => DBMS_REDEFINITION.CONS_USE_PK);
6 end;
7 /
PL/SQL procedure successfully completed.
3.创建中间表
SQL> create table jy.int_orders(
2 order_id number(12),
3 order_date timestamp with local time zone,
4 order_mode varchar2(8),
5 customer_id number(6),
6 order_status number(2),
7 order_total number(8,2),
8 sales_pid number(6),
9 promotion_id number(6));
Table created.
注意，在中间表中sales_rep_id列被修改为sales_pid。
4.开始联机重定义操作
SQL> begin
2 dbms_redefinition.start_redef_table (
3 uname => 'jy',
4 orig_table => 'orders',
5 int_table => 'int_orders',
6 col_mapping => 'order_id order_id, order_date order_date, order_mode
7 order_mode, customer_id customer_id, order_status
8 order_status, order_total order_total, sales_rep_id
9 sales_pid, promotion_id promotion_id',
10 options_flag => dbms_redefinition.cons_use_pk,
11 orderby_cols => null,
12 part_name => null,
13 copy_vpd_opt => dbms_redefinition.cons_vpd_manual);
14 end;
15 /
PL/SQL procedure successfully completed.
因为原始表与中间表存在不同的列名，那么copy_vpd_opt参数必须设置为dbms_redefinition.cons_vpd_manual。
5.对中间表创建VPD策略
5.1创建一个名为jy.auth_orders_sales_pid的函数来创建VPD策略，这里使用sales_pid列来代替sales_rep_id列。
SQL> create or replace function jy.auth_orders_sales_pid(
2 schema_var in varchar2,
3 table_var in varchar2
4 )
5 return varchar2
6 as
7 return_val varchar2 (400);
8 unm varchar2(30);
9 begin
10 select user into unm from dual;
11 if (unm = 'jy') then
12 return_val := null;
13 else
14 return_val := 'sales_pid = 159';
15 end if;
16 return return_val;
17 end auth_orders_sales_pid;
18 /
Function created.
5.2执行dbms_rls.add_policy过程来使用jy.auth_orders_sales_pid函数来为中间表增加VPD策略
SQL> begin
2 dbms_rls.add_policy (
3 object_schema => 'jy',
4 object_name => 'int_orders',
5 policy_name => 'orders_policy',
6 function_schema => 'jy',
7 policy_function => 'auth_orders_sales_pid');
8 end;
9 /
PL/SQL procedure successfully completed.
6.复制依赖对象
SQL> declare
2 num_errors pls_integer;
3 begin
4 dbms_redefinition.copy_table_dependents(
5 uname => 'jy',
6 orig_table => 'orders',
7 int_table => 'int_orders',
8 copy_indexes => dbms_redefinition.cons_orig_params,
9 copy_triggers => true,
10 copy_constraints => true,
11 copy_privileges => true,
12 ignore_errors => true,
13 num_errors => num_errors);
14 end;
15 /
PL/SQL procedure successfully completed.
注意在这里ignore_errors参数被设置为true。原因是因为原始表对于列sales_rep_id存在索引与约束，并且在中间表中列被修改为sales_pid。
7.查询dba_redefinition_errors视图来检查是否存在错误
SQL> set long 8000
SQL> set pages 8000
SQL> column object_name heading 'object name' format a20
SQL> column base_table_name heading 'base table name' format a10
SQL> column ddl_txt heading 'ddl that caused error' format a40
SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors;
no rows selected
8.可选操作同中间表
SQL> begin
2 dbms_redefinition.sync_interim_table(
3 uname => 'jy',
4 orig_table => 'orders',
5 int_table => 'int_orders');
6 end;
7 /
PL/SQL procedure successfully completed.
9.完成联机重定义操作
SQL> begin
2 dbms_redefinition.finish_redef_table(
3 uname => 'jy',
4 orig_table => 'orders',
5 int_table => 'int_orders');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> desc jy.orders
Name Type Nullable Default Comments
------------ --------------------------------- -------- ------- --------
ORDER_ID NUMBER(12)
ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE
ORDER_MODE VARCHAR2(8) Y
CUSTOMER_ID NUMBER(6)
ORDER_STATUS NUMBER(2) Y
ORDER_TOTAL NUMBER(8,2) Y
SALES_PID NUMBER(6) Y
PROMOTION_ID NUMBER(6) Y
10.等待任何查询中间表的语句执行完成后将其删除
SQL> drop table jy.int_orders;
Table dropped
到此重定义操作就完成了。