Oracle数据库常用脚本

Oracle数据库常用脚本

• 锁表维护相关

--查询锁表
SELECT s.sid, s.serial# FROM v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid; 

--删除锁表
ALTER system KILL session '693,37361';

--直接查询出删除锁表的sql
select 'alter system kill session '''||s.sid ||','||s.SERIAL#||'''IMMEDIATE;' from v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid; 

--跟踪session中的锁表
SELECT s.username, l.type, 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,s.status
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL  /*and s.username = 'NC633'*/ and  decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) is not null
order by l.type;

• 数据库相关信息查询

--查询数据库版本
select * from pro