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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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 24, 2020 - 12:02 am UTC

Category: Database - Version: 10.2.0.5

Viewed 10K+ times! This question is

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

  (21 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.

February 18, 2020 - 12:39 pm UTC

Reviewer: A reader

Hello,
I am getting below error, please help.

select * from df;
*

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command /oracle/product/db/11.2.0/rdbms/log/run_df.sh
encountered error "/oracle/product/db/11.2.0/rdbms/log/run_df.sh[2]: df: not
found.
"

Regards,
Taoqir
Chris Saxon

Followup  

February 18, 2020 - 6:04 pm UTC

"/oracle/product/db/11.2.0/rdbms/log/run_df.sh[2]: df: not found."

Either that file doesn't exist on the database server, or you've not got permission to execute it.

March 11, 2020 - 10:53 am UTC

Reviewer: Anders from Switzerland

Hi,

I have a small issue, if I use df "direct" /bin only one row are returned..


create or replace directory exec_dir as '/bin/';
drop table df;

CREATE TABLE df
(
"FILESYSTEM" VARCHAR2(100),
"BLOCKS" NUMBER,
"USED" NUMBER,
"AVAILABLE" NUMBER,
"CAPACITY" VARCHAR2(10),
"MOUNT" VARCHAR2(100)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY DATA_PUMP_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
preprocessor exec_dir:'df'
DISABLE_DIRECTORY_LINK_CHECK
READSIZE 1048576
SKIP 1
FIELDS TERMINATED BY WHITESPACE LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"FILESYSTEM" CHAR(255)
TERMINATED BY WHITESPACE,
"BLOCKS" CHAR(255)
TERMINATED BY WHITESPACE,
"USED" CHAR(255)
TERMINATED BY WHITESPACE,
"AVAILABLE" CHAR(255)
TERMINATED BY WHITESPACE,
"CAPACITY" CHAR(255)
TERMINATED BY WHITESPACE,
"MOUNT" CHAR(255)
TERMINATED BY WHITESPACE
)
)
location
(
exec_dir:'df'
)
)
/



Chris Saxon

Followup  

March 11, 2020 - 4:43 pm UTC

Creating a directory pointing at /bin is a BAAAAAAAD idea, it potentially allows hackers to run all sorts of commands and generally mess with your OS.

Use a script in its own directory as in Tom's example.

Very Useful getting information from DB

March 14, 2020 - 8:40 am UTC

Reviewer: Fazarudeen Mohamed Sheriff

Dear Team
Greetings!!

Very Useful getting information from DB without login to OS

Need a help how to convert BLOCKS into GB

Thanks
Fazarudeen Mohamed Sheriff

Connor McDonald

Followup  

March 16, 2020 - 10:18 am UTC

Use a modified 'df' to get the units you want. Some distributions allow df -k for kilobytes or df -h


Solved on 12.1, new problems on 12.2

March 20, 2020 - 4:14 pm UTC

Reviewer: Leandro Martins de Lima from Canada

Wow, there's been more than a year since I wrote here and you guys provided me a nice solution.

Everything worked pretty well until last month when I deployed it in a 12.2 running on Windows 2016. I'm getting a weird incomplete error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.

Just that. After the ORA-29913 it was expected to have a real error message but no, no more details. So I just took a look on the alert.log to see if there was something relevant, and found this:

2020-03-19T11:07:59.356847-04:00
Errors in file E:\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_28852.trc (incident=701191):
KUP-4038 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: E:\ORACLE\diag\rdbms\orcl\orcl\incident\incdir_701191\orcl_ora_28852_i701191.trc


An internal KUP error, great. Google gave me just an entry in a shady blog saying something about KUP language files, to copy the kupus.msb over the actual language file in use, but this look too esotheric to me.

If you remember, by your suggestion I modified my bat file to look like this:

@echo off
set SystemRoot=C:\WINDOWS
set windir=C:\WINDOWS
wmic logicaldisk get size,freespace,caption

To set the systemroot and windir variables. So I just tried messing with it to see what could happen. When I took out both set I went back to my previous problem, selecting the table returns nothing but I got no errors. Nice, I must be in the right direction?

Then I added just systeroot one. The query took some seconds and voilà the probleme again: just the ORA-29913 and nothing else.

OK, let me take out the systemroot and try with just the windir. Got no errors, but no rows either.

On 12.1 with the systemroot variable the preprocessor works fine and it can fetch lines. On 12.2 it fails without any error message. Without the variable it works but can't fech the data.

So, riddle me this masters, what am I doing wrong? This is not an isolated case since I have four others servers with the same issue. I'm still puzzled with the KUP error message on the alert.log. Should I investigate further the language file theory?

Solved on 12.1, new problems on 12.2

March 20, 2020 - 9:03 pm UTC

Reviewer: Leandro Martins de Lima from Canada

OK, did some more investigation and found some interesting clues.

First of all, the KUP error does seem to be linked with language somehow, but maybe not the language file exactly. I changed a bit the external table and took out the preprocessor. Instead I manually ran the batch file and redirected its output to a txt file and then used the file in the external table. The txt file looks just like this:


Caption FreeSpace Size
A:
C: 79453487104 106847793152
D:
E: 212585013248 4617053138944
F: 103490457600 214745214976
G: 33384615936 214745214976
H: 127736635392 214745214976

And the table:

