查询临时表空间和回滚表空间的情况：
--临时表空间使用查询：
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 Settingsweifengz>
C:Documents and Settingsweifengz>
--临时表空间使用率查询，持续增长：
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;
Tag: Oracle Oracle优化 TTTBLOG