PL/SQL: Wenn nicht vorhanden, dann

[chrisD_5bfd72]

Hallo,

ich suche nach einer Möglichkeit über eine Schleife Daten in eine Tabelle zu schreiben, wenn das Schlüsselwort noch nicht vorhanden ist.

Hintergrund, ich will ein Script schreiben das in geregelten Abständen den Füllgrad der einzelnen Tablespaces in eine Tabelle einträgt und bei 90% oder anderen Faktoren einen Flag setzt.

Dabei sollen natürlich die Tablespaces auch Dynamisch eingetragen werden, da es ja schon mal vorkommt das ein neuer dazu kommt.

leider enden meine Versuche diese Füllung zu automatisieren meißt mit

ERROR at line 1:
ORA-01403: no data found

Ich bitte also um leichte Schläge mit dem Zaunpfahl wie ich das hin bekomme.

Grüße

Chris

[Ulrich_Vaid]

Hallo,

ich suche nach einer Möglichkeit über eine Schleife Daten in
eine Tabelle zu schreiben, wenn das Schlüsselwort noch nicht
vorhanden ist.

Hintergrund, ich will ein Script schreiben das in geregelten
Abständen den Füllgrad der einzelnen Tablespaces in eine
Tabelle einträgt und bei 90% oder anderen Faktoren einen Flag
setzt.

>>> Wäre gut, wenn du dass Script mitgeliefert hättest, dann könnte es jemand anschauen…

[Ulrich_Vaid]

Hallo,

ich suche nach einer Möglichkeit über eine Schleife Daten in
eine Tabelle zu schreiben, wenn das Schlüsselwort noch nicht
vorhanden ist.

Hintergrund, ich will ein Script schreiben das in geregelten
Abständen den Füllgrad der einzelnen Tablespaces in eine
Tabelle einträgt und bei 90% oder anderen Faktoren einen Flag
setzt.

>>
Probiers damit :

select nvl(b.tablespace_name,nvl(a.tablespace_name,‚UNKOWN‘)) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,max(bytes)/1024 largest,tablespace_name
from sys.dba_free_space group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
/

>>>

Gruss

[chrisD_5bfd72]

Hallo Ulrich,

schöne Abfrage

Ich hatte das Problem das mir meine Loop ausgestiegen ist, weil ich ein Select auf meine Tabelle gemacht habe und da konnte es sein das kein Wert zurück kam. Das ganze habe ich jetzt so gelöst, das ich in einer ersten Schleife nur das laufen lasse:

BEGIN
insert into Tspace_grow (name )
values (space_rec );
EXCEPTION WHEN OTHERS THEN NULL;
END;

Erst in der zweiten werden dann die Werte gefüllt und bearbeitet.
Ist vielleicht nicht das schönste, aber es funktioniert

Danke dir für deine Mühe

Grüße

Chris

[TheBeast]

Hallo Chris!

Ich hatte das Problem das mir meine Loop ausgestiegen ist,
weil ich ein Select auf meine Tabelle gemacht habe und da
konnte es sein das kein Wert zurück kam. Das ganze habe ich
jetzt so gelöst, das ich in einer ersten Schleife nur das
laufen lasse:

BEGIN
insert into Tspace_grow (name )
values (space_rec );
EXCEPTION WHEN OTHERS THEN NULL;
END;

Erst in der zweiten werden dann die Werte gefüllt und
bearbeitet.
Ist vielleicht nicht das schönste, aber es funktioniert

Warum nicht in einem Schritt? Mach es doch (vom Prinzip her) so:

BEGIN
 UPDATE my\_table SET wert='XXX' WHERE primary\_key=999;
 IF SQL%NOTFOUND THEN
 INSERT INTO my\_table (primary\_key, wert) VALUES (999, 'XXX');
 END IF;
END;

oder habe ich dich falsch verstanden?

Gruß
Martin

[chrisD_5bfd72]

Hallo Martin,

Warum nicht in einem Schritt? Mach es doch (vom Prinzip her)
so:

BEGIN
UPDATE my_table SET wert=‚XXX‘ WHERE primary_key=999;
IF SQL%NOTFOUND THEN
INSERT INTO my_table (primary_key, wert) VALUES (999,
‚XXX‘);
END IF;
END;

oder habe ich dich falsch verstanden?

nein, du hast da nichts falsch verstanden…

Dein Wissen möcht ich auch mal haben.

Ich weiß nur nicht ob ich das jetzt alles noch einmal neu baue. Später vielleicht…

Zur Zeit schaut´s so aus:

http://www.dohm.biz/Tablespace_auslastung.sql

Ich weiß nur noch nicht warum mir die Proz bei der Erstbefüllung in der Spalte Tgrowth die „0“ klaut, aber das finde ich irgendwann nooch raus.

Nette Grüße

Chris

[chrisD_5bfd72]

Also ich hab´s jetzt fertig, bitte um konstruktive Kritik

http://www.dohm.biz/Tablespace_auslastung.sql

Wenn noch etwas fehlt oder ihr der Meinung seid das ich irgendwo Unsinn abfrage oder ausgebe oder oder…

Ist ja bald Weihnachten, da habe ich Zeit alles wieder über´n Haufen zu werfen

Grüße

Chris

[TheBeast]

Hallo Chris!

Also da ist noch einiges zu tun.

