一時表領域の使用率を調べる

セッション毎

SELECT
    ss.sid
    ,ss.serial#
    ,ss.username
    ,tu.tablespace
    ,SUM(tu.blocks) * dts.block_size / 1024 / 1024 used_mb
    ,sq.sql_text
FROM
    v$tempseg_usage tu
    ,v$session ss
    ,dba_tablespaces dts
    ,v$sql sq
WHERE
    tu.session_addr = ss.saddr
AND ss.sql_id = sq.sql_id
AND tu.tablespace = dts.tablespace_name
GROUP BY
    ss.sid
    ,ss.serial#
    ,ss.username
    ,dts.block_size
    ,tu.tablespace
    ,sq.sql_text
ORDER BY
    ss.sid;

全体

SET LINESIZE 170;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
COLUMN MAX_BYTES  FORMAT 999,999,999,999;
COLUMN FREE_BYTES FORMAT 999,999,999,999;
COLUMN USED_BYTES FORMAT 999,999,999,999;
SELECT SYSDATE
     , T.TABLESPACE_NAME
     , T.MAX_BYTES
     , TF.FREE_BYTES
     , T.MAX_BYTES - TF.FREE_BYTES AS USED_BYTES
  FROM (
    SELECT TABLESPACE_NAME AS TABLESPACE_NAME
         , SUM(BYTES)      AS MAX_BYTES
      FROM DBA_TEMP_FILES
     GROUP BY TABLESPACE_NAME
  ) T ,
  (
    SELECT TABLESPACE_NAME AS TABLESPACE_NAME
         , SUM(BYTES_FREE) AS FREE_BYTES
      FROM V$TEMP_SPACE_HEADER TF
     GROUP BY TABLESPACE_NAME
  ) TF
 WHERE T.TABLESPACE_NAME = TF.TABLESPACE_NAME(+);

一時表領域を表示する SQL

SELECT
    DT.TABLESPACE_NAME "表領域",
    ROUND(DT.BYTES / (1024 * 1024), 0) "割当済(MB)",
    ROUND(T.BYTES_CACHED / (1024 * 1024), 0) "空容量(MB)",
    ROUND(T.BYTES_CACHED / DT.BYTES * 100, 2) "使用率(%)"
FROM
    SYS.DBA_TEMP_FILES DT,
    V$TEMP_EXTENT_POOL T,
    V$TEMPFILE V
WHERE
    T.FILE_ID(+)= DT.FILE_ID AND
    DT.FILE_ID = V.FILE#
/

表領域が SMALLFILE、BIGFILE のどちらかを確認する

select tablespace_name, status, bigfile from dba_tablespaces;

表領域とデータファイルの確認

データファイル一覧

set lines 500
set pages 100
set term off
--clear col
col TABLESPACE_NAME  format a10
col FILE_NAME        format a45
col STATUS           format a10
col MBYTES           format 9,999,990
col INCRE            format 9,999,990
col AUTOEXTENSIBLE   format a5
col MAX_MBYTES       format 999,999,999,990
col ONLINE_STATUS    format a6
select
  tablespace_name,
  file_name,
  status,
  bytes/1024/1024 mbytes,
  increment_by,
  autoextensible,
  ROUND(MAXBYTES/1024/1024,0) MAX_MBYTES,
  online_status
from
  dba_data_files
order by 1, 2
/

一時表領域も含める

col TABLESPACE_NAME format a30
col FILE_NAME format a100
col AUTOEXTENSIBLE format a10
select TABLESPACE_NAME, FILE_NAME, 'NO' "TEMP?", AUTOEXTENSIBLE from dba_data_files
union all
select TABLESPACE_NAME, FILE_NAME, 'YES' "TEMP?", AUTOEXTENSIBLE from dba_temp_files
order by 1, 2;

表領域の空き確認

