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

Зачастую для тех или иных нужд возникает необходимость выполнить команду OS из pl/sql или даже sql внутри Oracle Database.
Ниже описывается один из способов и его применение в задаче определения доступного дискового пространства.
Предлагаемый способ заключается в использование добавленного в 11.2 функционала «Препроцессинг данных внешних таблиц».

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

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

Автор: giam

Источник