Tempfiles

November 12th, 2012

Temp tablespaces datafiles

SET LINESIZE 147
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN tablespace      FORMAT a29                HEADING 'Tablespace Name'
COLUMN filename        FORMAT a64                HEADING 'Filename'
COLUMN filesize        FORMAT 9,999,999,999,999  HEADING 'File Size'
COLUMN autoextensible  FORMAT a4                 HEADING 'Auto'
COLUMN increment_by    FORMAT 999,999,999        HEADING 'Next'
COLUMN maxbytes        FORMAT 99,999,999,999     HEADING 'Max'

BREAK ON report
COMPUTE SUM OF filesize  ON report

SELECT
    d.tablespace_name                     tablespace 
  , d.file_name                           filename
  , d.bytes                               filesize
  , d.autoextensible                      autoextensible
  , d.increment_by * e.value              increment_by
  , d.maxbytes                            maxbytes
FROM
    sys.dba_temp_files d
  , (SELECT value
     FROM v$parameter 
     WHERE name = 'db_block_size') e
ORDER BY 1,2
/


Temp tablespace usage

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'

BREAK ON report
COMPUTE SUM OF tablespace_size  ON report
COMPUTE SUM OF used             ON report
COMPUTE SUM OF current_users    ON report

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'
/


Add new datafile to temp tablespace

alter tablespace TEMP add tempfile 'new_temp_file' size 1G autoextend on next 128M maxsize 4G;


Comments are closed.