Archiv der Kategorie: Oracle

PL/SQL: Flashback – ein sauberer Blick in die Vergangenheit

Zeige alle Datensätze der Tabelle ABC_TABLE zum Stand von vor 60 Minuten:

SELECT * FROM ABC_TABLE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);

Variante 1: Zeige alle Datensätze der Tabelle ABC_TABLE an, die in den letzten 60 Minuten geändert wurden:

SELECT versions_operation, versions_xid, versions_starttime, ABC_TABLE.*
FROM ABC_TABLE
VERSIONS BETWEEN     timestamp (SYSTIMESTAMP - INTERVAL '60' MINUTE) and (SYSTIMESTAMP)
WHERE versions_operation is not null

Variante 2: Zeige alle Datensätze der Tabelle ABC_TABLE an, die in den letzten 60 Minuten geändert wurden:

SELECT   versions_starttime
       , versions_endtime
       , case versions_operation
             when 'I' then 'Insert'
             when 'U' then 'Update'
             when 'D' then 'Delete'
             else NULL
         end AS versions_operation
       , t.*
FROM     ABC_TABLE
         versions between timestamp (systimestamp - numtodsinterval(60, 'MINUTE')) and (systimestamp) t
WHERE       versions_starttime > (systimestamp - numtodsinterval(60, 'MINUTE'))
         OR versions_endtime > (systimestamp - numtodsinterval(60, 'MINUTE'))
ORDER BY 1 nulls first, 2
;

In welchen Tabellen des kompletten Schemas gab es Änderungen in den letzten 5 min

Danke an Gert!

Warnung: Die Flashback Queries sind sehr I/O-intensiv. Daher möglichst nicht auf Produktivsystem abfeuern und auf einen kurzen Zeitraum begrenzen!

Warnung2: es kann eine Fehlermeldung invalid irgendwas snapshot kommen – dann einfach das query nochmal ausführen.

Wielange die Abfrage dauert, hängt sehr davon ab – exemplarisch meine beiden ersten:

SELECT   q'[union select versions_starttime
       , versions_endtime
       , case versions_operation
             when 'I' then 'Insert'
             when 'U' then 'Update'
             when 'D' then 'Delete'
             else NULL
         end AS versions_operation,']'
         ||table_name ||
         q'[' as table_name  from ]' ||table_name||
         q'[ versions between timestamp (systimestamp - numtodsinterval(5, 'MINUTE')) and (systimestamp) t
                              WHERE       versions_starttime > (systimestamp - numtodsinterval(5, 'MINUTE'))
         OR versions_endtime > (systimestamp - numtodsinterval(5, 'MINUTE'))]'
                  from user_tables
                  where tablespace_name is not null 

Mit beispielsweise dem SQL Developper als IDE exportiert (context menue) man das Query Result im Format Text, ohne Header und ohne Left- und Right Exclosure ins Clipboard.

Das Schlüsselwort UNION nun noch entfernen und abfeuern …

Als Ergebnis erhält man alle Tabellen, bei denen etwas in den letzten 5 Minuten geändert wurde. Dem kann man nun mit den im oberen Kapitel beschriebenen Verfahren im Detail nachgehen.

Oracle SQL: Konvertiere Text in Zahl

SELECT to_number('1.01', '999999.99') FROM DUAL;

Der erste Parameter ist der in eine Zahl zu konvertierende Text.

Der zweite Parameter ist die sogenannte Formatmaske (
Ausführliche Erläuterung). Jedenfalls muss die Zahl in der Formatmaske mehr Stellen haben, als die zu konvertierende Zahl. 

SELECT to_number('10.01', '9.99') FROM DUAL;

bringt als die Fehlermeldung:

ORA-01722: invalid number
00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.