Skip to Main Content
  • Questions
  • Is it possible - to show OS disk free space together with DBA_DATAFILES data ?

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, José Laurindo.

Asked: June 19, 2012 - 3:48 pm UTC

Answered by: Tom Kyte - Last updated: March 18, 2019 - 8:52 am UTC

Category: Database - Version: 10.2.0.5

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Problematic SQL ? PL/SQL is your friend.

You Asked

In some of my custoner´s databases the DBAs are using AUTOEXTENSIBLE datafiles, but with many datafiles comparting the same filesystem, in this sense :

tablespace A, datafiles /u01/oradata/ts_A_file01.dbf autoextend unlimited
tablespace B, datafiles /u01/oradata/ts_B_file01.dbf autoextend unlimited

and so on... The requested monitoring is that all datafiles must be capable of growing at least 20% of its current size, so if (say) ts_A_file01.dbf currently have 100 GB and ts_B_file01.dbf have 200 GB, we must assure that at least 20 + 40 = 60 GB are free in the /u01/oradata filesystem...
The question is , how we could monitor it in a single-query inside the database ? Right now we have a complex script, gathering free space from df command in a text file and later opening a cursor and calculating the current allocated space from DBA_DATA_FILES and reading the df data via external table....

Best Regards,

J. Laurindo Chiappa

and we said...

I did this on the fly, it is not extensively tested.


Step one is to be able to query up df output - that is easy (in 10.2.0.5 and above)

ops$tkyte%ORA10GR2> create or replace directory exec_dir as '/home/tkyte/Desktop/Presentations/Seminar/11gr2'
  2  /

Directory created.


ops$tkyte%ORA10GR2> CREATE TABLE df
  2  (
  3    "FILESYSTEM" VARCHAR2(100),
  4    "BLOCKS" NUMBER,
  5    "USED" NUMBER,
  6    "AVAILABLE" NUMBER,
  7    "CAPACITY" VARCHAR2(10),
  8    "MOUNT" VARCHAR2(100)
  9  )
 10  ORGANIZATION external
 11  (
 12    TYPE oracle_loader
 13    DEFAULT DIRECTORY exec_dir
 14    ACCESS PARAMETERS
 15    (
 16      RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
 17          preprocessor  exec_dir:'run_df.sh'
 18      READSIZE 1048576
 19      SKIP 1
 20      FIELDS TERMINATED BY WHITESPACE LDRTRIM
 21      REJECT ROWS WITH ALL NULL FIELDS
 22      (
 23        "FILESYSTEM" CHAR(255)
 24          TERMINATED BY WHITESPACE,
 25        "BLOCKS" CHAR(255)
 26          TERMINATED BY WHITESPACE,
 27        "USED" CHAR(255)
 28          TERMINATED BY WHITESPACE,
 29        "AVAILABLE" CHAR(255)
 30          TERMINATED BY WHITESPACE,
 31        "CAPACITY" CHAR(255)
 32          TERMINATED BY WHITESPACE,
 33        "MOUNT" CHAR(255)
 34          TERMINATED BY WHITESPACE
 35      )
 36    )
 37    location
 38    (
 39      exec_dir:'run_df.sh'
 40    )
 41  )
 42  /

Table created.

ops$tkyte%ORA10GR2> !cat run_df.sh
#/bin/bash

/bin/df -Pl

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from df;

FILESYSTEM                           BLOCKS       USED  AVAILABLE CAPACITY   MOUNT
-------------------------------- ---------- ---------- ---------- ---------- ------------------------------
/dev/mapper/VolGroup00-LogVol00   102018408    7976248   88859904 9%         /
/dev/sda1                            101086       9178      86689 10%        /boot
none                                 517928          0     517928 0%         /dev/shm

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select mount, available/1024/1024 gb
  2    from df
  3  /

MOUNT                                  GB
------------------------------ ----------
/                              84.7434082
/boot                          .082673073
/dev/shm                       .493934631


once we have that, we just need to find the right mount joined to the right file name. We'll do that by joining on a "like" and keeping the longest match (this is the not totally tested out part - please review this and evaluate it and test it)


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with fs_data
  2  as
  3  (select /*+ materialize */ *
  4     from df
  5  )
  6  select *
  7    from (
  8  select a.file_name, b.mount, b.available, row_number() over (partition by a.file_name order by length(b.mount) DESC) rn
  9    from dba_data_files a, fs_data b
 10   where a.file_name like b.mount || '%'
 11         )
 12   where rn = 1
 13  /

FILE_NAME                      MOUNT                           AVAILABLE         RN
------------------------------ ------------------------------ ---------- ----------
/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_assm_2cq5x9b
d_.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_autoallo_5b9
8owxz_.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_manual_2cpzz
654_.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_p1_4wlc3t08_
.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_p2_4wlc3zc2_
.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_test_4t4htpw
o_.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_test_dro_35c
3n8md_.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_testing_4pqw
3dw6_.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/ORA10G
R2/datafile/o1_mf_uniform_5b98
ow4r_.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/exampl
e01.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/sysaux
01.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/system
01.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/undotb
s01.dbf

