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 T2SET 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 VARCHAR2IS  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 = '要查询约束名称'