ORACLE技术小整理

1.查询并杀死锁

select t2.username,t2.sid,t2.serial#,t2.logon_time,t3.sql_text
      from v$locked_object t1,v$session t2,v$sqltext t3
      where t1.session_id=t2.sid
      and t2.sql_address=t3.address
      order by t2.logon_time;

 alter system kill session 'sid,serial#';(sid,serial#为查询字段值);

 

2.分页

select * from (select rownum r,* from yourtable where rownum < N) a where a.r between N1 and N2;

 

3.用其他表的数据更新

 UPDATE (
    SELECT T1.BALANCE, T2.MONTH_CUMULATE, T2.QUARTER_CUMULATE, T2.YEAR_CUMULATE
    FROM PLSQL_MX_BBHQ_PROCESSED T1, PLSQL_MX_BBHQ_CUMULATE T2
    WHERE T1.ACNO = T2.ACNO)
    SET
      MONTH_CUMULATE   = MONTH_CUMULATE   + BALANCE ,
      QUARTER_CUMULATE = QUARTER_CUMULATE + BALANCE ,
      YEAR_CUMULATE    = YEAR_CUMULATE    + BALANCE ;
 

UPDATE PLSQL_MX_JJ_CUMULATE T2
SET t2.MONTH_CUMULATE = t2.MONTH_CUMULATE + ( SELECT  BALANCE FROM PLSQL_MX_JJ_SUM t1 WHERE t1.acno  = t2.acno ),
    t2.quarter_cumulate = t2.quarter_cumulate  + ( SELECT BALANCE FROM PLSQL_MX_JJ_SUM t1 WHERE t1.acno  = t2.acno ),
    t2.year_cumulate = t2.year_cumulate + ( SELECT BALANCE FROM PLSQL_MX_JJ_SUM t1 WHERE t1.acno  = t2.acno )
 WHERE EXISTS ( SELECT BALANCE FROM PLSQL_MX_JJ_SUM t1 WHERE t1.acno  = t2.acno);

 

 4.全半角转换

FUNCTION FUNC_FULLTOHALF(INSTR VARCHAR2) RETURN VARCHAR2
IS
  V_I INTEGER;
  OUTSTR varchar2(20);
BEGIN
  V_I    := 1;
  OUTSTR := '';
  WHILE V_I <= LENGTH(INSTR) LOOP
    IF ASCII(SUBSTR(INSTR, V_I, 1)) > 41856 AND
       ASCII(SUBSTR(INSTR, V_I, 1)) < 41856 + 128 THEN
      OUTSTR := OUTSTR || CHR(ASCII(SUBSTR(INSTR, V_I, 1)) - 41856);
    ELSE
      OUTSTR := OUTSTR || SUBSTR(INSTR, V_I, 1);
    END IF;
    V_I := V_I + 1;
  END LOOP;
 
  RETURN OUTSTR;

  EXCEPTION
   WHEN OTHERS THEN RETURN OUTSTR;
  
 
END FUNC_FULLTOHALF; 

5.关于计划dbms_scheduler

a.create job

BEGIN

 

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(job_name      => 'delete_goods_job',
                            job_type      => 'STORED_PROCEDURE',
                            job_action    => 'delete_goods',
                            repeat_interval => 'FREQ=SECONDLY; INTERVAL=6',
                            enabled       => true,
                            comments      => 'delete goods data'                  
                            );
END;
/

drop job

BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'delete_goods_job'); 
END;
/

disable job

BEGIN
    DBMS_SCHEDULER.DISABLE(name => 'delete_goods_job'); 
END;
/

enable job

BEGIN
    DBMS_SCHEDULER.ENABLE(name => 'delete_goods_job'); 
END;
/

select jobs

select * from USER_SCHEDULER_JOBS;

query logs(感谢itpub的teddyboy)

select * from ALL_SCHEDULER_JOB_RUN_DETAILS
where owner = 'SCOTT'
order by log_date desc

delete logs

delete from ALL_SCHEDULER_JOB_RUN_DETAILS
where owner = 'SCOTT'

 

6.查询约束

 select a.constraint_name , --约束名称
　　 a.CONSTRAINT_TYPE ,--约束类型
　　 a.TABLE_NAME ,--表名
　　 b.COLUMN_NAME ,--列名
　　 a.SEARCH_CONDITION --约束内容
　　 from USER_CONSTRAINTS a,USER_CONS_COLUMNS b
　　 where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
     AND a.constraint_name = '要查询约束名称'