/home/ora10gr2/oracle/product/ /                                88859904          1
10.2.0/oradata/ora10gr2/users0
1.dbf


14 rows selected.

and you rated our response

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Very useful confirmation...

June 20, 2012 - 3:42 pm UTC

Reviewer: A reader

I will test as soon as possible, but surely it appears to be on the rigth track - once we have the information together, I will need just to use some percentile function in the query, probably ... And I completely forgot about the preprocessor clause on 10gr2, many thanks for it.

Best regards,


J. Laurindo Chiappa

June 20, 2012 - 6:48 pm UTC

Reviewer: A reader


preprocessor is 10g?

June 21, 2012 - 9:15 am UTC

Reviewer: John from Boulder CO

Hi Tom,

You write that this approach works for databases 10.2.0.5 and above. My understanding was that the preprocessor keyword only works with external tables in version 11.2 and above. Am I mistaken or was this functionality backported?
Thanks,

John
Tom Kyte

Followup  

June 22, 2012 - 7:07 am UTC

it was backported into 10.2.0.5

My example was running 10.2.0.5

June 22, 2012 - 4:14 am UTC

Reviewer: A reader


June 26, 2012 - 6:14 am UTC

Reviewer: Michel Cadot from France


Need the same script in 10.2.0.3

March 05, 2015 - 9:58 am UTC

Reviewer: Aks from INDIA

Hi TOM,

May I know is there any way to monitor the same in 10.2.0.3 versions also ? Because some of our production databases are in 10.2.0.3. Please provide script if possible

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

May 25, 2018 - 8:36 pm UTC

Reviewer: Vera from Kennewick, WA USA

