一時表領域の使用率を調べる
セッション毎
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 (+);