Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "Oracle 12.2 how to redefine the table using VPD strategy online", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn how to redefine the table using VPD strategy online in Oracle 12.2 this article.

The creation statement of the original table jy.employees is as follows:

SQL > create table jy.employees (2 employee_id number (6) primary key, 3 first_name varchar2 (20), 4 last_name varchar2 (25) 5 constraint emp_last_name_nn not null, 6 email varchar2 (25) constraint emp_email_nn not null, 7 phone_number varchar2 (20), 8 hire_date date constraint emp_hire_date_nn not null, 9 job_id varchar2 (10) constraint emp_job_nn not null, 10 salary number (8 salary number), 11 commission_pct number (2) 12 manager_id number (6), 13 department_id number (4), 14 constraint emp_salary_min check (salary > 0), 15 constraint emp_email_uk unique (email) 16) Table created.

Use the following jy.auth_emp_dep_100 function to create a VPD policy

SQL > create or replace function jy.auth_emp_dep_100 (2 schema_var in varchar2, 3 table_var in varchar2 4) 5 return varchar2 6 as 7 return_val varchar2; 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: =' department_id = 100; 15 end if; 16 return return_val 17 end auth_emp_dep_100; 18 / Function created.

Execute the dbms_rls_add_policy procedure to specify the VPD policy using the jy.auth_emp_dep_100 function on the original table jy.employees table

SQL > begin 2 dbms_rls.add_policy (3 object_schema = > 'jy', 4 object_name = >' employees', 5 policy_name = > 'employees_policy', 6 function_schema = >' jy', 7 policy_function = > 'auth_emp_dep_100'), 8 end; 9 / PL/SQL procedure successfully completed.

In this example, all triggers are disabled when the table jy.employees table is redefined. Note that redefinition will not change the column name or data type. Therefore, the copy_vpd_opt parameter is set to dbms_redefinition.cons_vpd_auto when the start_refef_table procedure is executed.

1. Log in to the database with the user to perform the online redefinition operation

SQL > conn jy/jy@jypdbConnected.

two。 Verify that the original table can perform online redefinition

SQL > begin 2 dbms_redefinition.can_redef_table ('hr','employees',DBMS_REDEFINITION.CONS_USE_PK); 3 end; 4 / PL/SQL procedure successfully completed.

3. Create an intermediate table jy.int_employees

SQL > create table jy.int_employees (2 employee_id number (6), 3 first_name varchar2 (20), 4 last_name varchar2 (25), 5 email varchar2 (25), 6 phone_number varchar2 (20), 7 hire_date date, 8 job_id varchar2 (10), 9 salary number (8 salary number 2), 10 commission_pct number (2 manager_id number 2), 11 manager_id number (6), 12 department_id number (4); Table created.

4. Start an online redefine operation

SQL > begin 2 dbms_redefinition.start_redef_table (3 uname = > 'jy', 4 orig_table = >' employees', 5 int_table = > 'int_employees', 6 col_mapping = > NULL, 7 options_flag = > DBMS_REDEFINITION.CONS_USE_PK, 8 orderby_cols = > NULL, 9 part_name = > NULL, 10 copy_vpd_opt = > DBMS_REDEFINITION.CONS_VPD_AUTO), 11 end; 12 / PL/SQL procedure successfully completed.

When the copy_vpd_opt parameter is set to dbms_redefinition.cons_vpd_auto, only the owner of the table and the user who invokes the online redefinition operation can access the table during the online redefinition. The col_mapping parameter is set to NULL. When the copy_vpd_opt parameter is set to dbms_redefinition.cons_vpd_auto, the col_mapping parameter must be set to NULL or'*'.

5. Copy dependent objects

SQL > declare 2 num_errors pls_integer; 3 begin 4 dbms_redefinition.copy_table_dependents (5 uname = > 'jy', 6 orig_table = >' employees', 7 int_table = > 'int_employees', 8 copy_indexes = > DBMS_REDEFINITION.CONS_ORIG_PARAMS, 9 copy_triggers = > TRUE, 10 copy_constraints = > TRUE, 11 copy_privileges = > TRUE, 12 ignore_errors = > FALSE, 13 num_errors = > num_errors), 14 end; 15 / PL/SQL procedure successfully completed.

6. Disable all triggers for intermediate tables

SQL > alter table jy.int_employees disable all triggers;Table altered.

7. Optional operation to synchronize intermediate tables

SQL > begin 2 dbms_redefinition.sync_interim_table (3 uname = > 'jy', 4 orig_table = >' employees', 5 int_table = > 'int_employees'), 6 end; 7 / PL/SQL procedure successfully completed.

8. Complete the online redefinition operation

SQL > begin 2 dbms_redefinition.finish_redef_table (3 uname = > 'jy', 4 orig_table = >' employees', 5 int_table = > 'int_employees'), 6 end; 7 / PL/SQL procedure successfully completed.

9. Wait for the execution of any statement to query the intermediate table to delete it

SQL > drop table jy.int_employees;Table dropped

At this point, the redefinition operation is complete.

These are all the contents of the article "how to redefine VPD policies online in Oracle 12.2". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.