表領域スクリプト

※コピーすると改行が抜ける場合はソースをダブルクリックして強調表示を外してからコピーしてください

--表領域作成(ディクショナリ管理・詳細パラメータ設定)
CREATE TABLESPACE DATA1
    DATAFILE '<フルパス>' SIZE 1M
    AUTOEXTEND OFF
    EXTENT MANAGEMENT DICTIONARY
    SEGMENT SPACE MANAGEMENT MANUAL
    DEFAULT STORAGE
    (INITIAL 100K
     NEXT 100K
     PCTINCREASE 50
     MAXEXTENTS 10)
;

--表領域作成(自動拡張有効・ローカル管理(サイズ自動割り当て)・自動セグメント管理有効。
--(なお、INITIALやNEXTなどを指定してもエラーにならないがローカル管理の場合は無視される)
CREATE TABLESPACE DATA1
    DATAFILE '<フルパス>' SIZE 1M
    AUTOEXTEND ON
    MAXSIZE 10M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
    --EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M --エクステントサイズを1Mで均一に割り当てる
    SEGMENT SPACE MANAGEMENT AUTO
;

--表領域作成(10.2以降は上と同じ設定で作成される。)
CREATE TABLESPACE TEST3
    DATAFILE '<フルパス>' SIZE 10M
    AUTOEXTEND ON
    MAXSIZE 10M
;

--データファイル追加
ALTER TABLESPACE USERS ADD DATAFILE '<フルパス>' SIZE 50M;

--表領域の拡張/縮小(LOCAL管理の一時表領域を除く)
ALTER DATABASE DATAFILE '<フルパス>' RESIZE 290M;

--データファイルの縮小可能なサイズ
SELECT FILE_ID,BYTES,BLOCKS FROM DBA_FREE_SPACE A
 WHERE  BLOCK_ID = (SELECT MAX(B.BLOCK_ID) FROM DBA_FREE_SPACE B WHERE  B.FILE_ID = A.FILE_ID);

--自動拡張ON
ALTER DATABASE DATAFILE '<フルパス>' AUTOEXTEND ON;

--エクステントの断片化解消(LOCAL管理の場合はコアレスする必要ないが処理自体はできる)
--リサイクルビンの領域は結合できない。
ALTER TABLESPACE USERS COALESCE;

--表領域を読取専用に変更する
ALTER TABLESPACE DATA1 READ ONLY;

--データファイル移動・リネーム(ユーザ表領域等でインスタンス停止が不要な場合)
ALTER TABLESPACE DATA3 OFFLINE;
ALTER TABLESPACE DATA3 RENAME DATAFILE
     '<移動元フルパス>'
  TO '<移動先フルパス>';
ALTER TABLESPACE DATA3 ONLINE;

--データファイルの移動・リネーム(SYSTEMやUNDO表領域等でインスタンス停止が必要な場合)
ALTER DATABASE RENAME FILE
     '<移動元フルパス>'
  TO '<移動先フルパス>';

--リカバリ時にOFFLINEにする(アーカイブログモードのみ)
ALTER DATABASE DATAFILE '<フルパス>' OFFLINE IMMEDIATE;

--表領域の削除
DROP TABLESPACE DATA2 INCLUDING CONTENTS AND DATAFILES;

--移動
ALTER TABLE TAB1 MOVE TABLESPACE USERS;

--表領域のサイズ及び使用状況(通常の表領域は合計確保エクステントサイズ、UNDO、TEMP表領域は実際の使用量)
set line 200
set pagesize 1000
COLUMN "表領域名" format 'a20'
COLUMN "使用率"  format 999.99
COLUMN "ステータス" format 'a10'
COLUMN "コンテンツ" format 'a10'
COLUMN "エクステント管理" format 'a16'
COLUMN "セグメント管理" format 'a14'
SELECT /*+ FIRST_ROWS */
    A.TABLESPACE_NAME "表領域名",
    NVL(B.BYTES/1024, 0) "確保領域", 
    DECODE(A.CONTENTS,'UNDO', NVL(D.BYTES, 0)/1024,
                              NVL(B.BYTES - NVL(C.BYTES, 0), 0)/1024) "使用領域",
    DECODE(A.CONTENTS,'UNDO', NVL(B.BYTES - NVL(D.BYTES, 0), 0)/1024,
                              NVL(C.BYTES, 0)/1024) "空き",
    TRUNC(DECODE(A.CONTENTS,'UNDO', NVL(D.BYTES/B.BYTES * 100, 0),
                                    NVL((B.BYTES-NVL(C.BYTES, 0))/B.BYTES*100,0)),2) "使用率",
    A.STATUS "ステータス",
    A.CONTENTS "コンテンツ",
    A.EXTENT_MANAGEMENT "エクステント管理",
    A.SEGMENT_SPACE_MANAGEMENT "セグメント管理"
FROM
    DBA_TABLESPACES A,
    (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B, 
    (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) C,
    (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_UNDO_EXTENTS 
      WHERE STATUS IN ('ACTIVE','UNEXPIRED') GROUP BY TABLESPACE_NAME) D
WHERE
    A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND
    A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) AND
    A.TABLESPACE_NAME = D.TABLESPACE_NAME(+) AND NOT
    (A.EXTENT_MANAGEMENT = 'LOCAL' AND A.CONTENTS = 'TEMPORARY') 
UNION ALL   --LOCAL管理のTEMP表領域
SELECT
    A.TABLESPACE_NAME,
    NVL(B.BYTES / 1024, 0),
    NVL(E.BYTES, 0)/1024,
    (NVL(B.BYTES ,0)/1024 - NVL(E.BYTES, 0)/1024),
    TRUNC(NVL(E.BYTES / B.BYTES * 100, 0),2),
    A.STATUS,
    A.CONTENTS,
    A.EXTENT_MANAGEMENT, 
    A.SEGMENT_SPACE_MANAGEMENT
FROM
    DBA_TABLESPACES A,
    (SELECT
         TABLESPACE_NAME,
         SUM(BYTES) BYTES,
         COUNT(FILE_ID) COUNT
     FROM
         DBA_TEMP_FILES
     GROUP BY
         TABLESPACE_NAME) B,
    (SELECT
         F.TABLESPACE_NAME,
         SUM((F.USED_BLOCKS*G.BLOCK_SIZE))  BYTES
     FROM
         GV$SORT_SEGMENT F,
         DBA_TABLESPACES G
     WHERE
         F.TABLESPACE_NAME = G.TABLESPACE_NAME
     GROUP BY F.TABLESPACE_NAME) E
WHERE 
  A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND
  A.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND
  A.EXTENT_MANAGEMENT = 'LOCAL'  AND
  A.CONTENTS = 'TEMPORARY'
ORDER BY 1;

--関連ディクショナリ
SELECT * FROM DBA_TABLESPACES;
SELECT * FROM DBA_DATA_FILES;
SELECT * FROM DBA_EXTENTS; 
SELECT * FROM DBA_FREE_SPACE; 
SELECT * FROM V$TABLESPACE;
SELECT * FROM V$DATAFILE;

マニュアル

一覧(10.2)
リファレンス 10g リリース2(10.2)
SQL リファレンス 10g リリース2(10.2)
★ORACLE案件承ります