-> 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;
Showing posts with label temporary tablespace. Show all posts
Showing posts with label temporary tablespace. Show all posts
Tuesday, 22 August 2017
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/
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/
Labels:
oracle,
temporary tablespace
Subscribe to:
Posts (Atom)