oracle 常用维护sql语句

1. 创建用户并授权：
  CREATE USER bomcbp IDENTIFIED BY bomcbp;
  grant connect,resource,dba to bomcbp;
 
--------------------------------------------
 
2. 快速备份表：
create table iptfn_bomcitsm_itrup_temp as select * from iptfn_bomcitsm_itrup;

-----------------------------------------------------------------------------

3. select条件表达式
select formseq,id,title,describe,breaktype,subtype,subtype1,subtype2,cause,solution,planstarttime,planendtime,starttime,finishtime,stopbiz,status,askername,
     (case when length(askerlink) > 30 then substr(askerlink, 0, 11)
            else askerlink end) askerlink,updatetime
       from iptfn_bomcitsm_itrup T
             where T.saveflag = 'false'
             and T.uploadok = 'false' order by T.formseq desc
   
--------------------------------------------------------------
   
4. select组装sql
select 'insert into iptfn_bomcitsm_itrup(formseq,createtime,resendflag,filename) values (' ||
       T.FORMSEQ || ',' || T.Createtime || ',' || T.Resendflag || ',''' ||
       T.Filename || ''')' sql
  from iptfn_bomcitsm_itrup T;
 
-------------------------------------------------------------
 
5. oracle锁表及释放
--锁表查询
SELECT object_name, machine, s.sid, s.serial#
  FROM gv$locked_object l, dba_objects o, gv$session s
 WHERE l.object_id　 = o.object_id
   AND l.session_id = s.sid;

--释放SESSION SQL:
--alter system kill session 'sid, serial#';
ALTER system kill session '194, 4579';
ALTER system kill session '133, 791';

------------------------------------------------------------