Oracle修改字段顺序和字段类型以及拼接字段,查看锁表

字段有数据,则类型不能直接rename

/*增加一个新字段sub_pol_code*/
alter table IPR_SUB_POLICY add sub_pol_code VARCHAR2(8);

/*将原字段ORDER_SEQ数据更新到增加的字段sub_pol_code*/
update IPR_SUB_POLICY set sub_pol_code=trim(ORDER_SEQ);

/*更新完,删除原字段ORDER_SEQ*/
alter table IPR_SUB_POLICY drop column ORDER_SEQ;

将字段

拼接为qwe-1

update IPR_SUB_POLICY set sub_pol_code= CONCAT(CONCAT(pol_code,'-'),sub_pol_code) where sub_pol_id>=1818

 

select * from sys.col$ where obj#=88368;
select * from all_objects where owner = 'PRODUCT' and object_name = 'IPR_SUB_POLICY' ;

select obj#,col#,name from sys.col$ where obj#=88368;

update sys.col$ set col#=7 where obj#=88368 and name='SUB_POL_CODE';
update sys.col$ set col#=3 where obj#=88368 and name='SUB_POL_NAME';
update sys.col$ set col#=4 where obj#=88368 and name='CREATE_DATE';
update sys.col$ set col#=5 where obj#=88368 and name='SALE_SERVE_OPTION';
update sys.col$ set col#=6 where obj#=88368 and name='APPLY_TYPE_CODE';

[test@rhel675771 ~]$ sqlplus sys/Clic1234 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期三 8月 17 17:15:31 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> update sys.col$ set col#=3 where obj#=88368 and name='SUB_POL_CODE'
  2  
SQL> select obj#,col#,name from sys.col$ where obj#=88368;

      OBJ#       COL# NAME
---------- ---------- ------------------------------
     88368          1 SUB_POL_ID
     88368          2 POL_CODE
     88368          3 SUB_POL_NAME
     88368          4 CREATE_DATE
     88368          5 SALE_SERVE_OPTION
     88368          6 APPLY_TYPE_CODE
     88368          7 SUB_POL_CODE

7 rows selected.

SQL> update sys.col$ set col#=3 where obj#=88368 and name='SUB_POL_CODE';

1 row updated.

SQL> select obj#,col#,name from sys.col$ where obj#=88368;

      OBJ#       COL# NAME
---------- ---------- ------------------------------
     88368          1 SUB_POL_ID
     88368          2 POL_CODE
     88368          3 SUB_POL_NAME
     88368          4 CREATE_DATE
     88368          5 SALE_SERVE_OPTION
     88368          6 APPLY_TYPE_CODE
     88368          3 SUB_POL_CODE

7 rows selected.

SQL> update sys.col$ set col#=4 where obj#=88368 and name='SUB_POL_NAME';

1 row updated.

SQL> update sys.col$ set col#=5 where obj#=88368 and name='CREATE_DATE';

1 row updated.

SQL> update sys.col$ set col#=6 where obj#=88368 and name='SALE_SERVE_OPTION';

1 row updated.

SQL> update sys.col$ set col#=7 where obj#=88368 and name='APPLY_TYPE_CODE';    

1 row updated.

SQL> select obj#,col#,name from sys.col$ where obj#=88368;

      OBJ#       COL# NAME
---------- ---------- ------------------------------
     88368          1 SUB_POL_ID
     88368          2 POL_CODE
     88368          4 SUB_POL_NAME
     88368          5 CREATE_DATE
     88368          6 SALE_SERVE_OPTION
     88368          7 APPLY_TYPE_CODE
     88368          3 SUB_POL_CODE

7 rows selected.

SQL> commit;

Commit complete.

 

select *  from v$locked_object x , dba_objects m where x.OBJECT_ID=m.OBJECT_ID
   
    select * from v$session where sid=3428
   
   alter system kill session '3428,3249'--sid ,serial#

 

转载于:https://my.oschina.net/u/2822116/blog/734188

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值