(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Контроль роста размера базы данных 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 »
Написать редактору 
 Рекомендовать » Дата публикации: 27.02.2012 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Standard Edition 2 Processor License
Oracle Database Personal Edition Named User Plus License
Oracle Database Standard Edition 2 Named User Plus License
Oracle Database Personal Edition Named User Plus Software Update License & Support
Oracle Data Access Components (ODAC) Standard single license
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
СУБД Oracle "с нуля"
Мир OLAP и Business Intelligence: новости, статьи, обзоры
Утиль - лучший бесплатный софт для Windows
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Access, Ключевое поле, Счетчик (10)
Подскажите пожалуйста, как изменить в Access в ключевом поле (счетчик последовательно),...
 
Решение задач на оптимизацию с помощью MS Excel (4)
Zdravstvuyte, ya pro4itala danniy material, no ne smogla ponat kakie formuli ispolzovalis v...
 
Отличается ли ДрифтКазино от беттинга? (4)
Друзья, давно заметил, что на Дрифте уже несколько месяцев во всю рекламируется и предлагается...
 
Сайт инструмент (1)
Я бывший программист пользовался 1 сайтом проверенным он мне действительно помог я блогодоря...
 
Где взять лицензионный ключ для AllFusion Process Modeler (BPwin) 7? (5)
Выручайте!!! где найти ключ, ужасно срочно нужна программа. заранее спасибо!
 
 
 



    
rambler's top100 Rambler's Top100