查询临时表空间和回滚表空间的情况|Oracle Oracle优化 TTTBLOG

2011-08-0253920

查询临时表空间和回滚表空间的情况：

--临时表空间使用查询：

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;