SELECT A.NM "NAME",
           ROUND(A.TS/1024/1024, 0) "FILE_MB",
           ROUND((A.TS-NVL(B.FS, 0))/1024/1024, 0) "USED_MB",
           ROUND(B.FS/1024/1024, 0) "FREE_MB",
           DECODE(A.MS, 0, 0, ROUND(A.MS/1024/1024, 0)) "MAX_MB",
           ROUND((A.TS-NVL(B.FS, 0))/A.TS*100, 1) "TS_PCT",
           DECODE(A.MS, 0, 0, ROUND(A.TS/A.MS*100, 1)) "EXT_PCT"
      FROM (SELECT TABLESPACE_NAME NM,
                   SUM(BYTES) TS,
                   SUM(MAXBYTES) MS
              FROM DBA_DATA_FILES
             GROUP BY TABLESPACE_NAME) A,
           (SELECT TABLESPACE_NAME NM,
                   SUM(BYTES) FS
              FROM DBA_FREE_SPACE
             GROUP BY TABLESPACE_NAME) B
     WHERE A.NM=B.NM(+)
     ORDER BY NAME;

表領域を作成する

CREATE TABLESPACE <表領域名>
 DATAFILE '<データファイル名（フルパス指定可）.dbf>' SIZE 100M
 AUTOEXTEND ON NEXT 500K MAXSIZE 1024M;

表領域を縮小する

ALTER DATABASE DATAFILE <データファイル名> RESIZE <変更後のファイルサイズ>;

表領域の縮小時に「ORA-03297」が発生した場合

ORA-03297: ファイルには、リクエストしたRESIZE値を超える使用中のデータが含まれています。

表領域の断片化をチェックしてテーブル・インデックスの再構築をする

-- テーブルの場合
alter table &TNAME move;
-- インデックスの場合
alter index &IDX_NAME rebuild online;

表領域内の断片化を確認

set linesize 150 pages 5000
col SEGMENT_NAME for a24
select distinct T1.TABLESPACE_NAME, T1.FILE_ID,
       T1.SEGMENT_TYPE, T1.OWNER||'.'||T1.SEGMENT_NAME "SEGMENT_NAME", T1.SEGMENT_TYPE
  from DBA_EXTENTS T1
 where ((T1.BLOCK_ID + 1) 
        * (select BLOCK_SIZE 
             from DBA_TABLESPACES T2
            where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
   and TABLESPACE_NAME = '&TBLSP_NAME'
 order by 1, 2;

ここういうのもあります。(参考)

COMP SUM OF nfrags totsiz avasiz ON REPORT
BREAK ON REPORT

SET PAGES 100
COL tsname FORMAT         a16 JUSTIFY c HEADING 'Tablespace'
COL nfrags FORMAT 999,999,990 JUSTIFY c HEADING 'Free|Frags'
COL mxfrag FORMAT 999,999,999 JUSTIFY c HEADING 'Largest|Frag (MB)'
COL totsiz FORMAT 999,999,999 JUSTIFY c HEADING 'Total|(MB)'
COL avasiz FORMAT 999,999,999 JUSTIFY c HEADING 'Available|(MB)'
COL pctusd FORMAT         990 JUSTIFY c HEADING 'Pct|Used'

SELECT
  TOTAL.TABLESPACE_NAME    tsname,
  D                        nfrags,
  C / 1024 / 1024          mxfrag,
  A / 1024 / 1024          totsiz,
  B / 1024 / 1024          avasiz,
  (1 - NVL(B,0) / A) * 100 pctusd
FROM
  (SELECT
     SUM(bytes) A,
     tablespace_name
     FROM dba_data_files
     GROUP BY tablespace_name) TOTAL,
  (SELECT SUM(bytes) B,
     MAX(bytes) C,
     COUNT(bytes) D,
     tablespace_name
     FROM dba_free_space
     GROUP BY tablespace_name) FREE
WHERE
  TOTAL.TABLESPACE_NAME = FREE.TABLESPACE_NAME (+);