Auswahl  

Freier Diskspace unter Windows mittels External Table ermitteln 

Oracle
DBA
RDBMS 18.1:RDBMS 18.3:RDBMS 19.1:RDBMS 19.3
28.07.20 (MP)
28.07.20
External Table, Preporcessing

Passende Schulungen zum Thema

Wer seine Datenbank gerne überwachen möchte, stößt früher oder später auf das Problem den freien Speicherplatz im Betriebssystem zu ermitteln.
Wir können hier zwar nur die lokalen Laufwerke unter Windows überwachen, aber es ist ein Anfang (das Original Powershell-Skript überwacht auch Netzwerklaufwerke, aber sobald der Oracle Benutzer den Prozess anstößt, verschwinden diese wieder...)
Wir lesen die aktuellen Plattendaten mittels eines Powershell Skripts aus, das aus folgender Zeile besteht:
 
gwmi win32_logicaldisk  | ForEach-Object {$_.DeviceId+";"+$_.Size+";"+$_.FreeSpace}

Diesen Befehl könnten wir zwar mittels External Job ausführen, aber dann braucht man wieder Oracle Credentials und mehr Rechte.... deswegen haben wir uns für einen anderen Ansatz entschieden:
External Table mit Preprocessing.

Dazu brauchen wir eine Tabellenhülle, die die Daten aufnimmt. Achtung: Die Daten werden bei jedem Select frisch vom Betriebssystem geholt und stehen niemals permanent in der Tabelle.
CREATE TABLE DISK_FREE_SPACE
  (   
    "DEVICE_ID"     VARCHAR2(32 BYTE),
    "DISK_SIZE"     VARCHAR2(32 BYTE),
    "FREE_SPACE"    VARCHAR2(32 BYTE)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER DEFAULT DIRECTORY "DATA_PUMP_DIR" ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    NOLOGFILE NOBADFILE NODISCARDFILE
    PREPROCESSOR data_pump_dir: 'read_disk_space.bat'      
    FIELDS TERMINATED BY ";"  
    )
    LOCATION ( 'read_disk_space.bat' )
  )
  REJECT LIMIT UNLIMITED ;
 
Wir haben uns dafür entschieden, die notwendigen Dateien im Verzeichnis DATA_PUMP_DIR abzulegen, weil dieses Directory gibt es fast immer.
Sie können natürlich auch jedes andere Verzeichnis verwenden, müssen das dann aber an allen Stellen entsprechend anpassen.

Die Idee des Preprocessing ist, vor dem eigentlich Lesen einer Text-Datei aus dem Betriebssystem einen Befehl auszuführen. Gedacht war das eigentlich dafür, z.B. eine Datei auszupacken (aus einem Zip) und dann erst in die Datenbank einzulesen.
Wir führen aber ein anderes Preprocessing aus:
1. Eine Batch-Datei erstellen, die als Wrapper für den Powershell Befehl dient. (read_disk_space.bat)
2. Eine Powershell-Datei erstellen die durch das Batchskript aufgerufen wird. (read_disk_space.ps1)
3. Das Batch-Skript starten, dies startet das Powershell-Skript, das führt unsere Datei-Speicherplatz Auswertung durch.
4. Die Daten werden an die external Table Routine weitergegeben und die Daten als Tabellendaten im Select zur Verfügung gestellt.

Geht das nicht einfacher? Leider unseres Wissens nicht weil es noch einen Bug gibt ( Doc ID 2586742.1, Stand 08/2020) der verhindert dass die Powershell direkt aufgerufen weden kann.

Jetzt fehlt uns nur noch eine Pipelined Function, die Daten dann in einer schönen Ergebnisform zurückgibt.
 
CREATE OR REPLACE FUNCTION read_disk_space
RETURN ista_os_disk_tab
AUTHID CURRENT_USER PIPELINED IS
    v_file      UTL_FILE.FILE_TYPE;
    v_dir       VARCHAR2(2001):='DATA_PUMP_DIR';
    v_dir_path  VARCHAR2(2001);
BEGIN
    v_file := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'read_disk_space.bat', 'w');
    SELECT directory_path INTO v_dir_path FROM all_directories
    WHERE directory_name=v_dir;
    UTL_FILE.PUT_LINE(v_file,'echo off');
    UTL_FILE.PUT_LINE(v_file,'set SystemRoot=C:\windows');
    UTL_FILE.PUT_LINE(v_file,'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Unrestricted "' || v_dir_path ||'\read_disk_space.ps1" -Priority BelowNormal');
    UTL_FILE.FCLOSE(v_file);
    v_file := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'read_disk_space.ps1', 'w');
    UTL_FILE.PUT_LINE(v_file, q'!gwmi win32_logicaldisk  | ForEach-Object {$_.DeviceId+";"+$_.Size+";"+$_.FreeSpace}!');
    UTL_FILE.FCLOSE(v_file);
    FOR r IN (SELECT * FROM DISK_FREE_SPACE) LOOP
        PIPE ROW ( ista_os_disk(r.DEVICE_ID,
                                r.DISK_SIZE,
                                r.FREE_SPACE));
     END LOOP;
     RETURN;
END ;
/


Zum Testen verwenden wir den folgenden Select:
SELECT
device_id,round(disk_size/1024/1024/1024,2) as disk_size,
round((free_space+0.0001)/1024/1024/1024,2) as free_space,
round((free_space+0.0001)/disk_size*100,2)||'%' as free_proc
FROM table(read_disk_space);

Das Ergebnis sieht bei uns dann so aus:
 
DEVICE_ID DISK_SIZE FREE_SPACE FREE_PROC
C:\  465,14   300,17  64,53%
D:\    223,62   164,77  73,68%
E:\  998,20  0 0%

Wer weitere spannende Dinge über Oracle im Allgemeinen oder im Besonderen erfahren möchte, kann gerne zu einer unserer vielen Oracle Schulungen kommen :-)
Ich hoffe wir sehen uns bald...