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

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

Oracle представила новое поколение Oracle Fusion Applications.

Представлена интеграционная платформа Oracle Application Integration Architecture Release 3.1.

Приложения SAP сертифицированы для машины баз данных Oracle Exadata Database Machine.

Oracle анонсирует SPARC SuperCluster T4-4 - самый быстрый в мире вычислительный комплекс общего назначения.

Главная » Oracle