1. Erstmal kannst du einen wirklich grossen Teil der Aufgaben, die du dir da gestellt hast mit SQL Bordmitteln lösen. Im konkreten Fall ist das zwar vermutlich egal, aber wenn geht, dann sollte man SQL verwenden, insbesondere, wenn keine Roundtrips notwendig sind (also wenn die Daten nicht zwischen Server und Client hin und her müssen, wie z.B. bei einem „update my_tab set col_a=col_b+1;“ (natürlich ist das wie jede Verallgemeinerung falsch, aber ein guter Ausgangspunkt).

2. Wenn eine Tabelle primär aus einer anderen tabelle gefüllt wird, dann sollte man (genau!) auf die verwendeten Datentypen achten oder generell einen CREATE TABLE … AS SELECT machen. Im konkreten Fall schreibst du den VARCHAR2(30) von v$tablespace in einen VARCHAR2(20), was keine so besonders gute Idee ist.

3. Wir hatten das ja oben schon, aber der Vollständigkeit nochmal: Unnötige Loops vermeiden, die zwei Durchgänge bei dir sind um einer zu viel. Du könntest übrigens den INSERT mitsamt der EXCEPTION direkt an den Anfang der zweiten Schleife stellen, das erspart dir die erste Schleife.

4. Mit WHEN OTHERS vorsichtig sein. Im konkreten Fall willst du eigentlich nur den DUP_VAL_ON_INDEX abfangen, kaschierst aber mit dem WHEN OTHERS auch jeden anderen Fehler (z.B. wenn der Tabelle der Platz ausgeht; ist insofern fies, weil die UPDATEs in der zweiten Schleife dann immer noch funktionieren und du nicht mal mitkriegst, dass dir einzelne Tablespaces fehlen). Wie die Auswirkungen auf den Rest der Prozedur sind müsste man sich aber da im Detail je nach Fehler ansehen.

5. Du machst haufenweise einen SELECT und verwendest den zurückgelieferten Wert dann für einen UPDATE. Das ist dann besser, wenn jeder UPDATE einen full table scan machen müsste und du mehrere Werte auf einmal ändern willst - tust du aber nicht… Also entweder die UPDATES zu einem einzelnen zusammenfassen (würde ich hier vermutlich machen) oder sonst wenigstens UPDATE space_grow SET foo=(SELECT bar FROM tab) [RETURNING foo INTO blah].

6. Ich würde auf biegen und brechen vermeiden undokumentierte Funktionen und Packages zu verwenden (DBMS_SYSTEM.KSDWRT). Es ist mir durchaus klar, dass man hin und wieder ganz gerne in die trace/alert logs schreiben würde, das lässt sich aber auch über einen Umweg machen: Wenn ein Job fehlschlägt erzeugt das einen Eintrag sowohl in einem trace file als auch im alert log. Du brauchst also im Prinzip nur am Ende, wenn einer deiner Tablespaces voll wird einen raise_application_error machen (aber erst wenn alle fertig sind, sonst prüfst du nur bis zum ersten beanstandeten TS).

7. Ich würde das Ganze etwas anders angehen. Im (Semi-)Pseudocode in etwa so:

   CREATE OR REPLACE PROCEDURE my_proc AS
   aCursor IS (SELECT * FROM space_grow FOR UPDATE);
   aTablespaceNearlyFull VARCHAR2(900);
   aTablespaceFull VARCHAR2(900);
   BEGIN
   /* sicherstellen, dass wir die aktuellen TS’s haben */
   DELETE FROM space_grow sg
   WHERE NOT EXISTS
   (SELECT * FROM dba_tablespaces ts
   WHERE ts.tablespace_name=sg.TSName);
   INSERT INTO space_grow(TSName) SELECT TSName FROM DBA_Tablespaces ts
   WHERE NOT EXISTS
   (SELECT * FROM space_grow sg
   WHERE ts.tablespace_name=sg.TSName);

   /* neue Werte berechnen */
   FOR aRec IN aCursor LOOP
   /* ich mach da jetzt irgendwas, bin zu faul,
   mir deine Berechnungslogik genauer anzusehen */
   aRec.old_free := aRec.currently_free;
   SELECT SUM(bytes) INTO aRec.currently_free
   FROM DBA_Free_Space
   WHERE tablespace_name = aRec.TSName;
   […]
   UPDATE space_grow SET ROW = aRec WHERE CURRENT OF aCursor;
   /* In aTablespaceNearlyFull und aTablespaceFull merken,
   ob’s Probleme gibt*/
   IF aRec.currently_free = 0 THEN
   aTablespaceFull := aTablespaceFull || ‚,‘ || aRec.TSName;
   END IF;
   […]
   END LOOP;

   /* das Folgende kann man sicher noch eleganter machen… */
   IF aTablespaceNearlyFull IS NOT NULL AND
   aTablespaceFull IS NOT NULL THEN
   raise_application_error(-20001, …);
   END IF;
   IF aTablespaceNearlyFull IS NOT NULL AND
   aTablespaceFull IS NULL THEN
   raise_application_error(-20002, …);
   END IF;
   IF aTablespaceNearlyFull IS NULL AND
   aTablespaceFull IS NOT NULL THEN
   raise_application_error(-20003, …);
   END IF;
   END my_proc;

So, das war’s auch schon. Deine Methode funktioniert übrigens im Grossen und Ganzen (und vermutlich auch problemlos), was ich hier zu meckern hatte dreht sich eher um ein paar Dinge, die man nach 10 Jahren PL/SQL halt einmal anders macht, als am Anfang, vor allem, wenn es um grosse Datenmengen geht (was ja hier nicht der Fall sein dürfte).

Liebe Grüße,
Martin

[chrisD_5bfd72]

Hallo Martin,

danke für die ausführliche Begutachtung !

Ich hab mir das mal alles in eine Textdatei kopiert, damit´s nicht verschütt geht.

Ich werde die Prozedur dann bei nächster Gelegenheit neu bauen, mal schauen wie ich mit deinen Vorschlägen fertig werde.

Liebe Grüße

Chris