QUESTION 2 Virtual Private Database (VPD) policies

QUESTION 2
Examine the following commands for redefining a table with Virtual Private Database (VPD) policies:
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'employees_policy',
function_schema => 'hr',
policy_function => 'auth_emp_dep_100',
statement_types => 'select, insert, update, delete'
);
END
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE (
uname => 'hr',
orig_table => 'employees',
int_table => 'int_employees',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_PK,
orderby_cols => NULL,
part_name => NULL,
copy_vpd_opt => DBMS_REDEFINITION.CONS_VPD_AUTO);
END;
Which two statements are true about redefining the table? (Choose two.)
A. All the triggers for the table are disabled without changing any of the column names or column types inthe table.
B. The primary key constraint on the EMPLOYEES table is disabled during redefinition.
C. VPD policies are copied from the original table to the new table during online redefinition.
D. You must copy the VPD policies manually from the original table to the new table during online
redefinition.
Correct Answer: AC
Section: (none)
Explanation
Explanation/Reference:
Explanation:
The triggers cloned to the interim table are disabled until the redefinition is completed. Once the
redefinition is complete, all cloned objects are renamed to the original names used by they objects they
were cloned from.
References: http://www.oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1.php

 

 

问题2

检查以下命令以重新定义具有虚拟专用数据库（VPD）策略的表：

BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'employees_policy',
function_schema => 'hr',
policy_function => 'auth_emp_dep_100',
statement_types => 'select, insert, update, delete'
);
END

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE (
uname => 'hr',
orig_table => 'employees',
int_table => 'int_employees',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_PK,
orderby_cols => NULL,
part_name => NULL,
copy_vpd_opt => DBMS_REDEFINITION.CONS_VPD_AUTO);
END;

关于重新定义表，哪两种说法是正确的？（选择两个。）

A、 表的所有触发器都将被禁用，而不会更改中的任何列名或列类型表。

B、 在重新定义期间，EMPLOYEES表上的主键约束被禁用。

C、 在联机重新定义期间，VPD策略从原始表复制到新表。

D、 联机期间，必须手动将VPD策略从原始表复制到新表

重新定义。

正确答案：AC

章节：（无）

解释

说明/参考：

说明：

VPD 策略可以应用在Select，insert，update，delete，index语句中，但不能支持过滤DDL语句，如truncate table或alter table。

在线重定义可以改变表的结构，同时对重定义过程中表上的DML语句影响非常小。因为独占锁模式所经过的时间窗口比较短。主要操作通过DBMS_REDEFINITION包来完成。而在线重定义的表上同时有VPD策略，我们该如何处理呢？

首先 DBMS_REDEFINITION 包中提供了 copy_vpd_opt 参数，在 START_REDEF_TABLE 过程中使用 copy_vpd_opt 参数来处理VPD策略。copy_vpd_opt 参数可以指定如下一些内容：

DBMS_REDEFINITION.CONS_VPD_NONE 参数用于源表没有 VPD 策略时。

DBMS_REDEFINITION.CONS_VPD_AUTO 参数用于 源表和中间表列名和类型相同时。要使用这个值，源表和中间表之间列的映射关系，也就是 col_mapping 参数，必须设置为 NULL 或者 ’*’ .还要注意，表的所有者和执行重定义的用户在重定义期间都可以访问中间表。

参考文献：http://www.oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1.php