LoginSignup
3

More than 3 years have passed since last update.

Oracleネタ帳 表領域についていろいろ

Last updated at Posted at 2019-01-24

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

セッション毎

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 (+);

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3