Совсем недавно на работе стало необходимым контролировать рост размера базы данных Oracle. Связано это было с тем, что места осталось не так много, что -то около 100 Гб, а в двух компаниях (компании крупные, страховые) базы стремительно растут.
Для начала мы создадим таблицу 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.
Ну и последним действием напишем job для сбора информации каждый день и заполнения наших таблиц.
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. В первой хранятся данные о размерах файлов табличных пространств, во второй размер освобожденный внутри файлов табличного пространства.
В таблицу db_tablespace_size_by_date ложиться разница между size и free_size, то есть, по сути, получаем фактическое занятое место.
Вот и все, теперь каждый день собирается информация о размеры базы данных и об изменениях.