oracle常用语句

#查询是否死锁
select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session s where l.object_id　=　o.object_id and l.session_id=s.sid

SELECT
S.USERNAME,
DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
O.OWNER,
O.OBJECT_NAME,
O.OBJECT_TYPE,
S.SID,
S.SERIAL#,
S.TERMINAL,
S.MACHINE,
S.PROGRAM,
S.OSUSER
FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID(+)
杀死SID:
ALTER SYSTEM KILL SESSION 'si_id名,serial名'

 

oracle 分页查询：
SELECT ROWNUM,t.* FROM (SELECT ROWNUM r,b.* FROM tableName b WHERE ROWNUM<= 10) t WHERE r>0;
创建自增ID：
CREATE SEQUENCE CATI_FTP_DATA_TMP_SEQ
MINVALUE 1
MAXVALUE 99999999
START WITH 1
INCREMENT BY 1
NOCACHE

 

update TB_AGENT_CALL_LIST_UPDATE_755@SZJS755 set UPDATE_DATE='20200705' where UPDATE_DATE='20200709' and ROWNUM<10000;

 

#mysql更新

select * from T_OB_TASKITEM_202000 a,T_OB_RECYCLE_DATA b where a.TASKITEM_ID=b.ITEM_ID and a.EXEC_STATUS=1 and a.ITEM_STATUS=2;

update T_OB_TASKITEM_202000 a,T_OB_RECYCLE_DATA b set a.EXEC_STATUS=2,a.ITEM_STATUS=2,a.EXT20='20201104故障处理' where a.TASKITEM_ID=b.ITEM_ID and a.EXEC_STATUS=1 and a.ITEM_STATUS=2;

#Oracle更新

update T_OB_TASKITEM_202000 a set a.EXEC_STATUS=2,a.ITEM_STATUS=2,a.EXT20='20201104故障处理' where a.TASKITEM_ID in( select a.TASKITEM_ID from T_OB_TASKITEM_202000 a,T_OB_RECYCLE_DATA b where a.TASKITEM_ID=b.ITEM_ID and a.EXEC_STATUS=1 and a.ITEM_STATUS=2);