Выполнение внешнего файла из БД Oracle с целью получения информации о дисковом пространстве

Таким образом нам потребуется создать объекты:

• Directory — в ней будет располагаться наш скрипт препроцессинга и на неё будет ссылаться таблица
• external organization table — обращение к которой будет вызывать выполнение скрипта
• script — собственно сам файл который будет выполнять требуемое нам действие в OS

Пример создания требуемых объектов:

-- directory create or replace directory UTIL_DIR as '/u01' / -- table CREATE TABLE T_OS_COMMAND  (  v_line varchar2(4000)  )  ORGANIZATION external  ( TYPE oracle_loader  DEFAULT DIRECTORY UTIL_DIR  ACCESS PARAMETERS  ( RECORDS DELIMITED BY NEWLINE  preprocessor UTIL_DIR:'os_command.sh'  FIELDS TERMINATED BY "\n" LDRTRIM  )  location ( 'os_command.sh')  ) / 

Лучше всего иметь отдельную directory для наших целей ввиду того что нам потребуется одновременно и права на запись в ней и права на выполнение, а такую комбинацию лучше никому не выдавать.
Оптимальное использование это создание пакета (хоть и в схеме SYS) в спецификации которого описаны процедуры, которым необходимо обращение к OS, а саму реализацию оставить внутри пакета и никого к ней не подпускать.
Далее подразумевается что права на чтение, запись и выполнение к UTIL_DIR у нас имеются, также как и права на select из T_OS_COMMAND.

Для создания файла который будет выполнятся достаточно выполнить в OS (да придется хоть раз выполнить действия в OS прибегнув к более тривиальным методам — например ssh):

$touch /u01/os_command.sh $chmod ug+x /u01/os_command.sh 

Данные команды должны быть выполнены от пользователя (либо члена группы) от которых запускается экземпляр БД.

Всё готово к использованию. Для выполнения произвольной команды OS нам следует записать её в файл os_command.sh и обратиться с запросом к таблице T_OS_COMMAND.

declare   F1 UTL_FILE.FILE_TYPE; begin   F1 := UTL_FILE.FOPEN('UTIL_DIR','os_command.sh','W', 4048);    UTL_FILE.PUT_LINE (file => F1, buffer => '#!/bin/sh');   UTL_FILE.PUT_LINE (file => F1, buffer => 'export LANG=en_US.UTF-8');   UTL_FILE.PUT_LINE (file => F1, buffer => 'export PATH=$PATH:/bin');   UTL_FILE.PUT_LINE (file => F1, buffer => 'df -k | grep /');   UTL_FILE.fclose (file => F1); end; / 

Теперь для получения результата работы нашего скрипт достаточно выполнить запрос к таблице T_OS_COMMAND

/dev/sda2             32414672  14870956  15870548  49% / /dev/sda1               124427     18001    100002  16% /boot tmpfs                  8219820    184808   8035012   3% /dev/shm /dev/sdb2            961432104 606013444 306580660  67% /u02 

/dev/sda2             32414672  14871076  15870428  49% /

/dev/sda1               124427     18001    100002  16% /boot

tmpfs                  8219820    184808   8035012   3% /dev/shm

/dev/sdb2            961432104 606013444 306580660  67% /u02

Далее можно приступать к реализации непосредственно методов которым необходим вызов команд OS.

Примером такой реализации может выступать пакет P_SYS_UTILITY. Пожелания по его развитию и участие в оном приветствуются.

