Execution of an external file from an Oracle database in order to obtain information about disk space

Often for those or other needs there is a need to execute the OS command from pl / sql or even sql inside the Oracle Database.
The following describes one of the methods and its application in the task of determining the available disk space.
The proposed method consists in using the “Preprocessing of data of external tables” functionality 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 execution of the script
• script - the actual file itself which will perform the action we need in 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 in view of the fact that we will need both the right to write to it and the right to execute, and it is better not to give out this combination to anyone.
Optimal use is the creation of a package (albeit in the SYS scheme) in the specification of which procedures are described that need to be addressed to the OS, and leave the implementation itself inside the package and not allow anyone to it.
Further it is assumed that we have rights to read, write and execute to UTIL_DIR, as well as rights to select from T_OS_COMMAND.
')
To create a file that will be executed, it is enough to execute it in OS (yes, you will have to perform actions in 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 a member of the group) from whom the database instance is started.

Everything is ready for use. To execute an arbitrary OS command, we should write it to the os_command.sh file and query the T_OS_COMMAND table.

 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 the work of our script, it is enough to execute a query to the table T_OS_COMMAND


Next, you can proceed to implement directly the methods that need to call OS commands.

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

Get_Disk_Usage method

p_file_name is the name of the file or folder for the location of which (th) will be calculated. Allows you to transfer names related to ASM disk groups.
o_mount_dev - the name of the device in the system where the specified location is mounted, is determined from the output of the df command. The name disk group will be returned to ASM.
o_used_space - the number of bytes used on the device / diskgroup
o_free_space - the number of bytes available on the device / diskgroup
Calls df with passing the file name as a parameter, or calling v $ asm_diskgroup if the file name starts with " + ".

Collect_Usage method

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

Get_Forecast method

pdt - the date on which you need to predict the size
pBASE - the number of days for which data will be included in the base for which the forecast will be built
pTYPE_F is a prediction method - either based on the covariance of the (general) population, or based on the mean change
pTABLESPACE - the tablespace for which the prediction is performed, if not pass it over all
pOWNER - the owner of the scheme for which the prediction is made, if not pass it on all
pTYPE - the type of data segments for which the prediction is made, if not transmit it for all
Performs the calculation of the projected occupancy of the segments according to the specified criteria. Result in bytes.

Get_Space_Status method

pFOREDAYS - the number of days for the forecast
pFREE_PRCNT - Percentage of available space (counted from projected employment)
Evaluates on a scale from 0 to 100 of available space for the growth of the database.

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

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

ps. and yes, we take into account that by issuing the rights to execute and write, we are making a breach in safety.