SQL集ーDB調査編

表領域の容量確認

---

col 表領域_最大        for 999,990
col 表領域_使用済み    for 999,990
col 表領域_空領域      for 999,990
col 表領域_使用率         for 990.9

col 表領域 for A24

set line 120
WITH
  total_free_space AS (
    SELECT
        tablespace_name
      , SUM(bytes) AS bytes
    FROM
      dba_free_space
    GROUP BY
      tablespace_name
    ORDER BY
      tablespace_name
  )
SELECT
    usage.tablespace_name                    AS "表領域名"
  , usage.tablespace_size * tblsp.block_size/1024/1024 AS "表領域_最大"
  , usage.used_space * tblsp.block_size/1024/1024      AS "表領域_使用済み"
  , free.bytes/1024/1024                               AS "表領域_空領域"
  , usage.used_percent                       AS "表領域_使用率"
FROM
  dba_tablespace_usage_metrics usage
    INNER JOIN dba_tablespaces tblsp
            ON usage.tablespace_name = tblsp.tablespace_name
    INNER JOIN total_free_space free
            ON usage.tablespace_name = free.tablespace_name
ORDER BY
   表領域名
 ;
  

データファイルの確認

---

col 表領域名       for A30
col データファイル名       for A64
col データファイルMbytes        for 999,990

SELECT
    dbf.tablespace_name AS "表領域名"
  , dbf.file_name AS "データファイル名"
  , dbf.autoextensible AS "データファイル自動拡張"
  , dbf.bytes/1024/1024 AS "データファイルMbytes"
FROM
  dba_data_files dbf
ORDER BY
   表領域名
 , データファイル名;
 

テーブルごとの容量確認

---

SELECT
  TABLE_NAME,
  NUM_ROWS,
  AVG_ROW_LEN,
  BLOCKS,
  EMPTY_BLOCKS,
  CASE AVG_ROW_LEN
    WHEN null THEN null
    WHEN 0 THEN 0
    ELSE ( CEIL( NUM_ROWS / TRUNC( ( ( BLOCK_SIZE - KCBH - UB4 - KTBBH
      - ( INI_TRANS - 1 ) * KTBIT - KDBH )
      * ( 100 - PCT_FREE ) / 100 - KDBT ) / AVG_ROW_LEN ) ) + 1 )
  END EST_BLOCKS
  FROM DBA_TABLES
    ,(SELECT TYPE_SIZE KCBH FROM V$TYPE_SIZE WHERE TYPE = 'KCBH')
    ,(SELECT TYPE_SIZE UB4 FROM V$TYPE_SIZE WHERE TYPE = 'UB4')
    ,(SELECT TYPE_SIZE KTBBH FROM V$TYPE_SIZE WHERE TYPE = 'KTBBH')
    ,(SELECT TYPE_SIZE KTBIT FROM V$TYPE_SIZE WHERE TYPE = 'KTBIT')
    ,(SELECT TYPE_SIZE KDBH FROM V$TYPE_SIZE WHERE TYPE = 'KDBH')
    ,(SELECT TYPE_SIZE KDBT FROM V$TYPE_SIZE WHERE TYPE = 'KDBT')
    ,(SELECT VALUE BLOCK_SIZE FROM V$PARAMETER WHERE NAME = 'db_block_size')
  WHERE OWNER = '<OWNER>'
  AND TEMPORARY='N'
  ORDER BY BLOCKS DESC;

その２

