Temporary Tablespace

 Contents of this Post
 

• Manage Temporary Tablespace
• Temporary Tablespace Group
• Monitor Temporary Tablespace
• Tune Temporary Tablespace

 

Manage Temporary Tablespace

Query Tablespace Details:-

set lines 200 pages 200

column TABLESPACE_NAME for a13
col EXTENT_MANAGEMENT for a6
col STATUS format a7
select * from dba_tablespaces where CONTENTS='TEMPORARY';

Temporary Tablespace Size:-

select sum(BYTES)/1024/1024/1024 "TEMP Size In GB" from dba_temp_files;

select tablespace_name, sum(BYTES)/1024/1024/1024 "TEMP Size In GB" from dba_temp_files group by tablespace_name;

 

Temporary Files:-

set lines 200 pages 200
col STATUS for a10
col FILE_NAME for a40
col TABLESPACE_NAME for a16
col USER_MB for 9,99,999
col MAX_MB for 9,99,999
col INCREMENT_Mb for 9,99,999
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 Size_GB, STATUS, USER_BYTES/1024/1024 USER_MB,
AUTOEXTENSIBLE,MAXBYTES/1024/1024 MAX_MB,(INCREMENT_BY*8192)/1024/1024 INCREMENT_Mb,
(MAXBYTES-BYTES)/1024/1024 "Increase Upto MB",STATUS
from dba_temp_files
order by TABLESPACE_NAME,FILE_ID;

 

Temp Tablespace Assigned To Users:-

select username,temporary_tablespace from dba_users order by temporary_tablespace;

Database Default TEMPORARY TABLESPACE:-

column PROPERTY_VALUE for a20

column DESCRIPTION for a40
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

 

Create/Alter Temporary tablespace & Temp Files:-

create temporary tablespace TEMP1 tempfile '/u02/oradata/DBNAME/temp11.dbf' size 3072m;

ALTER DATABASE TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ' RESIZE 4096M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/temp1.dbf ' SIZE 2048M AUTOEXTEND OFF;

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

ALTER DATABASE TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ' DROP INCLUDING DATAFILES;

ALTER TABLESPACE temp TEMPFILE OFFLINE;

ALTER TABLESPACE temp TEMPFILE ONLINE;

ALTER DATABASE TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ' ONLINE;

ALTER DATABASE TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ' OFFLINE;

Rename Temporary Files:-

ALTER DATABASE TEMPFILE '/u01/temp01.dbf' OFFLINE;

!cp -p /u01/temp01.dbf /u02/temp01.dbf

OR
!mv /u01/temp01.dbf /u02/temp01.dbf

ALTER DATABASE RENAME FILE '/u01/temp01.dbf' to '/u02/temp01.dbf';

ALTER DATABASE TEMPFILE '/u02/temp01.dbf' online;


Shrink Locally Managed Temporary Tablespace:-

ALTER TABLESPACE temp SHRINK SPACE;

ALTER TABLESPACE temp SHRINK SPACE KEEP 20M;
ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ';

TEMPORARY TABLESPACE GROUP (10g or latter):-

Temporary tablespace group has the following benefits:

It allows multiple default temporary tablespaces to be specified at the database level.

It allows the user to use multiple temporary tablespaces in different sessions at the same time.

It allows a single SQL operation to use multiple temporary tablespaces for sorting.

select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

Size of Temporary Tablespace Group:-

select sum(bytes)
from dba_temp_files
where tablespace_name in (select tablespace_name from dba_tablespace_groups where group_name = 'GROUP1');

 

Create/Add To temporary tablespace group:-

CREATE TEMPORARY TABLESPACE temp01

TEMPFILE ‘/u02/oradata/temp01.dbs’ SIZE 500M
TABLESPACE GROUP tempgroup_a;
 

Add Existing Temporary tablespace to Temporary tablespace group:-

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

Remove Temporary tablespace from Temporary tablespace group:-

ALTER TABLESPACE temp04 TABLESPACE GROUP '';

 

Assign Default Temporary Tablespace GROUP to Database:-

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempgroup_A;

Assign User to a default temporary tablespace group:-

In this case, user Scott will have multiple default temporary tablespaces.
A single SQL operation by Scott can use more than one temporary tablespace for sorting.

ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;

 

