-- 用户连接数
select username,count(username) from v$session where username is not null group by username;
-- 查看是否有未提交的事务
select SQL_TEXT from v$sql,v$transaction where LAST_ACTIVE_TIME=START_DATE;
-- 查看哪个session引起的表被锁住
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
-- 锁表进程
select * from v$locked_object;
-- 查看session
select* from v$session;
-- 最大的连接数
select value from v$parameter where name='processes';
-- 当前连接数
select count(1) from v$process;
-- 查看配置参数
select value from v$parameter;
-- 查询表的外键
select 'alert table ' || table_name || ' enable constraint '||constraint_name||';'
from user_constraints where constraint_type='R';
-- 活跃的连接数
select * from v$session where status='ACTIVE';
select count(*) from v$session where status='ACTIVE';
-- 查看所有的用户
select * from all_users;
-- 查看链接的用户和程序
select sid,serial#,username,program,machine,client_info
from v$session
where username is not null
order by username,program,machine;
-- 正在执行的sql
SELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value;
--
select a.username, a.sid,b.SQL_TEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address;
-- 执行的cpu排序
SELECT OSUSER 电脑登录身份,
PROGRAM 发起请求的程序,
USERNAME 登录系统的用户名,
SCHEMANAME,
B.Cpu_Time 花费cpu的时间,
STATUS,
B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC;
-- 查出oracle当前的被锁对象
SELECT l.session_id sid,
s.serial#,
l.locked_mode 锁模式,
l.oracle_username 登录用户,
l.os_user_name 登录机器用户名,
s.machine 机器名,
s.terminal 终端用户名,
o.object_name 被锁对象名,
s.logon_time 登录数据库时间
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 s.sid,
s.serial#,
s.event,
a.sql_text,
a.sql_fulltext,
s.username,
s.status,
s.machine,
s.terminal,
s.program,
a.executions,
s.sql_id,
p.spid,
a.direct_writes
FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s
LEFT JOIN v$sqlarea a
ON s.sql_id = a.sql_id
INNER JOIN v$process p
ON s.paddr = p.addr;
select * from v$sql;
select * from v$transaction;
-- 查看是否有未提交的事务
select SQL_TEXT from v$sql,v$transaction where LAST_ACTIVE_TIME=START_DATE;
-- 查看哪个session引起的表被锁住
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
select * from v$transaction;
-- 杀死session
alter system kill session 'sid,serial#';
alter system kill session '2276,3';
alter system kill session '393,49431';
-- 查询Oracle正在执行的sql语句及执行该语句的用户：
SELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.sid,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value;
-- 查看正在执行sql的发起者的发放程序:
SELECT OSUSER 电脑登录身份,
PROGRAM 发起请求的程序,
USERNAME 登录系统的用户名,
SCHEMANAME,
B.Cpu_Time 花费cpu的时间,
STATUS,
B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC;
-- 查看各种连接
select b.MACHINE, b.PROGRAM, count(*)
from v$process a,
v$session b
where a.ADDR = b.PADDR
and b.USERNAME is not null
group by b.MACHINE, b.PROGRAM
order by count(*) desc;
-- 查出oracle当前的被锁对象：
SELECT l.session_id sid,
s.serial#,
l.locked_mode 锁模式,
l.oracle_username 登录用户,
l.os_user_name 登录机器用户名,
s.machine 机器名,
s.terminal 终端用户名,
o.object_name 被锁对象名,
s.logon_time 登录数据库时间
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#;
-- 杀死session
alter system kill session '760,15';