ごく最近、職場では、Oracleデータベースのサイズの増加を制御することが必要になりました。 これは、100 GB程度の空き容量があまりないためであり、2つの会社（大企業、保険）で拠点が急速に拡大していました。
まず、DB_TABLESPACE_SIZEテーブルを作成します。このテーブルには、毎日のデータが格納されます。
-- Create table
create table DB_TABLESPACE_SIZE
(
DB_TABLESPACE_NAME VARCHAR2(30),
TIME_SNAPSHOT DATE ,
FREE_SPACE NUMBER(20),
MAX_LIMIT NUMBER(20),
CURRENT_SIZE NUMBER(20),
AUTOEXTEND_ON NUMBER(20),
AVAILABLE_SIZE NUMBER(20),
USED_FOR_DATA NUMBER(20),
UNUSED_FOR_DATA NUMBER(20),
FILES_COUNT NUMBER(5),
MIN_UNALLOCATED NUMBER(20),
MAX_UNALLOCATED NUMBER(20),
MIN_AVAILABLE NUMBER(20),
MAX_AVAILABLE NUMBER(20),
MIN_USED NUMBER(20),
MAX_USED NUMBER(20),
MIN_UNUSED NUMBER(20),
MAX_UNUSED NUMBER(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column DB_TABLESPACE_SIZE.FREE_SPACE
is ', (). UNUSED, ― , .' ;
comment on column DB_TABLESPACE_SIZE.MAX_LIMIT
is ' . ( AUTOEXTEND)' ;
comment on column DB_TABLESPACE_SIZE.CURRENT_SIZE
is ' ' ;
comment on column DB_TABLESPACE_SIZE.AVAILABLE_SIZE
is ', UNUSED+autoextend_on' ;
comment on column DB_TABLESPACE_SIZE.USED_FOR_DATA
is ', . ( "high watermark")' ;
comment on column DB_TABLESPACE_SIZE.UNUSED_FOR_DATA
is ', . .. , , UNUSED. ' ;
* This source code was highlighted with Source Code Highlighter .
次に、ビューcurrent_tablespace_sizeを作成します。ここには、表スペースに関する現在のデータと要約されたデータが保管されます。
create or replace view current_tablespace_size
(db_tablespace_name, time_snapshot, max limit on gb, current_size on gb, autoextend_on on gb, available_size on gb, used_for_data on gb, unused_for_data, free_space, files_count, min_unallocated, max_unallocated, min_available, max_available, min_used, max_used, min_unused, max_unused)
as
select DB_TABLESPACE_NAME, TIME_SNAPSHOT,
MAX_LIMIT/1024/1024/1024 ,
CURRENT_SIZE/1024/1024/1024,
AUTOEXTEND_ON/1024/1024/1024,
AVAILABLE_SIZE/1024/1024/1024 "AVAILABLE_SIZE",
USED_FOR_DATA/1024/1024/1024 "USED_FOR_DATA",
UNUSED_FOR_DATA/1024/1024 "UNUSED_FOR_DATA",
FREE_SPACE/1024/1024 "FREE_SPACE", "FILES_COUNT",
MIN_UNALLOCATED/1024/1024 "MIN_UNALLOCATED",
MAX_UNALLOCATED/1024/1024 "MAX_UNALLOCATED",
MIN_AVAILABLE/1024/1024 "MIN_AVAILABLE",
MAX_AVAILABLE/1024/1024 "MAX_AVAILABLE",
MIN_USED/1024/1024 "MIN_USED",
MAX_USED/1024/1024 "MAX_USED",
MIN_UNUSED/1024/1024 "MIN_UNUSED",
MAX_UNUSED/1024/1024 "MAX_UNUSED"
-- sum (CURRENT_SIZE)
from db_TABLESPACE_SIZE
where TIME_SNAPSHOT = ( select MAX (TIME_SNAPSHOT) from db_TABLESPACE_SIZE)
union select 'TOTAL' ,( select max (s.time_snapshot) from db_TABLESPACE_SIZE s ), null ,
( select sum (s.current_size/1024/1024/1024) from db_TABLESPACE_SIZE s where s.time_snapshot=( select max (s.time_snapshot) from db_TABLESPACE_SIZE s )), null , null , null , null , null , null , null , null , null , null , null , null , null , null from dual;
* This source code was highlighted with Source Code Highlighter .
次に、db_tablespace_size_by_dateテーブルを作成して、毎日のデータベースサイズの変更を保存します
-- Create table
create table DB_TABLESPACE_SIZE_BY_DATE
(
DB_TABLESPACE_NAME VARCHAR2(30),
TIME_SNAPSHOT DATE ,
DEFF_SIZE NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
* This source code was highlighted with Source Code Highlighter .
さて、最後のステップは、毎日情報を収集してテーブルに記入するジョブを書くことです。
begin
sys.dbms_job.submit(job => :job,
what => 'insert into db_TABLESPACE_SIZE( "DB_TABLESPACE_NAME", "TIME_SNAPSHOT", "MAX_LIMIT", "CURRENT_SIZE",
"AUTOEXTEND_ON", "AVAILABLE_SIZE", "USED_FOR_DATA", "UNUSED_FOR_DATA", "FREE_SPACE", "FILES_COUNT","MIN_UNALLOCATED", "MAX_UNALLOCATED",
"MIN_AVAILABLE", "MAX_AVAILABLE","MIN_USED", "MAX_USED", "MIN_UNUSED", "MAX_UNUSED")
select F."TABLESPACE_NAME",
F."TIME",
F."LIMIT",
F."SIZE",
F."UNALLOCATED",
F."AVAILABLE",
F."USED",
F."UNUSED",
nvl(S.TOTAL_BYTES, 0) "FREE_SPACE",
F.FILES,
F.MIN_UNALLOCATED,
F.MAX_UNALLOCATED,
F.MIN_AVAILABLE,
F.MAX_AVAILABLE,
F.MIN_USED,
F.MAX_USED,
F.MIN_UNUSED,
F.MAX_UNUSED
from
( select tablespace_name,
sysdate "TIME",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END ) "LIMIT",
SUM( BYTES ) "SIZE",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES - BYTES ELSE 0 END ) "UNALLOCATED",
SUM( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES ) "AVAILABLE",
SUM( USER_BYTES ) "USED",
SUM( BYTES - USER_BYTES ) "UNUSED",
COUNT( FILE_NAME ) "FILES",
MIN( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES - BYTES ELSE null END ) "MIN_UNALLOCATED",
MAX( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - BYTES ) "MAX_UNALLOCATED",
MIN( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES) "MIN_AVAILABLE",
MAX( CASE WHEN AUTOEXTENSIBLE=' 'YES' ' THEN MAXBYTES ELSE BYTES END - USER_BYTES) "MAX_AVAILABLE",
MIN( USER_BYTES ) "MIN_USED",
MAX( USER_BYTES ) "MAX_USED",
MIN( BYTES - USER_BYTES ) "MIN_UNUSED",
MAX( BYTES - USER_BYTES ) "MAX_UNUSED"
from dba_data_files
group by tablespace_name
) F left join dba_free_space_coalesced S on (F.TABLESPACE_NAME = S.TABLESPACE_NAME);
insert into db_tablespace_size_by_date ("DB_TABLESPACE_NAME","TIME_SNAPSHOT","DEFF_SIZE")
SELECT nvl(t1.db_tablespace_name, ' 'TOTAL' '),
MAX(t1.time_snapshot),
(SUM(t1.current_size / 1024 / 1024 / 1024) -
SUM(t1.free_space / 1024 / 1024 / 1024)) -
(SUM(t3.current_size / 1024 / 1024 / 1024) -
SUM(t3.free_space / 1024 / 1024 / 1024))
FROM db_tablespace_size t1, db_tablespace_size t3
WHERE t1.time_snapshot =
(SELECT MAX(t2.time_snapshot)
FROM db_tablespace_size t2
WHERE trunc(t2.time_snapshot) = trunc(SYSDATE))
AND t3.time_snapshot =
(SELECT MIN(t2.time_snapshot)
FROM db_tablespace_size t2
WHERE trunc(t2.time_snapshot) = trunc(SYSDATE-1))
AND t1.db_tablespace_name = t3.db_tablespace_name
GROUP BY CUBE(t1.db_tablespace_name);
commit;' ,
next_date => to_date( '15-02-2012 05:00:00' , 'dd-mm-yyyy hh24:mi:ss' ),
interval => 'trunc(SYSDATE,' 'hh' ')+1' );
commit ;
end ;
/
* This source code was highlighted with Source Code Highlighter .
コードについて少し：db_TABLESPACE_SIZEテーブルはdba_data_filesとdba_free_space_coalescedから生成されます。 最初は表スペースファイルのサイズに関するデータを保存し、2番目は表スペースファイル内で解放されたサイズを保存します。
サイズとfree_sizeの違いはdb_tablespace_size_by_dateテーブルにあります。つまり、実際には、実際の占有スペースが取得されます。
以上で、データベースのサイズと変更に関する情報が毎日収集されるようになりました。