Secure CRT

Oracle (SecureCRT) : Temp Tablespace Monitoring Queries

Dear Readers, In this article, we will see Oracle (SecureCRT) : Temp Tablespace Monitoring Queries. Menubar : TEMP Tabname : Def_temp

set linesize 300 
col property_name for a35 
col property_value for a20 
col description for a45

set linesize 300

col property_name for a35

col property_value for a20

col description for a45

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 

PROPERTY_NAME                       PROPERTY_VALUE       DESCRIPTION
----------------------------------- -------------------- ---------------------------------------------
DEFAULT_TEMP_TABLESPACE             TEMP                 Name of default temporary tablespace

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

----------------------------------- -------------------- ---------------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

Tabname : TS_Temp_Usage

set linesize 300 
SELECT   A.tablespace_name tablespace, D.mb_total, 
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, 
    D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free 
  FROM     v$sort_segment A, 
         ( 
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total 
         FROM     v$tablespace B, v$tempfile C 
         WHERE    B.ts#= C.ts# 
    GROUP BY B.name, C.block_size 
           ) D 
WHERE    A.tablespace_name = D.name 
GROUP by A.tablespace_name, D.mb_total; 
  
TABLESPACE                       MB_TOTAL    MB_USED    MB_FREE
------------------------------ ---------- ---------- ----------
TEMP                                32767          6      32761

set linesize 300

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

TABLESPACE MB_TOTAL MB_USED MB_FREE

------------------------------ ---------- ---------- ----------

TEMP 32767 6 32761

Tabname : Temp_Space_Usage_SQLID

set lines 232 pages 1000  

column username format a15 
column osuser format a10 
column module format a35 
column program format a20 
column Tablespace format a10

set lines 232 pages 1000

column username format a15

column osuser format a10

column module format a35

column program format a20

column Tablespace format a10

select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID, 
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, 
COUNT(*) statements 
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P 
WHERE T.session_addr = S.saddr 
AND S.paddr = P.addr 
AND T.tablespace = TBS.tablespace_name 
GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module, 
P.program, TBS.block_size, T.tablespace 
ORDER BY username) where mb_used > 1000;

select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID,

P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) statements

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module,

P.program, TBS.block_size, T.tablespace

ORDER BY username) where mb_used > 1000;

Tabname : Temp_sort_users

SET TERMOUT OFF; 
COLUMN current_instance NEW_VALUE current_instance NOPRINT; 
SELECT rpad(instance_name, 17) current_instance FROM v$instance; 
SET TERMOUT ON; 

PROMPT  
PROMPT +------------------------------------------------------------------------+ 
PROMPT | Report   : Temporary Sort Users                                        | 
PROMPT | Instance : &current_instance                                           | 
PROMPT +------------------------------------------------------------------------+ 

SET ECHO        OFF 
SET FEEDBACK    6 
SET HEADING     ON 
SET LINESIZE    180 
SET PAGESIZE    50000 
SET TERMOUT     ON 
SET TIMING      OFF 
SET TRIMOUT     ON 
SET TRIMSPOOL   ON 
SET VERIFY      OFF 

CLEAR COLUMNS 
CLEAR BREAKS 
CLEAR COMPUTES 

COLUMN instance_name      FORMAT a9                 HEADING 'Instance' 
COLUMN tablespace_name    FORMAT a15                HEADING 'Tablespace Name' 
COLUMN sid                FORMAT 99999              HEADING 'SID' 
COLUMN serial_id          FORMAT 99999999           HEADING 'Serial ID' 
COLUMN session_status     FORMAT a9                 HEADING 'Status' 
COLUMN oracle_username    FORMAT a18                HEADING 'Oracle User' 
COLUMN os_username        FORMAT a18                HEADING 'O/S User' 
COLUMN os_pid             FORMAT a8                 HEADING 'O/S PID' 
COLUMN session_terminal   FORMAT a10                HEADING 'Terminal'         TRUNC 
COLUMN session_machine    FORMAT a30                HEADING 'Machine'          TRUNC 
COLUMN session_program    FORMAT a20                HEADING 'Session Program'  TRUNC 
COLUMN contents           FORMAT a9                 HEADING 'Contents' 
COLUMN extents            FORMAT 999,999,999        HEADING 'Extents' 
COLUMN blocks             FORMAT 999,999,999        HEADING 'Blocks' 
COLUMN bytes              FORMAT 999,999,999,999    HEADING 'Bytes' 
COLUMN segtype            FORMAT a12                HEADING 'Segment Type' 

