Steps - Adding Space to Temporary Tablespace

STEPS - Adding Space to Temporary Tablespace


Set Environment
---------------
export ORACLE_SID=
export ORACLE_HOME=
export PATH=$PATH:$ORACLE_HOME/bin:.


To Check Database
-----------------
set pages 200 lines 170
col HOST_NAME for a15
select instance_name,name db_name,host_name,version db_version from gv$instance,v$database;


TEMP TABLESPACE REPORT
----------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   off


COLUMN tablespace_name       FORMAT a18               HEAD 'Tablespace Name'
COLUMN tablespace_status     FORMAT a9                HEAD 'Status'
COLUMN tablespace_size       FORMAT 999,999,999,999   HEAD 'Size'
COLUMN used                  FORMAT 999,999,999,999   HEAD 'Used'
COLUMN used_pct              FORMAT 999               HEAD 'Pct. Used'
COLUMN current_users         FORMAT 9,999             HEAD 'Current Users'


SELECT
    d.tablespace_name                      tablespace_name
  , d.status                               tablespace_status
  , NVL(a.bytes, 0)                        tablespace_size
  , NVL(t.bytes, 0)                        used
  , TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct
  , NVL(s.current_users, 0)                current_users
FROM
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_temp_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
      from v$temp_extent_pool
      group by tablespace_name
    ) t
  , v$sort_segment  s
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.tablespace_name = s.tablespace_name(+)
  AND d.extent_management like 'LOCAL'
  AND d.contents like 'TEMPORARY'
/


To Find the free space in Temp:
-------------------------------
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;


Temp Free Space:
----------------
SELECT tablespace_name, SUM(bytes_used)/1024/1024/1024 "used(GB)", SUM(bytes_free)/1024/1024/1024 "free(GB)" FROM V$temp_space_header GROUP BY tablespace_name;


Tempfile location:
------------------
col tablespace_name for a15
col file_name for a35
select TABLESPACE_NAME,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAX_MB from dba_temp_files where tablespace_name='TABLESPACENAME' order by 1,2;


Filesystem
----------
$ df -h /tempfile/path/
or
$ df -g /tempfile/path/


Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on


set pages 200 lines 160
spool ./resize.log
set echo on feed on time on term on


To resize a datafile:-
----------------------
alter database tempfile '/tempfile/path/tempfilename.dbf' resize 4096m;
To resize a datafile:-(ASM)
----------------------
ALTER DATABASE TEMPFILE '&FILE_NAME' RESIZE 4096M;


spool off


BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE
---------------------------------------------------------------
SQL> Select file_name from dba_temp_files where file_name like '%&tempdatafilename%';


set pages 200 lines 160
spool ./add.log
set echo on feed on time on term on


Adding space in temp tablespace:
--------------------------------
ALTER TABLESPACE <temptablespacename> ADD TEMPFILE '/tempfile/path/tempfilename.dbf' SIZE 4096M;


Adding space in temp tablespace:(ASM)
--------------------------------
ALTER TABLESPACE <temptablespacename> ADD TEMPFILE '+DATA' SIZE 64G;


spool off


Take Table Space Report.

"Hope for the best, prepare for the worst"
-Dharmendra