do exactly as the scripts, create external table, run_df.sh.
select * from df; got errors:
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29401: data cartridge internal error [KUP-04038: internal error: invalid
position for sequential file

Could you help?
Connor McDonald

Followup  

May 28, 2018 - 1:48 am UTC

Can you post the output of you run_df.sh command, making sure you post it within the code tags so we can see the precise spacing


ORA-29913: error in executing ODCIEXTTABLEFETCH callout

May 25, 2018 - 8:50 pm UTC

Reviewer: Vera from Kennewick, WA USA

Oracle 12c, on Linux.
In the log:KUP-05007: Warning: Intra source concurrency disabled because the preprocessor option is being used.

Field Definitions for table DF
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields

Fields in Data Source:

FILESYSTEM CHAR (255)
Terminated by whitespace
Trim whitespace same as SQL Loader
BLOCKS CHAR (255)
Terminated by whitespace
Trim whitespace same as SQL Loader
USED CHAR (255)
Terminated by whitespace
Trim whitespace same as SQL Loader
AVAILABLE CHAR (255)
Terminated by whitespace
Trim whitespace same as SQL Loader
CAPACITY CHAR (255)
Terminated by whitespace
Trim whitespace same as SQL Loader
MOUNT CHAR (255)
Terminated by whitespace
Trim whitespace same as SQL Loader
KUP-04038: internal error: invalid position for sequential file
KUP-04017: OS message: Error 0
KUP-04118: operation "pos_seq", location "skudmip:1"

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

May 29, 2018 - 4:37 pm UTC

Reviewer: Vera Lei from United States

Hi, Tom:
I remove the "CHARACTERSET US7ASCII" then the code works alright.
We use Oracle 12C on Linux.
Thanks a lot.
Connor McDonald

Followup  

May 30, 2018 - 6:05 am UTC

Glad you got to the bottom of it

What about Windows?

June 28, 2018 - 7:54 pm UTC

Reviewer: Brian Leach from United States

All of our databases run on Windows. Do you have an example of getting disk utilization that works on Microsoft Windows?

The only way I have discovered to get the same information as "df" in Windows is through this power shell script:
$work = "COMPUTERNAME" 
ForEach ($server in $work) { gwmi Win32_LogicalDisk -ComputerName $server -Filter "DriveType=3" `
   | select Name,FreeSpace,BlockSize,Size,UsedSpace `
   | % {$_.BlockSize=(($_.FreeSpace)/($_.Size))*100;$_.FreeSpace=($_.FreeSpace/1GB);$_.Size=($_.Size/1GB);$_.UsedSpace=($_.Size-$_.FreeSpace);$_} `
   | Format-Table @{n='Server'     ;e={$server}}`
                , Name `
                , @{n='Capacity GB';e={'{0:N2}' -f $_.Size};     a='right'}`
                , @{n='Used GB'    ;e={'{0:N2}' -f $_.UsedSpace};a='right'}`
              -autosize  -HideTableHeaders }


I invoke it with this windows batch file:
@SETLOCAL
@set scriptdir=d:\Oracle\Scripts\EXEC_DIR\
Path=d:\oracle\12.2\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\
@C:\Windows\System32\WindowsPowerShell\v1.0\powershelltest.exe -File %scriptdir%DiskSpaceOracle.ps1 > %scriptdir%deleteme.txt
@For /F "delims=" %%A in (%scriptdir%deleteme.txt) Do @Echo %%A  
@ENDLOCAL

If I pre-populate deleteme.txt by running the power shell script at a windows command prompt, and comment out the call to powershell, the external table invokes the batch file and I get my space list. If I include the invocation of powershell, the external table log file ends with:
KUP-04095: preprocessor command d:\Oracle\Scripts\EXEC_DIR\DiskSpaceORACLE.bat encountered error "I"


The external table definition is
CREATE TABLE CEAADMIN.DF
(
  SERVER    VARCHAR2(100 BYTE),
  DRIVE     VARCHAR2(10 BYTE),
  CAPACITY  NUMBER,
  USED      NUMBER
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXEC_DIR
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE 
        PREPROCESSOR  exec_dir:'DiskSpaceORACLE.bat'
    READSIZE 1048576
    SKIP 0
    FIELDS TERMINATED BY WHITESPACE LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "SERVER" CHAR(255)
        TERMINATED BY WHITESPACE,
      "DRIVE" CHAR(255)
        TERMINATED BY WHITESPACE,
      "CAPACITY" CHAR(255)
        TERMINATED BY WHITESPACE,
      "USED" CHAR(255)
        TERMINATED BY WHITESPACE
    )
    )
     LOCATION (EXEC_DIR:'DiskSpaceORACLE.bat')
  )
REJECT LIMIT 500;


Can you tell me how I can get this disk information from Oracle running on Windows?

What about Windows - I Made it work

June 28, 2018 - 10:19 pm UTC

Reviewer: Brian Leach from United States

I was able to get the disk listing to work using the code I previously posted. Just add
@set SystemRoot=C:\Windows
to the batch file, and it works great.


Connor McDonald

Followup  

June 29, 2018 - 4:21 am UTC

Nice work, and thanks for posting it for others.

Facing problems with 12.1

March 05, 2019 - 4:06 pm UTC

Reviewer: Leandro Martins de Lima from Montréal

Hello masters.

I'm using Tom's original solution against 11.x and it works as a charm. But then I deployed it in a 12.1 and then things went awry. First I got the file permission problem introduced on 12 and tried the work around with DISABLE_DIRECTORY_LINK_CHECK. It solved the file permission issue and I was again able to access the file with the external table.

But I'm experiencing a weird situtation here. Seems that the preprocessor is reading only the first line of the output. Yeah, I know about the SKIP claus and the REJECT LIMIT option, tried tuning both but nothing helped.

The definition of my ext table:


CREATE TABLE fs_size
 (
   disk VARCHAR2(100)
 )
 ORGANIZATION external
 (
   TYPE oracle_loader
   DEFAULT DIRECTORY DATAPUMP_DIR
   ACCESS PARAMETERS
   (
     RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
         preprocessor  DATAPUMP_DIR:'get_fs_size.bat'        
     DISABLE_DIRECTORY_LINK_CHECK         
     FIELDS TERMINATED BY WHITESPACE LDRTRIM
     (
       disk CHAR(255)
         TERMINATED BY WHITESPACE
     )
   )
   location
   (
     DATAPUMP_DIR:'get_fs_size.bat'
   )
 )REJECT LIMIT unlimited ;



The content of get_fs_size.bat is a single line, just one command:

wmic logicaldisk get caption


(It originally had more fields like freespace and size, but I took them out to investigate until I figure out what is happening).

Running it in a prompt the result is just:

E:\DATAPUMP_DIR>wmic logicaldisk get caption
Caption
A:
C:
D:
E:
F:
G:
H:
J:



Like I said, I'm aware of the missing @echo off in the bat file and also the skip clause, but just for the tests, selecting the table as it is I get only this:

SELECT * FROM fs_size;

DISK
---------------------------------------------------------------
G:\oracle\ora12\DATABASE>wmic


Yeah, that looks like the command echo due to the missing @echo off from the script. But why there were no more lines fetched like when I run the bat in a prompt?

OK, let me just add the clause the SKIP 1 to the table just to avoit the echo - I expect to see the word Caption in the result. But that's not what is happening:

SELECT * FROM fs_size;

DISK                                                                                                
---------------------------------------------------------------
G:\oracle\ora12\DATABASE>wmic


Again the promt and the command; it not skip the first line (the command echoed). Seems like the preprocessor clause is not really executing the bat file but just reading its contents and fetching into the temporary table.

And what it gives if I use the @echo off in the script? Well, If the table is simply reading the .bat file contents without executing it I should see the word @echo as the result, right? So I added the @echo off to the beginig of the script and took out the skip 1 from the ext table. Again, it did not do what I expected:

SELECT * FROM fs_size;
no rows selected;


What? Did it read from the file or from the output? Either way, why it did not fetch more data, since both the file and the output have more than one line?

Seems like the preprocessor is reading only the very first line or return from the script. Without @echo off it gets only the echoed commande and with @echo off it gets an empty OK from the command? But why isn't it feeding more lines? Did I forgot any clause or parameter? Maybe the FIELDS TERMINATED is not not well specified.

And why this very same solution work correctly on 11.2? As far as I understood the documented issue with 12.1 was the permission check of the file, which is no longer the issue here since it does read the file.

So masters, do you have an idea of what is going on here? What am I failing to see in this scenario?


Connor McDonald

Followup  

March 06, 2019 - 6:24 am UTC

How about trying this for 'get_fs_size.bat'

@echo off
set SystemRoot=C:\WINDOWS
set windir=C:\WINDOWS
echo get-psdrive -psprovider filesystem | powershell 


That gave me this:

SQL> CREATE TABLE fs_size
  2   (
  3     disk VARCHAR2(512)
  4   )
  5   ORGANIZATION external
  6   (
  7     TYPE oracle_loader
  8     DEFAULT DIRECTORY TEMP
  9     ACCESS PARAMETERS
 10     (
 11       RECORDS DELIMITED BY NEWLINE
 12       PREPROCESSOR  TEMP:'get_fs_size.bat'
 13       FIELDS
 14       (
 15         disk CHAR(512)
 16       )
 17     )
 18     location
 19     (
 20       TEMP:'empty.txt'
 21     )
 22   )REJECT LIMIT unlimited ;

Table created.

SQL>
SQL>  select * from fs_size;

DISK
------------------------------------------------------------------------------------------------------------------------
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

PS C:\oracle\product\12.2\DATABASE> get-psdrive -psprovider filesystem

Name           Used (GB)     Free (GB) Provider      Root                                               CurrentLocation
----           ---------     --------- --------      ----                                               ---------------
C                 193.14         29.77 FileSystem    C:\                                   oracle\product\12.2\DATABASE
D                 685.38       1177.51 FileSystem    D:\
E                   5.22         54.22 FileSystem    E:\
F                   0.97        930.51 FileSystem    F:\
G                   3.39         11.51 FileSystem    G:\
M                6172.81       1279.10 FileSystem    M:\
P                6199.59       1252.32 FileSystem    P:\
X                 192.57        273.17 FileSystem    X:\
Z                                      FileSystem    Z:\


which you could carve up however you please.

PS - setting systemroot/windir is a common requirement for running windows commands from the pre-processor

Facing problems with 12.1

March 06, 2019 - 3:04 pm UTC

Reviewer: Leandro Martins de Lima from Montréal, QC CA

Well, they don't call you The Oracle Masters for nothing.

Indeed it was the batch file missing the systemroot and windir. It's funny how it was not necessary on Windows 2008 (where this solution worked with Oracle 11.2) but seems mandatory on Windows 2012 (where it failed with Oracle 12.1). Should have guessed it at first but I did not bother to check the windows versions, I tought it was an Oracle 12 bug.

And I didn't get the powershell command to run, so I just used my original wmic.

Still about setting the systemroot/windir a requirement for the pre-processor, there's no mention to it in the doc. The only requirements it states are:

- The batch file must reside in directory_spec.

- The full path name must be specified for system commands.

- The preprocessor batch file must have EXECUTE permissions.

- The first line of the batch file should contain @echo off.

- To represent the input from the location clause, %1 should be used.

- A full path should be specified to any executables in the batch file.

- The batch file used on Windows must have either a .bat or .cmd extension.


(Just for the records, in my original batch file (before setting the systemroot/windir) I also tried adding the full path to the wmic command but it didn't change the result; still got no rows)

Thank you so much for your help, masters!
Connor McDonald

Followup  

March 09, 2019 - 2:24 am UTC

Glad we could help

please share script run_df.sh

March 18, 2019 - 5:52 am UTC

Reviewer: F K from Pakistan

Hi Tom,

I am also trying to incorporate same solution in our health check report. would appreciate if you could please share run_df.sh script. It's being used in the provided solution but I couldn't find it anywhere.

Thanks in advance,
Regards,
F K

Connor McDonald

Followup  

March 18, 2019 - 8:52 am UTC

saw that you found it

please share script run_df.sh

March 18, 2019 - 6:10 am UTC

Reviewer: F K from Pakistan

Never mind.... found it in above solution !


Connor McDonald

Followup  

March 18, 2019 - 8:52 am UTC

Cool.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.