BREAK ON instance_name SKIP PAGE

SET TERMOUT OFF;

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(instance_name, 17) current_instance FROM v$instance;

SET TERMOUT ON;

PROMPT

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Temporary Sort Users |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

SET ECHO OFF

SET FEEDBACK 6

SET HEADING ON

SET LINESIZE 180

SET PAGESIZE 50000

SET TERMOUT ON

SET TIMING OFF

SET TRIMOUT ON

SET TRIMSPOOL ON

SET VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN instance_name FORMAT a9 HEADING 'Instance'

COLUMN tablespace_name FORMAT a15 HEADING 'Tablespace Name'

COLUMN sid FORMAT 99999 HEADING 'SID'

COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'

COLUMN session_status FORMAT a9 HEADING 'Status'

COLUMN oracle_username FORMAT a18 HEADING 'Oracle User'

COLUMN os_username FORMAT a18 HEADING 'O/S User'

COLUMN os_pid FORMAT a8 HEADING 'O/S PID'

COLUMN session_terminal FORMAT a10 HEADING 'Terminal' TRUNC

COLUMN session_machine FORMAT a30 HEADING 'Machine' TRUNC

COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC

COLUMN contents FORMAT a9 HEADING 'Contents'

COLUMN extents FORMAT 999,999,999 HEADING 'Extents'

COLUMN blocks FORMAT 999,999,999 HEADING 'Blocks'

COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes'

COLUMN segtype FORMAT a12 HEADING 'Segment Type'

BREAK ON instance_name SKIP PAGE

SELECT 
    i.instance_name       instance_name 
  , t.tablespace          tablespace_name 
  , s.sid                 sid 
  , s.serial#             serial_id 
  , s.status              session_status 
  , s.username            oracle_username 
  , s.osuser              os_username 
  , p.spid                os_pid 
  , s.program             session_program 
  , t.contents            contents 
  , t.segtype             segtype 
  , (t.blocks * c.value)  bytes 
FROM 
    gv$instance     i 
  , gv$session      s 
  , gv$process      p 
  , gv$sort_usage   t 
  , (select value from v$parameter 
     where name = 'db_block_size') c 
WHERE 
      s.inst_id = p.inst_id 
  AND p.inst_id = i.inst_id 
  AND t.inst_id = i.inst_id 
  AND s.inst_id = i.inst_id 
  AND s.saddr = t.session_addr 
  AND s.paddr = p.addr 
ORDER BY 
    i.instance_name 
  , s.sid; 

  
Instance  Tablespace Name    SID Serial ID Status    Oracle User        O/S User           O/S PID  Session Program      Contents  Segment Type            Bytes
--------- --------------- ------ --------- --------- ------------------ ------------------ -------- -------------------- --------- ------------ ----------------
ORCL11    TEMP              1513     59797 ACTIVE                       oracle             25309    oracle@server1234567  TEMPORARY LOB_DATA            5,242,880
          TEMP              1513     59797 ACTIVE                       oracle             25309    oracle@server1234567  TEMPORARY LOB_INDEX           1,048,576

Instance  Tablespace Name    SID Serial ID Status    Oracle User        O/S User           O/S PID  Session Program      Contents  Segment Type            Bytes
--------- --------------- ------ --------- --------- ------------------ ------------------ -------- -------------------- --------- ------------ ----------------
ORCL12    TEMP              1513     40323 ACTIVE                       oracle             13260    oracle@serverAB34567  TEMPORARY LOB_DATA            5,242,880
          TEMP              1513     40323 ACTIVE                       oracle             13260    oracle@serverAB34567  TEMPORARY LOB_INDEX           1,048,576

SELECT

i.instance_name instance_name

, t.tablespace tablespace_name

, s.sid sid

, s.serial# serial_id

, s.status session_status

, s.username oracle_username

, s.osuser os_username

, p.spid os_pid

, s.program session_program

, t.contents contents

, t.segtype segtype

, (t.blocks * c.value) bytes

FROM

gv$instance i

, gv$session s

, gv$process p

, gv$sort_usage t

, (select value from v$parameter

where name = 'db_block_size') c

WHERE

s.inst_id = p.inst_id

AND p.inst_id = i.inst_id

AND t.inst_id = i.inst_id

