数据库锁表相关常用查询语句

alter session set current_schema=****

查询锁表及锁表人
select l.session_id sid,s.serial#,l.locked_mode,l.oracle_username,l.os_user_name,s.machine,o.object_name from v$locked_object l ,all_objects o,v$session s where l.object_id=o.object_id and l.session_id=s.sid order by sid,s.serial#;
--锁表情况查询
SELECT A.OWNER 方案名,
A.OBJECT_NAME 表名,
B.XIDUSN 回滚段号,
B.XIDSLOT 槽号,
B.OS_USER_NAME 系统用户名,
B.PROCESS 系统进程id, 
DECODE(C.STATUS,'INACTIVE','不活动','ACTIVE','活动') 活动情况 ,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM 连接方式,
C.LOGON_TIME
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C,
v$lock d
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
and C.sid = d.sid
and B.LOCKED_MODE = D.LMODE
ORDER BY 1,2;

--行锁的记录

select *
          from v$locked_object lo, dba_objects ao, v$session sess
         where ao.object_id = lo.object_id
           and lo.session_id = sess.sid
           and sess.username = 'SDBCREDIT' ;

---查询对应的sql
select * from v$sql t where t.SQL_ID in
  ('****','');


----查询关联情况
select b.SQL_TEXT, a.BLOCKING_SESSION, a.LAST_CALL_ET, a.SID, a.OSUSER
  from gv$session a, gv$sql b
where a.SQL_ID = b.SQL_ID
   and a.STATUS = 'ACTIVE'
   and a.INST_ID = b.INST_ID
order by a.LAST_CALL_ET desc;

解除锁表
ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';

查询服务器上占用的连接数
select *
          from gv$session t
         where username = '****'
           /*and t.MACHINE  IN ( '****')*/
select t.MACHINE,count(1)
          from gv$session t
         where username = '****'
           /*and t.MACHINE  IN ( '')*/
       ---  order by LOGON_TIME
         group BY T.MACHINE
查询数据库最大连接数
1、查看当前的数据库连接数
 select count(*) from v$process ;    --当前的数据库连接数
2、数据库允许的最大连接数
 select value from v$parameter where name ='processes';  --数据库允许的最大连接数
3、修改数据库最大连接数
 alter system set processes = 300 scope = spfile;  --修改最大连接数: