臨時表空間和回滾表空間使用率查詢
查詢臨時表空間和回滾表空間的情況：
--臨時表空間使用查詢：
11:06:52 SQL> select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text
11:08:37 2 from v$sort_usage sort, v$session sess ,v$sql sql
11:08:37 3 where sort.SESSION_ADDR = sess.SADDR
11:08:37 4 and sql.sql_id = sess.sql_id
11:08:37 5 order by blocks desc;
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 3667.968 select * from dba_objects a,db
a_objects b order by 1
11:08:38 SQL> /
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 3734.528 select * from dba_objects a,db
a_objects b order by 1
11:09:00 SQL> select 'the '||name||' temp tablespaces '||tablespace_name||' idle
'||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||'% at '||to_char(sysdat
e,'yyyymmddhh24miss')
11:09:24 2 from
11:09:24 3 (select d.tablespace_name tablespace_name,
11:09:24 4 nvl(sum(used_blocks),0) to
t_used_blocks,
11:09:24 5 sum(blocks) total_blocks
11:09:24 6 from v$sort_segment v ,dba_temp_files d
11:09:24 7 where d.tablespace_name=v.tablespace_name(+)
11:09:24 8 group by d.tablespace_name) s, v$database;
'THE'||NAME||'TEMPTABLESPACES'||TABLESPACE_NAME||'IDLE'||ROUND(100-(S.TOT_USED_B
--------------------------------------------------------------------------------
the ORCL temp tablespaces TEMP idle .027% at 20081007110925
--使用者取消查詢：
10:44:56 SQL> select * from dba_objects a,dba_objects b order by 1;
^C
C:\Documents and Settings\weifengz>
C:\Documents and Settings\weifengz>
--臨時表空間使用率查詢，持續增長：
11:12:23 SQL> select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text
11:12:24 2 from v$sort_usage sort, v$session sess ,v$sql sql
11:12:24 3 where sort.SESSION_ADDR = sess.SADDR
11:12:24 4 and sql.sql_id = sess.sql_id
11:12:24 5 order by blocks desc;
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 4314.112 select * from dba_objects a,db
a_objects b order by 1
11:12:25 SQL>
11:12:33 SQL> /
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 4337.664 select * from dba_objects a,db
a_objects b order by 1
11:12:35 SQL>
11:12:36 SQL> /
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 4345.856 select * from dba_objects a,db
a_objects b order by 1
11:12:37 SQL>
11:12:40 SQL> /
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 4359.168 select * from dba_objects a,db
a_objects b order by 1
11:12:41 SQL>
11:12:50 SQL>
11:12:51 SQL> /
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 4378.624 select * from dba_objects a,db
a_objects b order by 1
11:12:51 SQL>
11:12:53 SQL>
11:12:53 SQL>
11:12:53 SQL> /
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 4383.744 select * from dba_objects a,db
a_objects b order by 1
11:12:54 SQL>
11:12:57 SQL>
11:13:53 SQL>
11:13:53 SQL> /
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 4553.728 select * from dba_objects a,db
a_objects b order by 1
11:13:54 SQL> /
SID SEGTYPE MB SQL_TEXT
---------- --------- ---------- ------------------------------
131 SORT 4799.488 select * from dba_objects a,db
a_objects b order by 1
--殺掉該程式：
11:15:55 SQL> select sid,serial#,status from v$session where sid=131;
SID SERIAL# STATUS
---------- ---------- --------
131 16 ACTIVE
11:16:02 SQL> alter system kill session '131,16';
系統已更改。
11:16:33 SQL> select sid,serial#,status from v$session where sid=131;
未選定行
--臨時表空間釋放：
11:19:57 SQL> l
1 select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text
2 from v$sort_usage sort, v$session sess ,v$sql sql
3 where sort.SESSION_ADDR = sess.SADDR
4 and sql.sql_id = sess.sql_id
5* order by blocks desc
11:19:58 SQL> /
未選定行
11:19:59 SQL> select 'the '||name||' temp tablespaces '||tablespace_name||' idle
'||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||'% at '||to_char(sysdat
e,'yyyymmddhh24miss')
11:20:12 2 from
11:20:12 3 (select d.tablespace_name tablespace_name,
11:20:12 4 nvl(sum(used_blocks),0) to
t_used_blocks,
11:20:12 5 sum(blocks) total_blocks
11:20:12 6 from v$sort_segment v ,dba_temp_files d
11:20:12 7 where d.tablespace_name=v.tablespace_name(+)
11:20:12 8 group by d.tablespace_name) s, v$database;
'THE'||NAME||'TEMPTABLESPACES'||TABLESPACE_NAME||'IDLE'||ROUND(100-(S.TOT_USED_B
--------------------------------------------------------------------------------
the ORCL temp tablespaces TEMP idle 100% at 20081007112013
--回滾段使用率：
11:09:25 SQL> select decode(sign(round(100*((UNDOB-UNDO*DBS)/UNDOB),0)-10), 1,'
normal:',
11:10:10 2 decode(sign(round(100*((UNDOB-UNDO*DBS)/
UNDOB),0)-20), 1,'warning:','error:'))
11:10:10 3 ||' the '||instance_name||' undo tablesp
ace '||tablespace_name||' total space '
11:10:10 4 ||UNDOB/1024/1024||'MB used space '||rou
nd((UNDO*DBS/1024/1024),0)||'MB idle '||
11:10:10 5 round(100*((UNDOB-UNDO*DBS)/UNDOB),0)||'
% at '||to_char(sysdate,'yyyymmddhh24miss') as a
11:10:10 6 FROM
11:10:10 7 (select instance_name from V$instance),
11:10:10 8 (select nvl(sum(undoblks),0) UNDO from v
$undostat
11:10:10 9 where be
gin_time >(select sysdate - UR/(3600*24) from
11:10:10 10 (select
value as UR from v$parameter where name='undo_retention'))),
11:10:10 11 (select value as DBS from v$parameter w
here name='db_block_size'),
11:10:10 12 (select sum(bytes) as UNDOB,tablespace_n
ame from dba_data_files
11:10:10 13 where tablespace_name=(select upper(value) as UN
DO
11:10:10 14 from v$parameter where name='undo_tablespace')
11:10:10 15 group by tablespace_name);
A
--------------------------------------------------------------------------------
normal: the orcl undo tablespace UNDOTBS1 total space 105MB used space 0MB idle
100% at 20081007111011
11:11:38 SQL> select tablespace_name,status,sum(bytes)/1024/1024/1024 from dba_u
ndo_extents group by tablespace_name,status;
TABLESPACE_NAME STATUS SUM(BYTES)/1024/1024/1024
------------------------------ --------- -------------------------
UNDOTBS1 UNEXPIRED .000061035
UNDOTBS1 EXPIRED .041870117
--格式化的指令碼
select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess ,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.sql_id = sess.sql_id
order by blocks desc
select 'the '||name||' temp tablespaces '||tablespace_name||' idle '||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||'% at '||to_char(sysdate,'yyyymmddhh24miss')
from
(select d.tablespace_name tablespace_name,
nvl(sum(used_blocks),0) tot_used_blocks,
sum(blocks) total_blocks
from v$sort_segment v ,dba_temp_files d
where d.tablespace_name=v.tablespace_name(+)
group by d.tablespace_name) s, v$database;
select decode(sign(round(100*((UNDOB-UNDO*DBS)/UNDOB),0)-10), 1,'normal:',
decode(sign(round(100*((UNDOB-UNDO*DBS)/UNDOB),0)-20), 1,'warning:','error:'))
||' the '||instance_name||' undo tablespace '||tablespace_name||' total space '
||UNDOB/1024/1024||'MB used space '||round((UNDO*DBS/1024/1024),0)||'MB idle '||
round(100*((UNDOB-UNDO*DBS)/UNDOB),0)||'% at '||to_char(sysdate,'yyyymmddhh24miss') as a
FROM
(select instance_name from V$instance),
(select nvl(sum(undoblks),0) UNDO from v$undostat
where begin_time >(select sysdate - UR/(3600*24) from
(select value as UR from v$parameter where name='undo_retention'))),
(select value as DBS from v$parameter where name='db_block_size'),
(select sum(bytes) as UNDOB,tablespace_name from dba_data_files
where tablespace_name=(select upper(value) as UNDO
from v$parameter where name='undo_tablespace')
group by tablespace_name);
select tablespace_name,status,sum(bytes)/1024/1024/1024 from dba_undo_extents group by tablespace_name,status;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-468168/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 表空間，臨時表空間使用率查詢Oracle
- 臨時表空間的空間使用情況查詢
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- oracle表空間使用率查詢Oracle
- 查詢表空間的使用率
- 查詢表空間的大小和使用率
- Oracle下查詢臨時表空間佔用率Oracle
- Oracle 查詢表大小以及表空間使用率Oracle
- Oracle修改預設表空間和預設臨時表空間Oracle
- 表空間查詢和管理
- 臨時表空間被佔滿的原因查詢
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- oracle的臨時表空間使用率99.9%Oracle
- ORACLE 臨時表空間使用率過高分析Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- oracle回滾段 undo 表空間Oracle
- 臨時表空間的建立、刪除，設定預設臨時表空間
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- oracle表空間查詢Oracle
- 表空間大小查詢
- 表空間查詢資訊
- 臨時表空間的增刪改查
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- oracle的臨時表空間Oracle
- Oracle Temp 臨時表空間Oracle
- oracle清理和重建臨時表空間Oracle
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案
- oracle 查詢表空間使用率的語句Oracle
- 查詢表空間已使用空間和空閒空間的簡單檢視
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案（續）
- Oracle - 回滾表空間 Undo 的整理Oracle