Контроль роста размера базы данных Oracle

Совсем недавно на работе стало необходимым контролировать рост размера базы данных 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, то есть, по сути, получаем фактическое занятое место.

Вот и все, теперь каждый день собирается информация о размеры базы данных и об изменениях.