Useful shortcuts for vi editor

Showing posts with label temporary tablespace. Show all posts
Showing posts with label temporary tablespace. Show all posts

Tuesday 22 August 2017

How to monitor TEMP tablespace resource usage?

-> Check Total TEMP usage:
SQL>
SELECT B.TOTAL_MB,
       B.TOTAL_MB - ROUND (A.USED_BLOCKS * 8 / 1024) CURRENT_FREE_MB,
       ROUND (USED_BLOCKS * 8 / 1024) CURRENT_USED_MB,
       ROUND (MAX_USED_BLOCKS * 8 / 1024) MAX_USED_MB
  FROM V$SORT_SEGMENT A,
       (SELECT ROUND (SUM (BYTES) / 1024 / 1024) TOTAL_MB FROM DBA_TEMP_FILES) B;

-> Check Total TEMP usage according to database blocks:
SQL>
SELECT DISTINCT S.SID,
                  S.USERNAME,
                  U.TABLESPACE,
                  S.SQL_HASH_VALUE || '/' || U.SQLHASH HASH_VALUE,
                  U.SEGTYPE,
                  U.CONTENTS,
                  U.BLOCKS
    FROM V$SESSION S, V$TEMPSEG_USAGE U
   WHERE S.SADDR = U.SESSION_ADDR
ORDER BY U.BLOCKS DESC;

-> Find problematic SQL for TEMP
SQL>
SELECT 
      S.SID
     ,S.CLIENT_INFO
     ,S.MACHINE
     ,S.PROGRAM
     ,S.TYPE
     ,S.LOGON_TIME
     ,S.OSUSER
     ,SQ.SORTS
     ,SQ.DISK_READS
     ,SQ.BUFFER_GETS
     ,SQ.ROWS_PROCESSED
     ,SQ.SQLTYPE
     ,SQ.SQL_TEXT
 FROM GV$SESSION S    
    , GV$SQL SQ
WHERE S.SQL_HASH_VALUE = SQ.HASH_VALUE
  --AND s.inst_id= 1
  AND S.SID = 555 
  AND SQ.INST_ID= S.INST_ID;

Sunday 19 October 2014

How to define and drop temporary tablespace?

Please execute the commands as "sys";

SQL> CREATE TEMPORARY TABLESPACE ALPER_TS_TEMP TEMPFILE 
 'C:\tablespaces\data_01.dbf' 
 SIZE 8000M 
 AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

To alter the tablespace:
SQL> ALTER TABLESPACE ALPER_TS_TEMP ADD TEMPFILE 'C:\tablespaces\data_temp_new_02.dbf' size 128m;

To drop the tablespace:
SQL> DROP TABLESPACE ALPER_TS_TEMP INCLUDING CONTENTS AND DATAFILES;

Used Oracle Version and OS: Oracle XE and Win7

Ref: http://onlineappsdba.com/index.php/2008/02/08/tablespace-and-datafiles-in-oracle-database-back-to-basics/