oracle常用维护查询

查死锁

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S 
WHERE l.SESSION_ID=S.SID;

强制解除死锁

alter system kill session '1500,6160';

参考 www.jb51.net/article/85039.htm

根据sid查死锁的的sql和死锁前执行的sql

--死锁时
select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.SQL_ID and (b.SID=47 or b.SID=3847);
--死锁前
select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.PREV_SQL_ID and (b.SID=47 or b.SID=3847);

根据SERIAL#查会话

select * from v$session a where a.SERIAL# =28343

查询当前连接总数

--两个sql都可以
select count(*) from v$session;
select count(*) from v$process;

查询各机器对数据库的连接数

select machine ,count(machine) from v$session group by machine;

查询并发连接数

select count(*) from v$session where status='ACTIVE';

查看不同用户的连接数

select username,count(username) from v$session 
where username is not null group by username;

查看当前有哪些用户正在使用数据

SELECT osuser, a.username,
cpu_time/executions/1000000||'s', sql_fulltext,machine 
from v$session a, v$sqlarea b
where a.sql_address =b.address 
order by cpu_time/executions desc;

查询表空间列表

select tablespace_name, file_name, autoextensible
from dba_data_files
where tablespace_name in (
SELECT tablespace_name FROM dba_free_space 
GROUP BY tablespace_name);

查询各表空间使用率

select a.tablespace_name,
       round((a.maxbytes / 1024 / 1024), 2) "sun MB",
       round((a.bytes / 1024 / 1024), 2) "datafile MB",
       round(((a.bytes - b.bytes) / 1024 / 1024), 2) "used MB",
       round(((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024), 2) "free MB",
       round(((a.bytes - b.bytes) / a.maxbytes * 100), 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes, sum(maxbytes) maxbytes
          from dba_data_files
         where maxbytes != 0
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.maxbytes) desc;

查询表空间所属数据文件使用率

select bytes/1024/1024/1024 as 当前使用_GB,
maxbytes/1024/1024/1024 as 扩展最大值_GB,
maxblocks/1024/1024 as 每次扩展大小_MB,
file_name as 数据文件_名称,
tablespace_name as 所属表空间
from dba_data_files;

本作品采用《CC 协议》，转载必须注明作者和本文链接