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

Источник: habrahabr

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

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


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=28227