表領域スクリプト
※コピーすると改行が抜ける場合はソースをダブルクリックして強調表示を外してからコピーしてください
--表領域作成(ディクショナリ管理・詳細パラメータ設定)
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 --エクステントサイズを１Mで均一に割り当てる
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）
折りたたみ
展開