常用的SQL语句收集

--设置某个字段的默认值

alter table WCP_B_LEGAL_SHAREHOLDER modify INDUSTRY default '108,109';
--删除字段默认值
alter table testa_0823 modify nationality default null;

--根据身份证拿生日

UPDATE wcp_b_dlr_key_person k SET k.birthday = to_date(substr(k.id_no, 7, 8), 'YYYY/MM/DD')
WHERE k.id_type = '1' AND k.id_no IS NOT NULL AND LENGTH(k.id_no) = 18 AND k.birthday is null AND (k.role='1' or k.role='3' or k.role='4');

--表修改

--删除表
drop table WCP_BI_WHL_JL_DLR_BUSS_PERIOD;
--增加字段
alter table WCP_B_LEGAL_SHAREHOLDER add INDUSTRY varchar2(4000);
删除字段
alter table WCP_B_LEGAL_SHAREHOLDER drop column INDUSTRY; 

增加文字描述

-- Add comments to the table 
comment on table WCP_BI_WHL_JL_DLR_BUSS_PERIOD
  is 'BI额度激活历史表';
-- Add comments to the columns 
comment on column WCP_BI_WHL_JL_DLR_BUSS_PERIOD.DEALER_CODE
 is '经销商code';

--表锁

--查被用户锁住的表
SELECT B.USERNAME, B.SID, B.SERIAL#, LOGON_TIME
 FROM V$LOCKED_OBJECT A, V$SESSION B
     WHERE A.SESSION_ID = B.SID
     ORDER BY B.LOGON_TIME;
--杀死表锁
ALTER SYSTEM KILL SESSION '195,50001';（B.SID, B.SERIAL#）