Oracle12.2怎么修改表的列名

这篇文章主要介绍"Oracle12.2怎么修改表的列名"，在日常操作中，相信很多人在Oracle12.2怎么修改表的列名问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"Oracle12.2怎么修改表的列名"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

下面的例子将演示联机重定义使用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@jypdbConnected.

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 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.可选操作同中间表

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.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,2)                       YSALES_PID    NUMBER(6)                         YPROMOTION_ID NUMBER(6)                         Y

10.等待任何查询中间表的语句执行完成后将其删除

SQL> drop table jy.int_orders;Table dropped

到此重定义操作就完成了。

到此，关于"Oracle12.2怎么修改表的列名"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！