臨時表空間和回滾表空間使用率查詢
查詢臨時表空間和回滾表空間的情況：
--臨時表空間使用查詢：
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- oracle表空間使用率查詢Oracle
- 臨時表空間被佔滿的原因查詢
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- oracle 臨時表空間的增刪改查Oracle
- oracle臨時表空間相關Oracle
- 4.2.1.8規劃臨時表空間
- 刪除臨時表空間組
- MySQL InnoDB臨時表空間配置MySql
- 查詢表空間使用情況
- 表空間使用量查詢
- 臨時表空間使用率過高的解決辦法
- Oracle 查詢佔用臨時表空間大的歷史會話和SQLOracle會話SQL
- 2.5.7 建立預設臨時表空間
- 16、表空間 建立表空間
- 查詢過去一段時間內某條sql使用的臨時表空間大小SQL
- 消除臨時表空間暴漲的方法
- SQLServer如何釋放tempdb臨時表空間SQLServer
- MySQL 5.7 新特性 共享臨時表空間及臨時表改進MySql
- oracle建立使用者，表空間，臨時表空間，分配許可權步驟詳解Oracle
- 【UNDO】Oracle undo表空間使用率過高，因為一個查詢Oracle
- 達夢資料庫表空間等空間大小查詢方法總結資料庫
- MYSQL造資料佔用臨時表空間MySql
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- 查詢表空間使用情況的指令碼指令碼
- Oracle查詢表空間的每日增長量Oracle
- 臨時表空間ORA-1652問題解決
- undo表空間使用率過高解決
- Oracle表空間Oracle
- oracle 表空間Oracle
- PostgreSQL 表空間SQL
- PostgreSQL：表空間SQL
- 表空間集自包含檢查
- 表空間利用率及表空間的補充
- [20180423]表空間閃回與snapshot standby
- 檢視oracle臨時表空間佔用率的檢視Oracle