AND s.inst_id = i.inst_id

AND s.saddr = t.session_addr

AND s.paddr = p.addr

ORDER BY

i.instance_name

, s.sid;

Instance Tablespace Name SID Serial ID Status Oracle User O/S User O/S PID Session Program Contents Segment Type Bytes

--------- --------------- ------ --------- --------- ------------------ ------------------ -------- -------------------- --------- ------------ ----------------

ORCL11 TEMP 1513 59797 ACTIVE oracle 25309 oracle@server1234567 TEMPORARY LOB_DATA 5,242,880

TEMP 1513 59797 ACTIVE oracle 25309 oracle@server1234567 TEMPORARY LOB_INDEX 1,048,576

Instance Tablespace Name SID Serial ID Status Oracle User O/S User O/S PID Session Program Contents Segment Type Bytes

--------- --------------- ------ --------- --------- ------------------ ------------------ -------- -------------------- --------- ------------ ----------------

ORCL12 TEMP 1513 40323 ACTIVE oracle 13260 oracle@serverAB34567 TEMPORARY LOB_DATA 5,242,880

TEMP 1513 40323 ACTIVE oracle 13260 oracle@serverAB34567 TEMPORARY LOB_INDEX 1,048,576

Tabname : Temp_Space_Usage_SQLID_Login_Active_InActive

set lines 232 pages 1000  

column username format a15 
column osuser format a10 
column module format a35 
column program format a30 
column Tablespace format a10

set lines 232 pages 1000

column username format a15

column osuser format a10

column module format a35

column program format a30

column Tablespace format a10

select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID, 
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,s.logon_time,s.status, 
COUNT(*) statements 
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P 
WHERE T.session_addr = S.saddr 
AND S.paddr = P.addr 
AND T.tablespace = TBS.tablespace_name 
GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module, 
P.program, TBS.block_size, T.tablespace,s.logon_time,s.status 
ORDER BY s.logon_time) where mb_used > 10;

select * from (SELECT S.sid ,S.serial# , S.username, S.osuser, S.module,s.SQL_ID,

P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,s.logon_time,s.status,

COUNT(*) statements

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser,s.sql_id,P.spid, S.module,

P.program, TBS.block_size, T.tablespace,s.logon_time,s.status

ORDER BY s.logon_time) where mb_used > 10;

Tabname : Addspace… Read More

Oracle (SecureCRT) : Important Queries for UNDO Management

5 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the following Oracle (SecureCRT) : Important Queries for UNDO Management. MenuBar : UNDO Tab Name : Undo_Usage

set linesize 200 pagesize 200 
compute sum of "Total Space in GB" on report

1 2	set linesize 200 pagesize 200 compute sum of "Total Space in GB" on report

select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024) "Total Space in MB"
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name
/

TABLESPACE_NAME                SEGMENT_S Extent Count Total Blocks Total Space in MB
------------------------------ --------- ------------ ------------ -----------------
UNDOTBS1                       EXPIRED            173        10264           80.1875
UNDOTBS1                       UNEXPIRED           68         5944           46.4375
UNDOTBS2                       EXPIRED            177         9816           76.6875
UNDOTBS2                       UNEXPIRED           76         6608            51.625

select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks",

sum(blocks)*8/(1024) "Total Space in MB"

from dba_undo_extents

group by tablespace_name, status

order by tablespace_name

TABLESPACE_NAME SEGMENT_S Extent Count Total Blocks Total Space in MB

------------------------------ --------- ------------ ------------ -----------------

UNDOTBS1 EXPIRED 173 10264 80.1875

UNDOTBS1 UNEXPIRED 68 5944 46.4375

UNDOTBS2 EXPIRED 177 9816 76.6875

UNDOTBS2 UNEXPIRED 76 6608 51.625

Tab Name : Undo_Contetion

SET TERMOUT OFF; 
COLUMN current_instance NEW_VALUE current_instance NOPRINT; 
SELECT rpad(instance_name, 17) current_instance FROM v$instance; 
SET TERMOUT ON;

SET TERMOUT OFF;

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(instance_name, 17) current_instance FROM v$instance;

SET TERMOUT ON;

PROMPT  
PROMPT +------------------------------------------------------------------------+ 
PROMPT | Report   : Undo Contention                                             | 
PROMPT | Instance : &current_instance                                           | 
PROMPT +------------------------------------------------------------------------+ 