create or replace package P_SYS_UTILITY is    -- Author  : ALEXEY   -- Created : 23.08.2013   -- Purpose : Get system info (*nix versions)  /* Get on file or folder name its device or ASM group and used/free space on it   * raw devices not supported */ procedure Get_Disk_Usage ( p_file_name  in varchar2, -- file name (also accept only path)                            o_mount_dev  out nocopy varchar2, -- device or ASM group                            o_used_space out number, -- used space                            o_free_space out number); -- free space   -- Collect space USAGE in BD -- Recomended evry day schedule run procedure Collect_Usage;  -- Get Forecast on space usage -- Recomended base from 10 collects function Get_Forecast ( pDT         in date, -- date for forecast                         pBASE       in integer default 188, -- base days in calculate forecast                         pTYPE_F     in varchar2 default 'SLOPE', -- type forecast: SLOPE | AVG                         pTABLESPACE in varchar2 default null, -- tablespace ( null = all )                         pOWNER      in varchar2 default null, -- user ( null = all )                         pTYPE       in varchar2 default null )  -- segment type ( null = all ), allow like          return number; -- size in bytes on date pDT  -- Get score of space usage and availability -- Can be used in external monitoring tool : Nagios, etc function Get_Space_Status ( pFOREDAYS   in number default 60,  -- days after that                             pFREE_PRCNT in number default 25 ) -- free cpace greater than          return number; -- 0 - Space free enough .. 100 - not enough free space  end P_SYS_UTILITY; 

Метод Get_Disk_Usage

p_file_name — имя файла или папки для месторасположения которого(-ой) будет произведен расчет. Позволяет передавать имена относящиеся к ASM disk groups.
o_mount_dev — имя устройства в системе на которое смонтировано указанное месторасположение, определяется из вывода команды df. Для ASM будет возвращено имя disk group.
o_used_space — количество байт занятых на устройстве/diskgroup
o_free_space — количество байт доступных на устройстве/diskgroup
Осуществляет вызов df с передачей в качестве параметра имени файла, или обращение к v$asm_diskgroup в случае если имя файла начинается на "+".

Метод Collect_Usage

Осуществляет сбор информации об использовании пространства внутри БД. Группирует по табличным пространствам, владельцам и типам сегментов. Не берет в учет сегменты типа undo и temp. Сохраняет полученную информацию в таблицу T_SPACE_USAGE. Рекомендуется к ежедневному выполнению.

create table T_SPACE_USAGE (   dt$ date,   owner$ varchar2(30),   tablespace$ varchar2(30),   type$ varchar2(18),   bytes$ number,   blocks$ number); create index INDX_T_SPACE_USAGE_DT on T_SPACE_USAGE (dt$); comment on table T_SPACE_USAGE is 'Store archive data of usage space in RDBMS'; comment on column T_SPACE_USAGE.DT$ is 'Date collect space usage'; comment on column T_SPACE_USAGE.OWNER$ is 'Segment owner - user in BD'; comment on column T_SPACE_USAGE.TABLESPACE$ is 'Name of tablespace in BD'; comment on column T_SPACE_USAGE.TYPE$ is 'Segment type'; comment on column T_SPACE_USAGE.BYTES$ is 'Size in bytes'; comment on column T_SPACE_USAGE.BLOCKS$ is 'Size in blocks'; 

Метод Get_Forecast

pDT — дата на которую надо спрогнозировать размер
pBASE — количество дней, данные за которые войдут в базу по которой будет строиться прогноз
pTYPE_F — способ прогнозирования — либо на основе ковариация (генеральной) совокупности, либо на основе среднего изменения
pTABLESPACE — табличное пространство по которому производится прогнозирование, если не передавать то по всем
pOWNER — владелец схемы по которому производится прогнозирование, если не передавать то по всем
pTYPE — тип сегментов данных по которому производится прогнозирование, если не передавать то по всем
Выполняет расчет прогнозируемого занимаемого места сегментов согласно указанным критериям. Результат в байтах.

Метод Get_Space_Status

pFOREDAYS — количество дней для прогноза
pFREE_PRCNT — процент доступного пространства (считается от прогнозируемого занятого)
Производит оценку по шкале от 0 до 100 доступного для роста БД пространства.

Также учитывает ограничения на рост файлов внутри БД.

Простой результат позволяет интегрировать вызов функции в системы мониторинга с настройкой порогов критичности.

ps.и да, учитываем, что выдавая права на выполнение и запись мы прокладываем брешь в безопасности.