1. mako24

    Posted

    mako24
Changes in title
+Oracleネタ帳 表領域についていろいろ
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,190 @@
+# 一時表領域の使用率を調べる
+セッション毎
+
+``` sql
+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;
+```
+
+全体
+
+``` sql
+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
+
+``` 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 のどちらかを確認する
+
+``` sql
+select tablespace_name, status, bigfile from dba_tablespaces;
+```
+
+# 表領域とデータファイルの確認
+データファイル一覧
+
+``` sql
+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
+/
+```
+
+一時表領域も含める
+
+``` sql
+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;
+```
+
+# 表領域の空き確認
+
+``` sql
+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;
+```
+
+# 表領域を作成する
+
+``` sql
+CREATE TABLESPACE <表領域名>
+ DATAFILE '<データファイル名(フルパス指定可).dbf>' SIZE 100M
+ AUTOEXTEND ON NEXT 500K MAXSIZE 1024M;
+```
+
+# 表領域を縮小する
+
+``` sql
+ALTER DATABASE DATAFILE <データファイル名> RESIZE <変更後のファイルサイズ>;
+```
+
+表領域の縮小時に「ORA-03297」が発生した場合
+>ORA-03297: ファイルには、リクエストしたRESIZE値を超える使用中のデータが含まれています。
+表領域の断片化をチェックしてテーブル・インデックスの再構築をする
+
+``` sql
+-- テーブルの場合
+alter table &TNAME move;
+-- インデックスの場合
+alter index &IDX_NAME rebuild online;
+```
+
+# 表領域内の断片化を確認
+
+``` sql
+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;
+```
+