Dear Readers,
In this article, we will see Oracle (SecureCRT) : Temp Tablespace Monitoring Queries.
Menubar : TEMP
Tabname : Def_temp
|
1 2 3 4 |
set linesize 300 col property_name for a35 col property_value for a20 col description for a45 |
|
1 2 3 4 5 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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
|
1 2 3 4 5 6 7 |
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 |
|
1 2 3 4 5 6 7 8 9 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, 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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 : ¤t_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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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
|
1 2 3 4 5 6 7 |
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 |
|
1 2 3 4 5 6 7 8 9 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
|
1 |
-- alter tablespace temp add tempfile '+DATA01' size 30g use semicolon |
Tabname : Tempfile
|
1 2 3 4 5 6 7 |
set linesize 300 col file_name for a60 select file_name,file_id,user_bytes/1024/1024 from dba_temp_files; FILE_NAME FILE_ID USER_BYTES/1024/1024 ------------------------------------------------------------ ---------- -------------------- +DATA/ORCL1234/TEMPFILE/temp.264.1005927851 1 32766 |
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Follow Me
Linkedin : Venkat Vinod Kumar Siram
Note: Please test scripts in Non Prod before trying in Production.
Loading...
Atul Chandge
Very useful article
Thanks