1z0-060 Q2 – bushmelev-aa.ru

Examine the following commands for redefining a table with Virtual Private Database (VPD)

Which two statements are true about redefining the table?

A. All the triggers for the table are disabled without changing any of the column names or column types in the 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.

answer and check:

ANSWER A,C

A – as in dbms_redifinition we use options_flag => DBMS_REDEFINITION.CONS_USE_PK, so primary key on master mast be valid
C – as documentation says auto is copy all vpn rules
DBMS_REDEFINITION.CONS_VPD_AUTO Specify this value to copy the VPD policies automatically from the original table to the new table during online redefinition.
DBMS_REDEFINITION.CONS_VPD_MANUAL Specify this value to copy the VPD policies manually from the original table to the new table during online redefinition.
B is be wrong as we check
D VPD policy are copyed

drop old one pdb and create new one

[code language=”sql”]
(1)[CDB$ROOT]sys@orcl> alter pluggable database q1 close force;
(1)[CDB$ROOT]sys@orcl> drop pluggable database q1 including datafiles;
(1)[CDB$ROOT]sys@orcl> create pluggable database q2 admin user q2 identified by q2;
(1)[CDB$ROOT]sys@orcl> alter pluggable database q2 open read write;
[/code]

this question are explained by oracle docs

Assume that the following auth_emp_dep_100 function is created for the VPD policy:

[code language=”sql”]
CREATE OR REPLACE FUNCTION hr.auth_emp_dep_100(
schema_var IN VARCHAR2,
table_var IN VARCHAR2
)
RETURN VARCHAR2
AS
return_val VARCHAR2 (400);
unm VARCHAR2(30);
BEGIN
SELECT USER INTO unm FROM DUAL;
IF (unm = ‘HR’) THEN
return_val := NULL;
ELSE
return_val := ‘DEPARTMENT_ID = 100’;
END IF;
RETURN return_val;
END auth_emp_dep_100;
/

Function created.
[/code]

The following ADD_POLICY procedure specifies a VPD policy for the original table hr.employees using the auth_emp_dep_100 function:

[code language=”sql”]
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;
/

PL/SQL procedure successfully completed.
[/code]

Create an interim table hr.int_employees.

[code language=”sql”]
CREATE TABLE hr.int_employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4));

Table created.
[/code]

Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.

[code language=”sql”]
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(‘hr’,’employees’,
DBMS_REDEFINITION.CONS_USE_PK);
END;

PL/SQL procedure successfully completed.
[/code]

Start the redefinition process.

[code language=”sql” highlight=”7,10″]
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;
/

PL/SQL procedure successfully completed.
[/code]

lets check vpd:

[code language=”sql”]
(1)[Q2]c##bushmelev_aa@orcl> select SQL_ID,CHILD_NUMBER,OBJECT_OWNER,OBJECT_NAME,POLICY_GROUP,POLICY,POLICY_FUNCTION_OWNER,con_id from v$vpd_policy where OBJECT_NAME=’INT_EMPLOYEES’;

SQL_ID | CHILD_NUMBER | OBJECT_OWN | OBJECT_NAME | POLICY_GROUP | POLICY | POLICY_FUNCTION_OWNER | CON_ID
————- | ———— | ———- | —————————— | ——————– | ———————- | —————————— | ———-
6f4qba0kra2pu | 0 | HR | INT_EMPLOYEES | SYS_DEFAULT | RD_PLCY$$INT_EMPLOYEE | HR | 4
[/code]

VPD policy exists on int table, answer D is wrong and C is correct

check constraints and triggers:

[/code]

all of them are on emmployees table and all constraint are enabled, answer B is wrong

[свернуть]

Examine the following commands for redefining a table with Virtual Private Database (VPD)

Primary Sidebar