Oracle踩过的坑

/*字段为字符型 查询指定日期之间数据*/SELECT count( * ) FROM COMP_OUTPUT_INVOICE WHERE to_date( invoice_date, 'yyyy-mm-dd' ) BETWEEN to_date( TO_CHAR( LAST_DAY( ADD_MONTHS( SYSDATE, - 2 )) + 1, 'YYYY-MM-DD' ), 'yyyy-mm-dd' ) AND to_date( TO_CHAR( LAST_DAY( ADD_MONTHS( SYSDATE, - 1 )), 'YYYY-MM-DD' ),'yyyy-mm-dd') /*字段为日期类型 查时...

read more..

Oracle的几种分页

-- 1111111SELECT * FROM ( SELECT  row_.*,  ROWNUM rownum_ FROM  ( SELECT * FROM ( SELECT * FROM LISTEDCO_COLLECT_LOG WHERE 1 = 1 AND NAME LIKE '%深圳%' ) tmp ORDER BY tmp.id DESC ) row_ WHERE  ROWNUM <= 10 ) lcl WHERE rownum_ > 0 -- 22222 SELECT * FROM ( SELECT  row_.*,  ROWNUM rownum_ FROM  ( SELECT * FROM LISTEDCO_COLLEC...

read more..

数据库死锁问题解决

今天写项目无法oracle数据库插入数据,是死锁造成的,解决如下:   1)查找死锁的进程:sqlplus "/as sysdba" (sys/change_on_install)SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;  2)kill掉这个死锁的进程:  alter system kill session ‘sid,serial#’; (其...

read more..