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 協議》，轉載必須註明作者和本文連結