Database Reference
In-Depth Information
The requested extension is that all data files must be capable of growing by at least 20 percent of their current
size; so if, for example,
ts_A_file01.dbf
currently is 100GB and
ts_B_file01.dbf
is 200GB, we must ensure that at
least 20GB + 40GB = 60GB is free in the
/u01/oradata
file system.
The question is: How can we monitor this in a single query inside the database? Right now, we have a complex
script gathering free space from the
df
command in a text file, opening a cursor, calculating the current allocated
space from
DBA_DATA_FILES
, and reading the
df
data via an external table.
This can be done in a single SQL query. To accomplish this, I'll need to be able to query disk free (
df
)
interactively—without a complex set of maneuvers such as running a script or redirecting output. I'll start by making it
so that the
df
output can be queried as if it were in a table. I'll use the preprocessor directive to achieve this.
To start I'll create a directory where I can place a small shell script that will produce the
df
output:
EODA@ORA12CR1> create or replace directory exec_dir as '/orahome/oracle/bin';
Directory created.
To perform this operation safely, we need to grant
EXECUTE
on the directory object containing the program we
want to invoke. This allows us to control precisely which program will be executed by Oracle database and avoid
executing a “Trojan horse” program by accident. As a privileged account, I grant the following to the
EODA
user:
SYS@ORA12CR1> grant execute on directory exec_dir to eoda;
Next, I'll create a shell script named
run_df.bsh
in that directory. This script will contain only the following:
#!/bin/bash
/bin/df -Pl
Also, I'll make the shell script executable:
$ chmod +x run_df.bsh
And the output of that script will look something like this:
EODA@ORA12CR1> !./run_df.bsh
Filesystem 512-blocks Used Available Capacity Mounted on
rpool/ROOT/solaris-1 205406208 4576073 92886960 5% /
...
orapool1/ora01 629145600 382371882 246773718 61% /ora01
orapool2/ora02 629145600 429901328 199244272 69% /ora02
orapool1/ora03 629145600 415189806 213955794 66% /ora03
orapool2/ora04 629145600 343152972 285992628 55% /ora04
Note that in the
run_df.bsh
script, I used explicit pathnames to run
df
; I did not rely on the environment and on
the path environment variable in particular. This is very important: when coding scripts for external tables—when
coding scripts, in general—you always want to use explicit paths to run the program you actually intend to run. You
don't have any real control over the environment this script will run in, so relying on the environment being set a
certain way is a recipe for disaster.