Monitor Temporary Tablespace

/*TO SEE THE BLOCKS */ (Who Are the Users)

without user list>>

column tablespace format a10 heading 'Tablespace|Name'

column segfile# format 9,999 heading 'File|ID'
column segblk# format 999,999,999 heading 'Block|ID'
column blocks format 999,999,999 heading 'Blocks'
break on tablespace on segfile#
select b.tablespace,b.segfile#,b.segblk#,b.blocks
from v$sort_usage b
order by b.tablespace,b.segfile#,b.segblk#;


With user list>>

column username format a10
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;


/*TO SEE THE TOTAL FREESPACE AND USED SPACE IN TEMPORARY TABLESPACE */

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;


OR From v$temp_space_header

select tablespace_name, SUM(bytes_used)/(1024*1024) USED_MB, sum(bytes_free)/(1024*1024) FREE_MB

from v$temp_space_header
where tablespace_name = 'TEMP'
group by tablespace_name;


/*It will say how much extents is taking space*/

select u.tablespace, u.contents, u.segtype, u.extents,u.blocks,
round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s,v$sort_usage u,SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
and UPPER(P.NAME)='DB_BLOCK_SIZE'
order by MB DESC;


/*To see the users taking the temporary tablespace/*

col "User,SID,SERIAL" for a18
col "Tablsp Name, ID" for a15
col SEGTYPE for a7
col MODULE for a30
col TERMINAL for a10
col PROGRAM for a18
col ACTION for a10
col OSUSER for a12
col MACHINE for a17
select u."USER"||','||s.sid||','||s.SERIAL# "User,SID,SERIAL"
,u.tablespace||', '||u.SEGRFNO# "Tablsp Name, ID", u.contents, u.segtype
,round(u.blocks*8192/1024/1024,2) Temp_MB
,s.MODULE,s.TERMINAL
--,s.PROGRAM
,s.OSUSER,s.MACHINE
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY'
order by Temp_MB DESC;


/* How many extents are allocated and actually used per datafile */

select tablespace_name,

       file_id,
       extents_cached extents_allocated,
       extents_used,
       bytes_cached/1024/1024 mb_allocated,
       bytes_used/1024/1024 mb_used
from v$temp_extent_pool;

 

/* Identify temp segment usages per session */

col TABLESPACE for a10

col SID_SERIAL for a12
col USERNAME for a10
col OSUSER for a10
col MODULE for a35
col PROGRAM for a35
SELECT  S.sid ||','|| S.serial# sid_serial
                ,S.username,S.osuser,P.spid,S.module,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, P.spid, S.module, P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

 

/* Identify temp segment usages per SQL statement */

col TABLESPACE for a10
col SID_SERIAL for a12
col USERNAME for a10
SELECT  S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
                T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;


/* Who's using temp space, what queries are they executing and how much of that space is each one consuming? */

In 10g and later releases

select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;
 
For 9iR2 and 9iR1

select u.username, s.sql_text, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.address = u.sqladdr
and s.hash_value = u.sqlhash;
 
For 8.1.7, 8.1.6 and 8.1.5
 
select u.username, s.sql_text, u.extents, u.blocks
from v$sort_usage u, v$sql s
where s.address = u.sqladdr
and s.hash_value = u.sqlhash;
 

How can you estimate the amouant of temp space a query may use?

V$SQL_PLAN can provide that information if you know the SQL_ID of the query:
select temp_space from v$sql_plan where sql_id = '< some sql_id value >';


Tune Temporary Tablespace

 

• Note If below Error coming repeatedly to user after increasing the TEMP tablespace then

          ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
          Increase the pga_aggregate_target size to to keep more temp info
          ALTER SYSTEM SET pga_aggregate_target='800M' SCOPE=BOTH;

• Generate query explain plan and see what the estimated size of the sort operation is, this will help you to sizing your TEMP Tablespace.

           V$SQL_PLAN can provide that information if you know the SQL_ID of the query:
          select temp_space from v$sql_plan where sql_id = '< some sql_id value >';

• Gather DATABASE and SCHEMA statistics (DBMS_STATS) is very help full to reduce the occupying of the TEMP space

 

Thanks......