Executing an external file from an Oracle database to obtain information about disk space

Often for certain needs, it becomes necessary to execute an OS command from pl / sql or even sql inside an Oracle Database.
One of the methods and its application in the task of determining the available disk space is described below.
The proposed method consists in using the functionality “Preprocessing data of external tables” added in 11.2.


Thus, we need to create objects:

• Directory - our preprocessing script will be located in it and the table will refer to it
• external organization table - access to which will cause the script to execute
• script - the actual file itself that will perform the required action on the OS

An example of creating the required objects:

-- 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')
 )
/

It is best to have a separate directory for our purposes because we will need both write rights and execute rights, and it’s better not to give this combination to anyone.
The best use is the creation of a package (albeit in the SYS scheme), the specification of which describes procedures that require access to the OS, and leave the implementation itself inside the package and do not allow anyone to access it.
Further, it is assumed that we have the rights to read, write and execute to UTIL_DIR, as well as the rights to select from T_OS_COMMAND.

To create a file that will be executed, it is enough to execute on the OS (yes, you have to perform actions on the OS at least once by resorting to more trivial methods - for example, ssh):

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

These commands must be executed from the user (or group member) from whom the database instance is launched.

Everything is ready to use. To execute an arbitrary OS command, we should write it to the os_command.sh file and request the table 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;
/

Now, to get the result of our script, it is enough to execute a query on the table T_OS_COMMAND


Next, you can begin to implement directly the methods that require calling OS commands.

An example of such an implementation is the P_SYS_UTILITY package . Suggestions for its development and participation in it are welcome.

Get_Disk_Usage Method

p_file_name - the name of the file or folder for the location of which (s) the calculation will be made. Allows you to transfer names related to ASM disk groups.
o_mount_dev - the name of the device in the system to which the specified location is mounted is determined from the output of the df command. For ASM, the name disk group will be returned.
o_used_space - the number of bytes occupied on the device / diskgroup
o_free_space - the number of bytes available on the device / diskgroup
Makes a df call with a file name as a parameter, or calls v $ asm_diskgroup if the file name starts with " + ".

Collect_Usage Method

It collects information about the use of space inside the database. Groups by table spaces, owners, and segment types. Does not take into account segments like undo and temp. Saves the received information to the T_SPACE_USAGE table. Recommended for daily use.

Get_Forecast Method

pDT - date for which to predict the size
pBASE - number of days for which data will be included in the base for which the forecast will be made
pTYPE_F - forecasting method - either based on the covariance of the (general) population or based on the average change
pTABLESPACE - the table space by which forecasting, if not pass it on to all
pOWNER - the owner of the scheme for which you are forecasting, if not pass it on to all
ptype - the type of data segments for which you are forecasting, if not transmitting s something for everyone
Calculates projected space requirements of segments according to specified criteria. Result in bytes.

Get_Space_Status Method

pFOREDAYS - the number of days for the forecast
pFREE_PRCNT - percentage of available space ( calculated from the projected occupied)
Estimates on a scale from 0 to 100 available for growth database space.

Also takes into account restrictions on the growth of files inside the database.

A simple result allows you to integrate a function call into monitoring systems with the setting of criticality thresholds.

ps. and yes, we take into account that by issuing rights to execute and record we are making a security breach.