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

This article mainly introduces "how to modify the column name of the table by Oracle12.2". In the daily operation, I believe that many people have doubts about how to modify the column name of the table by Oracle12.2. The editor consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubt of "how to modify the column name of the table by Oracle12.2". Next, please follow the editor to study!

The following example demonstrates the online redefinition of a table that uses VPD policy and modifies a column name in the table. The creation statement of the original table jy.orders is as follows:

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 sales_rep_id number 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.

Create the following jy.auth_orders function to create the VPD policy

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; 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.

Let's execute the dbms_rls.add_policy procedure to use the jy.auth_orders function to specify the VPD policy for the original table jy.orders:

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.

In this example, the sales_rep_id column in the table is changed to sale_pid after being redefined. When one or more columns or the data type of a column can be modified during a redefinition process, dbms_redefinition.cons_vpd_manual must be specified for the copy_vpd_opt parameter during the start_refef_table process.

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 (3 uname = > 'jy', 4 tname = >' orders', 5 options_flag = > DBMS_REDEFINITION.CONS_USE_PK), 6 end; 7 / PL/SQL procedure successfully completed.

3. Create an intermediate table

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 sales_pid number 2), 8 sales_pid number (6), 9 promotion_id number (6)); Table created.

Notice that the sales_rep_id column is changed to sales_pid in the intermediate table.

4. Start an online redefine operation

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.

Because the original table has different column names from the intermediate table, the copy_vpd_opt parameter must be set to dbms_redefinition.cons_vpd_manual.

5. Create a VPD policy on the intermediate table

Create a function called jy.auth_orders_sales_pid to create the VPD policy, where the sales_pid column is used instead of the sales_rep_id column.

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; 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 execute the dbms_rls.add_policy procedure to use the jy.auth_orders_sales_pid function to add the VPD policy to the intermediate table

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. Copy dependent objects

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.

Notice here that the ignore_errors parameter is set to true. The reason is that the original table has indexes and constraints on the column sales_rep_id, and the column in the intermediate table is modified to sales_pid.

7. Query the dba_redefinition_errors view to check for errors

SQL > set long 8000SQL > set pages 8000SQL > column object_name heading 'object name' format a20SQL > column base_table_name heading' base table name' format a10SQL > column ddl_txt heading 'ddl that caused error' format a40SQL > select object_name, base_table_name, ddl_txt from dba_redefinition_errors;no rows selected

8. Optional operation is the same as intermediate table

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. Complete the online redefinition operation

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.ordersName Type Nullable Default Comments--ORDER_ID NUMBER (12) ORDER _ DATE TIMESTAMP (6) WITH LOCAL TIME ZONEORDER_MODE VARCHAR2 (8) YCUSTOMER_ID NUMBER (6) ORDER_STATUS NUMBER (2) YORDER_TOTAL NUMBER (8) YSALES_PID NUMBER (6) YPROMOTION_ID NUMBER (6) Y

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

SQL > drop table jy.int_orders;Table dropped

At this point, the redefinition operation is complete.

At this point, the study on "how to modify the column name of the table by Oracle12.2" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.