SET ECHO        OFF 
SET FEEDBACK    6 
SET HEADING     ON 
SET LINESIZE    180 
SET PAGESIZE    50000 
SET TERMOUT     ON 
SET TIMING      OFF 
SET TRIMOUT     ON 
SET TRIMSPOOL   ON 
SET VERIFY      OFF 

CLEAR COLUMNS 
CLEAR BREAKS 
CLEAR COMPUTES 

COLUMN instance_name      FORMAT a10    HEAD 'Instance' 
COLUMN class              FORMAT a18    HEADING 'Class'     
COLUMN ratio                            HEADING 'Wait Ratio'        

BREAK ON instance_name SKIP 2

PROMPT

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Undo Contention |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

SET ECHO OFF

SET FEEDBACK 6

SET HEADING ON

SET LINESIZE 180

SET PAGESIZE 50000

SET TERMOUT ON

SET TIMING OFF

SET TRIMOUT ON

SET TRIMSPOOL ON

SET VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN instance_name FORMAT a10 HEAD 'Instance'

COLUMN class FORMAT a18 HEADING 'Class'

COLUMN ratio HEADING 'Wait Ratio'

BREAK ON instance_name SKIP 2

SELECT 
    i.instance_name                     instance_name 
  , w.class                             class 
  , ROUND(100*(w.count/SUM(s.value)),8) ratio 
FROM 
    gv$instance i 
  , gv$waitstat w 
  , gv$sysstat s 
WHERE 
      i.inst_id = w.inst_id 
  AND i.inst_id = s.inst_id 
  AND w.class IN (  'system undo header' 
                  , 'system undo block' 
                  , 'undo header' 
                  , 'undo block' 
                 ) 
  AND s.name IN ('db block gets', 'consistent gets') 
GROUP BY 
    i.instance_name 
  , w.class 
  , w.count 
ORDER BY 
    i.instance_name 
  , w.class; 
  
Instance   Class              Wait Ratio
---------- ------------------ ----------
ORCL11     system undo block           0
           system undo header          0
           undo block           .0001595
           undo header          .0002848


ORCL12     system undo block           0
           system undo header          0
           undo block          .00018723
           undo header         .00030043

SELECT

i.instance_name instance_name

, w.class class

, ROUND(100*(w.count/SUM(s.value)),8) ratio

FROM

gv$instance i

, gv$waitstat w

, gv$sysstat s

WHERE

i.inst_id = w.inst_id

AND i.inst_id = s.inst_id

AND w.class IN ( 'system undo header'

, 'system undo block'

, 'undo header'

, 'undo block'

)

AND s.name IN ('db block gets', 'consistent gets')

GROUP BY

i.instance_name

, w.class

, w.count

ORDER BY

i.instance_name

, w.class;

Instance Class Wait Ratio

---------- ------------------ ----------

ORCL11 system undo block 0

system undo header 0

undo block .0001595

undo header .0002848

ORCL12 system undo block 0

system undo header 0

undo block .00018723

undo header .00030043

Tab Name : Undo_Trend_Today

set linesize 300 
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, 
         TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME, 
         UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON", MAXQUERYLEN, TUNED_UNDORETENTION 
         FROM v$UNDOSTAT WHERE rownum <= 144; 
		 
Instance   Class              Wait Ratio
---------- ------------------ ----------
ORCL11     system undo block           0
           system undo header          0
           undo block           .0001595
           undo header          .0002848


ORCL12     system undo block           0
           system undo header          0
           undo block          .00018723
           undo header         .00030044

set linesize 300

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,

TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,

UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON", MAXQUERYLEN, TUNED_UNDORETENTION

FROM v$UNDOSTAT WHERE rownum <= 144;

Instance Class Wait Ratio

---------- ------------------ ----------

ORCL11 system undo block 0

system undo header 0

undo block .0001595

undo header .0002848

ORCL12 system undo block 0

system undo header 0

undo block .00018723

undo header .00030044

Tab Name : Undo_Usage_For_Sessions

set linesize 300 
set pagesize 300 
col sid_serial for a30 
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, 
NVL(s.username, 'None') orauser, 
s.program, 
r.name undoseg, 
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" 
FROM sys.v_$rollname r, 
sys.v_$session s, 
sys.v_$transaction t, 
sys.v_$parameter x 
WHERE s.taddr = t.addr 
AND r.usn = t.xidusn(+) 
AND x.name = 'db_block_size';