CREATE TABLE fs_size
(
caption VARCHAR2(100)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY DATAPUMP_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
NOBADFILE
NOLOGFILE
DISABLE_DIRECTORY_LINK_CHECK
READSIZE 1048576
FIELDS TERMINATED BY WHITESPACE LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
Caption CHAR(255)
TERMINATED BY WHITESPACE
)
)
location
(
DATAPUMP_DIR:'fs.txt'
)
)REJECT LIMIT UNLIMITED;

So, querying the table gave me this:

SQL> select * from fs_size;

CAPTION
--------------------------------------------------------------------------------
┐┐C a p t i o n

SQL>

Weird, just the first line? And even it have some weird characters? That's suspicious. With the help of notepad++ I noticed that the enconding of the file was UCS-2 LE BOM. Tha heck is this? Never saw that before.

So I just checked on the others 12.1 servers and constated that in all of them when running manually the bat file into an output file, they all have this strange enconding. So I suspect that the defaut stdout is this LE BOM thing, but that's OK, it works on 12.1, it should work in 12.2 too, this should not be the problem right?

Well, still on notepad++ I tried changing the enconding of the txt file on the 12.2 server to something more traditional like ANSI. Surprise surprise:

SQL> select * from fs_size;

CAPTION
--------------------------------------------------------------------------------
Caption
A:
C:
D:
E:
F:
G:
H:

8 lignes sÚlectionnÚes.

SQL>

There you go, gentlemen. An enconding problem not present in 12.1. Like I said, seems that the KUP language hint I found was in the right direction; not really a language but a encoding problem.

Did a couple of more tries with other encodings and found out that the issue is with the BOM ones. I don't really know how this BOM enconding really works, but here are my main questions: why does the 12.1 preprocessor works fine with it but the 12.2 doesn't? Is there any option to the preprocessor to make it work? If not, do you have an idea of how I can change the output encoding of a batch file?
Connor McDonald

Followup  

March 23, 2020 - 2:50 am UTC

I suggest something is different in either the OS or 12.2 configuration compared to your 12.1 example. Perhaps characterset definitions?

The reason I say that is that i can run the (original) script without issues on 12.1, 12.2, 18 and 19...but that of course is all running on my own machine (Win 10).

So I don't think its a database *version* issue per se, but some config difference that has come along for the ride.

(All my databases are AL32UTF8)

Perhaps check out the 'chcp' command to see if that can help with changing the target format of your file

Solved on 12.1, new problems on 12.2

March 23, 2020 - 3:07 pm UTC

Reviewer: Leandro Martins de Lima from Canada

Yeah, I thought there was some difference with characterset or the OS code page but no, the characterset is the same on both Oracles (WE8MSWIN1252) and so are the Windows code pages (both 437). The most significat difference is that the 12.1 is running on a Windows 2012 while the 12.2 is running on a Windows 2016.

So I just tried modifying the external table definition to change its behavior, you know, the good old "the error changed" method.

The original complet definition which is giving me the error is this:

CREATE TABLE fs_size
  (
    caption VARCHAR2(100),
    freespace NUMBER,
    "Size" NUMBER
  )
  ORGANIZATION external
  (
    TYPE oracle_loader
    DEFAULT DIRECTORY DATAPUMP_DIR
    ACCESS PARAMETERS
    (
      RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
   NOBADFILE
      NOLOGFILE
      DISABLE_DIRECTORY_LINK_CHECK
          preprocessor DATAPUMP_DIR:'get_fs_size.bat'
      READSIZE 1048576
      SKIP 1
      FIELDS TERMINATED BY WHITESPACE LDRTRIM
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
        Caption CHAR(255)
          TERMINATED BY WHITESPACE,
        FreeSpace CHAR(255)
          TERMINATED BY WHITESPACE,
        "Size" CHAR(255)
          TERMINATED BY WHITESPACE
      )
    )
    location
    (
      DATAPUMP_DIR:'get_fs_size.bat'
    )
  )REJECT LIMIT UNLIMITED;





I think I already posted the batch file before, but just for the records:

@echo off
set SystemRoot=C:\WINDOWS
set windir=C:\WINDOWS
wmic logicaldisk get size,freespace,caption



Changing or deleting some parameters changed the error but did not really helped until I simply deleted the SKIP 1 (sorry Connor, after so many tests in my previous answer I gave an incorrect definition without it already).

The thing magically works now, without the SKIP 1. I don't really understand why, maybe the header line returned by the wmic has something unreadable by the SKIP clause? Weirder is the fact that even without the SKIP 1 I'm not getting header in the results.

And then I had another revelation: went back to the 12.1 where the solution is deployed and working perfectly to check the SKIP thing. Surprise surprise: I take it out the and still get no header in the results. The skip still works though, if I put a SKIP 2 it skips the first line only. The external table is skipping the first line by default and the skip clause works like X-1. I dont' really understand what is happening here.

Back to my situation, maybe Microsoft changed something on the wmic from 2012 to 2016? Added an unreadable character somewhere on its header that is messing with the preprocessor and/or the skip option?

Would like test a bit more, but since it's working now I will sitck with it and hope I don't have any other surprises.

Thanks for your insight, masters.

P.S.: Connor, I found in your blog the post about my first situation, even my example! It feels funny to be of help like that ;)
Connor McDonald

Followup  

March 24, 2020 - 12:02 am UTC

A lot of blog content comes out of AskTOM experiences. Thats how we build a bigger and better community.

Thanks for keeping us updated.

More to Explore

Analytics

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