SELECT
    Dtbl.owner
   ,Dtbl.table_name
   ,TO_NUMBER(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT COUNT(*) c FROM '||table_name)),'/ROWSET/ROW/C')) as rec_ccount
   ,trunc(bytes/(1024*1024),0) as tblsizeMB
FROM DBA_tables Dtbl
    ,DBA_segments Dseg
where table_name=<TABLENAME>
--and   owner=<OWNER>
and   Dseg.segment_name = Dtbl.table_name
;

実データと空き（HWM）の確認

---

/*

未使用領域が大きい=HWMが伸びているので、SHRINKするとよい

※HWMが下がることによってTABLE FULL SCAN時に無駄な読み込みが減る。

ALTER TABLE fragment_table SHRINK SPACE [ COMPACT ] [ CASCADE ] ;

*/

set lines 120
set veri off
set echo off
set feed off
set serveroutput on
exec dbms_output.enable(1000000);
spool 表HWMサイズ.lis

declare
i_OWNER varchar2(15);
p_OWNER varchar2(15);
i_TABLE_NAME varchar2(20);
TotalBlck number;
TotalByte number;
UnUsedBlck number;
UnUsedByte number;
LastUsedExtFileID number;
LastUsedExtBlckID number;
LastUsedBlk number;
v_PARTNAME varchar2(25);

LenOwner constant number :=15;
LenSegment_name constant number :=27;
LenPartition_name constant number :=22;
LenSegment_type constant number :=16;
LenByte constant number :=12;

begin
i_OWNER := upper('&&OWNER');
i_TABLE_NAME := upper('&&TABLE_NAME');

if i_OWNER is null or i_OWNER = '%' then
p_OWNER := '全ユーザ';
i_OWNER := '%';
end if;
if i_TABLE_NAME is null or i_TABLE_NAME = '%' then
i_TABLE_NAME := '%';
end if;

dbms_output.put_line(rpad('セグメント' ,LenOwner)||
rpad('テーブル' ,LenSegment_name)||
rpad('パーティション',LenPartition_name)||
rpad('セグメント' ,LenSegment_type)||
lpad('全体' ,LenByte)||
lpad('未使用' ,LenByte)||
lpad('使用済' ,LenByte)
);
dbms_output.put_line(rpad('所有者' ,LenOwner)||
rpad('名',LenSegment_name)||
rpad('名',LenPartition_name)||
rpad('タイプ',LenSegment_type)||
lpad('サイズ(KB)',LenByte)||
lpad('サイズ(KB)',LenByte)||
lpad('サイズ(KB)',LenByte)
);
dbms_output.put_line(rpad('-',LenOwner,'-')||
rpad('-',LenSegment_name,'-')||
rpad('-',LenPartition_name,'-')||
rpad('-',LenSegment_type,'-')||
lpad('-',LenByte,'-')||
lpad('-',LenByte,'-')||
lpad('-',LenByte,'-')
);

for Cur1 in (
select
owner,
segment_name,
partition_name,
segment_type
from sys.dba_segments
where  owner not in
('SYS','SYSTEM','CTXSYS','MDSYS','ORDSYS','AURORA$JIS$UTILITY$','OUTLN','OSE$HTTP$ADMIN','XDB','WMSYS','AUDSYS')
and segment_type in ('TABLE','TABLE PARTITION')
order by Owner,Bytes)
loop
dbms_space.unused_space(cur1.owner,cur1.segment_name,cur1.segment_type,
TotalBlck,TotalByte,UnUsedBlck,UnUsedByte,LastUsedExtFileID,LastUsedExtBlckID,
LastUsedBlk,cur1.partition_name);
if cur1.partition_name is null then
v_partname := rpad(' ',LenPartition_name,' ');
else
v_partname := rpad(cur1.partition_name,LenPartition_name,' ');
end if;

dbms_output.put_line(rpad(cur1.owner,LenOwner)||
rpad(cur1.segment_name,LenSegment_name)||
v_partname||
rpad(cur1.segment_type,LenSegment_type)||
lpad(TotalByte/1024,LenByte)||
lpad(UnUsedByte/1024,LenByte)||
lpad((TotalByte-UnUsedByte)/1024,LenByte)
);
end loop;
end;
/

 

スキーマの権限・ロールを確認する

---

select * from DBA_TAB_PRIVS where GRANTEE='&&GRANTEE_USER' ORDER BY OWNER,TABLE_NAME ;
select * from DBA_SYS_PRIVS where GRANTEE='&&GRANTEE_USER';
select * from DBA_ROLE_PRIVS where GRANTEE='&&GRANTEE_USER';
 

---

スキーマのオブジェクトのDDLリスト

INVALIDオブジェクトをリコンパイルするSQLを生成

---

SELECT 'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
FROM all_objects
WHERE status = 'INVALID';