oracle易忘知识点

树形结构同级排序

select t.parent_id, t.node_id, level, t.node_order from table_test t
start with t.parent_id = -99999 --起始父节点
connect by t.parent_id = prior t.node_id --本层level的父节点=上一层level的子节点
order siblings by t.node_order; --同级用node_order排序

clob使用

超长字符串用clob，对应java端和varchar2的使用一致

杀进程

1、
select SID, SERIAL#, t.*
from V$session t
where SID in (select sid from v$enqueue_lock t where t.type = 'TO')
and MACHINE = 'xxx';--条件要换

select vs.*
from v$session vs
, v$lock vl
, dba_objects obj
where 1 = 1
and obj.object_id = vl.id1
and vl.sid = vs.sid
and obj.object_name = upper('xxx');--这里填表名，可以查出用这张表的session

alter system kill session '216,2147';--填入SID, SERIAL#

2、
select * FROM dba_ddl_locks where name =upper('xxxx');--存储过程名→→sid
select t.sid, t.serial# from v$session t where t.sid in ('5333');--sid→→sessionid
alter system kill session '5333,54425';--sid,sessionid

---

版权声明：本文所有权归作者！ 商业用途转载请联系作者授权！ 非商业用途转载，请标明本文链接及出处！
赞成、反驳、不解的小伙伴，欢迎一起交流！