Skip to Main Content
 

Search Results

Oracle Tipp Text

Oracle SET Operatoren und Gruppenfunktionen und Unterabfragen

Bereich:SQL, Version: ab RDBMS 11.2, Letzte Überarbeitung: 05.07.2023

Keywords:Oracle SET Operatoren, Oracle Gruppenfunktionen, Oracle Unterabfragen

Oracle SET-Operatoren

INFOBESCHREIBUNGBEISPIEL
UNIONgibt alle Zeilen aus Mehrfachabfragen zurück (doppelte werden ausgeblendet)SELECT empno, ename, dname, loc, d.deptno    
FROM emp e, dept d    
WHERE e.deptno = d.deptno(+)    
UNION    
SELECT empno, ename, dname, loc, d.deptno    
FROM emp e, dept d    
WHERE e.deptno(+) = d.deptno;
UNION ALLgibt alle, auch doppelte Zeilen aus Mehrfachabfragen zurück 
INTERSECTgibt ausschließlich gemeinsame Zeilen aus beiden Anfragen zurück 
MINUSgibt nur die Zeilen zurück, die von der ersten, nicht aber von der Zweiten Abfrage zurück gegeben werden(Abfrage 1 minus Abfrage 2) 

 

 

 

Oracle Gruppenfunktionen

INFOBESCHREIBUNGBEISPIEL
COUNT(*/Ausdruck/Spalte)zählt alle ausgewählten ZeilenSELECT count(*) FROM emp WHERE deptno = 30;
MIN/MAX(spalte/ausdruck)ermittelt minimalen / maximalen Wert eines AusdrucksSELECT min(sal), max(sal) FROM emp;
AVG/SUM(spalte/ausdruck)Durchschnitts/Summenwert einer SpalteSELECT sum(sal), avg(sal) FROM emp;
grpfkt(NVL(spalte, n))auch NULL-Werte gehen in die Berechnungen mit einSELECT avg(nvl(comm, 0)) FROM emp;
GROUP BY spalte[, spalte]unterteilt Zeilen einer Tabelle in GruppenSELECT job, avg(sal)    
FROM emp GROUP BY job;
HAVING group_condlegt fest welche Gruppen angezeigt werden sollenSELECT deptno, job, count(*), avg(sal)    
FROM emp GROUP B> deptno, job    
HAVING count(*) = 1 AND avg(sal>2000);

 

 

 

Oracle Unterabfragen

INFOBESCHREIBUNGBEISPIEL
Single-Row UAgibt eine Zeile aus der inneren SELECT Anweisung zurück(=,><,<>)SELECT * FROM emp WHERE sal >    
(SELECT avg(sal) FROM emp);    
SELECT ename, sal FROM emp    
WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'DALLAS')    
AND sal > (SELECT sal FROM emp    
WHERE ename = 'TURNER');
Multiple-Row Unterabfragegibt mehrere Zeilen Zurück in, ANY, ALL 
IN Unterabfrageentspricht einem ListenelementSELECT ename, sal deptno FROM emp    
WHERE sal IN (SELECT max(sal)    
FROM emp GROUP BY deptno);
ANY UnterabfrageWert wird mit jedem einzelnen Unterabfragewert verglichenSELECT ename, job, sal FROM emp    
WHERE sal < ANY (SELECT sal FROM emp    
WHERE job = 'SALESMAN');
ALL UnterabfrageWert wird mit allen Unterabfragewert verglichen 

 

 



Weitere Interessente Artikel zum Thema:



Empfohlene Schulungen zum Thema:



    Reguläre Ausdrücke in Oracle (Praxisbeispiele)

    Bereich:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 29.02.2024

    Keywords:

    Im heutigen Tipp widmen wir uns ein paar Praxisbeispielen zu Regulär Expression (Reguläre Ausdrücke) in Oracle.

    Beispiel 1: Kreditkartennummern haben meist das Format 1234-1234-1234-1234
    Wir suchen in einem String zwei 4er Blöcke mit Zahlen un einem Minus dazwischen und ersetzen jeden Block durch xxxx-xxxx:

    • [[:digit:]] steht nur für Zahlen
    • [4} 4 Zahlen
    select regexp_replace(
'1234-5678-1111-2222',
'[[:digit:]]{4}-[[:digit:]]{4}','xxxx-xxxx') from dual;

    Ergebnis: xxxx-xxxx-xxxx-xxxx

    Oder wir suchen vier 4er Blöcke mit Zahlen und ersetzen das mit XXXX:

    select regexp_replace(
'1234-5678-1111-2222',
'[[:digit:]]{4}','xxxx') 
from dual;

    Ergebnis: xxxx-xxxx-xxxx-xxxx

    Nur der letzte 4er Block soll ausgegeben werden

    select regexp_replace(
'1234-5678-1111-2222',
'([[:digit:]]{4})(-)([[:digit:]]{4})(-)([[:digit:]]{4})(-)([[:digit:]]{4})','XXXX-XXXX-XXXX-\7') from dual;

    Ergebnis:

    XXXX-XXXX-XXXX-2222

     

    Alternativ können sie anstatt [[:digit:]] durch \d ersetzen

    select regexp_replace(
'1234-5678-1111-2222',
'\d{4}','x') 
from dual;

    Ergebnis: x-x-x-x

    Beispiel 2: Wir löschen einige HTML Tags in einem String:

    • \/?div findet div, 
    • \/?p findet p und 
    • \/?strong findet strong Tags 
    • (da / ein Sonderzeichen ist, muss es mit \ entwertet (escaped) werden)
    select regexp_replace(
'<div>Hallo</div><strong> Kurs</strong>',
'(<\/?div>|<\/?p>|<\/?strong>)','') 
from dual;

    oder wir löschen alle HTML Tags im String

    select regexp_replace(
'<div>Hallo</div><strong> Kurs</strong>',
'<\/?[^>]*>,'') 
from dual;

    Das war eine kleine Beispielauswahl, die von uns ständig erweitert wird. Oder Sie kommen z.B. in den PL/SQL oder PL/SQL II Kurs, da werden diese Themen auch ausführlich besprochen.



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Oracle Constraints nachträglich anlegen

    Bereich:SQL, Version: ab RDBMS 12.x:RDBMS 18.3:RDBMS 19.3:RDBMS 21.1, Letzte Überarbeitung: 08.08.2022

    Keywords:Primary Key, Foreign Key, Not Null

    Wie oft habe ich schon nach der Syntax gesucht um nachträglich einen Constraint auf eine Tabelle zu legen. Google ist ja da immer eine gute Hilfe, aber in deutsch gab es bisher wenig.
    Das ändert sich nun, wir beschätigen uns mit dem Thema Constraints hier nun.

    Nachträglich einen Not Null Constraints auf eine Tabelle legen:

    ALTER TABLE emp MODIFY (ename VARCHAR2(10) CONSTRAINT emp_ename_nn NOT NULL);


    Constraint wieder löschen:

    ALTER TABLE emp DROP CONSTRAINT emp_ename_nn;


    Nachträglich einen Primärschlüssel anlegen (Achtung es darf keiner bisher existieren):

    ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY(empno);


    Constraint wieder löschen:

    ALTER TABLE emp DROP CONSTRAINT pk_emp;


    Nachträglich einen Unique Index anlegen (davon dürfen Sie mehrere auf der gleichen Tabelle besitzen)
    Hier erlauben wir pro Tag nur einen Mitarbeiter mit gleichem Nachname einzustellen.

    ALTER TABLE emp ADD CONSTRAINT emp_hiredate_ename_uk UNIQUE KEY(hiredate,ename);


    Constraint wieder löschen:

    ALTER TABLE emp DROP CONSTRAINT emp_hiredate_ename_uk;



    Foreign Key Constraint anlegen. (Dieser hat einen Bezug zu einer lokalen Spalte der Tabelle und eine Verbindung zu einer Spalte einer anderen Tabelle)

    ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
    REFERENCES dept(deptno);


    Der Foreign Key Constraint hat aber noch eine weitere nette Option: On Delete Cascade
    Wenn hier ein Hauptdatensatz (bei uns z.B. die Abteilung) gelöscht wird, werden die Kinddatensätze (hier die Mitarbeiter der Abteilung) auch rekursiv gelöscht

    ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
    REFERENCES dept(deptno) ON DELETE CASCADE;


    Alternativ kann auch nur die abhängige Spalte auf NULL gesetzt werden

    ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
    REFERENCES dept(deptno) ON DELETE SET NULL;


    Constraint wieder löschen:

    ALTER TABLE emp DROP CONSTRAINT fk_deptno;

     
    Sonderfälle
    Constraints können auch verzögert geprüft werden. Das ist dann sinvoll, wenn im Datenmodell mit Constraints Änderungen durchgeführt werden sollen, die gegen die Regeln des Constraint verstossen würden.
    Durch die verzögerte Prüfung erfolgt dies erst beim Commit und nicht schon während der Transaktion.

    ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
    REFERENCES dept(deptno) DEFERRABLE INITIALLY IMMEDIATE;

    Bei der Option DEFERRABLE ENABLE NOVALIDATE  wird bei einem Unique Index ein Non-Unique Index angelegt.
    Sollte ein Index auf den benötigten Spalten bereits vorhanden sein, wird dieser verwendet.

    Danach kann die verzögerte Prüfung in der Session aktiviert werden:
    ALTER SESSION SET CONSTRAINTS = DEFERRED;
    oder wieder zurück auf Default:
    ALTER SESSION SET CONSTRAINTS = IMMEDIATE;

     

    Mit einem ALTER TABLE kann der Zustand der verzögerten Constraints verändert werden:

    • ENABLE VALIDATE   entspricht dem ENABLE. Der Constraint wird für alle Zeilen geprüft
    • ENABLE NOVALIDATE  hier werden nur neue Zeilen geprüft, bereits existierende Zeilen können gegen den Constraint verstossen:
      Oracle muss hier einen Non-Unique Index anlegen, weil ja evtl Doubletten bereits vorhanden sind.
    • DISABLE NOVALIDATE  entspricht dem Zustand DISABLE. Der Constraint wird nicht geprüft und damit kann es Daten geben, die gegen die Constraint Regel verstossen.
      Bei Unique Constraints wird der dazugehörige Index gelöscht
    • DISABLE VALIDATE bedeutet, dass der Constraint nicht geprüft wird und verbietet Änderungen an den betroffenen Spalten. Sinnvoll bei einem Exchange Parition Vorgng im DWH.
      Bei Unique Constraints wird der dazugehörige Index gelöscht
     

     

     



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Unsichtbare Spalten

    Bereich:SQL, Version: ab RDBMS 12.2, Letzte Überarbeitung: 04.07.2018

    Keywords:Oracle Neuerungen, SQL, 12C Release 1

    Ab Version 12c können Spalten ausgeblendet werden, indem man sie als "invisible" deklariert. Das ist sowohl beim Anlegen einer Tabelle oder Spalte möglich als auch nachträglich.
    Das bedeutet aber nicht, dass auf eine solche Spalte nicht zugegriffen werden kann, sondern nur, dass sie ohne Angabe einer expliziten Spaltenliste nicht berücksichtigt wird:

    CREATE TABLE UNSICHTBAR
    (
      A        NUMBER,
      B        NUMBER INVISIBLE,
      C        NUMBER
    );

     

    INSERT INTO UNSICHTBAR   
    VALUES(1, 1);

     

    INSERT INTO UNSICHTBAR   
    (A, B, C )
    VALUES(2, 2, 2);

     

    SELECT * FROM UNSICHTBAR;
             A          C
    ---------- ----------
             1          1
             2          2

     

    SELECT A, B, C FROM UNSICHTBAR;
             A          B          C
    ---------- ---------- ----------
             1                     1
             2          2          2

    Solange man die Spalte explizit anspricht, kann man also ganz normal mit ihr arbeiten.

    Interessant ist, dass TOAD die Spalte beim DESCRIBE mit angibt - und an welcher Position:

    DESC UNSICHTBAR   -- in SQL*Plus
     Name                         Null?    Typ
     ---------------------------- -------- -----------
     A                                     NUMBER
     C                                     NUMBER
     
    DESC UNSICHTBAR   -- im TOAD
     Name                         Null?    Type                       
     ---------------------------- -------- ------------
     A                                     NUMBER     
     C                                     NUMBER     
     B                                     NUMBER                    

    Hier lohnt sich ein Blick ins Data Dictionary:

      SELECT column_name, column_id
        FROM user_tab_columns
       WHERE table_name = 'UNSICHTBAR'
    ORDER BY column_id;
    COL  COLUMN_ID
    --- ----------
    A            1
    C            2
    B            

    Die column_id ist also bei unsichtbaren Spalten nicht gefüllt. Interessanter noch ist die etwas weniger bekannte user_tab_cols:

      SELECT column_name, column_id, internal_column_id,
             hidden_column, virtual_column
        FROM user_tab_cols
       WHERE table_name = 'UNSICHTBAR'
    ORDER BY internal_column_id;
    COL  COLUMN_ID INTERNAL_COLUMN_ID HIDDEN_COLUMN VIRTUAL_COLUMN
    --- ---------- ------------------ ------------- --------------
    A            1                  1 NO            NO           
    B                               2 YES           NO           
    C            2                  3 NO            NO           

    Wenn eine unsichtbare Spalte wieder sichtbar gemacht wird, bekommt sie grundsätzlich die höchste column_id und rückt dementsprechend ans Ende der Spaltenliste:

    ALTER TABLE UNSICHTBAR MODIFY (b VISIBLE);

     

    SELECT * FROM UNSICHTBAR;
             A          C          B
    ---------- ---------- ----------
             1          1           
             2          2          2

     

      SELECT column_name, column_id,  internal_column_id,
             hidden_column, virtual_column
        FROM user_tab_cols
       WHERE table_name = 'UNSICHTBAR'
    ORDER BY internal_column_id;
    COL  COLUMN_ID INTERNAL_COLUMN_ID HIDDEN_COLUMN VIRTUAL_COLUMN
    --- ---------- ------------------ ------------- --------------
    A            1                  1 NO            NO           
    B            3                  2 NO            NO           
    C            2                  3 NO            NO                  

    Auf diese Art könnte man beispielsweise auch eine neu eingefügte Spalte, die ja bis 11g grundsätzlich als letzte Spalte angezeigt wird, in der Spaltenliste weiter nach vorne wandern lassen:

    ALTER TABLE UNSICHTBAR ADD (e NUMBER);
    ALTER TABLE UNSICHTBAR MODIFY (b INVISIBLE, c INVISIBLE);
    ALTER TABLE UNSICHTBAR MODIFY (b VISIBLE, c VISIBLE);

     

    SELECT *  FROM UNSICHTBAR;
             A          E          B          C
    ---------- ---------- ---------- ----------
             1                                1
             2                     2          2

    So ganz neu ist das Konzept der unsichtbaren Spalten nicht. Haben Sie sich schon einmal angeschaut, wie eine Spalte vom Typ XMLTYPE intern dargestellt wird?

    ALTER TABLE UNSICHTBAR ADD (xml XMLTYPE);

     

      SELECT column_name, column_id
        FROM user_tab_columns
       WHERE table_name = 'UNSICHTBAR'
    ORDER BY column_id;
    COLUMN_NAME    COLUMN_ID
    ------------- ----------
    A                      1
    E                      2
    B                      3
    C                      4
    XML                    5

     

      SELECT column_name, column_id, internal_column_id,
             hidden_column, virtual_column
        FROM user_tab_cols
       WHERE table_name = 'UNSICHTBAR'
    ORDER BY internal_column_id;        
    COLUMN_NAME    COLUMN_ID INTERNAL_COLUMN_ID HIDDEN_COLUMN VIRTUAL_COLUMN
    ------------- ---------- ------------------ ------------- --------------
    A                      1                  1 NO            NO            
    B                      3                  2 NO            NO            
    C                      4                  3 NO            NO            
    E                      2                  4 NO            NO            
    XML                    5                  5 NO            YES           
    SYS_NC00006$           5                  6 YES           NO                 

     

    Hier kann die unsichtbare Spalte SYS_NC00006$ prinzipiell auch direkt angesprochen werden. Da es sich aber bei Speicherung des XMLTYPE als binary xml (dem Default in 12c) um einen BLOB handelt, ist das allerdings nicht sehr sinnvoll. Und bei Speicherung als CLOB (deprecated in 12c) bietet es zumindest keine Vorteile.

    Das Prinzip ist also ähnlich, auch wenn es offensichtlich Unterschiede im Detail gibt. Interessierte sollten sich die Spalten column_id (hier gefüllt!), segment_column_id, virtual_column und - in Version 12c neu eingeführt - user_generated in user_tab_cols genauer anschauen.

    Sie würden gerne wissen, was ist alles neu in 12c? - dann schauen Sie doch in unserer Oracle Schulung Neuerungen 12c vorbei.



    Weitere Interessente Artikel zum Thema:



    Empfohlene Schulungen zum Thema:


    SQL*PLUS Hilfe Erweitern

    Bereich:SQL, Version: ab RDBMS 11.x, Letzte Überarbeitung: 09.07.2020

    Keywords:DBA, Oracle Tools, SQL

    Die SQL*Plus Hilfe war bis zur Version 8.1 eigentlich recht hilfreich, da sie bei der Syntax von SQL und PL/SQL immer recht gut weitergeholfen hat. Leider wird dieser Bereich von Oracle nicht mehr gepflegt, es wurde nur noch die SQL*Plus Syntax in der Hilfe gelassen.

    Es ist Zeit, hier etwas zu verbessern. Hinweis: Dies ist von Oracle nicht supported! und sollte deshalb nur auf Testdatenbanken verwendet werden.

    Wenn die Hilfe-Tabelle noch nicht existiert, legen Sie diese bitte mit folgendem Skript an:

    connect system/sys
    @?/sqlplus/admin/help/hlpbld.sql helpus.sql

    Dann schauen wir uns die Struktur der Tabelle einmal genauer an:

    SQL> desc system.help
     Name    Null?    Typ
     ------ --------- ----------------
     TOPIC   NOT NULL VARCHAR2(50)
     SEQ     NOT NULL NUMBER
     INFO             VARCHAR2(80)

    Die Topic-Spalte ist für den Text verantwortlich nach dem gesucht wird, die Info-Spalte gibt dann den Hilfetext zurück. Die SEQ-Spalte ist für mehrzeilige Texte gedacht. Sie muss pro Topic eindeutig sein.

    Wenn wir in die Tabelle die Werte ('Muniqsoft-Training',1,'Schulung Tel.: 089 67909040') eintragen dann liefert der Befehl zurück:

    SQL> help muniqsoft-training
    Schulung Tel.: 089 67909040

    Hinweis:
    Für einige Hilfetexte ist in den Spalten zu wenig Platz, deswegen vergrößern wir zwei Spalten. In der Version 12.1 hat das funktioniert. In älteren Versionen kann es zu Problemen kommen, dann lassen Sie den Schritt weg. Jedoch können Sie dann natürlich auch nicht so viel Text in die Spalten eintragen.

    ALTER TABLE system.help MODIFY (info VARCHAR2(140));
    ALTER TABLE system.help MODIFY (topic VARCHAR2(100));

    Zum warmwerden, tragen wir ein paar Texte ein (Ein bisschen Schleichwerbung muss schon sein :-) )

    INSERT INTO system.help VALUES('MUNIQSOFT',0,'Muniqsoft GmbH www.muniqsoft-training.de Tel.: 089/67 90 90 40');
    INSERT INTO system.help VALUES('MUNIQSOFT',1,'Beratung, Schulungen, Consulting, Lizenzvertrieb');
    INSERT INTO system.help VALUES('MUNIQSOFT',2,'Ihr Oracle Partner, wenn es um Oracle DBA, Administration, Migration,');
    INSERT INTO system.help VALUES('MUNIQSOFT',3,'Schulungen (auch Inhouse), Tuning oder Backup + Recovery geht');

    Und testen das mit:

    help muniqsoft-training

    Nun legen wir richtig los:

    INSERT INTO system.help VALUES('ALTER DATABASE',0,'REM +++ Muniqsoft Training GmbH www.muniqsoft-training.de Tel.: 089/67909040 +++');
    INSERT INTO system.help VALUES('ALTER DATABASE',1,'ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <temp>;');
    INSERT INTO system.help VALUES('ALTER DATABASE',2,'ALTER DATABASE DATAFILE ''<file>'' RESIZE <x>M;');

     

    INSERT INTO system.help VALUES('ALTER DATABASE',3,'ALTER DATABASE ADD LOGFILE <name> SIZE <x>M;');
    INSERT INTO system.help VALUES('ALTER DATABASE',4,'ALTER DATABASE DROP LOGFILE GROUP <n>;');
    INSERT INTO system.help VALUES('ALTER DATABASE',5,'ALTER DATABASE DROP LOGFILE MEMBER ''<file>'';');

     

    INSERT INTO system.help VALUES('ALTER DATABASE',10,'ALTER DATABASE RENAME GLOBAL_NAME TO <demo.world.muniqsoft.com>;');
    INSERT INTO system.help VALUES('ALTER DATABASE',11,'ALTER DATABASE CHARACTER SET <charset>;');
    INSERT INTO system.help VALUES('ALTER DATABASE',12,'ALTER DATABASE NATIONAL CHARACTER SET <charset>;');
    INSERT INTO system.help VALUES('ALTER DATABASE',13,'ALTER DATABASE OPEN RESETLOGS;');

     

    REM RECOVERY
    INSERT INTO system.help VALUES('RECOVER',100,'REM +++ Muniqsoft GmbH www.muniqsoft-training.de Tel.: 089/67909040 +++');
    INSERT INTO system.help VALUES('RECOVER',101,'RECOVER DATABASE [USING BACKUP CONTROLFILE];');
    INSERT INTO system.help VALUES('RECOVER',102,'RECOVER DATABASE UNTIL CANCEL [USING BACKUP CONTROLFILE];');
    INSERT INTO system.help VALUES('RECOVER',103,'RECOVER DATABASE UNTIL TIME ''YYYY-MM-DD:HH24:MI:SS''[USING BACKUP CONTROLFILE];');
    INSERT INTO system.help VALUES('RECOVER',104,'RECOVER DATABASE UNTIL CHANGE <nr>;');

    Wie wäre es mit allen undokumentierten Parametern mit Beschreibung (in zwei Zeilen wegen der Länge):

    connect sys/<pwd>@db as sysdba

     

    INSERT INTO system.help
    SELECT  b.ksppinm ,1,b.ksppinm||'='||a.ksppstvl||' (Def:'||a.ksppstdf||')'
    FROM sys.x$ksppi b, sys.x$ksppcv a
    WHERE a.indx = b.indx
    AND substr(b.ksppinm,1,1)='_';

     

    INSERT INTO system.help
    SELECT b.ksppinm,2,'Info: '||substr(b.ksppdesc,1,130)
    FROM sys.x$ksppi b, sys.x$ksppcv a
    WHERE a.indx = b.indx
    AND substr(b.ksppinm,1,1)='_';

    Aber es geht noch besser: Wenn wir dynamische Infos wie z. B. aus V$SESSION oder DBA_USER oder ... mit der Hilfe anzeigen wollen, dann verschieben Sie die Ursprungstabelle help in eine Tabelle help_tab und konsolidieren alle Informationen in einer View:

    GRANT SELECT ON dba_users TO system;
    GRANT SELECT ON dba_data_files TO system;
    GRANT SELECT ON sys.v_$session TO system;
    GRANT SELECT ON system.help TO <user>; -- Geben Sie den Benutzer an, der mit dem HELP Befehl arbeiten soll. Dieser muss dann auch auf die obigen Views Rechte bekommen.
    ALTER TABLE SYSTEM.help RENAME TO help_tab;
    CREATE OR REPLACE VIEW system.help
    AS
    SELECT * FROM system.help_tab
    UNION ALL /* ++++ Benutzer ++++++ */
    SELECT 'USERS',0,rpad('ID',3,' ')||' '||rpad('USER',25,' ')||rpad(' Account Status',19,' ')
    ||rpad('DEF TBS',27,' ')||rpad('TMP TBS',18,' ') FROM DUAL
    UNION ALL
    SELECT 'USERS',0,rpad('-',3,'-')||' '||rpad('-',25,'-')||' '||rpad('-',17,'-')||' '||
    rpad('-',26,'-')||' '||rpad('-',18,'-') FROM DUAL
    UNION ALL
    select 'USERS',user_id+1,rpad(user_id,3,' ')||' '||rpad(username,25,' ')||' '||rpad(account_status,17,' ')||
    rpad(default_tablespace,18,' ')||temporary_tablespace
    from dba_users
    UNION ALL /* +++++ Tablespace und deren Größe ++++++*/
    SELECT 'TABLESPACES',0,rpad('TABLESPACE',22,' ')||' F_id Fileame (Groesse)' FROM DUAL
    UNION ALL
    SELECT 'TABLESPACES',0,rpad('-',22,'-')||' '||rpad('-',2,'-')||' '||rpad('-',62,'-') FROM DUAL
    UNION ALL
    SELECT 'TABLESPACES',file_id,rpad(tablespace_name,25,' ')||' '||file_id||' '||file_name||' ('||
    round(bytes/1024/1024,2)||' MB)'  FROM dba_data_files
    UNION ALL /* +++++++ Sessions +++++++++ */
    SELECT 'SESSIONS',0,rpad('USER',12,' ')||' Status          SQLID            Last CALL        EVENT' FROM DUAL
    UNION ALL
    SELECT 'SESSIONS',0,rpad('-',12,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')
    ||' '||rpad('-',16,'-')||' '||rpad('-',34,'-') FROM DUAL
    UNION ALL
    select 'SESSIONS',sid,rpad(username,12,' ')||' Status:'||rpad(status,8,' ')||' SQLID:'||rpad(nvl(sql_id,'-'),10,' ')
    ||' Last Call:'||rpad(last_call_et,6,' ')||' Event:'||event from v$session
    where type='USER' and username is not null
    ;

    Das testen wir z. B. mit:

    SQL> help sessions
    USER  Status          SQLID            Last CALL        EVENT
    ----- --------------- --------------- ---------------- ----------------------------------
    SYS   Status:ACTIVE   SQLID:b9jqaugh1w Last Call:0      Event:SQL*Net message to client
    SYS   Status:INACTIVE SQLID:-          Last Call:75900  Event:SQL*Net message from client
    SQL> help users
    ID  USER    Account Status    DEF TBS         TMP TBS
    --- ------- ----------------- --------------- ---------
    0   SYS     OPEN              SYSTEM          TEMP
    7   AUDSYS  EXPIRED & LOCKED  USERS           TEMP
    8   SYSTEM  EXPIRED(GRACE)    SYSTEM          TEMP
    13  OUTLN   EXPIRED           SYSTEM          TEMP
    SQL> help tablespaces
    TABLESPACE   F_id Fileame (Groesse)
    ------------ -- -------------------------------------------------
    SYSTEM          1 D:\ORACLE\ORADATA\O19C\SYSTEM01.DBF (700 MB)
    SYSAUX          3 D:\ORACLE\ORADATA\O19C\SYSAUX01.DBF (650 MB)
    UNDOTBS1        5 D:\ORACLE\ORADATA\O19C\UNDOTBS01.DBF (630 MB)
    USERS           6 D:\ORACLE\ORADATA\O19C\USERS01.DBF (5 MB)
    KUNDEN_TBS      12 D:\ORACLE\ORADATA\O19C\KUNDEN01.DBF (6144 MB)

     

    Weitere Ideen zu diesem Thema lernen Sie in unserem SQL*Plus Tageskurs. Sie können das Konzept natürlich selbst nach Belieben erweitern, z. B. SQL Area Befehle anhand der SQL_ID ausgeben oder alle Locks anzeigen. Wenn Sie weitere Punkte integriert haben, schicken Sie sie uns doch bitte, dann machen wir nochmal einen Bonus Track zu diesem Tipp.

     



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Autoincrement Spalten in 12c (Identity)

    Bereich:SQL, Version: ab APEX 5.x, Letzte Überarbeitung: 29.06.2018

    Keywords:SQL

    Lange haben wir darauf gewartet, endlich ist sie da! Oracle 12c! Das "c" steht für Cloud.
    Wir wollen in dieser Reihe einige neue Features vorstellen, die wir natürlich auch in unserem Oracle 12c Kurs ausführlich besprechen.

    Oracle 12c: Identity Spalten
    Der SQL Server kann es schon seit einiger Zeit, Oracle nun auch ab Version 12c.
    Eine Primärschlüsselspalte kann automatisch mit einem Wert gefüllt werden. Einfachster Fall, Sequenz soll bei 1 starten, Schrittweite 1:

    CREATE TABLE t (
    id    NUMBER GENERATED AS IDENTITY PRIMARY KEY,
    text  VARCHAR2(10));
    INSERT INTO t (text) VALUES ('X');
    SELECT * from t;
    ID TEXT     
    -- ----------
    1   X


    Oder etwas aufwendiger mit Startwert 100 und Schrittweite 10 (aber ohne Primärschlüssel):

    CREATE TABLE t (
    id    NUMBER GENERATED BY DEFAULT AS IDENTITY
           (START WITH 100 INCREMENT BY 10),
    text  VARCHAR2(10));


    Möchten Sie eine bestehende Identity löschen ?

    ALTER TABLE t MODIFY
    (id DROP IDENTITY);


    Auch ein nachträgliches Setzen der Identity ist möglich, mit neuer Spalte:

    ALTER TABLE T ADD (ID_NEU NUMBER GENERATED AS IDENTITY
    (START WITH 100));


    Wenn ein bestehender Sequenzwert um 100 erhöht werden soll, kann man die Identity auf der Spalte löschen und einfach neu setzen.

    Sie können pro Tabelle nur eine Identity Spalte festlegen. Wird die Identity Klausel verwendet, dann muss der Datentyp in der Spaltendefinition numerisch sein. Ein benutzerdefinierter Datentyp kann nicht spezifiziert werden.

    Sie müssen nicht zwingend eine Primärschlüsselspalte für die Identity verwenden.
    Das Setzen eines Defaultwertes in der Spaltendefinition ist bei der Verwendung der Identity Klausel nicht erlaubt. Wird die Identity Klausel verwendet, so wird automatisch ein NOT NULL Constraint auf die Spalte gesetzt.

    Wie funktionierts ?
    Oracle legt eine Sequenz im Schema der Tabelle an (z. B. mit Namen ISEQ$$_12345 und als Default mit Cache=20). Einen Insert-Trigger haben wir jedoch nicht entdeckt.

    Sie können feststellen, welche Tabelle Identity Spalten verwendet:

    SELECT owner, table_name, has_identity
    FROM all_tables
    WHERE table_name='T';
            
    OWNER  TABLE_ HAS_IDENTITY
    ------ ------ ------------
    SCOTT  T      YES    


    Oder wenn Sie wissen wollen, welche Spalte vom Typ Identity ist:

    SELECT owner,table_name, column_name, identity_column
    FROM all_tab_columns
    WHERE table_name='T';


    Ausführliche Optionssyntax:

    …( START WITH ( <int> | LIMIT VALUE ) | INCREMENT BY <int>|
    ( MAXVALUE integer | NOMAXVALUE ) |
    ( MINVALUE integer | NOMINVALUE ) |
    ( CYCLE | NOCYCLE ) |
    ( CACHE integer | NOCACHE ) |
    ( ORDER | NOORDER ) }


    Komplexes Beispiel:

    CREATE TABLE T (ID NUMBER
    GENERATED AS IDENTITY (START WITH 1000 INCREMENT
    BY 10 MAXVALUE 10000 CYCLE CACHE 1000 ORDER),
    text VARCHAR2(10));

     

    >100 weitere neue Funktionen der Oracle 12c Version lernen Sie in unserem Oracle 12c Neuerungen Kurs kennen.



    Weitere Interessente Artikel zum Thema:



    Empfohlene Schulungen zum Thema:


    Upgrade und Migration einer Non-Container-DB (12.1.0.2) in eine Container-DB (12.2.0.1)

    Bereich:DBA:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 16.04.2020

    Keywords:Non-Container-DB, Container-DB

    In diesem Tipp geht es um die Migration einer Non-Container Datenbank in die Container Architektur. Dabei soll auch gleichzeitig ein Upgrade auf die Version 12.2.0.1 erfolgen.

    Notwendigkeit des Umstiegs

    Seitdem im März 2013 die Version 12c erschienen ist, wird verstärkt über das zentrale neue Feature "Pluggable Database"
    diskutiert. Für die Einen endlich die Möglichkeit, ihre zahlreichen, kleinen (ähnlich gestrickten) Datenbanksysteme in einem
    zentralen DBMS zu konsolidieren und sich damit die Verwaltung zu vereinfachen, für die Anderen aus den verschiedensten
    Gründen nicht umsetzbar. Nicht zuletzt wegen der anfallenden Lizenzkosten für die Multitenant Option.

    Aber egal, wie sehr man diese neue Architektur auch ablehnt, wer Oracle weiterhin produktiv einsetzen und dabei auch Support
    "genießen" möchte, der kommt um die Migration auf die Container-Architektur nicht herum. Der Oracle Premier Support für 12.1.0.2
    ist (derzeit) bis Mitte 2019 und für 12.2.0.1 bis Mitte 2022 festgelegt worden. Danach wird laut Oracle Dokumentation (vermutlich)
    nur noch die neue Architektur supportet. Hier ein Auszug aus der Doku dazu:

    Note:

    Starting with Oracle Database 12c, release 1 (12.1), non-CDB architecture is deprecated. It
    can be desupported in a future release. Oracle Database deployed with the multitenant
    architecture is the default configuration option. All Oracle Database releases earlier than
    Oracle Database 12c release 1 (12.1.0.1) use non-CDB architecture.

    Oracle strongly recommends that you upgrade your source and target databases to the most
    recent bundle patch or patch set update (BP or PSU) before starting an upgrade ...


    Also wollen wir uns in diesem Monatstipp einmal ansehen, wie der eigentliche Migrations- und Upgrade-Vorgang aussehen
    könnte. An der Stelle sei darauf hingewiesen, dass es verschiedene Alternativen gibt, um eine Migration von Non-CDB in CDB
    durchzuführen, auf die hier nicht ganzheitlich eingegangen wird.

    Folgende Vorbereitungen müssen aber für alle Varianten getroffen werden:

    Die Binaries der Zielversion (12.2.0.1) sind bereits in einem neuen ORACLE_HOME installiert und eine neue (Ziel-)Datenbank (hier
    mit Namen O122CDB) wurde als Container-DB angelegt.

    Aber Achtung: Sobald Sie mehr als eine Pluggable Database innerhalb einer CDB installiert haben, müssen Sie die Multitenant-
    Lizenz erwerben. Falls dies nicht gewünscht ist, muss die CDB zunächst ohne Pluggable Database erzeugt werden. Dort hinein
    wird in den folgenden Schritten die Non-Container Quell-DB (o12c der Version 12.1.0.2) als PDB (pdb_o12c der Version 12.2.0.1)
    eingehängt.

    Die Empfehlung von Oracle, vor dem Upgrade den neuesten Bundle Patch (BP) oder PSU einzuspielen ignorieren wir zunächst
    einmal, was sich allerdings sehr bald rächen soll ...

    Preupgrade Prüfung

    Für die Vorabprüfung eines Upgrades auf 12.2 gibt es ein neues Skript (PREUPGRADE.JAR). Dieses wird aus dem (alten)
    ORACLE_HOME heraus aufgerufen:

    $ cd $ORACLE_HOME_<old>/jdk/bin
    $ ./java -jar $ORACLE_HOME_<new>/rdbms/admin/preupgrade.jar


    Im Idealfall sieht die Rückmeldung folgendermaßen aus:

    Preupgrade generated files:

    /u01/app/oracle/cfgtoollogs/o12c/preupgrade/preupgrade.log
    /u01/app/oracle/cfgtoollogs/o12c/preupgrade/preupgrade_fixups.sql
    /u01/app/oracle/cfgtoollogs/o12c/preupgrade/postupgrade_fixups.sql


    Falls bei der Durchführung Probleme auftreten, sollten die OS-Variablen ORACLE_HOME, PATH, PERL und PERL5LIB überprüft
    werden.

    Durchführung des Upgrades und Fehlerbehandlung

    Im Internet findet man etliche Anleitungen für das Upgrade, allerdings habe ich gemerkt, dass keine davon zusammenfassend
    alle Probleme behandelt, in die ich gelaufen bin, geschweige denn Lösungen dafür anbietet. Der folgende Tipp bietet hoffentlich
    eine hilfreiche Liste der möglichen Schritte und Hindernisse, erhebt jedoch ebenfalls keinen Anspruch auf Darstellung aller
    möglichen Fehler.

    Als Grundlage für den Upgrade, soll an dieser Stelle eine Variante gewählt werden, die zwar in der Doku enthalten, aber nach
    meiner Erkenntnis noch nicht allzu weit verbreitet ist: (siehe Mike Dietrich: Öffnet externen Link in neuem Fensterhttps://mikedietrichde.com/2015/05/18/create
    -a-pdb-directly-from-a-stand-alone-database/ )
    Das remote Klonen einer Non-CDB über die NON$CDB Option mit Database Link.

    Dazu melden Sie sich am Root-Container der Ziel-Datenbank an und erzeugen einen Database Link zur Quell-Datenbank. Achten
    Sie auf den korrekten Eintrag in der TNSNAMES.ORA.

    Der Benutzer, mit dem die Verbindung über den DB-Link zur Quell-DB vorgenommen wird, benötigt das CREATE PLUGGABLE
    DATABASE Recht.
     

    SQL> conn / as sysdba
    SQL> CREATE DATABASE LINK o12c CONNECT TO system
           IDENTIFIED BY <pwd> using 'o12c';


    Nun der erste Versuch des Klonvorgangs:
     

    $ mkdir /u01/app/oracle/oradata/o122cdb/pdb_o12c
    SQL> CREATE PLUGGABLE DATABASE pdb_o12c FROM NON$CDB@o12c
           file_name_convert=('/u01/app/oracle/oradata/o12c',
              '/u01/app/oracle/oradata/o122cdb/pdb_o12c');
    /*
     CREATE PLUGGABLE DATABASE pdb_o12c FROM NON$CDB@o12c ...
    *
    ERROR at line 1:
    ORA-17628: Oracle error 17630 returned by remote Oracle server
    ORA-17630: Mismatch in the remote file protocol version client  server
    */


    Die Internetsuche ergab einen Bug, der durch das Einspielen des Patches 18633374 auf der Quellseite behoben wird (bitte
    Readme dazu lesen).
     

    …
    $ cd /tmp/18633374
    $ opatch apply
    …
    ## CODE ##

    Danach der zweite Versuch:

    ## CODE ##
    SQL> CREATE PLUGGABLE DATABASE pdb_o12c FROM NON$CDB@o12c
           file_name_convert=('/u01/app/oracle/oradata/o12c',
              '/u01/app/oracle/oradata/o122cdb/pdb_o12c');
    /*
     CREATE PLUGGABLE DATABASE pdb_o12c FROM NON$CDB@o12c ...
    *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [25029], [3], [3], [2], [], [], [], [], [], [], [], []
    */


    Dieser Fehler erfordert das Einspielen des aktuellen BP auf der Zielseite. In diesem Fall wird der Patch 27105253 im neuen
    ORACLE_HOME eingespielt (Achtung Readme dazu).
     

    …
    $ cd /tmp/27105253
    $ opatch apply
    …

    SQL> conn / as sysdba
    SQL> startup
    SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

    $ cd $ORACLE_HOME/OPatch
    $ ./datapatch -verbose
    …


    Und der dritte Versuch:
     

    SQL> CREATE PLUGGABLE DATABASE pdb_o12c FROM NON$CDB@o12c
           file_name_convert=('/u01/app/oracle/oradata/o12c',
              '/u01/app/oracle/oradata/o122cdb/pdb_o12c');
    /*
     CREATE PLUGGABLE DATABASE pdb_o12c FROM NON$CDB@o12c ...
    *
    ERROR at line 1:
    ORA-65353: The undo tablespace is missing from the XML metadata file.
    */


    Das Problem ist in diesem Fall der in 12.2 standardmäßig eingestellte LOCAL UNDO Modus. Der UNDO-Tablespace aus der Quell-
    DB kommt nicht mit, wird aber im Ziel erwartet. Also wird temporär LOCAL UNDO in der Ziel-DB auf FALSE gesetzt.
     

    SQL> shutdown immediate
    SQL> startup upgrade
    SQL> ALTER DATABASE LOCAL UNDO OFF;
    SQL> shutdown immediate
    SQL> startup


    Aber jetzt, der vierte Versuch - na geht doch:
     

    SQL> CREATE PLUGGABLE DATABASE pdb_o12c FROM NON$CDB@o12c
           file_name_convert=('/u01/app/oracle/oradata/o12c',
              '/u01/app/oracle/oradata/o122cdb/pdb_o12c');
    -- Pluggable database created.


    In jedem Fall sollte man sich die PDB_PLUG_IN_VIOLATIONS View ansehen, um über WARNINGs und ERRORs informiert zu
    werden.
     

    SQL> col message for a200
         col action for a150
         col cause for a20
         col time for a30
         col name for a10
    SQL> SELECT time, name, cause, type, message, status, action
           FROM pdb_plug_in_violations;

    ...
    VSN not match
    ERROR
    PDB's version does not match CDB's version: PDB's version 12.1.0.2.0. CDB's version 12.2.0.1.0.
    PENDING
    Either upgrade the PDB or reload the components in the PDB.
    ...
    Non-CDB to PDB       
    WARNING             
    PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
    PENDING       
    Run noncdb_to_pdb.sql.


    Also muss die Pluggable Database PDB_O12C zunächst upgegradet werden. Dies erfolgt über den Aufruf des Perl-Skripts
    CATCTL.PL
     

    SQL> ALTER SESSION SET CONTAINER=pdb_o12c;
    SQL> ALTER PLUGGABLE DATABASE pdb_o12c OPEN UPGRADE;

    $ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
    $ export ORACLE_SID=o122cdb
    $ export PERL=$ORACLE_HOME/perl/bin
    $ export PERL5LIB=$ORACLE_HOME/rdbms/admin
    $ export PATH=$ORACLE_HOME/bin:$PERL:$PATH
    $ cd $ORACLE_HOME/rdbms/admin

    $ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'PDB_O12C' catupgrd.sql

    /*
    Argument list for [catctl.pl]
    Run in                c = PDB_O12C
    …

    catctl.pl VERSION: [12.2.0.1.0]
               STATUS: [production]
                BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]

    /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/orahome =
    [/u01/app/oracle/product/12.2.0.1/dbhome_1]
    /u01/app/oracle/product/12.2.0.1/dbhome_1/bin/orabasehome =
    [/u01/app/oracle/product/12.2.0.1/dbhome_1]
    catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1/dbhome_1]

    Analyzing file /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catupgrd.sql
    …

    *****************   Post Upgrade   *****************
    °Serial   Phase #:112  [PDB_O12C] Files:1    Time: 55s
    ****************   Summary report   ****************
    Serial   Phase #:113  [PDB_O12C] Files:1    Time: 1s
    Serial   Phase #:114  [PDB_O12C] Files:1    Time: 25s
    Serial   Phase #:115  [PDB_O12C] Files:1     Time: 0s

    ------------------------------------------------------
    Phases [0-115]         End Time:[2018_01_30 11:33:48]
    Container Lists Inclusion:[PDB_O12C] Exclusion:[NONE]
    ------------------------------------------------------

    Grand Total Time: 1620s [PDB_O12C]
    …
    */


    Da sich die neue PDB im Modus RESTRICTED befindet, muss anschließend das Skript NONCDB_TO_PDB.SQL aufgerufen werden

    SQL> ALTER SESSION SET container=pdb_o12c;
    SQL> @?/rdbms/admin/noncdb_to_pdb.sql


    Falls beim erneuten Versuch die PDB zu öffnen eine Warnung kommt
     

    SQL> startup
    Warning: PDB altered with errors.
    Pluggable Database opened.
    sollte folgendermaßen vorgegangen werden:
    SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
    SQL> ALTER PLUGGABLE DATABASE pdb_o12c CLOSE;
    SQL> ALTER PLUGGABLE DATABASE pdb_o12c OPEN RESTRICTED;
    SQL> exec dbms_pdb.sync_pdb();
    SQL> ALTER PLUGGABLE DATABASE pdb_o12c CLOSE;
    SQL> ALTER PLUGGABLE DATABASE pdb_o12c OPEN;


    Zu überprüfen ist, ob der STATUS bei allen Meldungen von PENDING auf RESOLVED geändert wurde. Falls nicht, gibt die Spalte
    ACTION Auskunft darüber, was zu tun ist. Nun sollte sich die PDB (ohne Probleme, Warnungen oder Fehler) öffnen lassen.

    Tipp: Überprüfen Sie ebenfalls die ungültigen Objekte und, falls welche vorhanden, versuchen Sie diese zu rekompilieren.
     

    SQL> SELECT COUNT(*) FROM dba_invalid_objects;
    SQL> @?/rdbms/admin/utlrp.sql


    Falls Sie wieder auf den LOCAL UNDO Modus wechseln wollen, gehen Sie analog zum Ausschalten vor.
     

    SQL> shutdown immediate
    SQL> startup upgrade
    SQL> ALTER DATABASE LOCAL UNDO ON;
    SQL> shutdown immediate
    SQL> startup


    Oracle erzeugt dabei automatisch einen UNDO-Tablespace für die PDB. Dazu der Auszug aus der Alertdatei:
     

    ...
    PDB_O12C(3):CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/o122cdb/pdb_o12c/system01_i1_undo.dbf'
    SIZE 104857600 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
    ...


    FAZIT

    Sie haben nun einen Eindruck davon bekommen, was auf Sie zukommen kann, wenn Sie auf die Container-Architektur umstellen
    und dabei auch gleich noch einen Versions-Upgrade durchführen möchten.

    Es sei an dieser Stelle aber noch einmal ausdrücklich erwähnt, dass dieser Tipp keine Besonderheiten wie RAC, Data Guard, ASM
    oder andere Oracle Optionen berücksichtigt. Deshalb kann nicht vorhergesagt werden, welche zusätzlichen Hürden sich Ihnen
    in den Weg stellen …



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Oracle JSON Date Datentyp Problem

    Bereich:PL/SQL:SQL, Version: ab RDBMS 21.1, Letzte Überarbeitung: 14.01.2021

    Keywords:Oracle JSON json_serialize json_scalar

    Wer schon mal mit dem JSON Date Datentyp gearbeitet hat, ist sich dessen Problemen sicherlich bewusst.
    Wir wollen uns im nachfolgenden Atrikel dem Problem mal annehmen:

    Nehmen wir mal die in Oracle 21c neue eingeführte Funktion json_scalar und json_serialize

    JSON_SCALAR wandelt einen Text/ ein Datum oder eine Anzahl in eine interne BLOB Repräsentanz.

    SELECT json_scalar(date '2021-01-01') as datum FROM dual;
    DATUM                                                                           
    --------------------------------------------
    22323032312D30312D30315430303A30303A303022


    Wenn wir das zurückwandeln passiert folgendes:

    SELECT json_serialize(  (date '2021-01-01')) as datum FROM dual;

    DATUM                                                                                                           
    ---------------------
    "2021-01-01T00:00:00"


    Da fallen einem gleich zwei Problemzonen ins Auge:
    1. "" am Anfang / Ende
    2. ein T zwischen Datum und Uhrzeit

    Der erste Versuch scheitert deswegen auch:

    select to_date(json_serialize(json_scalar(date '2021-01-01')),'YYYY-MM-DD"T"HH24:MI:SS') as scalar_datum
    from   dual;

    ORA-01841: (Volles) Jahr muss zwischen -4713 und +9999 liegen und darf nicht 0 sein
    01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"


    Das liegt an der etwas eigenartigen Behandlung von Gänsefüßen bei Oracle Strings

    select to_date('"01.01.01"') from dual;
    ORA-01858: Ein nicht numerisches Zeichen wurde gefunden, während ein numerisches Zeichen erwartet wurde
    01858. 00000 -  "a non-numeric character was found where a numeric was expected"

    oder auch

    select to_date('"01.01.01"','"DD.MM.YY"') from dual;
    RA-01861: Literal stimmt nicht mit Formatzeichenfolge überein
    01861. 00000 -  "literal does not match format string"

    auch das geht schief:

    select to_date('"01.01.01"','"""DD.MM.YY"""') from dual;
    ORA-01861: Literal stimmt nicht mit Formatzeichenfolge überein
    01861. 00000 -  "literal does not match format string"


    Deshalb lösen wir das Problem mit zwei Funktionen:
    1. Wir ersetzen "" durch nix
    2. Wir konvertieren den String mit Hilfe der Funktion to_date und dem Format String 'YYYY-MM-DD"T"HH24:MI:SS'

    select
    to_date(replace(json_serialize(json_scalar(date '2021-01-01')),'"',''),'YYYY-MM-DD"T"HH24:MI:SS') as scalar_datum
    from   dual;

    SCALAR_DATUM       
    -------------------
    01.01.2021 00:00:00


    Weitere Tipps erhalten Sie in einem unserer Oracle Kurse...



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Oracle SQL Insert

    Bereich:SQL, Letzte Überarbeitung: 28.07.2020

    Keywords:SQL Insert

    Wie fügt man eine Zeile in die Oracle Datenbank ein?

    Zuerst legen wir uns mal eine Tabelle an:

    CREATE TABLE mitarbeiter (
      mitarb_id                 NUMBER,
      mitarb_name               VARCHAR2(100),
      mitarb_einstellungs_datum date );


    1. Single Line Insert (fügt nur eine Zeile ein), kurze Variante:

    INSERT INTO mitarbeiter VALUES (1,'Patzwahl',sysdate);

    Der Datensatz ist noch persistent in der DB gespeichert, im Gegensatz zu Postgres Datenbanken !
    Erst ein Commit, schreibt die Daten permanent in die Tabelle

    COMMIT;


    Bei Tabellen mit vielen Spalten wird diese Variante schnell unübersichtlich. Deswegen empfiehlt sich

    2. Single Line Insert, lange Variante

    INSERT INTO mitarbeiter (mitarb_id, mitarb_name, mitarb_einstellungs_datum)
      VALUES (1, 'Patzwahl', sysdate);


    Bei dieser Variante wurden die Spalten mit angegeben. Das hat mehrere Vorteile:

    • Man sieht welche Spalten in der Tabelle gefüllt werden
    • Es müssen nicht alle Spalten der Tabelle gefüllt werden
    • Die Reihenfolge bei der Befüllung der Tabellenspalten kann beliebig durchgeführt werden

    3. Multi Line Insert (hier werden eine oder mehrere Zeilen aus einer anderen Tabelle kopiert)

    Voraussetzung ist, dass es eine Tabelle mit Namen mitarbeiter_muenchen gibt, die über die gleiche Spaltenzahl und Spaltentypen verfügt.

    INSERT INTO mitarbeiter SELECT * FROM mitarbeiter_muenchen:


    4. Die Zieltabelle kann auch auf einer entfernt liegenden Oracle Datenbank liegen. Dazu muss nur ein [Public] Database Link vorhanden sein. Dieser meldet sich bei der Datenbank rcl auf dem Server 162.20.10.1 via Port 1521 als Benutzer SCOTT mit Passwort tiger an.

    CREATE DATABASE LINK remote_db CONNECT TO scott IDENTIFIED BY tiger
      USING '162.20.10.1:1521/orcl';

    INSERT INTO mitarbeiter@remote_db SELECT * FROM mitarbeiter_muenchen:


    Oder die Daten sollen lokal in einer Tabelle gespeichert werden, die Ursprungsdaten liegen aber Remote:

    INSERT INTO mitarbeiter SELECT * FROM mitarbeiter_muenchen@remote_db:


    SQL lernen ist nicht schwer, kommen Sie einfach zu uns her:-)

    Wir haben auch für Sie die passende Schulung im Programm.



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Datumskonvertierungen und Datumsformate in Oracle

    Bereich:PL/SQL:SQL, Letzte Überarbeitung: 26.11.2022

    Keywords:to_date,to_char, Datum

    Wer hat nicht schon mal vor dem Problem einer Datumskonvertierung bei Oracle Datenbanken gestanden?
    Da kann sehr viel Zeit verstreichen, bis man eine Lösung gefunden hat. Wir wollen Ihnen hier ein Paar Fallstricke erklären.

    Welches Datumsformat wird als Default ausgegeben?

    Das hängt vom Parameter nls_date_format ab, der auf Clientseite im Betriebssystem gesetzt wird.

    z.B. können Sie unter DOS in einem Fenster angeben:

    dos> set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
    dos> sqlplus scott/tiger

    SELECT sysdate FROM dual;

    SYSDATE
    -------------------
    31.01.2021 11:12:59


    Für Linux:

    $> export NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
    $> sqlplus scott/tiger

    SELECT sysdate FROM dual;

    SYSDATE
    -------------------
    31.01.2021 11:12:59


    Im SQL*Developer kann stattdessen auch unter Extras/Voreinstellungen/Datenbank/NLS das Datumsformat eingestellt werden.

    Die Konvertierung in einem SQL-Befehl

    Wenn Sie sich in einzelnen Befehlen nicht an den Default halten möchten, können Sie eine Ausgabeformatierung verwenden:

    SELECT to_char(sysdate,'DD.MM.YYYY HH24:MI:SS') FROM dual;


    Achten Sie darauf, dass die Minuten mit "MI" gekennzeichnet sind. Für Oracle wäre auch folgendes Format OK:

    SELECT to_char(sysdate,'Day.Month.Year HH:MM:SS') AS datum FROM dual;

    DATUM
    --------------------------------------------
    Friday   .June     .Twenty Eighteen 11:06:17


    Nur leider ist das Format falsch, weil im String "HH:MM:SS" Stunden:Monat:Sekunden ausgegeben werden, und das stimmt nur ganz selten mit der tatsächlichen Uhrzeit überein (eine Minute pro Stunde :-)

    Es gibt eine riesige Liste an möglichen Datumskonvertierungen die hier nachgelesen werden können.

    Nur ein paar Beispiele für Oracle Datumsformate:

    • DD   Tagnummer (0-31)
    • DAY Tagesname  (Montag)
    • DY Tagesname, kurz (DO)
    • DDD Tagesnummer im Jahr (0-366)
    • MM   Monat (1-12)
    • MONTH   Monatsname ( Mai)
    • YY  Jahreszahl zweistellig
    • YYYY Jahreszahl vierstellig

    Datum für Oracle eingeben

    Wenn Sie ein Datum in die Oracle Datenbank einpflegen möchten, sollten Sie das idealerweise auch mit einer expliziten Datums-Konvertierung tun.

    Beispiel:

    INSERT INTO emp (empno,ename,hiredate) VALUES
      (8000, 'MARCO', to_date('30.12.2020','DD.MM.YYYY'));


    Hier werden Stunden, Minuten und Sekunden auf 0 gesetzt. Das spart zwar keinen Speicherplatz, erleichtert aber später die Suche nach einem Einstellungstag. Wenn Sie stattdessen schreiben:

    INSERT INTO emp (empno,ename,hiredate) VALUES (8000, 'MARCO', sysdate);


    Dann wird die aktuelle Uhrzeit im Datum mit gespeichert. Jetzt versuchen Sie mal nach Mitarbeitern zu suchen, die heute eingestellt wurden:

    SELECT * FROM emp WHERE hiredate=sysdate; -- FALSCH
    SELECT * FROM emp WHERE trunc(hiredate)=trunc(sysdate); -- RICHTIG


    Nur sollte es einen normalen Index auf der Spalte hiredate geben, wird er nicht benutzt werden. Deswegen könnte man folgende Variante benutzen:

    SELECT * FROM emp
     WHERE hiredate BETWEEN trunc(sysdate)
                        AND trunc(sysdate+INTERVAL '1' HOUR - INTERVAL '1' SECOND);


    Hier suchen Sie nach allen Mitarbeitern, die zwischen 00:00.00 und 23:59:59 des aktuellen Tages eingestellt wurden.


    Für Freunde des JSON Datentyps: JSON verwendet gerne folgendes Datumsformat: 01.08.2022T12:30:39Z
    Man beachte das T in der Mitte und das Z am Ende.
    Das können wir wieder in ein Oracle Datumsformat zurückwandeln:

    SELECT to_date('01.12.2022T12:30:39Z','DD.MM.YYYY"T"HH24:MI:SS"Z"') FROM dual;



    Hat das Ihre Lust auf noch mehr SQL Tipps geweckt? Wir haben bestimmt auch die passende Schulung für Sie!



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    SQL Tuning mit SAMPLE und ROWNUM

    Bereich:SQL, Version: ab RDBMS 12.x:RDBMS 19.3:RDBMS 21.1, Letzte Überarbeitung: 04.07.2018

    Keywords:SQL, Tuning, ROWNUM, SAMPLE

    Mit der SAMPLE-Klausel kann die SQL-Ergebnismenge von 0,000001% bis 100% der ursprünglichen reduziert werden. Oracle greift einfach zufällig in die Ergebnismenge und holt (wählt) einen gewissen Prozentsatz der Daten heraus.In der Schule nannte man das Experiment: Ziehen mit zurücklegen :-)

    Schauen wir uns ein paar Beispiele an:

    SELECT count(*) FROM big
    SAMPLE (0.001);

     

      erster Versuch  zweiter Versuch  dritter Versuch 
     Rückgabe 11106


    Man sieht also, die Rückgabemenge kann durch statistische Abweichungen mal mehr, mal weniger Zeilen zurückliefern.

    Mit dem SEED-Parameter kann die Ergebnismenge auf einer Speicherplatznummer gespeichert werden (hier: Position 1)

    SELECT name FROM big SAMPLE (0.0001) SEED (1);


    Ergebnis:

    ORD_DICOM
    WWV_FLOW_PREFERENCES
    DBMS_SQLTUNE

     

    SELECT name FROM big SAMPLE (0.0001) SEED (2);


    Ergebnis:

    DBMS_STATS


    Wenn Sie die erste Ergebnismenge nochmal benötigen, setzen Sie einfach das Statement mit SEED(1) nochmal ab:

    SELECT name FROM big
    SAMPLE (0.0001) SEED (1);


    Ergebnis:

    ORD_DICOM
    WWV_FLOW_PREFERENCES
    DBMS_SQLTUNE


    Wir konnten bei unseren Tests bis Speicheradresse SEED(4294967295) gehen. Nach dieser Speicheradresse wurden die Ergebnismengen nicht mehr geändert. Also egal welche SEED-Adresse darüber hinaus Sie verwenden, Sie bekommen immer die Daten der Nummer SEED(4294967295) zu sehen.

    Sie können in das Statement auch eine WHERE-Klausel einbauen:

    SELECT name FROM big SAMPLE(0.01) WHERE name LIKE 'A%';
     

    Auch ein Alias Name für die Tabelle ist möglich (wenn auch an eigenartiger Stelle):

    SELECT name FROM big SAMPLE(0.01) b WHERE name LIKE 'A%';


    Auch bei Joins steht die Sample-Klausel direkt hinter dem Tabellennamen:

    SELECT * FROM emp SAMPLE(10), dept
     WHERE emp.deptno=dept.deptno;


    Hier endet auch leider der Nutzen der SAMPLE-Klausel, denn sobald Sie komplexere Statements haben, funktioniert sie nicht mehr!

    Gut, dann verwenden wir wieder die altbewährte ROWNUM-Klausel. Wenn Sie verschiedene Varianten eines SQL-Statements austesten möchten und dabei feststellen, jeder Versuch dauert mehr als 3 Stunden, werden Sie diese Möglichkeit zu schätzen wissen.    

    Wenn das Statement also lautet:

    SELECT empno, ename, sal, loc FROM emp, dept
     WHERE emp.deptno=dept.deptno;


    Machen wir daraus:

    SELECT * FROM ( SELECT empno, ename, sal, loc
                      FROM emp, dept WHERE emp.deptno=dept.deptno )
     WHERE rownum<10;
     

    Und Statements mit WITH-Klausel?

    WITH t as (select deptno, loc FROM dept)
    SELECT e.empno, e.ename, t.loc, t.deptno FROM t, emp e
     WHERE e.deptno=t.deptno;


    Ab Oracle 12.2:

    SELECT * FROM (
                    WITH t as ( SELECT deptno,loc FROM dept )
                    SELECT e.empno, e.ename, t.loc, t.deptno FROM t, emp e
                     WHERE t.deptno=e.deptno )
     WHERE rownum<10;


    Oder alternativ:

    WITH t as ( SELECT deptno, loc FROM dept )
    SELECT * FROM (   -- ### Hier wurde die Zusatzklausel eingefügt
                    SELECT e.empno, e.ename, t.loc, t.deptno FROM t, emp e
                     WHERE t.deptno=e.deptno )
     WHERE rownum<10; -- ### Hier endet die Zusatzklausel


    Wenn die Ergebnismenge sehr groß ist, kann man auch nur einen COUNT(*) verwenden.

    Dieser muss auch die ganze Ergebnismenge durchgehen, sich aber nicht mit der (lästigen und langwierigen) Zeilenausgabe beschäftigen:

    SELECT count(*) FROM (
                           WITH t as (SELECT deptno, loc FROM dept)
                           SELECT e.empno, e.ename, t.loc, t.deptno FROM t, emp e
                            WHERE t.deptno=e.deptno )
     WHERE rownum<10;


    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Neuerungen zu Joins in Oracle Version 12c

    Bereich:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 13.01.2021

    Keywords:Joins, Oracle Neuerungen, SQL, 12C Release 1

    Da wir immer wieder nach der Syntax zu (Outer) Joins gefragt werden, beschäftigen wir uns in diesem Tipp einmal mit den Neuerungen in Version 12c und den Einschränkungen in den vorangegangenen Versionen.

    Vorausgesetzt wird, dass der Leser bereits Erfahrungen mit der Formulierung von Joins gemacht hat und ihm auch die in Oracle 9i eingeführte ANSI-konforme Join Syntax nicht unbekannt ist.

    AUFHEBUNG DER ORA-01417 EINSCHRÄNKUNG

    Beginnen wir mit einer Einschränkung, mit der Entwickler und Anwender bis einschließlich Version 11gR2 konfrontiert wurden: Wer über die Oracle-eigene Syntax zwei oder mehr Tabellen mit einer anderen Tabelle über Outer Join verknüpfen wollte, bekam in der Regel einen ORA-01417 Fehler.

    Anhand der Tabellen EMP, DEPT und SALGRADE des Benutzers SCOTT soll dies im weiteren Verlauf demonstriert werden. Dabei wurde die Tabelle SALGRADE um eine weitere Zeile ergänzt, die keine Entsprechung in der Tabelle EMP findet.

    SQL> INSERT INTO scott.salgrade VALUES (6, 10000, 12999);

    SQL> SELECT * FROM scott.salgrade;

         GRADE      LOSAL      HISAL
    ---------- ---------- ----------
             1        700       1200
             2       1201       1400
             3       1401       2000
             4       2001       3000
             5       3001       9999
             6      10000      12999
             
    6 Zeilen ausgewählt.

    Die beiden Tabellen DEPT und SALGRADE sollen jeweils durch einen Outer Join mit EMP verbunden werden, was auf folgende Weise bisher noch nicht möglich war:

    SQL> SELECT d.deptno, e.ename, s.grade
           FROM scott. dept d, scott.emp e, scott.salgrade s
          WHERE d.deptno = e.deptno(+)
            AND e.sal(+) BETWEEN s.losal AND s.hisal;

    ORA-01417: Tabelle darf mit max. einer anderen Tabelle durch Outer-Join verbunden werden

    Mit der Version 12c ist diese Einschränkung aufgehoben worden. Macht man sich anhand der Ergebnismenge allerdings klar, welche Logik hinter dieser Syntax steht, stellt sich die Frage, ob und wann dies wirklich gewünscht ist.

    SQL> SELECT d.deptno, e.ename, s.grade
           FROM dept d, emp e, salgrade s
          WHERE d.deptno = e.deptno(+)
            AND e.sal(+) BETWEEN s.losal AND s.hisal;

        DEPTNO ENAME           GRADE
    ---------- ---------- ----------
            20 SMITH               1
            30 ALLEN               3
            30 WARD                2
            20 JONES               4
            30 MARTIN              2
            30 BLAKE               4
            10 CLARK               4
            20 SCOTT               4
            10 KING                5
            30 TURNER              3
            20 ADAMS               1
            30 JAMES               1
            20 FORD                4
            10 MILLER              2
            10                     6
            10                     3
            10                     1
            30                     6
            30                     5
            20                     6
            20                     5
            20                     3
            20                     2
            40                     6
            40                     5
            40                     4
            40                     3
            40                     2
            40                     1

    29 Zeilen ausgewählt.

    Offensichtlich erzeugt Oracle ein Kreuzprodukt zwischen den beiden Outer-Tabellen DEPT und SALGRADE und gibt davon alle Zeilen aus, die keine direkte Entsprechung zu EMP haben. Bereits vor Version 12c hätte man das selbe Ergebnis über den Trick einer Inline View erhalten können:

    SQL> SELECT i.deptno, e.ename, i.grade
           FROM scott.emp e, (SELECT d.deptno, s.grade, s.losal, s.hisal
                                FROM scott.dept d, scott.salgrade s) i
          WHERE i.deptno = e.deptno(+)
            AND e.sal(+) BETWEEN i.losal AND i.hisal;

    Bezogen auf die drei Tabellen von SCOTT, möchte man - sinnvollerweise - zu den 14 Mitarbeitern lediglich noch die Abteilungen aus DEPT sehen, in denen keine Mitarbeiter sind (hier: 40) und die Gehaltsstufen aus SALGRADE in denen sich kein Gehalt aus EMP befindet (hier: 6).

    Mittels ANSI-Join-Syntax lautet die - recht übersichtliche - Lösung dazu bereits seit Version 9i:

    SQL> SELECT d.deptno, e.ename, s.grade
           FROM scott.dept d LEFT JOIN scott.emp e     
                   ON d.deptno = e.deptno
                             FULL JOIN scott.salgrade s 
                   ON e.sal between s.losal and s.hisal;

        DEPTNO ENAME           GRADE
    ---------- ---------- ----------
            40
            10 KING                5
            20 FORD                4
            20 SCOTT               4
            20 JONES               4
            30 BLAKE               4
            10 CLARK               4
            30 ALLEN               3
            30 TURNER              3
            10 MILLER              2
            30 WARD                2
            30 MARTIN              2
            20 ADAMS               1
            30 JAMES               1
            20 SMITH               1
                                   6

    16 Zeilen ausgewählt.

    Über die Oracle-eigene Syntax sieht das schon wieder wesentlich komplizierter aus (und mit zunehmender Tabellenanzahl wird dies nicht besser ...):

    SQL> SELECT d.deptno, e.ename, s.grade
           FROM scott.dept d, scott.emp e, scott.salgrade s
          WHERE d.deptno = e.deptno(+)
            AND e.sal BETWEEN s.losal(+) AND s.hisal(+)
    UNION
         SELECT d.deptno, e.ename, s.grade
           FROM scott.dept d, scott.emp e, scott.salgrade s
          WHERE d.deptno(+) = e.deptno
            AND e.sal(+) BETWEEN s.losal AND s.hisal;
    NEUERUNGEN DER ANSI-JOIN SYNTAX

    Mit Version 12c hat Oracle drei neue Varianten im Bereich der ANSI-Join Syntax aufgenommen.

    • CROSS APPLY
    • OUTER APPLY
    • LATERAL

    Das Interessante an allen drei Neuerungen ist die Unterstützung der sog. Left Correlation. Dabei kann z. B. aus der FROM-Klausel innerhalb einer Inline View auf Werte aus einer anderen Tabelle der äußeren FROM-Klausel Bezug genommen werden. Diese andere Tabelle muss sich links von den neuen Schlüsselwörtern APPLY oder LATERAL befinden. Daher der Name Left Correlation.

    CROSS APPLY

    Der CROSS APPLY ist eine Erweiterung des CROSS JOIN und bildet in der einfachsten Form ein Kreuzprodukt der beteiligten Tabellen. Beispiel:

    SQL> SELECT d.deptno, d.dname, e.ename
           FROM scott.dept d CROSS APPLY scott.emp e;

        DEPTNO DNAME          ENAME
    ---------- -------------- ----------
            10 ACCOUNTING     SMITH
            10 ACCOUNTING     ALLEN
    ...
            40 OPERATIONS     FORD
            40 OPERATIONS     MILLER

    56 Zeilen ausgewählt.

    Verwendet man den CROSS APPLY jedoch in Zusammenhang mit einer Inline View, ergibt sich eine wesentlich reizvollere Alternative. Dazu sollen im nächsten Beispiel ausschließlich Zeilen der (linken) Tabelle DEPT zurückkommen, die auch eine Entsprechung innerhalb der Inline View besitzen. Relevant sind nur die Abteilungen RESEARCH, SALES und OPERATIONS.

    SQL> SELECT d.deptno, d.dname, i.ename
           FROM scott.dept d CROSS APPLY (SELECT * FROM scott.emp e
                                           WHERE e.deptno = d.deptno) i
          WHERE d.dname IN ('RESEARCH', 'SALES', 'OPERATIONS');

        DEPTNO DNAME          ENAME
    ---------- -------------- -------
            20 RESEARCH       SMITH
            30 SALES          ALLEN
            30 SALES          WARD
            20 RESEARCH       JONES
            30 SALES          MARTIN
            30 SALES          BLAKE
            20 RESEARCH       SCOTT
            30 SALES          TURNER
            20 RESEARCH       ADAMS
            30 SALES          JAMES
            20 RESEARCH       FORD

    11 Zeilen ausgewählt.

    Die Abteilung OPERATIONS kommt nicht zurück, da es über die Inline View keinen Treffer zur Abteilungsnummer 40 gibt.

    OUTER APPLY

    Dabei handelt es sich um eine Variante des LEFT OUTER JOIN. Alle Zeilen der linken Tabelle sollen ausgegeben werden, unabhängig davon, ob es dazu einen Treffer innerhalb der Inline View gibt oder nicht.

    SQL> SELECT d.deptno, d.dname, i.ename
           FROM scott.dept d OUTER APPLY (SELECT * FROM scott.emp e
                                           WHERE e.deptno = d.deptno) i
          WHERE d.dname IN ('RESEARCH', 'SALES', 'OPERATIONS');

        DEPTNO DNAME          ENAME
    ---------- -------------- -------
            20 RESEARCH       SMITH
            30 SALES          ALLEN
            30 SALES          WARD
            20 RESEARCH       JONES
            30 SALES          MARTIN
            30 SALES          BLAKE
            20 RESEARCH       SCOTT
            30 SALES          TURNER
            20 RESEARCH       ADAMS
            30 SALES          JAMES
            20 RESEARCH       FORD
            40 OPERATIONS

    12 Zeilen ausgewählt.

    Im Gegensatz zum CROSS APPLY-Beispiel erhält man nun auch die Abteilung 40, auch wenn sie keinen Mitarbeiter besitzt.

    LATERAL

    LATERAL bietet eine Alternative zum Equi- oder Inner Join. Dabei kann auch hier über eine Inline View ein Bezug zur äußeren (linken) Tabelle der FROM-Klausel her

    Bind Variable oder Substitutionsvariable?

    Bereich:SQL, Version: ab RDBMS 8.X, Letzte Überarbeitung: 11.12.2018

    Keywords:Bind Variable, Substitutionsvariable

    1. Fangen wir mal mit der Substitutionsvariable an:

    Wir definieren uns eine Textvariable (etwas anderes gibt es auch nicht)

    SQL> DEFINE my_ename='KING'

    Diese können wir nun in einem SELECT einsetzen:

    SQL> SELECT sal FROM emp
    WHERE ename='&my_ename.';
    alt   2: WHERE ename='&my_ename.'
    neu   2: WHERE ename='KING'

           SAL
    ----------
          5000

         
    Die alt und neu ... stört etwas, aber die bekommen wir weg:

    SET VERIFY OFF

    SELECT sal FROM emp
    WHERE ename='&my_ename.';

           SAL
    ----------
          5000


          
    Mit DEFINE bekommen sie alle gesetzten Variablen angezeigt:

    SQL> define
    DEFINE _DATE           = "11.10.2018 16:18:05" (CHAR)
    DEFINE _CONNECT_IDENTIFIER = "o18c" (CHAR)
    DEFINE _USER           = "SCOTT" (CHAR)
    DEFINE _PRIVILEGE      = "" (CHAR)
    DEFINE _SQLPLUS_RELEASE = "1803000000" (CHAR)
    DEFINE _EDITOR         = "Notepad" (CHAR)
    DEFINE _O_VERSION      = "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
    Version 18.3.0.0.0" (CHAR)
    DEFINE _O_RELEASE      = "1803000000" (CHAR)
    DEFINE MY_ENAME        = "KING" (CHAR)      

    Die Variable bleibt nun bis zum Sessionende am Leben, es sei denn sie löschen sie wieder:

    SQL> UNDEFINE my_ename

    Was machen wir, wenn wir denn Wert der Variablen vom Benutzer erfragen möchten:

    Lösung 1:

    SQL> SELECT sal FROM emp
    WHERE ename='&my_ename.';
    Geben Sie einen Wert f³r my_ename ein: KING

           SAL
    ----------
          5000

        
    Der Inhalt der Variablen wird nach dem Einsatz gelöscht:

    SQL> define my_ename
    SP2-0135: Symbol my_ename ist nicht definiert (UNDEFINED)

    Lösung 2:

    SQL> SELECT sal FROM emp
    WHERE ename='&&my_ename.';
    Geben Sie einen Wert f³r my_ename ein: KING

           SAL
    ----------
          5000

    Hier wird der Inhalt der Variablen bis zum Sessionende (oder UNDEFINE) aufgehoben:

    SQL> define my_ename
    DEFINE MY_ENAME        = "KING" (CHAR)

    Lösung 3: Der Benutzer soll höflich nach dem Wert gefragt werden:

    accept my_ename DEFAULT 'KING' PROMPT "Hätten Sie die Güte, den Namen rauszurücken? "

    Hinweis:
    1. der DEFAULT Wert ist Optional.
    2. wenn hinter das Statement noch HIDE geschrieben wird, ist die Eingabe verdeckt möglich.

    Was machen wir, wenn der Wert der Variablen aus einem Ergebnis einer Query zurückkommt?

    SQL> COL my_ename NEW_VALUE my_ename
    SQL> SELECT ename As my_ename FROM emp
    WHERE empno=7839;

    MY_ENAME
    ----------
    KING

    SQL> define my_ename
    DEFINE MY_ENAME        = "KING" (CHAR)

    Und nun zu den Bindvariablen:
    Diese können nur im Filter einer Query eingesetzt werden, sind dafür aber auch in PL/SQL nutzbar.

    Wir definieren die Bindvariable mit einem Datentyp:

    SQL> VARIABLE my_ename VARCHAR2(100)

    Danach füllen wir die Variable mit einem Wert:

    SQL> EXEC :MY_ENAME:='KING';

    Wir setzen den Bindvariablen-Wert in einer Query ein:

    SQL> SELECT sal FROM emp
         WHERE ename=:MY_ENAME
    SQL> /

           SAL
    ----------
          5000

         
    Oder befüllen die Variable direkt aus einer Query

    SQL> EXEC SELECT ename INTO :my_ename FROM emp WHERE sal=5000;



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Nützliche Parameter von SYS_CONTEXT

    Bereich:SQL, Version: ab APEX 4.x, Letzte Überarbeitung: 23.05.2022

    Keywords:SQL

    Als DBA steht man oft vor dem Problem der Rechtevergabe an die Benutzer. Zu viele Rechte stellen ein Sicherheitsrisiko dar, zu wenige verhindern möglicherweise das Abfragen wichtiger Daten. Damit normale Benutzer Informationen über die genutzte Instanz abfragen können, ohne dass ihnen Rechte an den nötigen v$-Views zugewiesen werden müssen, gibt es eine Funktion namens SYS_CONTEXT.

    In diesem Tipp werden von rund 60 Parametern nur die nützlichsten kurz erläutert und die seit Version 12c neu hinzugefügten Parameter aufgezeigt. Diese Parameter sind sehr hilfreich z. B. bei der Erstellung von Automatismen über Skripte.

    Mit der Funktion SYS_CONTEXT ist es möglich, Werte verschiedener Parameter der aktuellen Instanz abzufragen. Die Parameter sind verbunden mit einem bestimmten Namensraum. Oracle hat standardmäßig zwei Namensräume integriert, nämlich USERENV, der die aktuelle Session beschreibt, und SYS_SESSION_ROLES (verfügbar ab 11.2.0.4), der anzeigt, ob eine bestimmte Rolle für die aktuelle Session aktiv ist.

    Ein Beispiel für eine Abfrage mit USERENV, ausgegeben wird der Name der Datenbank:

    SQL> select sys_context ('USERENV', 'DB_NAME') from dual;
    SYS_CONTEXT('USERENV','DB_NAME')
    ---------------------------------------------------------
    o11g


    Die folgende Abfrage zeigt mithilfe von SYS_SESSION_ROLES, ob der angemeldete Benutzer die DBA-Rolle innehat:

    SQL> show user
    USER ist "SCOTT"
    SQL> select sys_context('SYS_SESSION_ROLES', 'DBA') from dual;
    SYS_CONTEXT('SYS_SESSION_ROLES','DBA')
    --------------------------------------------------------------
    FALSE
    SQL> conn sys/<pwd> as sysdba;
    SQL> grant dba to scott;
    SQL> conn scott/tiger
    SQL> select sys_context('SYS_SESSION_ROLES', 'DBA') from dual;
    SYS_CONTEXT('SYS_SESSION_ROLES','DBA')
    --------------------------------------------------------------
    TRUE


    Nachfolgend eine Liste von Parametern für den Namensraum USERENV, die für Skripte wohl am gebräuchlichsten sind:

    CURRENT_USERID: Die ID des Benutzers, dessen Rechte momentan aktiv sind.

    DATABASE_ROLE: Die Rolle der Datenbank ist entweder PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY oder SNAPSHOT STANDBY.

    DB_UNIQUE_NAME: Name der Datenbank, wie er im Initialisierungsparameter angegeben ist.

    HOST: Gibt den Computernamen (mit Domäne) aus.

    INSTANCE: Gibt die ID der Instanz aus, zu der die Session verbunden ist.

    INSTANCE_NAME: Name der Instanz, zu der die Session verbunden ist.

    NLS-Parameter

    • NLS_CALENDAR: Aktueller Kalender der momentanen Session
    • NLS_CURRENCY: Die Währung der aktuellen Session
    • NLS_DATE_FORMAT: Das Datumsformat der aktuellen Session
    • NLS_DATE_LANGUAGE: Die Sprache, mit der Datumsangaben ausgegeben werden
    • NLS_SORT: Sortierungsart, binär oder nach aktueller Sprache
    • NLS_TERRITORY: Land der aktuellen Session
    • SERVER_HOST: Gibt den Hostnamen der Maschine aus, auf der die Instanz läuft

    SERVER_HOST: Gibt den Hostnamen der Maschine aus, auf der die Instanz läuft.

    SERVICE_NAME: Name des Dienstes, zu der eine Session verbunden ist.

    TERMINAL: Gibt den Computernamen (ohne Domäne) aus.

    Seit Version 12.1 gibt es drei neue Parameter:

    CDB_NAME: Gibt den Namen der Container-Datenbank aus:

    SQL> SELECT sys_context('USERENV', 'CDB_NAME') FROM dual;
    SYS_CONTEXT('USERENV','CDB_NAME')
    ---------------------------------------------------------
    o12c


    CON_ID: Gibt den Identifier des Containers aus:

    SQL> SELECT sys_context('USERENV', 'CON_ID') FROM dual;
    SYS_CONTEXT('USERENV','CON_ID')
    -------------------------------------------------------
    3


    CON_NAME: Gibt den Namen des Containers aus:

    SQL> SELECT sys_context('USERENV', 'CON_NAME') FROM dual;
    SYS_CONTEXT('USERENV','CON_NAME')
    ---------------------------------------------------------
    PDB3


    Die Funktion SYS_CONTEXT bietet den Vorteil, dass man immer die gleiche Syntax verwendet und lediglich die Parameter austauschen muss. Auch wenn es für manche Parameter Alternativen gibt, wie beispielsweise für den Parameter "SESSION_USER", der dieselbe Information wie die SQL-Abfrage "show USER" ausgibt, ist es leichter mit einer Syntax zu arbeiten, als sich viele Views zu merken, auf die man evtl. gar keine Rechte hat.

    Sollten Sie Interesse an weiteren neuen Funktionen der Version 12c haben, so besuchen Sie doch unseren Kurs Oracle Neuerungen 12c!



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Index oder nicht, das ist hier die Frage

    Bereich:DBA:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 01.03.2019

    Keywords:Index

    Indizes sind ein spannendes Thema, manchmal hat man zu viele, oder zu wenige. Wir wollen uns in diesem Tipp mal anschauen, wie man diese Probleme löst.

    Der nachfolgende SELECT ermittelt, welche Spalten am häufigsten im Einsatz waren, aber nicht indiziert worden sind. Dabei werden folgende Spalten ausgegeben:

    • SIZE_M Größe des Index in Megabyte
    • =  <Spalte> wurde verwendet z.B. in der Form: ename='KING'
    • E-Join Spalte wurde verwendet z.B. in der Form: e.deptno=d.deptno
    • NE-Join Spalte wurde verwendet z.B. in der Form: e.deptno<>d.deptno
    • RANGE Spalte wurde verwendet z.B. in der Form: e.deptno>10
    • LIKE Spalte wurde verwendet z.B. in der Form: ename LIKE 'K%'
    • NULL Spalte wurde verwendet z.B. in der Form: ename IS NULL
    • SUMME Summe der Zugriffsspalten
    • INSERTS Wie viele Inserts wurden seit der letzen Statistikerzeugung auf der Tabelle durchgeführt
    • UPDATES Wie viele Updates wurden seit der letzen Statistikerzeugung auf der Tabelle durchgeführt
    • DELETS Wie viele Deletes wurden seit der letzen Statistikerzeugung auf der Tabelle durchgeführt

    Es wird sortiert nach der Benutzungshäufigkeit. Sie können nun entscheiden, ob Sie einen Index auf die Spalte legen möchten.

    Bedenken Sie aber, dass ein Index auch Wartungszeit für Inserts/Updates und Deletes benötigt

    /*****************************************************************************/
    /* Oracle Index Vorschläge                     */
    /*****************************************************************************/

    SELECT * FROM (
    SELECT tc.owner||'.'||o.object_name as segment_name,tc.column_name,
    round(s.bytes/1024/1024) Size_M,
    cu.equality_preds "=",
    cu.equijoin_preds "E-JOIN",cu.nonequijoin_preds "NE-JOIN",cu.range_preds range,cu.like_preds "LIKE", cu.null_preds "NULL",
    cu.equality_preds+cu.equijoin_preds+cu.nonequijoin_preds+cu.range_preds+cu.like_preds+cu.null_preds as summe,
    dtm.inserts,dtm.updates,dtm.deletes
    FROM sys.col_usage$ cu, dba_objects o,
    dba_tab_columns tc, dba_ind_columns ic, dba_segments s, dba_tab_modifications dtm
    WHERE o.object_id=cu.obj#
    AND o.object_name=tc.table_name
    AND o.owner=tc.owner
    AND cu.equality_preds>0
    AND cu.intcol#= tc.column_id
    AND tc.owner=ic.table_owner(+)
    AND tc.table_name=ic.table_name(+)
    AND tc.column_name=ic.column_name(+)
    AND ic.index_name is NULL
    AND o.owner=s.owner(+)
    AND o.object_name=s.segment_name(+)
    AND o.owner=dtm.table_owner
    AND o.object_name=dtm.table_name
    AND o.owner IN (select username from dba_users where oracle_maintained='N' ) -- alternativ für Oracle <12.1: o.owner IN ('SCOTT','X')
    ORDER BY 10 desc,1,2)
    WHERE rownum <30
    /

    oder anders herum betrachtet: Welche Indizes sind evtl unnötig, weil eine Spalte durch mehrere Indizes abgedeckt wird.

    REM ###################################################################
    REM Welche Spalten haben einen Index (alle Indizes in einer Spalte ausgeben)
    REM ###################################################################
     
    SELECT  owner,table_name,column_name, index_names FROM (
    SELECT owner,table_name,column_name, index_names,
    RANK() OVER (PARTITION BY owner,table_name,column_name ORDER BY len DESC NULLS LAST) AS col_Rank
    FROM(
    SELECT owner,table_name,column_name,rtrim(index_name||','||i2||','||i3||','||i4||','||i5||','||i6||',',',') index_names,
    length(index_name||i2||i3||i4||i5||i6) len
    FROM (
    select c.owner,c.table_name,c.column_name,i.index_name||'('|| i.column_position||')' index_name,
    CASE WHEN
    lead(c.owner||'#'||c.table_name||'#'||i.column_name,1) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
    THEN lead(i.index_name||'('|| i.column_position||')',1) OVER (order by c.owner,c.table_name,c.column_name) END i2,
    CASE WHEN
    lead(c.owner||'#'||c.table_name||'#'||i.column_name,2) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
    THEN lead(i.index_name||'('|| i.column_position||')',2) OVER (order by c.owner,c.table_name,c.column_name) END i3,
    CASE WHEN
    lead(c.owner||'#'||c.table_name||'#'||i.column_name,3) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
    THEN lead(i.index_name||'('|| i.column_position||')',3) OVER (order by c.owner,c.table_name,c.column_name) END i4,
    CASE WHEN
    lead(c.owner||'#'||c.table_name||'#'||i.column_name,4) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
    THEN lead(i.index_name||'('|| i.column_position||')',4) OVER (order by c.owner,c.table_name,c.column_name) END i5,
    CASE WHEN
    lead(c.owner||'#'||c.table_name||'#'||i.column_name,5) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
    THEN lead(i.index_name||'('|| i.column_position||')',5) OVER (order by c.owner,c.table_name,c.column_name) END i6
    FROM all_tab_columns c,all_ind_columns i
    WHERE c.owner=i.table_owner
    AND c.table_name= i.table_name
    AND c.column_name= i.column_name
    AND c.owner='SCOTT' )))
    WHERE col_rank=1
    AND index_names like '%,%'; /* Die letzte Klausel gibt nur Spalten mit mehr als 2 Indizes aus */

    Man sollte jetzt nicht gleich alle Indizes löschen, nur weil Sie eine Spalte mehrfach indiziert haben. Evtl. hatte der Index genau eine spezielle Funktion, die ohne ihn nicht mehr funktioniert.
    Aber Sie haben durch die beiden Select´s eine Diskussionsgrundlage.

    Weitere tolle Tipps erhalten Sie in einem unserer Oracle Kurse. Ich hoffe wir sehen uns bald dort :-)



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Interessantes zum Recycle Bin-Konzept

    Bereich:DBA:SQL, Version: ab RDBMS 10.x, Letzte Überarbeitung: 12.12.2018

    Keywords:SQL , DBA , Neuerungen 10g

    Haben Sie sich vielleicht schon einmal gewundert, warum Sie nach dem Löschen einer Tabelle keinen freien Speicherplatz gewonnen haben, aber dafür plötzlich Objekte mit Namen BIN$c7WXyzMkT1q9DglxITmbRw==$0 (oder so ähnlich) vorhanden sind

    Das liegt an einem Feature names RECYCLE BIN.

    Durch einen (alten) DROP TABLE-Befehl wird eine Tabelle nicht mehr - wie in den vorangegangenen Versionen - endgültig gelöscht und der Speicherplatz sofort freigegeben, sondern die Tabelle und alle darauf liegenden Indizes (inkl. Constraints) werden umbenannt und dem Recycle Bin zugeordnet. Der Inhalt des Recycle Bin wird somit weiter zur Quota des Benutzers gezählt.
     

    Views zu Recycle Bin

    Folgende Befehle zeigen den Inhalt des eigenen "Papierkorbs" an:

    SELECT * FROM recyclebin;
    -- oder
    SELECT * FROM user_recyclebin;
    -- oder
    SHOW RECYCLEBIN

    Und über folgenden SELECT kann sich der Administrator den Recycle Bin der gesamten Datenbank anzeigen lassen:

    SELECT * FROM dba_recyclebin;

    Einsatz des Recycle Bin

    Im ersten Beispiel soll die Tabelle EMP gelöscht und anschließend wieder hergestellt werden.

    DROP TABLE emp;

    SELECT object_name, object_type FROM user_objects
         WHERE object_name LIKE 'BIN$%';

    OBJECT_NAME                    OBJECT_TYPE
    ------------------------------ -------------------
    BIN$c7WXyzMkT1q9DglxITmbRw==$0 INDEX
    BIN$vvz7kZGhSoqCVWbdTtoSbQ==$0 TABLE

    Hinweis:

    Die gelöschten Objekte werden nicht mehr in der USER_OBJECTS-View angezeigt. Gesehen werden sie nur noch in der View USER_RECYCLEBIN oder über:

    SHOW RECYCLEBIN

    ORIGNAME_PLUS_SHOW_RECYC OBJECTNAME_PLUS_SHOW_RECYC     OBJTYPE_PLUS_SHOW_RECYC 
    DROPTIME_PLUS_SHOW_
    ------------------------ ------------------------------ ----------------------- -------------------
    EMP                      BIN$vvz7kZGhSoqCVWbdTtoSbQ==$0 TABLE                   2018-05-26:09:42:33

    FLASHBACK TABLE emp TO BEFORE DROP; 
    -- die Tabelle wird unter ihrem alten Namen wieder hergestellt.

    Indizes und Constraints bekommen beim Flashback ihren alten Namen nicht automatisch wieder. Sie müssen extra umbenannt werden:

    ALTER INDEX "BIN$c7WXyzMkT1q9DglxITmbRw==$0" RENAME TO pk_emp;

    ALTER TABLE emp RENAME CONSTRAINT "BIN$c7WXyzMkT1q9DglxITmbRw==$0" 
           TO pk_emp;

    Im zweiten Beispiel wird die Tabelle EMP endgültig gelöscht und der Speicherplatz freigegeben.

    DROP TABLE emp PURGE;

    -- alternativ
    DROP TABLE emp;
    PURGE TABLE emp;
    -- oder
    PURGE RECYCLEBIN; 
    -- alle Objekte aus dem Benutzer-Papierkorb werden endgültig gelöscht

     

    Ausschalten des Recycle Bin

    Vielleicht erscheint dem ein oder anderen Benutzer das Recycle Bin-Konzept überflüssig und er möchte gerne auf dieses Feature verzichten. Da aus Gewohnheit die PURGE-Option beim Löschen aber vergessen wird und der "Papierkorb" somit noch einmal separat geleert werden muss, kann dieses Verhalten auch ausgeschaltet werden.

    SHOW PARAMETER recyclebin
    ALTER SESSION SET recyclebin = OFF; 
    -- oder
    ALTER SYSTEM  SET recyclebin = OFF;

    Weitere Tipps zu Recycle Bin

    Tabellen der Benutzer SYS und SYSTEM werden nicht in den Recycle Bin verschoben."      

    Beim Löschen von Tablespaces oder Benutzern werden die Tabellen nicht in den Recycle Bin verschoben."      

    SELECT-Zugriffe sind auf die Tabellen des Recycle Bin unter dem vom System-generierten Namen möglich. DDL- und DML-Anweisungen sind nicht zulässig und ergeben den Fehler ORA-38301."      

    Die Objekte im Recycle Bin werden automatisch gelöscht, wenn die Quota eines Benutzers ausgeschöpft wäre oder ein Tablespace erweitert werden müsste, weil kein Platz mehr verfügbar ist."  

    Beim Wiederherstellen einer Tabelle müssen eventuelle Fremdschlüssel manuell erzeugt werden."      



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:


    Index To Rebuilt or not to Rebuilt

    Bereich:DBA:SQL:Tuning, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.12.2018

    Keywords:DBA , TUNING , SQL

    In unserer täglichen Praxis erleben wir häufig, dass Indizes beim Kunden sehr groß geworden sind. In vielen Fällen wurden in der zum Index gehörenden Tabelle viele Einträge gelöscht. Wenn nicht die Tabelle die gleichen Einträge wieder auffüllt, können Lücken im Index entstehen, die dazu führen, dass der Index sich unnötigerweise aufbläht.

    Beispiel: Herr Huber wird aus der Kundentabelle gelöscht, Herr Meyerhuber kommt neu dazu.

    Ein Index auf der Namensspalte fragmentiert dadurch. Wie kann man nun herausfinden, ob der Index ein Kandidat für einen Neuaufbau ist? Führen Sie dazu eine ausführliche Analyse des Index durch mit:
     

    ANALYZE INDEX scott.emp_ind VALIDATE STRUCTURE;

    Dadurch wird eine weitere Tabelle (index_stats) mit Informationen gefüllt:

    SELECT 
    DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PROZ_GELOESCHT, 
    (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS 
    FROM index_stats;

    PROZ_GELOESCHT gibt an, wieviel Prozent der Indexeinträge gelöscht und nicht wieder gefüllt wurden. Je höher dieser Wert ist, desto schlechter ist der Indexbaum ausbalanciert. Ab 10 % kann und ab 20 % sollte reorganisiert werden.

    DISTINCTIVENESS gibt an, wie häufig ein Indexwert wiederholt vorkommt (<Ges. Anzahl>/ <anz. verschiedener Werte>) *100 / <Ges. Anzahl>. Bei häufiger Wiederholung (z.B. bei 90% sollte ein Bitmap-Index in Betracht gezogen werden).

    Hinweis: 

    Ein Index Rebuild können Sie wie folgt vornehmen:
     

    ALTER INDEX <owner>.<index_name> REBUILD; /* Tabelle zum Schreiben gesperrt, für Standard Edition */
    ALTER INDEX <owner>.<index_name> REBUILD ONLINE; /* Tabelle zum Schreiben NICHT gesperrt, für Enterprise, Express und Personal Edition */

    oder mit Speicherparametern:
     

    ALTER INDEX <owner>.<index_name> REBUILD ONLINE
    TABLESPACE <indx_tbs> STORAGE (INITIAL <n>M NEXT <n>M);



    Weitere Interessente Artikel zum Thema:


    Empfohlene Schulungen zum Thema:



      Besonderheiten des Datentyps LONG

      Bereich:PL/SQL:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 18.11.2019

      Keywords:DBA , LONG , PL/SQL

      Das soll sich ändern, wir haben hier nun die wichtigsten Punkte zu Oracle LONG Spalten zusammengefasst:LONG Spalten wurden in Oracle 7.3 eingeführt und sollten die Beschränkung von VARCHAR2 Spalten die nur 4000 Zeichen aufnehmen können, beheben.Deswegen wurden zwei neue Datentypen geschaffen LONG (kann bis 2 GB an Textdaten aufnehmen) und LONG RAW (kann bis zu 2 GB an Binärdaten speichern).

      Leider hat man keine Routinen dazu entwickelt, die mit dieser Speichermenge umgehen konnten. Man hat deshalb mit 2 Speicherbegrenzungen zu kämpfen:

      SQL Funktionen können (wenn überhaupt) nur die ersten 4000 Zeichen einer LONG-Spalte nutzen.
      In PL/SQL kann man die ersten 32760 Zeichen lesen/schreiben. Mit einem Trick (dazu später mehr) lesen wir aber auch darüber hinaus.
      Wo kann man LONG Spalten verwenden?

      •     SELECT auf eine LONG Spalte
      •     In der SET Klausel eines UPDATE Statements
      •     In der VALUES Klausel eines INSERT Statements

      1.) Beschränkungen von LONG Spalten

      •     Objekt Typen dürfen keine LONG Attribute besitzen.
      •     LONG Spalten dürfen nicht in einer WHERE Klausel vorkommen.
      •     LONG Spalten dürfen nur NULL oder NOT NULL Constraints besitzen.
      •     LONG Spalten dürfen keinen Index besitzen (Ausnahme Text-Index).
      •     Reguläre Ausdrücke können nicht auf LONG Spalten verwendet werden.
      •     Funktionen (auch innerhalb eines Package) dürfen keinen LONG Wert zurückgeben (Nur VARCHAR2(4000)).
      •     Bei einem Join über mehrere Datenbanken müssen sich alle LONG Spalten auf einer Datenbankseite befinden.
      •     LONG und LONG RAW Spalten können nicht repliziert werden.
      •     Wenn eine Tabelle LONG und LOB Spalten enthält, dürfen nur jeweils 4000 Zeichen in die Spalten geschrieben werden.
      •     GROUP BY, ORDER BY, CONNECT BY oder DISTINCT dürfen nicht in Verbindung mit LONG Spalten verwendet werden.
      •     Eine Tabelle darf nur eine LONG/LONG RAW Spalte besitzen.
      •     Eine Tabelle mit LONG Spalte kann nicht mittels CREATE TABLE ... AS SELECT <LONG> FROM <table> kopiert werden.
      •     " ALTER TABLE ... MOVE"-Befehl kann nicht verwendet werden, wenn die Tabelle eine LONG Spalte besitzt.
      •     Immerhin werden LONG Spalten wohl auch noch in Oracle 11g unterstützt :-)

      Das schauen wir uns mal genauer an ...

      CREATE TABLE t (
        id   NUMBER,
        text LONG,
        bild LONG RAW);

      FEHLER in Zeile 4:
      ORA-01754: Eine Tabelle darf nur eine einzige Spalte des Typs LONG aufweisen

      Zweiter Versuch funktioniert, da nur eine LONG Spalte verwendet wird:

      CREATE TABLE t (
       id   NUMBER,
       text LONG);

      Das gleiche kann man auch mit einer LONG RAW Spalte machen:

      CREATE TABLE test_LONG_raw (
       id           NUMBER,
       long_col_raw LONG RAW);

       
      2.) Constraints auf LONG-Spalten

          Nur NOT NULL Constraints sind erlaubt
          Die Constraint-Typen P (Primary Key), U (Unique Key), C (Check) können nicht verwendet werden.

      CREATE TABLE t (
       id   NUMBER,
       l    LONG NOT NULL);

      ... alle anderen Constraints sind nicht erlaubt

      CREATE TABLE t (
       id   NUMBER,
       l    LONG PRIMARY KEY);

      ORA-02269:Schlüsselspalte kann nicht vom Datentyp LONG sein
      02269: "key column cannot be of LONG datatype"
      *Cause: Self-evident.
      *Action: Change the datatype of the column, or remove the column from the key.

       
      3.) Indizierung von LONG-Spalten

      Ein normaler Index kann nicht auf eine LONG-Spalte gesetzt werden, aber ein Text-Index schon.

      CREATE INDEX i ON t(text);
      Error: ORA-00997: Unzulässige Verwendung des Datentyps LONG

      Workaround: Text-Index setzen

      CREATE INDEX i ON t(text) INDEXTYPE IS ctxsys.context;
      SELECT * FROM t WHERE CONTAINS( text, 'Muniqsoft GmbH' ) > 0;

       

      4.) LONG Spalte füllen (bis zu 4000 Zeichen)

      INSERT INTO t VALUES (1,lpad('#',4000,'#'));

      LONG RAW Spalte füllen

      INSERT INTO test_LONG_raw (id,LONG_col_raw)
         VALUES(1,utl_raw.cast_to_raw('abcdefghijklmnopqrstuvwxyz'));

       

      5.) Füllung einer LONG-Spalte mit 32760 Bytes in PL/SQL

      DECLARE
         v_l LONG:=lpad('#',32760,'#');
      BEGIN
         INSERT INTO t VALUES (1,v_l);
         COMMIT;
      END;
      /

      Update eines LONG-Datentyps mit bis zu 32760 Bytes

      DECLARE
         v_l LONG;
         CURSOR c IS SELECT text FROM t
                     WHERE id=2 FOR UPDATE;
      BEGIN
         FOR r IN c LOOP
               v_l:=r.text || ' Neuer Text';
               UPDATE t SET text = v_l WHERE CURRENT OF c;
         END LOOP;
         COMMIT;
      END;
      /

       

      6.) Die Funktionen INSTR, SUBSTR und LENGTH..

      ...können nicht auf LONG-Spalten angewendet werden

      SELECT LENGTH(text) FROM t WHERE id=1;

      FEHLER in Zeile 1:
      ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalten

      SELECT SUBSTR(text,1,5) FROM t;

      FEHLER in Zeile 1:
      ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalt

      SELECT INSTR(text,1,'#') FROM t;

      FEHLER in Zeile 1:
      ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalten

      Aber mit einem kleinen Trick bekommen wir das doch noch hin

      Wenn Sie z.B. auf die Spalte view_text aus DBA_VIEWS zugreifen möchten, gehen Sie wie folgt vor:
      1. rufen Sie die Funktion sys_dburigen mit den Parametern: Eigentümer der Tabelle/View, Name der Tabelle/View und Spaltenname auf:

      SELECT sys_dburigen( owner,view_name, text, 'text()').getclob()
      FROM dba_views
      WHERE owner='SCOTT';


      2. verwenden Sie nund ie gewünschte Funktion aus 6. (z.B. substr)

      SELECT substr(sys_dburigen( owner,view_name, text, 'text()').getclob(),1,100)
      FROM dba_views
      WHERE owner='SCOTT';

      Das Ganze geht natürlich mit der Long Spalte:
      TRIGGER_BODY aus Tabelle DBA_TRIGGERS
      SEARCH_CONDITION aus Tabelle DBA_CONSTRAINTS
      u.v. weiteren LONG Spalten
      Achtung: Die 3 Parameter müssen genau eine Zeile zurückgeben, sonst erhält man den Fehler:
      ORA-19003 XML document must have a top level element
      Deswegen funktioniert der TRcik auch leider nicht bei:

      SELECT sys_dburigen( table_owner,partition_name,HIGH_VALUE, 'text()').getxml() FROM all_tab_partitions
      WHERE table_owner='AUDSYS' AND table_name='AUD$UNIFIED' ;


      7.) Trigger auf LONG Spalten

      Ein SQL Statement innerhalb eines Trigger kann Daten in eine LONG oder LONG RAW Spalte einfügen.Die maximale Länge einer Text-Variable (VARCHAR2 oder LONG) in einem Trigger beträgt 32000.:NEW und :OLD dürfen nicht auf LONG oder LONG RAW Spalten gesetzt werden.

      LONG oder LONG RAW Spalten dürfen nur referenziert werden, wenn sie in CHAR oder VARCHAR2 konvertierbar sind.

      CREATE OR REPLACE TRIGGER tr
         BEFORE UPDATE OR INSERT OR DELETE
         ON t
         FOR EACH ROW
      BEGIN
         :new.text:='A';
      END;
      /
      Error: ORA-04093: Referenzen auf Spalten von Typ LONG sind in Triggern nicht zulässig.

       

      8.) Ausgabelänge für Oracle LONG-Spalten in SQL*Plus auf 2 Mio Zeichen setzen (Default= 80)

      Wenn Sie diesen Parameter nicht umsetzen, werden bei einer Ausgabe in SQL*Plus nur die ersten 80 Zeichen einer LONG Spalte ausgegeben.

      SET LONG 200000


      9.) Tabellen mit LONG Spalten kopieren

      Eine Tabelle kann mit einem CREATE TABLE AS SELECT ... nicht kopiert werden, wenn LONG Spalten enthalten sind.

      CREATE TABLE t2 AS SELECT * FROM t;

      FEHLER in Zeile 1:
      ORA-00997: Unzulässige Verwendung des Datentyps LONG

      Workaround mit Konvertierung in einen CLOB Datentypen

      CREATE TABLE t2 AS SELECT to_lob(text) as text FROM t;

      Tabelle wurde erstellt.


      10.) LONG Spalten konvertieren

      Oracle empfiehlt, möglichst zeitnah auf die Datentypen CLOB, BLOB und NCLOB zu konvertieren. Diese stehen ab Version 8.0 zur Verfügung.

      Beispiel: Umwandeln von LONG in CLOB und LONG Raw in BLOB

      ALTER TABLE test_LONG MODIFY (LONG_col CLOB);
      ALTER TABLE test_LONG_raw MODIFY (LONG_col_raw BLOB);

       
      11.) LONG Spalten in PL/SQL

      In PL/SQL wird ein LONG-Datentyp als VARCHAR2(32760) abgebildet. Das heißt folgende beiden Deklarationen sind identisch:

      DECLARE
         v_a LONG;
         v_b VARCHAR2(32760);
      BEGIN
         NULL;
      END;
      /

       
      12.) PL/SQL Package zum Berechnen der LONG Spaltenlänge und zum Auslesen von Daten

      Das nachfolgende kleine PL/SQL Beispiel zeigt, dass man mit einem Spezial-Package (dbms_sql) auch an die Daten einer LONG Spalte herankommt, die hinter der magischen Grenze von 32760 liegen.

      Das Package LLONG bietet Ihnen die folgenden Möglichkeiten:

          Berechnung der Länge eines LONG Feldes. Damit können Sie feststellen, ob Sie die Daten auch in VARCHAR2(4000) bekommen könnten.

          Der Aufruf dazu lautet:

          LLONG.LEN(<rowid>)

          Beispiel:

          SELECT LLONG.LEN(rowid) FROM t;

          Herausschneiden von beliebigen 400 Zeichen in der LONG-Spalte

          Der Aufruf lautet:

          LLONG.SUB_STR(<rowid>, <start_pos>, <end_pos>)

          Der Default für die Startposiion ist 1 und der Default für die Endposition ist 4000.

          Beispiele:

          SELECT LLONG.SUB_STR(rowid) FROM t;
          SELECT LLONG.SUB_STR(rowid, 400000,404000) FROM t;

          Das Suchen innerhalb einer Long Spalte mit Like

          Der Aufruf dazu lautet:

          LLONG.LIKE2(<rowid>,'<such_str>')

          Beispiel:

          SELECT * FROM t
          WHERE LLONG.LIKE2(rowid,'MARCO%') >0

          Das Anhängen von Daten an eine bestimmte Zeile

          Der Aufruf lautet:

          LLONG.APPEND(<rowid>,'<TEXT>')

          Beispiel:

          EXEC LLONG.APPEND('AAAUfXAAEAAAAEAAAC',' Anhänger')

          Das Anzeigen der Position eines Suchstring:

          Der Aufruf dazu lautet:

          LLONG.IN_STR(<rowed>,'<TEXT>',<pos>)

          Beispiel:

          SELECT LLONG.IN_STR(rowid,'MARCO',1) FROM t;

      Und so sieht das komplette Package aus:

      CREATE OR REPLACE PACKAGE LLONG
         AS FUNCTION sub_str(
            p_rowid in rowid,
            p_startpos IN NUMBER DEFAULT 1,
            p_endpos IN NUMBER DEFAULT 4000)
          RETURN CLOB;
         
          FUNCTION len( p_rowid in rowid)
          RETURN VARCHAR2;
         
          PROCEDURE append (
            p_rowid IN ROWID,
            p_value IN VARCHAR2);
           
          FUNCTION like2 (
            p_rowid IN ROWID,
            Like_str In VARCHAR2)
          RETURN NUMBER;
         
          FUNCTION in_str (
          p_rowid IN ROWID,
          search_char In VARCHAR2,
          position IN NUMBER DEFAULT 1)
          RETURN NUMBER;

      END;
      /
      SHOW ERRORS

      CREATE OR REPLACE PACKAGE BODY LLONG
         AS
            l_cursor   integer default dbms_sql.open_cursor;
            l_n        number;
            --l_LONG_val varchar2(4000);
            l_LONG_val CLOB;
            l_LONG_len number;
            l_buflen   number := 4000;
            l_curpos   number := 0;
            v_tab      VARCHAR2(62);
            v_col      VARCHAR2(30);
            offset     integer;
            v_length   integer;
            v_collen   integer;

         FUNCTION get_obj_name (r ROWID) RETURN VARCHAR2
         AS
         BEGIN
         select owner||'.'||object_name INTO v_tab
         from all_objects where object_id=(
         select dbms_rowid.rowid_object(r)
         from dual);
         RETURN v_tab;
         END;
         FUNCTION get_col_name (r ROWID) RETURN VARCHAR2
         AS
         BEGIN
         select column_name INTO v_col
         from all_tab_columns where (owner,table_name) IN  (select
      owner,object_name
         from all_objects where object_id=(
         select dbms_rowid.rowid_object(r)
         from dual)) AND data_type='LONG';
         RETURN v_col;
         END;

         PROCEDURE open_curs (p_rowid IN ROWID)
         AS
         BEGIN
          v_tab:=get_obj_name(p_rowid);
          v_col:=get_col_name(p_rowid);
          l_cursor:=dbms_sql.open_cursor;
          dbms_sql.parse( l_cursor,'select ' || v_col || ' from ' ||    v_tab ||
          ' where rowid = :x',dbms_sql.native );
          dbms_sql.bind_variable( l_cursor, ':x', p_rowid );
          dbms_sql.define_column_LONG(l_cursor, 1);
          l_n := dbms_sql.execute(l_cursor);
         END;

         FUNCTION sub_str(
            p_rowid in rowid,
            p_startpos IN NUMBER DEFAULT 1,
            p_endpos IN NUMBER DEFAULT 4000)
         RETURN CLOB
         AS
      BEGIN
         IF p_endpos-p_startpos > 4000 THEN
            RETURN 'Max Return Length 4000 Bytes !';
         END IF;
         open_curs(p_rowid);
         IF (dbms_sql.fetch_rows(l_cursor)>0) then
            dbms_sql.column_value_LONG(
            l_cursor, 1, p_endpos-p_startpos, p_startpos ,l_LONG_val, l_LONG_len
      );
         END IF;
         dbms_sql.close_cursor(l_cursor);
         return l_LONG_val;
         EXCEPTION WHEN OTHERS THEN RETURN sqlerrm;
      end; --sub_str

        FUNCTION len (p_rowid in rowid)
        RETURN VARCHAR2
        AS
        BEGIN
        open_curs(p_rowid);
      °  IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
            offset := 0;
            v_collen := 0;
        LOOP
            dbms_sql.column_value_LONG(l_cursor,
      1,32767,offset,l_LONG_val, v_length);
            v_collen := v_collen + v_length;
            EXIT WHEN v_length < 32767 OR v_length IS NULL;
            offset := offset + v_length;
        END LOOP;
        END IF;
      dbms_sql.close_cursor(l_cursor);
      return v_collen;
      EXCEPTION WHEN OTHERS THEN RETURN sqlerrm;
      END; --len

      PROCEDURE append (
            p_rowid IN ROWID,
            p_value IN VARCHAR2)
      AS
      v_app  VARCHAR2(32760);
      s      VARCHAR2(1000);
      BEGIN
      open_curs(p_rowid);
      IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
          offset := 0;
          LOOP
             dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length);
              v_app := v_app || l_LONG_val;
            EXIT WHEN v_length < 32767 OR v_length IS NULL;
                      offset := offset + v_length;
          END LOOP;
        END IF;
      dbms_sql.close_cursor(l_cursor);
       BEGIN
        v_app:=v_app||p_value;
        s:='UPDATE '||get_obj_name(p_rowid)||' SET '||get_col_name(p_rowid)||' =:x WHERE rowid=:y';
        EXECUTE immediate s USING v_app,p_rowid;
        EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000,'Max Col Length Limit 32767
      eached');
       END;
      END; --append

      FUNCTION like2 (
       p_rowid IN ROWID,
       Like_str In VARCHAR2)
      RETURN NUMBER
      IS
      BEGIN
      open_curs(p_rowid);
      IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
            offset := 0;
        LOOP
            dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length);
            IF l_LONG_val LIKE like_str THEN
              RETURN 1;
            END IF;
            EXIT WHEN v_length < 32767 OR v_length IS NULL;
            offset := offset + v_length;
        END LOOP;
      END IF;
      dbms_sql.close_cursor(l_cursor);
      RETURN 0;
      END; --like2

      FUNCTION in_str (
          p_rowid IN ROWID,
          search_char IN VARCHAR2,
          position IN NUMBER DEFAULT 1)
          RETURN NUMBER
      IS
      pos NUMBER;
      BEGIN
      open_curs(p_rowid);

      IF (dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
            offset := 0;
         LOOP
            dbms_sql.column_value_LONG(l_cursor,1,32767,offset,l_LONG_val, v_length);
            pos:= instr(l_LONG_val,search_char,mod(position,32767));
            IF pos>0 and pos+offset>position THEN
              RETURN pos+offset;
            END IF;
            EXIT WHEN v_length < 32767 OR v_length IS NULL;
            offset := offset + v_length;
         END LOOP;
      END IF;
      dbms_sql.close_cursor(l_cursor);
      RETURN pos;    
      END; -- instr Function

      END; -- PACKAGE
      /
      SHOW ERROR

      Beispielszenario:

      CREATE TABLE t ( text LONG );

      INSERT INTO t VALUES ( RPAD( '*', 32000, '*' ) );

      BEGIN
        INSERT INTO t VALUES('#');
        FOR i in 1.. 10000 LOOP
        UPDATE T SET text=text||';'||to_char(i);
        END LOOP;
        COMMIT;
      END;
      /

      SELECT LLONG.LEN( rowid) FROM t;

      GETLONG(ROWID)
      ---------------------------------
      4000

      SELECT LLONG.SUB_STR(rowid, 400000, 404000) FROM t;

      Suchen in LONG Data Dictionary Spalten:

      Als Beispiel wird hier in der Tabelle user_constraints Spalte search_condition nach einem Wert gesucht. Das Beispiel ist leich abänderbar, damit auch die Tabellen DBA_VIEWS (Spalte text) oder DBA_TRIGGERS (Spalte trigger_body) mit ihren LONG Spalten ausgelesen werden können.

      CREATE OR REPLACE FUNCTION get_search_condition( p_cons_name IN VARCHAR2 )
      RETURN VARCHAR2
         authid current_user
        IS
          l_search_condition user_constraints.search_condition%type;
        BEGIN
          SELECT search_condition into l_search_condition
            FROM all_constraints
            WHERE constraint_name = p_cons_name;
          RETURN l_search_condition;
      END;
      /

      SELECT constraint_name FROM all_constraints
      WHERE owner='SYSTEM'
      AND get_search_condition(constraint_name) LIKE '%NOT NULL%';

      PS: Die Konvertierung der Funktion kann einige Zeit in Anspruch nehmen!

      CONSTRAINT_NAME
      ------------------------------
      SYS_C002892
      .....

      Zum Vergleich ohne Funktion:

      SELECT constraint_name FROM user_constraints;
          WHERE search_condition LIKE '%NOT NULL%';
         
      where search_condition like '%NOT NULL%'
          *
      FEHLER in Zeile 2:
      ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, LONG erhalten



      Weitere Interessente Artikel zum Thema:


      Empfohlene Schulungen zum Thema:


      Monatskalender in SQL*Plus

      Bereich:PL/SQL:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.12.2018

      Keywords:PL/SQL , SQL

      Monatskalender in SQL*Plus

      Haben Sie sich auch schon mal gewünscht, dass SQL*Plus einen Monat als schönen Kalender darstellt? Mit der folgenden Funktion können Sie sich den aktuellen Monat (Default) oder einen beliebig anderen Monat anzeigen lassen:

      CREATE OR REPLACE FUNCTION show_cal (month IN VARCHAR2 DEFAULT sysdate)
      RETURN VARCHAR2 IS
         v_week VARCHAR2(4096);
         v_firstday NUMBER;
         v_lastday NUMBER;
      BEGIN
         v_firstday:=to_char(trunc(to_date(month),'MM'),'D');
         v_lastday:=to_number(to_char(last_day(to_date(month)),'DD'));
         v_week:='Monat: '||to_char(to_date(month),'FM Month RRRR')||chr(10);
         v_week:=v_week||'Mon Die Mit Don Fre Sam Son'||chr(10)||
         '-------------------------------------'||chr(10);
         FOR i IN 1.. v_lastday+v_firstday-1 LOOP
            IF i>=v_firstday then
               v_week:=v_week||rpad((i-v_firstday+1),4,' ');
            ELSE
               v_week:=v_week||rpad(chr(32),4,chr(32));
            END IF;
            IF mod(i,7)=0 THEN
               v_week:=v_week||chr(10);
            END IF;
         END LOOP;
         RETURN v_week;
      END;
      /
      show errors
      SELECT show_cal FROM dual;

      Ausgabe:

      SHOW_CAL
      ---------------------------------------------------
      Monat: März 2007 
      Mon    Die    Mit    Don    Fre    Sam    Son
      ---------------------------------------------------
                            1     2      3      4
       5       6     7      8     9     10     11
       12      13    14     15    16    17     18
       19      20    21     22    23    24     25
       26      27    28     29    30    31    

      Da viele grafische Tools (Toad, SQL Developer, ...) mit einem Zeilenumbruch nichts anfangen können, hier eine zweite Alternative mit einer Pipelined Function (nur ab Oracle 10g).

      CREATE OR REPLACE TYPE calender_week_type
      AS OBJECT (woche VARCHAR2(64));
      /
      CREATE OR REPLACE TYPE calender_type as table of calender_week_type;
      /
      CREATE OR REPLACE FUNCTION show_cal (month IN VARCHAR2 DEFAULT sysdate)
      RETURN calender_type PIPELINED IS
         v_week VARCHAR2(4096);
         v_firstday NUMBER;
         v_lastday NUMBER;
      BEGIN
         v_firstday:=to_char(trunc(to_date(month),'MM'),'D');
         v_lastday:=to_number(to_char(last_day(to_date(month)),'DD'));
         PIPE ROW (new calender_week_type('Monat: '||to_char(to_date(month),
                                                        'FM Month RRRR') ));
         PIPE ROW (new calender_week_type('Mon Die Mit Don Fre Sam Son'));
         PIPE ROW (new calender_week_type('--- --- --- --- --- --- ---'));
         FOR i IN 1.. v_lastday+v_firstday-1 LOOP
            IF i>=v_firstday then
               v_week:=v_week||rpad((i-v_firstday+1),4,' ');
            ELSE
               v_week:=v_week||rpad(chr(32),4,chr(32));
            END IF;
            IF mod(i,7)=0 THEN
               PIPE ROW (new calender_week_type(v_week));
               v_week:='';
            END IF;
         END LOOP;
      END;
      /
      show errors
      select * from TABLE(CAST(show_cal AS calender_type));

      Ausgabe:

      Woche
      --------------------------------------------------
      Monat: März 2007 
      Mon    Die    Mit    Don    Fre    Sam    Son
      --------------------------------------------------
                            1      2      3      4
       5      6      7      8      9      10     11
       12     13     14     15     16     17     18
       19     20     21     22     23     24     25
       26     27     28     29     30     31     
      7 Zeilen ausgewählt.



      Weitere Interessente Artikel zum Thema:


      Empfohlene Schulungen zum Thema:


      Mehrspaltige Ausgabe einer Tabelle

      Bereich:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.12.2018

      Keywords:SQL , PL/SQL

      Tabellenzusammenfassung

      Wollten Sie immer schon mal eine Ausgabe, die nur wenige Spalten besitzt, Platz sparend ausgeben?

      In unserem Beispiel machen wir aus zwei Spalten eine, geben das Ganze aber dann doch wieder zweispaltig aus.

      Verwirrt? Kein Problem, ein kleines Beispiel soll die Ausgabe verdeutlichen:

      Normale Ausgabe:
      A   B
      --  --
      1   2
      3   4
      5   6
      7   8

      Wird zu

      C1   C2
      ----  ----
      1  2  3  4
      5  6  7  8

       

      Das Ganze mit der Tabelle v$parameter:

       SELECT *
         FROM (
            SELECT
               rn,name||' = '||value parameter1, lag(name||' = '||value) over (order by rn) parameter2
            FROM ( SELECT rownum rn,p.*
              FROM v$parameter p))
      WHERE MOD(rn,2)=0
       
       
       
      Parameter1                       Parameter2
      -----------------------------   ----------------------------
      timed_statistics = TRUE         sessions = 170
      resource_limit = FALSE          timed_os_statistics = 60
      license_sessions_warning = 0    license_max_sessions = 0
      ...

       

      Oder mit der Tabelle v$option:

      SELECT *
         FROM (
            SELECT
               rn,parameter||' = '||value parameter1, lag(parameter||' =          '||value) over (order by rn) parameter2
            FROM ( SELECT rownum rn,p.*
         FROM v$option p))
      WHERE MOD(rn,2)=0
       

      Viel Spaß beim Einsparen von Ausgabeseiten und damit Zellulose und damit CO2



      Weitere Interessente Artikel zum Thema:



      Empfohlene Schulungen zum Thema:


      Umbenennen Ihrer Constraints

      Bereich:DBA:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.12.2018

      Keywords:DBA , SQL

      Wollten Sie schon immer auf einfache Art und Weise Ihre Constraints umbenennen, damit Sie sofort erkennen, um welchen Typ es sich handelt (beispielsweise in Fehlermeldungen, in welchen eine Verletzung eines Constraints bemerkt wird)? Dann haben wir jetzt den passenden Tipp für Sie, mit dem Sie alle Ihre Constraints mit wenig Aufwand umbenennen können.

      Der Befehl zum Umbenennen lautet allgemein:

      ALTER TABLE <table_name> RENAME CONSTRAINT <constraint_name> TO <new_constraint_name>;


      Alle notwendigen Informationen erhalten wir aus den Data Dictionary Views DBA_CONSTRAINTS und DBA_CONS_COLUMNS. Aus DBA_CONSTRAINTS lesen wir den Eigentümer (OWNER), den Namen des Constraint (CONSTRAINT_NAME), den Typ des Constraint (CONSTRAINT_TYPE) und den Tabellennamen (TABLE_NAME), aus DBA_CONS_COLUMNS den Namen der Spalte, auf welcher der Constraint liegt (COLUMN_NAME). Um die zusammengehörenden Constraint-Eigenschaften zu erhalten, müssen die Tabellen verknüpft werden (OWNER und CONSTRAINT_NAME müssen bei beiden gleich sein). Durch einen SELECT geben wir alle Informationen automatisch als fertigen ALTER TABLE-Befehl aus. Dazu müssen Sie Strings (Konstanten) und Spaltennamen miteinander verknüpfen.

      SELECT 'ALTER TABLE '||a.owner||'.'||a.table_name||
       ' RENAME CONSTRAINT '||a.constraint_name||' TO '||
       a.table_name||'_'||b.column_name||'_'||a.constraint_type||';'
      FROM dba_constraints a , dba_cons_columns b
      WHERE a.constraint_name=b.constraint_name
      AND a.owner=b.owner
      AND a.owner = 'SCOTT';


      Ein Beispiel wäre hier (der Eigentümer lautet SCOTT, die Tabelle heißt EMP, der Spaltenname EMPNO und auf dieser Spalte liegt ein Constraint vom Typ Primary Key):

      ALTER TABLE scott.emp RENAME CONSTRAINT sys_c008518 TO emp_empno_p;

      Da wir aber gerne eine genauere Bezeichnung der Constraint-Typen im Namen hätten, für

           C (not null) = NN
           C (check) = CK
           P (Primary Key) = PK
           U (Unique Constraint) = UK
           R (Foreign Key) = FK

      müssen wir noch weitere Bedingungen einfügen. Geben Sie hierzu eine CASE-Klausel in den SELECT-Befehl ein:

      CASE
       WHEN a.constraint_type = 'C' THEN 'NN'
       WHEN a.constraint_type = 'C' THEN 'CK'
       WHEN a.constraint_type = 'P' THEN 'PK'
       WHEN a.constraint_type = 'U' THEN 'UK'
       WHEN a.constraint_type = 'R' THEN 'FK'
      END


      Wie Sie sehen, kann das noch nicht funktionieren, da für 'C' zwei verschiedene Werte ausgegeben werden sollen (zwei verschiedene Typen, C (not null) und C (check) werden mit dem gleichen Buchstaben gekennzeichnet). Nun müssen Sie noch zwei weitere Bedingungen anhängen. Dazu lässt sich die Spalte SEARCH_CONDITION der View DBA_CONSTRAINTS verwenden. Allerdings ist diese vom Datentyp LONG, was zur Folge hat, dass man auf diese Spalten keinen LIKE-Operator anwenden kann.

      Deshalb greifen Sie auf einen bereits vorhandenen Tipp zurück, in welchem erklärt wird, wie in LONG-Spalten gesucht werden kann.

      CREATE OR REPLACE FUNCTION get_search_condition( p_cons_name IN VARCHAR2 )
      RETURN VARCHAR2
        authid current_user
        IS
          l_search_condition dba_constraints.search_condition%type;
        BEGIN
          SELECT search_condition into l_search_condition
            FROM dba_constraints
            WHERE constraint_name = p_cons_name;
          RETURN l_search_condition;
      END;
      /


      Aus Vereinfachungsgründen wird ein extra View erzeugt, der die Data Dictionary Views DBA_CONSTRAINTS und DBA_CONS_COLUMNS joint.

      CREATE OR REPLACE VIEW cons_list
      AS
      SELECT t1.owner,
             t1.constraint_name,
             t1.constraint_type,
             t1.table_name,
             t2.column_name,
             t2.position,
             t1.search_condition
          FROM dba_constraints t1,
               dba_cons_columns t2
            WHERE t1.owner = t2.owner
              AND t1.constraint_name = t2.constraint_name;


      Der eigentliche SELECT-Befehl, der alle Befehle zum Umbenennen ausgibt lautet schließlich folgendermaßen:

      SELECT 'ALTER TABLE '||A.OWNER||'.'||A.TABLE_NAME||
             ' RENAME CONSTRAINT '||A.CONSTRAINT_NAME||
             ' TO '||A.TABLE_NAME||'_'||A.COLUMN_NAME||'_'||
             CASE
                  WHEN B.ANZAHL > 1
                       THEN 'COMBINE_'
                  END||
             CASE
                  WHEN A.CONSTRAINT_TYPE = 'C'
                       AND GET_SEARCH_CONDITION(A.CONSTRAINT_NAME)
      LIKE '%NOT NULL%'
                       THEN 'NN'
                  WHEN A.CONSTRAINT_TYPE = 'C'
                       AND GET_SEARCH_CONDITION(A.CONSTRAINT_NAME)
      NOT LIKE '%NOT NULL%'
                       THEN 'CK'
                  WHEN A.CONSTRAINT_TYPE = 'P'
                       THEN 'PK'
                  WHEN A.CONSTRAINT_TYPE = 'U'
                       THEN 'UK'
                  WHEN A.CONSTRAINT_TYPE = 'R'
                       THEN 'FK'
                  END ||';' AS BEFEHLE
          FROM CONS_LIST A,
               ( SELECT OWNER, CONSTRAINT_NAME, COUNT(*) AS ANZAHL
                   FROM CONS_LIST
                  GROUP BY OWNER, CONSTRAINT_NAME ) B
            WHERE A.OWNER = 'SCOTT'
              AND A.OWNER = B.OWNER
              AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
              AND ( A.POSITION = 1
                    OR A.POSITION IS NULL )
      /

       

      Dann nur noch die Ausgabe in eine Spool-Datei umleiten, die Datei ausführen und fertig.
      Viel Erfolg!



      Weitere Interessente Artikel zum Thema:



      Empfohlene Schulungen zum Thema:


      Oracle Indexüberwachung

      Bereich:DBA:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.12.2018

      Keywords:DBA , SQL

      Eigentlich bin ich ja ein großer Oracle Fan. Aber manchmal ärgere ich mich auch über Unzulänglichkeiten der Software. Man kann zwar einen Index danach überwachen, ob er verwendet wird, aber nicht, wie oft. Das ist so, als wenn der Tacho eines Autos nicht die Tageskilometer anzeigen würde, sondern nur "Auto wurde bewegt". Das zweite Problem ist, dass bei einer Überwachung eines Index nur der Inhaber des Index sieht, ob er verwendet worden ist oder nicht. Dem Administrator fehlt diese Möglichkeit der Überprüfung. Das ist doch Anarchie :-)

      Nun, Problem bekannt, Problem gebannt. Das folgende Skript ist auf den Benutzer SCOTT abgestimmt und legt für ihn eine Tabelle und eine Procedure an. Sie können natürlich gerne auch einen anderen Benutzer verwenden (z.B. SYSTEM).

      Die Idee ist folgende:

      Die Datenbank zeichnet in der View v$object_usage auf, ob ein Index benutzt worden ist:

      Beispiel:

      connect scott/tiger

      ALTER INDEX scott.pk_emp MONITORING USAGE;

      SELECT * FROM scott.emp WHERE empno=7369;

      SELECT index_name,used FROM v$object_usage;

      INDEX_NAME                     USED
      ------------------------------ ----
      PK_EMP                         YES
       

      Wir prüfen also in einem bestimmten Zeitintervall, ob der Index benutzt worden ist. Wenn ja, dann zählen wir einen Zähler (Tabelle scott.index_usage_count Spalte count_usage) um eins hoch. Dann schalten wir die Indexüberwachung aus und wieder ein. Nach einer gewissen Zeitspanne wiederholt sich der Vorgang. Je kleiner Sie das Intervall setzen, desto genauer ist das Ergebnis. Auf jeden Fall ist es genauer als die Angabe benutzt/nicht benutzt. :-)

      Auf der anderen Seite steigt mit der Verkleinerung des Intervalls die CPU-Last.

      Insofern sollte man sich für den goldenen Mittelweg entscheiden (wir verwenden bei uns 15 Minuten).

      Noch ein paar Anmerkungen:

       

      Die Procedure schaltet nicht die Indexüberwachung ein, sondern überwacht sie nur.

      Wenn Sie die Indexüberwachung für ein komplettes Schema (z.B. SCOTT) einschalten wollen, gehen Sie bitte wie folgt vor:

      SPOOL c:\temp\index_monitoring.sql
      SELECT 'ALTER INDEX '||owner||'.'||index_name||' MONITORING USAGE;'
      FROM dba_indexes
      WHERE owner='SCOTT';
      SPOOL OFF
      @c:\temp\index_monitoring.sql
       

      Noch ein paar Anmerkungen:

      Die Procedure schaltet nicht die Indexüberwachung ein, sondern überwacht sie nur.

      Wenn Sie die Indexüberwachung für ein komplettes Schema (z.B. SCOTT) einschalten wollen, gehen Sie
      bitte wie folgt vor:

      SPOOL c:\temp\index_monitoring.sql
      SELECT 'ALTER INDEX '||owner||'.'||index_name||' MONITORING USAGE;'
      FROM dba_indexes
      WHERE owner='SCOTT';
      SPOOL OFF
      @c:\temp\index_monitoring.sql

       

      So, und nun viel Spaß bei der Indexüberwachung. Wenn Sie noch mehr tolle Tricks erfahren wollen, dann kommen Sie doch z.B. in einen unserer Kurse über DB Monitoring, Tabellen/Index-Administration oder DB Tuning. :-)
      Aber alle anderen Kurse bei uns kann man natürlich auch empfehlen...



      Weitere Interessente Artikel zum Thema:



      Empfohlene Schulungen zum Thema:



        Quote Operator

        Bereich:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.12.2018

        Keywords:SQL

        Hatten Sie auch schon mal mit (einfachen) Anführungszeichen (… ' …) in Ihrer Datenbank zu kämpfen?? Entweder sie sind durch einen Ladevorgang mit fehlerhaften Daten (ungewollt) entstanden und sollen nun bereinigt werden oder sie sind ein korrekter Teil Ihres Datenbestandes, der durch Lese- bzw. Schreiboperationen angesprochen werden soll.

        Egal, ob für Sie der erste oder der zweite Fall zutrifft, da das Anführungszeichen in aller Regel den Anfang bzw. das Ende eines Strings kennzeichnet, bedarf es schon eines Tricks, um es gezielt als Teil eines Strings zu verwenden.

        Im folgenden Beitrag werden verschiedene Beispiele mit jeweils zwei Lösungsmöglichkeiten (mit bzw. ohne Quote Operator) vorgestellt. Da der Quote Operator erst ab Version 10g zulässig ist, muss in älteren Versionen mit anderen Mitteln (z.B. CHR(39)) gearbeitet werden.

        Beispiel 1

        Das Anführungszeichen ist Teil des zurückgegebenen Strings. Es soll der folgende String ausgegeben werden "Willkommen auf Muniqsoft's Homepage".

        Lösung mit Quote Operator:

        SQL> SELECT q'#Willkommen auf Muniqsoft's Homepage#' as intro FROM dual;

        INTRO
        -----------------------------------
        Willkommen auf Muniqsoft's Homepage
         

        Lösung ohne Quote Operator (diese basiert auf einem zu ASCII kompatiblen Zeichensatz):

        SQL> SELECT 'Willkommen auf Muniqsoft'||CHR(39)||'s Homepage' as intro      
        FROM dual;

         

        Beispiel 2

        Das Anführungszeichen ist Teil eines Strings, der über einen INSERT-Befehl in eine Tabelle eingetragen werden soll.

        Achtung: Für das Beispiel muss die ENAME-Spalte der EMP-Tabelle auf VARCHAR2(12) verlängert werden.

        Lösung mit Quote Operator:

        SQL> INSERT INTO scott.emp (empno, ename)
               VALUES ( 8000, q'#Hell's Angel#' );
         

        Lösung ohne Quote Operator (diese basiert auf einem zu ASCII kompatiblen Zeichensatz):

        SQL> INSERT INTO scott.emp (empno, ename)
               VALUES ( 8000, 'Hell'||CHR(39)||'s Angel' );
         
         

        Beispiel 3

        Es sollen alle Werte aus einer Spalte ermittelt werden, die ein Anführungszeichen enthalten.

        Lösung mit Quote Operator:

        SQL> SELECT empno, ename FROM scott.emp
              WHERE ename LIKE q'#%'%#';

        EMPNO ENAME
        ----- ------------
         8000 Hell's Angel
         

        Lösung ohne Quote Operator (diese basiert auf einem zu ASCII kompatiblen Zeichensatz):

        SQL> SELECT empno, ename FROM scott.emp
              WHERE ename LIKE '%'||chr(39)||'%';
         

        Eine weitere Alternative bietet die in 10g eingeführte REGEXP_LIKE-Funktion:

        SQL> SELECT empno, ename FROM scott.emp
              WHERE REGEXP_LIKE(ename, CHR(39));
         

        Beispiel 4

        Die Anführungszeichen in einer einzelnen Spalte sind ungewollt und sollen entfernt werden.

        Lösung mit Quote Operator:

        SQL> UPDATE scott.emp SET ename = REPLACE(ename, q'#'#', '')
              WHERE ename LIKE q'#%'%#';
         

        Lösung ohne Quote Operator (diese basiert auf einem zu ASCII kompatiblen Zeichensatz):

        SQL> UPDATE scott.emp SET ename = REPLACE(ename, chr(39), '')
              WHERE ename LIKE '%'||chr(39)||'%';
         

        Beispiel 5

        Es soll ein Skript erzeugt werden, das zu jedem Datensatz der EMP-Tabelle einen entsprechenden INSERT-Befehl erzeugt. Damit können Sie sich die Inhalte Ihrer wichtigsten Tabelle sichern.

        Lösung mit Quote Operator:

        SQL> SELECT 'INSERT INTO scott.emp VALUES
                   ('||empno||q'#,'#'||ename||q'#','#'||job||q'#',#'||
                    decode(mgr,    null, 'NULL', mgr) ||q'#,'#'||
                    hiredate||q'#',#'||
                    decode(sal,    null, 'NULL', sal) ||','||
                    decode(comm,   null, 'NULL', comm) ||','||
                    decode(deptno, null, 'NULL', deptno)||');'
                 AS emp_insert_befehle
               FROM scott.emp;
         

        Lösung ohne Quote Operator:

        SQL> SELECT 'INSERT INTO scott.emp VALUES
                   ('||empno||','''||ename||''','''||job||''','||
                    decode(mgr,    null, 'NULL', mgr) ||','''||
                    hiredate||''','||
                    decode(sal,    null, 'NULL', sal) ||',' ||
                    decode(comm,   null, 'NULL', comm) ||',' ||
                    decode(deptno, null, 'NULL', deptno)||');'
                 AS emp_insert_befehle
               FROM scott.emp;
         

        Fazit

        Die vorangegangenen Beispiele haben Ihnen einen Überblick im Umgang mit Anführungszeichen gegeben und Ihnen den in Version 10g neu eingeführten Quote Operator vorgestellt. Stattdessen kann natürlich auch weiterhin die Charakterdarstellung des ASCII-Wertes 39 benutzt werden (CHR(39)).

        Egal, welche Variante Sie einsetzen, wir hoffen, dass Sie nun allen Problemen mit Anführungszeichen gewachsen sind.

        Die Verallgemeinerung des letzten Beispiels für eine beliebige Tabelle ist etwas komplizierter und allein mit SQL-Mitteln nicht zu erreichen.



        Weitere Interessente Artikel zum Thema:



        Empfohlene Schulungen zum Thema:



          Oracle FLASHBACK

          Bereich:DBA:SQL, Version: ab RDBMS 8.x, Letzte Überarbeitung: 14.01.2019

          Keywords:

          Haben Sie nicht auch schon einmal einen SQL-Befehl abgesetzt (z.B. DROP TABLE oder DROP INDEX) und sich anschließend gewünscht, Sie könnten die Zeit wieder zurückdrehen?
          Bislang war dies entweder nur mit dem Einspielen eines funktionierenden Backups und einem anschließenden unvollständigem Recovery (was in der Regel sehr zeitaufwändig war) oder mit dem Import einer zuvor angelegten Dump-Datei möglich. Bereits in Version 9i wurde das Feature FLASHBACK eingeführt, mit dessen Hilfe es möglich ist, Daten zu einem in der Vergangenheit liegenden Zeitpunkt auf Session- bzw. Statementebene zur Verfügung zu stellen.                                                                               Es konnten jedoch keine DDL-Anweisungen rückgängig gemacht werden. Seit Version 10g wird dieses Feature nun um zusätzliche Konzepte erweitert, durch die sich u.a. auch schwerwiegende Anwenderfehler schnell und einfach beheben lassen.


          Das FLASHBACK DATABASE-Konzept

          Das FLASHBACK DATABASE-Konzept bietet eine schnelle und unkomplizierte Möglichkeit, ein unvollständiges Recovery durchzuführen und damit einen irrtümlich abgesetzten DROP TABLE oder DROP USER-Befehl zurück zu setzen. Dafür gibt es einen neuen Bereich in der Datenbank, die sog. FLASH RECOVERY AREA, in der spezielle FLASHBACK Log-Dateien abgelegt werden. Die Konfiguration dieses Bereichs wird durch verschiedene Initialisierungsparameter gesteuert:

          DB_RECOVERY_FILE_DEST: Datei-Verzeichnis der FLASH RECOVERY AREA
          DB_RECOVERY_FILE_DEST_SIZE: Größe der FLASH RECOVERY AREA (Default:3G in 11g)
          DB_FLASHBACK_RETENTION_TARGET: Zeitspanne in Minuten, um die die DB zurückversetzt werden kann (Default: 1440 = 1 Tag)
          Damit die FLASHBACK DATABASE-Option genutzt werden kann, muss FLASHBACK (bis Oracle 10.x) in der MOUNT-Phase der DB eingeschaltet werden (und sich die DB im Archivierungs-Modus befinden). Ab Version 11.x können Sie den Flashback Modus auch im laufenden Betrieb ein- und ausschalten.

          SQL> SHUTDOWN IMMEDIATE
          SQL> STARTUP MOUNT
          SQL> ALTER DATABASE ARCHIVELOG; -- Archivierung muss eingeschaltet sein
          SQL> ALTER DATABASE FLASHBACK ON;
          -- ALTER DATABASE FLASHBACK OFF schaltet FLASHBACK wieder aus.
          SQL> ALTER DATABASE OPEN;

          Informationen zu Speicherplatz und Rücksprungzeit liefert Ihnen die View v$flashback_database_log:

          SQL> SELECT * FROM v$flashback_database_log;
          OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET
          ---------------------------------------- ------------------------------ ---------------             
          937657 17.03.19 13:41:56             1440
          FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
          -------------------------- -----------------------     
          12288000                122634240


          Das Zurücksetzen der Datenbank im Fehlerfall erfolgt entweder anhand einer SCN oder eines Zeitstempels. Die aktuelle SCN kann über folgenden SELECT ermittelt werden:

          SQL> SELECT current_scn FROM v$database;
          CURRENT_SCN
          ----------    
          935620

          Beispielszenario:
          Vor einer Stunde ist ein wichtiger Benutzer mitsamt seinen Objekten gelöscht worden. Das gesamte Benutzerschema muss unbedingt wieder hergestellt werden. Flashback ist bereits eingeschaltet worden.

          SQL> SHUTDOWN IMMEDIATE
          SQL> STARTUP MOUNT
          SQL> FLASHBACK DATABASE TO TIMESTAMP (sysdate-1/24); -- 1 Stunde zurück
          -- oder SQL> FLASHBACK DATABASE TO SCN 935620;

          Die Datenbank ist nun physikalisch in einen früheren, konsistenten Zustand versetzt worden und lässt sich nur noch mit OPEN RESETLOGS öffnen:

          SQL> ALTER DATABASE OPEN RESETLOGS;

          Das FLASHBACK TABLE-Konzept
          Muss nicht die gesamte Datenbank zurückgesetzt werden, sondern lediglich einzelne Tabellen, wird dies nun mit Hilfe des FLASHBACK TABLE-Kommandos erreicht:  
          Beispiel:
          Tabelle um 12 Minuten zurücksetzen

          SQL> FLASHBACK TABLE scott.emp, scott.dept TO TIMESTAMP (SYSTIMESTAMP -INTERVAL '12' MINUTE);

          Zurücksetzen auf SCN 1234567:

          SQL> FLASHBACK TABLE scott.emp TO SCN 1234567;


          Zurücksetzen auf den 8.8.2019 11 Uhr 09

          FLASHBACK TABLE scott.emp TO TIMESTAMP '2019-08-08 11:09:51';


          Weitere Interval-Beispiele:
          3 Stunden, 4 Minuten und 11 Sekunden:

          INTERVAL '03:04:11.000' HOUR TO SECOND

          1 Stunde, 52 Minuten:

          INTERVAL '01:52' HOUR TO MINUTE


          Mit folgenden SQL Befehl können alte Versionen von Tabellendaten ermittelt werden.

          SELECT versions_xid XID,        
          versions_startscn START_SCN,       
          versions_endscn END_SCN,
          versions_operation OP,
          empno, ename, sal
          FROM scott.emp     VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
          WHERE empno = 7934;
          XID              START_SCN END_SCN O ENAME  SAL
          ---------------- --------- ------- - ----- ----
          0004000700000058    115855         I King1 5000
          000200030000002D    115564         D King1 5000
          000200030000002E    114670  113564 I King2 4000


          Hinweise:
          Auf diese Weise können nur DML-Anweisungen zurückgesetzt werden, keine DDL-Anweisungen. Constraints und Indizes werden mitberücksichtigt. Trigger sind standardmäßig ausgeschaltet, können aber mittels ENABLE TRIGGERS während des FLASHBACKs aktiviert bleiben. Da es während des FLASHBACKs u.U. zu Änderungen der ROWIDs kommen kann, muss vorher die Option ROW MOVEMENT eingeschaltet werden:

          SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
          SQL> ALTER TABLE scott.dept ENABLE ROW MOVEMENT;

          Hinweis:

          Beim Zurückspielen einer Tabelle aus dem Mülleimer, behalten alle mit der Tabelle verbundenen Objekte wie Constraints oder Indizes, den Namen, den Sie im Mülleimer bekommen hatten (laut Oracle ist das ein Feature und kein Bug:-) ).
          Sie heißen dann z.B. BIN$fTZdwdToK3rgVQAAAAAAAQ==$0. Der fettmarkierte Teil ist bei allen gelöschten Objekten gleich.

          Das RECYCLEBIN-Konzept
          Seit Version 10g gibt es bei Oracle eine Art Papierkorb, den sog. RECYCLEBIN. Das normale Löschen einer Tabelle verschiebt die Tabelle lediglich in den RECYCLEBIN und der Speicher wird nicht freigegeben. Den Inhalt des Papierkorbs kann man sich anzeigen lassen:

          SQL> SELECT * FROM recyclebin;
          oder: SQL> SELECT * FROM user_recyclebin;
          oder: SQL> SHOW RECYCLEBIN
          SQL> SELECT * FROM dba_recyclebin;

          Eine gelöschte Tabelle kann wiederhergestellt werden über:

          SQL> FLASHBACK TABLE [<owner>.]<tab_name> TO BEFORE DROP [RENAME TO <tab_neu>];

          Hinweis: Es dürfen seit dem Zeitpunkt, auf dem zurückgesetzt werden soll, keine DDL Befehle (ALTER, TRUNCATE,...) auf dem Objekt abgesetzt worden sein.

          Endgültig gelöscht wird eine Tabelle nun über folgende Befehle:

          SQL> DROP TABLE <tab_name> PURGE; /* löscht die Tabelle sofort und gibt den Speicherplatz frei */
          SQL> PURGE TABLE <tab_name>; /* löscht die Tabelle endgültig aus dem Papierkorb, wenn zuvor ein DROP auf sie abgesetzt wurde */
          SQL> PURGE TABLESPACE <tbs_name>; /* alle bereits gelöschten Objekte aus dem TBS <tbs_name> werden nun endgültig gelöscht */
          SQL> PURGE RECYCLEBIN; /* alle Objekte aus dem Benutzer-Papierkorb werden nun endgültig gelöscht */

          Sie können jedoch auch den Mülleimer der Datenbank abschalten:

          ALTER SYSTEM SET recyclebin=off scope=spfile;


          Jedoch muss für einen Effekt danach die Datenbank neu durchgestartet werden. Witzigerweise konnten wir auch noch gedropte Tabellen aus dem Mülleimer in unserer Oracle 19c SE Datenbank retten, obwohl der Mülleimer abgeschalten war.

          Transaction Flashback



          Weitere Interessente Artikel zum Thema:



          Empfohlene Schulungen zum Thema:


          Spool-file als HTML-Datei ausgeben

          Bereich:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 14.12.2018

          Keywords:SQL

          In diesem Monatstipp befassen wir uns mit der Erstellung eines Spool-file als HTML Dokument. 

          Mit dem SQL*Plus Befehl Set Markup HTML ON [SPOOL ON] lässt sich leicht ein Report bzw. ein ganzes Spool-file als HTML-Datei ausgeben. Dies ist nützlich, falls Sie beispielsweise einen Report in eine Webanwendung einbauen möchten.

          Am besten schreiben Sie sich ein Skript, in welchem alle für Sie wichtigen Abfragen vorkommen. Auf jeden Fall sollte am Anfang des Skripts der Befehl spool <Pfad\Dateiname> vorkommen. Dieser bewirkt die Aufzeichnung Ihrer Abfragen (zum Beispiel: spool c:\TEMP\test.html). Anschließend müssen Sie noch die HTML-Ausgabe einschalten. Das erreichen Sie durch den Markup-Befehl: Set Markup HTML [ON/OFF] (Gibt die Markup-Sprache an)

              SPOOL [ON/OFF] (Gibt an, ob diese auch für das Spool-file gilt) 

          Geben Sie nun mit dem Befehl prompt das Grundgerüst Ihrer HTML-Datei an.

          Das kann zum Beispiel so aussehen:

          prompt <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">-

                 <html><head><title> Spool-file als HTML</title></head> 

          Wenn sich ein SQL-Befehl wie in diesem Beispiel über mehrere Zeilen erstreckt, müssen Sie das durch ein "-" am Ende einer jeden Zeile signalisieren.

          Wenn Sie nun einen einfachen SELECT-Befehl absetzen und anschließend das Spool-file mit spool off abschließen, wird dieser als HTML-Tabelle abgespeichert.

          Das bisherige Skript sieht beispielsweise so aus:

          spool c:\temp\test.html
          set markup HTML ON
          prompt <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">-
                 <html><head><title>SQL*Plus Ausgabe der EMP-Tabelle-
                 </title></head>
          select * from emp;
          select * from dept;
          select * from salgrade;
          spool off
          set markup HTML OFF

           

          Zu sehen ist nun eine einfache Tabelle ohne spezielle Formatierung:

          Um das Ganze jetzt noch ein wenig aufzupeppen können wir noch Stylesheets einfügen. Platzieren Sie dazu einfach in dem ersten prompt-Befehl zwischen </title> und </head>-Tag einen weiteren Zusatz:
           

          <style type="text/css">-
          <!-- a:link{color:#99CCFF;} a:visited{color:#6666FF;} .ora_tab-
          {font-family:Arial; border-width:4px; border-color:#6666FF;-
           border-style:groove; width:auto; }-
           caption.ora_tab {-
           text-align:left;-
           font-size:20pt;-
           font-family:Arial, Verdana, sans-serif;-
           background-color: #dddddd;-
           margin: 0px 0px 0px 0px;-
           padding: 2px 10px 2px 10px;}-
           td { border: 1px solid #d9d9d9; }-
           th { border-color:#66CC66; background-color:#99CCFF;}-
           --></style> 
           

          In diesem CSS-Zusatz können Sie alle möglichen Formatierungsangaben machen. Die genaue HTML-Syntax zu erläutern würde den Rahmen des Monatstipps hier sprengen. (Der CSS-Einschub ist außerdem nicht zwingend erforderlich).

          Um die Angaben für alle weiteren Tabellen zu übernehmen müssen Sie in dem

          set markup-Befehl eine Kleinigkeit erweitern.

          Der Befehl lautet dann wie folgt:
           

          set markup HTML ON TABLE class="ora_tab"
           

          Bisher sieht das Ganze dann so aus:

          Als besonderes "Schmankerl" können Sie sich noch eine Übersichtsliste einfügen mit entsprechenden Links zu verschiedenen Tabellen.

          Angenommen Sie haben eine HTML-Datei mit etwa 20 Tabellen vor sich, da wäre ein kleines Inhaltsverzeichnis mit eingebauten Links doch ziemlich praktisch...

          Realisieren können Sie das, indem Sie zunächst in dem prompt-Befehl eine Liste mit Links einbauen. Der Tag dazu lautet <ul></ul>. Dieser erzeugt eine neue Liste, diese hat allerdings noch keine Einträge. Neue Einträge erstellen Sie mit dem Tag <li></li>. Eine Liste sieht dann beispielsweise so aus:

          <ul>
               <li>1ster Eintrag</li>
               <li>2ter Eintrag</li>
               <li>3ter Eintrag</li>
          </ul>
           

          In unserem Fall müssen wir nach jeder Zeile noch ein "-" einfügen, da SQL*Plus die Eingabe nicht als ganzen Befehl wahrnimmt.

          Jetzt benötigt man nur noch die Syntax für den Hyperlink. Dieser lautet:

          <a name="Linkname">Linktext</a> (Legt einen Link fest)

          <a href="#Linkname">Linktext</a> (verweist auf den Link mit Namen "Linkname") 

          Um einer Tabelle einen Link zuweisen zu können, müssen Sie erst noch einen Tabellentitel erstellen, auf diesen kann dann verwiesen werden. 

          Geben Sie einfach vor einem SELECT-Statement title 'Titeltext mit entsprechendem Link' ein und über der Tabelle erscheint eine Überschrift, auf welche man sich dann im Inhaltsverzeichnis beziehen kann.

          Jetzt sieht das SQL-Skript so aus:

          Tabelle EMP

          Tabelle DEPT

          • Tabelle SALGRADE



          Weitere Interessente Artikel zum Thema:



          Empfohlene Schulungen zum Thema:



            Pivoting in 11g - Zeilen zu Spalten

            Bereich:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 14.12.2018

            Keywords:SQL

            Eine immer wiederkehrende Fragestellung - z.B. bei der Erstellung von Berichten - lautet vereinfacht: "Wie mache ich Zeilen zu Spalten?" Das heisst, Sie haben normalisierte Tabellen, wollen aber den Inhalt gruppieren und in Form einer Kreuztabelle (auch Pivot Tabelle genannt) darstellen.

            Ein sehr vereinfachtes Beispiel soll demonstrieren, was damit gemeint ist. Sie haben folgende Tabelle mit Verkaufszahlen:

            ID    KUNDE      PRODUKT      MENGE
            ---   --------   --------     -----
              1   Kunde A    Kalender       100
              2   Kunde B    Block           40
              3   Kunde C    Heft            70
              4   Kunde D    Block          100
              5   Kunde A    Block          200
              6   Kunde D    Heft            30


            Diese wurde durch folgendes Skript erzeugt:

            CREATE TABLE VERKAUF
            (
            ID      NUMBER CONSTRAINT PK_VERKAUF PRIMARY KEY,
            KUNDE   VARCHAR2(100) NOT NULL,
            PRODUKT VARCHAR2(100) NOT NULL,
            MENGE   NUMBER NOT NULL
            )
            /
            INSERT INTO VERKAUF
            VALUES(1, 'Kunde A', 'Kalender', 100);
            INSERT INTO VERKAUF
            VALUES(2, 'Kunde B', 'Block', 40);
            INSERT INTO VERKAUF
            VALUES(3, 'Kunde C', 'Heft', 70);
            INSERT INTO VERKAUF
            VALUES(4, 'Kunde D', 'Block', 100);
            INSERT INTO VERKAUF
            VALUES(5, 'Kunde A', 'Block', 200);
            INSERT INTO VERKAUF
            VALUES(6, 'Kunde D', 'Heft', 30);


            Nun hätten Sie gerne folgende Darstellung:

            KUNDE     KALENDER      BLOCK               HEFT
            --------  ----------    ----------    ----------
            Kunde A          100           200    
            Kunde B                         40    
            Kunde C                                       70
            Kunde D                        100            30


            Das geht auch schon vor Version 11g, z.B. durch folgende Anweisung:

            SELECT kunde,
               SUM(DECODE (produkt, 'Kalender', menge, NULL)) Kalender,
               SUM(DECODE (produkt, 'Block', menge, NULL)) Block,
               SUM(DECODE (produkt, 'Heft', menge, NULL)) Heft
              FROM VERKAUF
             GROUP BY kunde
             ORDER BY kunde;

            Zugegeben - nicht sonderlich intuitiv, nicht gut lesbar und einiges an Tipparbeit - aber es funktioniert.

            In 11g wurde für solche Zwecke eigens eine neue Klausel eingeführt - PIVOT. Ein Select, der das gleiche Ergebnis (mit einem kleinen Schönheitsfehler, s.u. ) liefert, würde dann so aussehen:

            SELECT * FROM
               (SELECT kunde, produkt, menge FROM VERKAUF)
              PIVOT
              (
               SUM(menge)
               FOR produkt
               IN ('Kalender', 'Block', 'Heft')
              )
            ORDER BY kunde;


            Sehen wir uns die Syntax genauer an. Innerhalb der PIVOT-Klausel müssen Sie angeben:

            Die Art der Aggregierung(en) - hier SUM(menge)

            FOR-Klausel: Die Inhalte welcher Spalte zu Zeilen werden sollen - hier produkt

            • IN-Klausel: Welche Werte innerhalb dieser Spalte verwendet werden sollen - hier 'Kalender', 'Block' und 'Heft'

            Nach allen weiteren im Select angegebenen Spalten wird implizit gruppiert - hier also nach Kunde. Sobald Sie nur nach bestimmten Spalten gruppieren wollen (was praktisch immer der Fall ist), müssen Sie mit einer Inline View (wie hier gezeigt) oder alternativ mit der WITH-Klausel arbeiten.

            Der oben angesprochene Schönheitsfehler liegt darin, dass die Spaltenüberschriften genau den angegebenen Werten entsprechen, inklusive Hochkommata. Um das zu umgehen - oder weil Sie sowieso andere Überschriften wollen - können Sie mit Spaltenaliasen arbeiten, wie Sie es gewohnt sind:

            SELECT * FROM
               (SELECT kunde, produkt, menge FROM VERKAUF)
              PIVOT
              (
               SUM(menge)
               FOR produkt
               IN ('Kalender' AS kalender,
                   'Block' block,
                   'Heft' "Anzahl Hefte")
              )
            ORDER BY kunde;


            Sie können auch, durch Kommata getrennt, mehrere Aggregatsfunktionen angeben; in diesem Fall müssen Sie zusätzlich einen Alias mit angeben, der dann an die Spaltenüberschrift angehängt wird:

            SELECT * FROM
               (SELECT produkt, menge
                 FROM VERKAUF )
              PIVOT
              (
               SUM(menge) summe,
               COUNT(menge) anzahl
               FOR produkt
               IN ('Kalender' as kal, 'Block' as Block, 'Heft' as Heft)
              );

             

            KAL_SUMME KAL_ANZAHL BLOCK_SUMME BLOCK_ANZAHL HEFT_SUMME HEFT_ANZAHL
            --------- ---------- ----------- ------------ ---------- -----------
                  100          1         340            3        100           2


            Eine Einschränkung bleibt: Sie können nicht dynamisch arbeiten, sondern müssen explizit die Werte angeben.

            Gibt man das Schlüsselwort XML mit an, so erhält man statt einzelner Spalten für jeden Wert eine einzige neue Spalte vom Typ XMLType, die alle Wertepaare als XML-Fragment beinhaltet. Hier hat man die Wahl zwischen dem Schlüsselwort ANY und einer Unterabfrage. Die explizite Angabe einzelner Werte ist in diesem Fall wiederum nicht zulässig. ANY steht als Platzhalter für alle Werte, die in der Spalte vorkommen. Der Unterschied in der Ausgabe liegt darin, dass bei einer Unterabfrage im erzeugten XML grundsätzlich alle Produkte erscheinen, auch wenn kein Eintrag für das entsprechende Produkt da ist, während bei ANY nur diejenigen Produkte erscheinen, für die es einen Wert gibt, in folgenden nur gezeigt für Kunde B. Beachten Sie, dass die PIVOT-Klausel noch vor der WHERE-Klausel stehen muss:

            COL PRODUKT_XML FOR a50
            SET LONG 2000
            SELECT * FROM
               (SELECT kunde, produkt, menge FROM VERKAUF)
              PIVOT XML
              (
               SUM(menge) menge
               FOR produkt
               IN (ANY)
              )
            WHERE kunde = 'Kunde B';

             

            KUNDE      PRODUKT_XML
            ---------- --------------------------------------------------
            Kunde B    <PivotSet><item><column name = "PRODUKT">Block</co
                       lumn><column name = "MENGE">40</column></item></Pi
                       votSet>

            -- bzw:

            SELECT * FROM
               (SELECT kunde, produkt, menge FROM VERKAUF)
              PIVOT XML
              (
               SUM(menge) menge
               FOR produkt
               IN (SELECT DISTINCT produkt FROM VERKAUF)
              )
            WHERE kunde = 'Kunde B';

             

            KUNDE      PRODUKT_XML
            ---------- --------------------------------------------------
            Kunde B    <PivotSet><item><column name = "PRODUKT">Block</co
                       lumn><column name = "MENGE">40</column></item><ite
                       m><column name = "PRODUKT">Heft</column><column na
                       me = "MENGE"></column></item><item><column name =
                       "PRODUKT">Kalender</column><column name = "MENGE">
                        </column></item></PivotSet>


            UNPIVOT – Spalten zu Zeilen

            Die Umkehrung - Spalten als Zeilen ausgeben - ist ab 11g auch sehr leicht möglich. Gehen wir von folgender Tabelle aus:

            KUNDE     KALENDER    BLOCK       HEFT
            --------  ----------  ----------  ----------
            Kunde A          100        200    
            Kunde B                      40    
            Kunde C                                   70
            Kunde D                     100           30


            Erzeugt wurde sie durch:

            CREATE TABLE REPORT AS
            SELECT * FROM
               (SELECT kunde, produkt, menge FROM VERKAUF)
              PIVOT
              (
               SUM(menge)
               FOR produkt
               IN ('Kalender' AS kalender,
                   'Block' block,
                   'Heft' heft)
              )
            ORDER BY kunde;


            Will man vor 11g aus Artikel- und zugehörigen Mengenangaben Einzeleinträge machen, so ist das zwar machbar, aber umständlich und sehr imperformant, da die Tabelle mehrfach gelesen werden muss:

            SELECT kunde, 'KALENDER', kalender FROM REPORT
            -- WHERE kalender IS NOT NULL
            UNION ALL
            SELECT kunde, 'BLOCK', block FROM REPORT
            -- WHERE block IS NOT NULL
            UNION ALL
            SELECT kunde, 'HEFT', heft FROM REPORT
            -- WHERE heft IS NOT NULL
            ;


            Hier ist die neue UNPIVOT-Klausel wesentlich einfacher:

            SELECT * FROM REPORT
              UNPIVOT
              (
               menge
               FOR produkt
               IN (kalender, block, heft)
              );

             

            KUNDE      PRODUKT       MENGE
            ---------- -------- ----------
            Kunde A    KALENDER        100
            Kunde A    BLOCK           200
            Kunde B    BLOCK            40
            Kunde C    HEFT             70
            Kunde D    BLOCK           100
            Kunde D    HEFT             30


            Werfen wir auch hier einen Blick auf die Syntax. Angegeben werden muss hier:

            Eine Spaltenüberschrift für die Werte - hier menge

            Eine Spaltenüberschrift für die Spalten, die zu Zeilen werden sollen - hier produkt

            • Eine Liste der Spalten, die einbezogen werden sollen - hier kalender, block, heft

            Auch hier gilt: Soll nur ein Teil der Spalten ausgegeben werden, so muss mit einer Unterabfrage gearbeitet werden.

            Obiger Select beispielsweise wäre folgendermaßen umzuwandeln, wenn die Tabelle noch mehr Spalten enthielte:

            SELECT * FROM
               (SELECT kunde, kalender, block, heft FROM REPORT)
              UNPIVOT
              (
               menge
               FOR produkt
               IN (kalender, block, heft)
              );


            Standardmäßig werden für NULL-Werte keine Zeilen ausgegeben. Diese können aber optional angefordert werden durch INCLUDE NULLS:

            SELECT * FROM REPORT
              UNPIVOT INCLUDE NULLS
              (
               menge
               FOR produkt
               IN (kalender, block, heft)
              );
             
            KUNDE      PRODUKT       MENGE
            ---------- -------- ----------
            Kunde A    KALENDER        100
            Kunde A    BLOCK           200
            Kunde A    HEFT
            Kunde B    KALENDER
            Kunde B    BLOCK            40
            Kunde B    HEFT
            Kunde C    KALENDER
            Kunde C    BLOCK
            Kunde C    HEFT             70
            Kunde D    KALENDER
            Kunde D    BLOCK           100
            Kunde D    HEFT             30


            Wird statt INCLUDE NULLS angegeben EXCLUDE NULLS, so entspricht dies dem Standardverhalten.



            Weitere Interessente Artikel zum Thema:



            Empfohlene Schulungen zum Thema:



              Sperren auf Tabellen in einer Oracle Datenbank

              Bereich:DBA:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 14.12.2018

              Keywords:DBA , SQL

              In jeder Oracle Datenbank mit mehr als einer Session kann es zu Konflikten mit Sperren kommen. Sperren, in der Datenbanksprache Locks genannt, verhindern unter anderem, dass mehrere Benutzer zur gleichen Zeit die gleiche Zeile einer Tabelle verändern können.

              Innerhalb einer Opens external link in new windowTransaktion können eine oder mehrere Zeilen, sowie ganze Tabellen gesperrt werden. Oracle versucht automatisch (implizit) zu Beginn einer Transaktion die betroffenen Zeilen auf niedrigstem Level zu sperren. Jeder Benutzer kann auch manuell (explizit) eine Sperre auf Tabellen Ebene setzen.

              Es gibt unterschiedliche Modi, wie Tabellen und Zeilen gesperrt werden. Die einzelnen Sperrmodi und welcher Zugriff dabei für andere Benutzer noch möglich ist, wird in diesem Monatstipp etwas genauer beleuchtet.

              ROW SHARE

              Ist die am wenigsten restriktive Sperrmethode

              LOCK TABLE tabelle IN ROW SHARE MODE;


              Erlaubt anderen Transaktionen gleichzeitigen Zugriff auf die gesperrte Tabelle. Select, Update, Insert, Delete Operationen, sowie row share, row exclusive, share und share row exclusiv Sperren sind erlaubt.

              Verhindert, dass andere Transaktionen die Tabelle für exklusiven Zugriff sperren:

              LOCK TABLE tabelle IN EXCLUSIVE MODE;


              Beispiel:

              -- Session 1 (Scott):
              lock table emp in row share mode;
              Tabelle(n) wurde(n) gesperrt.

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;

              SESSION_ID OWNER    NAME  MODE_HELD       BLOCKING_OTHERS
              ---------- -------- ----- --------------- ---------------
              141        SCOTT    EMP   Row-S (SS)      Not Blocking
              -- Session 2 (Scott):
              update emp set job = 'BOSS' where empno = 7788;
              1 Zeile wurde aktualisiert.
              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ---------------
              141        SCOTT    EMP   Row-S (SS)    Not Blocking
              133        SCOTT    EMP   Row-X (SX)    Not Blocking

              -- Session 2 (Scott):
              lock table emp in exclusive mode;

              -- Wartet auf rollback oder commit der Session 1

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;

              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ---------------
              141        SCOTT    EMP   Row-S (SS)    Blocking
              133        SCOTT    EMP   None          Not Blocking


              Erläuterung:

              Nach dem Lock Table und Update Statement halten beide Sessions Sperren auf der Tabelle EMP. Wird mit der zweiten Session versucht, die Tabelle exklusiv zu sperren, wird dieses verhindert, indem die 1. Session die 2. Session blockiert. Gleichzeitig wechselt der Status der Sperre der 1. Session in der Spalte BLOCKING_OTHERS in der View DBA_DML_LOCKS von Not Blocking auf Blocking.
              Aufgelöst wird diese Situation, indem die blockierende Session die Transaktion mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden.


              ROW EXCLUCIVE

              Diese Sperrmethode wird automatisch von Oracle für DML Operationen verwendet.

              Insert INTO TABLE tabelle;
              Update TABLE tabelle;
              Delete FROM tabelle;
              Select for update;
              LOCK TABLE tabelle IN ROW EXCLUSIVE MODE;


              Erlaubt anderen Transaktionen gleichzeitigen Zugriff auf die gesperrte Tabelle. Select, Update, Insert, Delete Operationen, sowie row share und row exclusive Sperren sind erlaubt.
              Wird versucht, auf eine im row exclusive Mode gesperrte Zeile eine weitere Schreiboperation (UPDATE) durchzuführen, funktioniert das jeweilige Statement erst, wenn die Sperre aufgehoben wird.
              Verhindert, dass andere Transaktionen die Tabelle für exklusiven Zugriff sperren und verhindert auch, dass die Tabelle im SHARE Mode gesperrt wird:

              LOCK TABLE tabelle IN SHARE MODE;
              LOCK TABLE tabelle IN SHARE ROW EXCLUSIVE MODE;
              LOCK TABLE tabelle IN EXCLUSIVE MODE;


              Beispiel:

              -- Session 1(Scott):
              update emp set job = 'BOSS' where empno = 7788;
              1 Zeile wurde aktualisiert.

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;

              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              141        SCOTT    EMP   Row-X (SX)    Not Blocking

              -- Session 2 (Scott):
              update emp set job = 'BOSS' where empno = 4711;
              1 Zeile wurde aktualisiert.

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;

              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              141        SCOTT    EMP   Row-X (SX)    Not Blocking
              133        SCOTT    EMP   Row-X (SX)    Not Blocking

              -- Session 2 (Scott):
              lock table emp in share mode;

              -- Wartet auf rollback oder commit der Session 1

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;

              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              141        SCOTT    EMP   Row-X (SX)    Blocking
              133        SCOTT    EMP   None          Not Blocking


              Erläuterung:
              Auch in diesem Beispiel wird klar, dass alle nicht erlaubten Sperrversuche einen Statuswechsel von BLOCKING_OTHERS auslösen.
              Aufgelöst wird diese Situation, indem die blockierende Session die Transaktion mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden.


              SHARE

              Diese Sperrmethode ist erforderlich, wenn ein Index auf einer Tabelle erstellt werden soll und wird automatisch von Oracle gesetzt, wenn ein CREATE INDEX Statement abgesetzt wird.

              Create Index name ON tabelle(spalte);
              LOCK TABLE tabelle IN SHARE MODE;


              Erlaubt anderen Transaktionen gleichzeitige lesende Zugriffe auf die Tabelle. Auch eine andere Transaktion kann auf die gleiche Tabelle einen LOCK TABLE IN SHARE MODE absetzen. In einem solchen Fall kann keine der beiden Transaktionen Schreiboperationen auf der Tabelle durchführen. Schreiboperationen (siehe Beispiel update) warten bis die Sperre aufgehoben ist.
              Verhindert gleichzeitig Schreiboperationen auf Tabellenebene und Sperren in folgenden Modi:

              LOCK TABLE tabelle IN SHARE ROW EXCLUSIVE MODE;
              LOCK TABLE tabelle IN ROW EXCLUSIVE MODE;
              LOCK TABLE tabelle IN EXCLUSIVE MODE;


              Beispiel:

              -- Session 1(Scott):
              create index idx_big on big_emp(hiredate);

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME    MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ------- ------------- ----------------
              141        SCOTT    BIG_EMP Share         Not Blocking

              -- Session 2 (Scott):
              update big_emp set comm = 100 where empno = 4711

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME    MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ------- ------------- ----------------
              141        SCOTT    BIG_EMP Share         Blocking
              133        SCOTT    BIG_EMP None          Not Blocking

              -- Session 1(Scott):
              Index wurde erstellt.

              -- Session 2(Scott):
              1048576 Zeilen wurden aktualisiert.

              -- Session 1(Scott):
              lock table emp in share mode;

              Tabelle(n) wurde(n) gesperrt.

              -- Session 2(Scott):
              lock table emp in share mode;

              Tabelle(n) wurde(n) gesperrt.

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              141        SCOTT    EMP   Share         Not Blocking
              133        SCOTT    EMP   Share         Not Blocking

              -- Session 2 (Scott):
              insert into emp(empno) values(2222);
              -- Session 2 wartet

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              141        SCOTT    EMP   Share         Blocking
              133        SCOTT    EMP   Share         Blocking


              Erläuterung:
              Aufgelöst wird diese Situation, indem die erste Session die Sperre mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden.


              SHARE ROW EXCLUSIVE

              Dieser Modus wird verwendet um eine komplette Tabelle unverändert zu lesen:

              LOCK TABLE tabelle IN SHARE ROW EXCLUXIVE MODE;


              Erlaubt anderen Transaktionen die Tabelle zu lesen.
              Verhindert dass andere Transaktionen die Tabelle im SHARE Modus sperren, oder Änderungen durchführen dürfen:

              LOCK TABLE tabelle IN SHARE MODE;
              LOCK TABLE tabelle IN SHARE ROW EXCLUSIVE MODE;
              LOCK TABLE tabelle IN ROW EXCLUSIVE MODE;
              LOCK TABLE tabelle IN EXCLUSIVE MODE;


              Beispiel:

              -- Session 1(Scott):
              lock table emp in share row exclusive mode;

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              141        SCOTT    EMP   S/Row-X (SSX) Not Blocking

              -- Session 2 (Scott):
              update emp set comm = 100 where empno = 4711;
              -- Session 2 wartet

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              141        SCOTT    EMP   S/Row-X (SSX) Blocking
              133        SCOTT    EMP   None          Not Blocking

              -- Session 2 (Scott):
              lock table emp in share mode;
              -- Session 2 wartet

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              141        SCOTT    EMP   S/Row-X (SSX) Blocking
              133        SCOTT    EMP   None          Not Blocking


              Erläuterung:
              Aufgelöst wird diese Situation indem die erste Session die Sperre mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden.


              EXCLUSIVE

              Das ist die restriktivste Methode eine Tabelle zu sperren. Dieser Sperrmodus erlaubt nur der eigenen Transaktion exklusiven Schreibzugriff auf die Tabelle.

              LOCK TABLE tabelle IN EXCLUSIVE MODE;
              DROP TABLE tabelle;
              ALTER TABLE tabelle;
              TRUNCATE TABLE tabelle;


              Erlaubt anderen Transaktionen lesenden Zugriff auf die gesperrte Tabelle. Nur eine Transaktion kann eine exklusive Sperre auf eine Tabelle setzen. Weitere Schreiboperationen warten darauf, dass die Sperre aufgehoben wird.
              Verhindert jeglichen schreibenden Zugriff auf irgendeine Zeile durch andere Transaktionen, sowie manuelle Sperren in folgenden Modi:

              LOCK TABLE tabelle IN ROW SHARE MODE;
              LOCK TABLE tabelle IN SHARE MODE;
              LOCK TABLE tabelle IN SHARE ROW EXCLUSIVE MODE;
              LOCK TABLE tabelle IN ROW EXCLUSIVE MODE;
              LOCK TABLE tabelle IN EXCLUSIVE MODE;


              Beispiel:

              -- Session 1(Scott):
              lock table emp in exclusive mode;

              -- Session 3 (Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              163        SCOTT    EMP   Exclusive     Not Blocking

              -- Session 2(Scott):
              lock table emp in row share mode;
              -- Session 2 wartet

              -- Session 3(Sys)
              select session_id, owner, name, mode_held, blocking_others from dba_dml_locks;
              SESSION_ID OWNER    NAME  MODE_HELD     BLOCKING_OTHERS
              ---------- -------- ----- ------------- ----------------
              163        SCOTT    EMP   Exclusive     Blocking
              138        SCOTT    EMP   None          Not Blocking


              Erläuterung:
              Aufgelöst wird diese Situation indem die erste Session die Sperre mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden.

              An dieser Stelle kommt noch eine kurze Aufschlüsselung der Abkürzungen in der Spalte MODE_HELD der View DBA_DML_LOCKS. Die View DBA_DML_LOCKS wird von dem Skript ORACLE_HOME\rdbms\ADMIN\catblock.sql angelegt.

              ModusWird von Oracle auch genanntAbkürzung in MODE_HELD
              ROW EXCLUSIVEsubexclusive table lockRow-X (SX)
              ROW SHAREsubshare table lockRow-S (SS)
              SHARE-Share
              SHARE ROW EXCLUSIVEshare-subexclusive tableS/Row-X (SSX)
              EXCLUSIVElockExclusive


              Dieser Monatstipp enthält nur einen kleinen Ausschnitt der in Oracle möglichen Sperren. Die unterschiedlichen Sperrmöglichkeiten werden u.a. in der View V$LOCK_TYPE gelistet. Dort findet sich auch eine kurze Beschreibung zu den jeweiligen Sperren.



              Weitere Interessente Artikel zum Thema:



              Empfohlene Schulungen zum Thema:



                Undokumentierte Funktion zum Abfragen von NULL Werten

                Bereich:PL/SQL:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.12.2018

                Keywords:PL/SQL , SQL

                Haben wir uns nicht alle schon mal darüber geärgert, dass auf NULL Spalten nur mit der folgenden Klausel abgefragt werden kann:

                SELECT * FROM tab WHERE col IS NULL;
                 

                Seit Version 10g gibt es eine undokumentierte Funktion (und damit ist sie

                leider auf Produktionsdatenbanken nur auf eigene Gefahr einsetzbar).

                Die Funktion sys_op_map_nonnull kann zwei NULL Spalten vergleichen.

                Beispiel: Alle Zeilen sollen ausgegeben werden, in denen die Spalte

                mgr=comm= NULL ist:

                select * from scott.emp
                where sys_op_map_nonnull(mgr)=sys_op_map_nonnull(comm)
                 

                Oder, alle Zeilen ausgeben, deren Spalte COMM = NULL ist:

                select * from scott.emp
                where sys_op_map_nonnull(comm)=sys_op_map_nonnull(NULL);
                 

                Zum Vergleich: Die offizielle Klausel lautet:

                select * from scott.emp
                where comm is NULL;


                Weitere Interessente Artikel zum Thema:



                Empfohlene Schulungen zum Thema:



                  Löschen von doppelten Datensätzen

                  Bereich:SQL, Version: ab APEX 4.x, Letzte Überarbeitung: 29.06.2018

                  Keywords:SQL

                  Zum Löschen von doppelten Datensätzen gibt es eine ganze Reihe von Ansätzen. Die Suche nach der Schlagwort-Kombination duplicate delete und Oracle ergibt bei Google ca. 451.000 Treffer. Viele der Methoden funktionieren hervorragend bei den 10 bis 30 Beispieldatensätzen, die meist auch keine NULL-Werte enthalten. Wenn die Tabellen aber größer werden, stellt sich die Frage nach der performantesten Methode.

                  Dieser Monatstipp stellt die gängigsten Methoden am Beispiel einer manipulierten emp-Tabelle vor und vergleicht danach deren Performance anhand einer Tabelle mit 2 Mio. Datensätzen.

                  Vorbereitung der emp-Tabelle:
                  Ein Datensatz wird verdoppelt, ein anderer verdreifacht, nur die Primärschlüssel bleiben unangetastet.

                  UPDATE emp SET (ename, job, mgr, hiredate, sal, comm, deptno)
                      = (SELECT ename, job, mgr, hiredate, sal, comm, deptno
                         FROM emp WHERE empno = 7369)
                  WHERE empno = 7566;
                  UPDATE emp SET (ename, job, mgr, hiredate, sal, comm, deptno)
                       = (SELECT ename, job, mgr, hiredate, sal, comm, deptno
                          FROM emp WHERE empno = 7499)
                   WHERE empno in (7839, 7902);
                  COMMIT;

                   

                  WIE FINDET MAN DIE DUPLIKATE


                  Auch hier gibt es mehrere Ansätze, der gebräuchlichste ist sicher:

                  SELECT spaltenliste, COUNT(*)
                  FROM tabelle
                  GROUP BY spaltenliste
                  HAVING COUNT(*) > 1;

                  Mit Spaltenliste ist hier die Kombination der Spalten gemeint, die keine Duplikate aufweisen soll.

                  SELECT ename, job, mgr, hiredate, sal, comm, deptno, COUNT(*)
                  FROM emp
                  GROUP BY ename, job, mgr, hiredate, sal, comm, deptno
                  HAVING COUNT(*) > 1;
                  ENAME   JOB          MGR HIREDATE    SAL  COMM DEPTNO   COUNT(*)
                  ------- ---------- ----- -------- ------ ----- ------ ----------
                  ALLEN   SALESMAN    7698 20.02.81   1600   300     30          3
                  SMITH   CLERK       7902 17.12.80    800           20          2

                  Dieser alternative Select mit einer korrelierten Unterabfrage liefert die kompletten Datensätze der Duplikate, sofern man NULL-Spalten mit NVL entschärft (sonst wird hier z.B. das Duplikat von Smith nicht angezeigt)

                  SELECT * FROM emp e1
                  WHERE EXISTS (SELECT 1 FROM emp e2
                                 WHERE e2.ename = e1.ename
                                     AND e1.job = e2.job
                                     AND e1.mgr = e1.mgr
                                AND e1.hiredate = e2.hiredate
                                     AND e1.sal = e2.sal
                             AND NVL(e1.comm,0) = NVL(e2.comm,0)
                                  AND e1.deptno = e1.deptno
                                   AND e2.rowid < e1.rowid);
                  EMPNO ENAME   JOB          MGR HIREDATE    SAL  COMM DEPTNO
                  ----- ------- ---------- ----- -------- ------ ----- ------
                   7566 SMITH   CLERK       7902 17.12.80    800           20
                   7839 ALLEN   SALESMAN    7698 20.02.81   1600   300     30
                   7902 ALLEN   SALESMAN    7698 20.02.81   1600   300     30

                  Eine dritte, eher exotische Methode besteht darin, einen Unique-Constraint auf die Spaltenkombination zu setzen, die eindeutig sein soll und die Fehler (die Duplikate) in die von Oracle über das Skript utlexcpt.sql zur Verfügung gestellte Exceptions-Tabelle zu schreiben.

                  @ ?\rdbms\admin\utlexcpt
                  ALTER TABLE emp ADD CONSTRAINT emp_uq
                  UNIQUE (ename, job, mgr, hiredate, sal, comm, deptno)
                  EXCEPTIONS INTO EXCEPTIONS;
                  SELECT e.* FROM exceptions x JOIN emp e ON e.rowid = x.row_id;
                  =>
                  EMPNO ENAME   JOB          MGR HIREDATE    SAL  COMM DEPTNO
                  ----- ------- ---------- ----- -------- ------ ----- ------
                   7369 SMITH   CLERK       7902 17.12.80    800           20
                   7499 ALLEN   SALESMAN    7698 20.02.81   1600   300     30
                   7566 SMITH   CLERK       7902 17.12.80    800           20
                   7839 ALLEN   SALESMAN    7698 20.02.81   1600   300     30
                   7902 ALLEN   SALESMAN    7698 20.02.81   1600   300     30

                   

                  METHODE 1: LÖSCHEN DER DUPLIKATE ÜBER EINE NICHT-KORRELIERTE UNTERABFRAGE


                  Dies ist sicher der bekannteste Ansatz:

                  DELETE FROM tabelle
                  WHERE rowid NOT IN (SELECT MIN(rowid) FROM tabelle
                                      GROUP BY spaltenliste);

                  In unserem Beispiel also:

                  DELETE FROM emp
                  WHERE rowid NOT IN (SELECT MIN(rowid) FROM emp
                                      GROUP BY ename, job, mgr, hiredate, sal, comm, deptno);
                  3 rows deleted.
                  ROLLBACK;

                   

                  METHODE 2: LÖSCHEN DER DUPLIKATE ÜBER EINE KORRELIERTE UNTERABFRAGE.


                  Allgemeine Syntax:

                  DELETE FROM tabelle t1
                       WHERE rowid < (SELECT MAX(rowid) FROM tabelle t2
                                      WHERE t1.col1 = t2.col1
                                      AND   t1.col2 = t2.col2
                                      AND   t1.col3 = t2.col3 ....);

                  Statt rowid < (SELECT MAX(rowid)... kann man natürlich auch rowid > (SELECT MIN(rowid)... oder rowid < ANY (SELECT rowid... verwenden

                  Nachteile:
                  •    Ohne die Behandlung von NULL-Spalten mit NVL erwischt man nur einen Teil der Datensätze !!!!
                  •    Korrelierte Update- und Delete-Statements sind als besonders unperformant berüchtigt
                  •    Bei Tabellen mit vielen Spalten wird das Statement sehr lang

                  Das Statement sieht bei der manipulierten emp-Tabelle dann so aus:

                  DELETE FROM emp e1
                       WHERE rowid < (SELECT MAX(rowid) FROM emp e2
                                      WHERE e1.ename     = e2.ename
                                      AND  e1.job        = e2.job
                                      AND  e1.mgr        = e2.mgr
                                      AND  e1.hiredate   = e2.hiredate
                                      AND  e1.sal        = e2.sal
                                      AND NVL(e1.comm,0) = NVL(e2.comm,0)
                                      AND  e1.deptno     = e2.deptno);
                  3 rows deleted.
                  ROLLBACK;

                   

                  METHODE 3: LÖSCHEN DER DUPLIKATE ÜBER ANALYTISCHE FUNKTIONEN


                  Von Tom Kyte empfohlen. Näheres zu analytischen Funktionen erfahren Sie in unserem SQL II Kurs.

                  Allgemeine Syntax:
                  DELETE FROM tabelle
                  WHERE rowid IN
                     (SELECT rid FROM
                        (SELECT rowid rid,
                               ROW_NUMBER() OVER(PARTITION BY spaltenliste ORDER BY rowid) rn
                         FROM tabelle)
                  WHERE rn <> 1);

                  In unserem Beispiel also:

                  DELETE FROM emp
                  WHERE rowid IN
                     (SELECT rid FROM
                        (SELECT rowid rid,
                               ROW_NUMBER()
                               OVER(PARTITION BY ename, job, mgr, hiredate, sal, comm, deptno
                               ORDER BY rowid) rn
                         FROM emp)
                      WHERE rn <> 1);
                  3 rows deleted.
                  ROLLBACK;

                   

                  METHODE 4 (AUSSER KONKURRENZ): ERSTELLEN EINER NEUEN TABELLE OHNE DUPLIKATE


                  Auch diesen Ansatz findet man in dem oben angeführten Artikel von Tom Kyte. Er ist besonders schnell, aber in Produktivumgebungen kaum umzusetzen. Man erstellt aus den gewünschten Daten eine neue Tabelle, löscht die alte, erstellt die Indizes neu und benennt die neue Tabelle um.

                  CREATE TABLE emp2 AS
                  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
                  FROM (SELECT b.*,
                            ROW_NUMBER()
                            OVER(PARTITION BY ename, job, mgr, hiredate, sal, comm, deptno
                            ORDER BY rowid) rn
                        FROM emp b)
                  WHERE rn = 1;
                  DROP TABLE emp PURGE;
                  ALTER TABLE emp2 ADD CONSTRAINT emp_pk PRIMARY KEY(empno);
                  RENAME emp2 TO emp;

                   

                  PERFORMANCE-TESTS MIT GROSSEN TABELLEN


                  Für die Performance-Tests wurde eine Tabelle mit 2 Mio. Datensätzen auf Basis der dba_objects (Tom Kytes big_tab) im Schema Scott verwendet. Auch hier sind nur die Primärschlüssel noch unique.
                  Duplikate ermitteln:

                  conn sys/sys as sysdba
                  set timing on
                  SELECT COUNT(*), zahl
                  FROM (SELECT owner, object_name, subobject_name, object_id, data_object_id,
                               object_type, created, last_ddl_time, timestamp, status,
                               temporary, generated, secondary, COUNT(*) zahl
                       FROM scott.big_tab
                       GROUP BY owner, object_name, subobject_name, object_id, data_object_id,
                                object_type, created, last_ddl_time, timestamp, status,
                               temporary, generated, secondary
                       HAVING COUNT(*) > 1)
                  GROUP BY zahl;
                  =>
                  -- für 10g
                    COUNT(*)       ZAHL
                  ---------- ----------
                       48518         40
                        1520         39
                  -- für 11g
                  COUNT(*)       ZAHL
                  ---------- ----------
                       36128         28
                       36608         27

                   Löschen der Duplikate über eine nicht-korrelierte Unterabfrage

                  DELETE FROM scott.big_tab
                  WHERE rowid NOT IN (SELECT MIN(rowid) FROM scott.big_tab
                                      GROUP BY owner, object_name, subobject_name, object_id,
                                           data_object_id, object_type, created, last_ddl_time,
                                           timestamp, status, temporary, generated, secondary);
                  -- Laufzeiten für 10g: zwischen 1:29,76 und 1:35.00 Minuten
                  -- Laufzeiten für 11g: zwischen 1:30.18 und 2:00.85 Minuten

                  Löschen der Duplikate über eine korrelierte Unterabfrage.

                  Trotz der prinzipiell gleichen Hard- und Software-Ausstattung waren die Laufzeiten hier sehr unterschiedlich. 2 Server lagen reproduzierbar zwischen 16 und 18 Minuten, einer brachte es auf 5-6 Minuten.


                  DELETE FROM scott.big_tab b1
                  WHERE rowid <
                    (SELECT MAX(rowid) FROM scott.big_tab b2
                     WHERE b1.owner                      = b2.owner
                     AND b1.object_name                  = b2.object_name
                     AND NVL(b1.subobject_name, 'nn')    = NVL(b2.subobject_name, 'nn')
                     AND b1.object_id                    = b2.object_id
                     AND NVL(b1.data_object_id,0)        = NVL(b2.data_object_id,0)
                     AND b1.object_type                  = b2.object_type
                     AND b1.created                      = b2.created
                     AND NVL(b1.last_ddl_time,sysdate)   = NVL(b2.last_ddl_time,sysdate)
                     AND NVL(b1.timestamp, systimestamp) = NVL(b2.timestamp, systimestamp)
                     AND b1.status                       = b2.status
                     AND b1.temporary                    = b2.temporary
                     AND b1.generated                    = b2.generated
                     AND b1.secondary                    = b2.secondary);
                  -- Laufzeiten für 10g: zwischen 5:44,96 und 16:39,74 Minuten
                  -- Laufzeiten für 11g: zwischen 6:31,56 und 18:22.90 Minuten

                  Löschen der Duplikate über analytische Funktionen

                  DELETE FROM scott.big_tab

                  Neue Klausel in der Create Table Anweisung

                  Bereich:SQL, Version: ab RDBMS 11.2, Letzte Überarbeitung: 29.06.2018

                  Keywords:DBA, Oracle Neuerungen, SQL, 11g Release

                  Mit dem Release 11.2 hat Oracle eine Erweiterung des CREATE TABLE Statements eingeführt. Es handelt sich um die verzögerte Segmenterzeugung oder DEFERRED SEGMENT CREATION.

                  Was macht dieses neue Feature? Wird eine neue Tabelle erzeugt, werden nur die Metainformationen angelegt, aber noch kein Segment im Tablespace erzeugt. Das bedeutet, dass eine neue Tabelle erstmal keinen Platz "verschwendet". Solange die Tabelle leer ist, ändert sich auch daran nichts. Das Segment im Tablespace wird erst angelegt, wenn die ersten Daten in die Tabelle geschrieben werden. Das gleiche gilt auch für zugehörige Indizes und Lobsegmente.

                  Was bedeutet das für die Praxis? Es gibt viele große Softwareinstallationen die tausende von Tabellen anlegen, jedoch nur einen Teil davon in der jeweiligen Ausbaustufe benötigen. Bis jetzt wurde für jede Tabelle sofort das zugehörige Segment angelegt. Dabei kann unter Umständen viel Platz verschwendet werden, der so nicht benötigt wird.

                  Es gibt natürlich auch einen Nachteil den man bedenken sollte. Der erste Insert in eine leere Tabelle dauert länger, weil das Segment angelegt werden muss.

                  Doch nun zur Praxis. Welche Voraussetzungen sind nötig um die verzögerte Speicherplatzbelegung zu nutzen?

                  DEFERRED_SEGMENT_CREATION=TRUE
                  • Dieser Parameter hat bei einer 11.2 Installation den Defaultwert TRUE.
                  • Der Parameter kann auf Session- oder Systemebene geändert werden.
                  • Der Tablespace muß Locally Managed sein.

                  Die Syntax der CREATE TABLE Anweisung hat sich um die SEGMENT CREATION Klausel erweitert.

                  CREATE TABLE tablename (col1 DATATYPE,...)
                  ...
                  SEGMENT CREATION DEFERRED | IMMEDIATE;

                  Das Verhalten der SEGMENT CREATION Klausel ist abhängig von dem Parameter DEFFERED_SEGMENT_CREATION. Steht der Parameter auf TRUE (default), dann werden die Segmente einer neuen Tabelle automatisch verzögert angelegt. Steht der Parameter auf FALSE, werden die Segmente sofort angelegt. Dieses Verhalten kann durch Angabe der Klausel jedoch überschrieben werden.

                  Nun einige Beispiele zur Verdeutlichung des Verhaltens:

                  -- DEFERRED_SEGMENT_CREATION=true

                  create table test_def1 
                  (id number, constraint test_def1_pk primary key(id));
                  Table created.

                  select object_name, object_type 
                  from user_objects 
                  where object_name like 'TEST_DEF%';

                  OBJECT_NAME            OBJECT_TYPE
                  ---------------------- ------------------------------
                  TEST_DEF1              TABLE
                  TEST_DEF1_PK           INDEX

                  select segment_name, segment_type, bytes/1024/1024 MB
                  from user_segments 
                  where segment_name like 'TEST_DEF%';

                  no rows selected

                  In diesem Beispiel wird deutlich, dass die Tabelle existiert, aber bis jetzt noch kein Speicherplatz allokiert wurde.

                  Im nächsten Beispiel überschreiben wir das Defaultverhalten mit der SEGMENT DEFERRED Klausel:

                  create table test_def2
                  (id number, constraint test_def2_pk primary key(id))
                  SEGMENT CREATION IMMEDIATE;
                  Table created.

                  select object_name, object_type
                  from user_objects 
                  where object_name like 'TEST_DEF%';

                  OBJECT_NAME            OBJECT_TYPE
                  ---------------------- ------------------------------
                  TEST_DEF1              TABLE
                  TEST_DEF1_PK           INDEX
                  TEST_DEF2              TABLE
                  TEST_DEF2_PK           INDEX

                  select segment_name, segment_type, bytes/1024/1024 MB 
                  from user_segments 
                  where segment_name like 'TEST_DEF%';

                  SEGMENT_NAME           SEGMENT_TYPE                           MB
                  ---------------------- ------------------------------ ----------
                  TEST_DEF2              TABLE                               .0625
                  TEST_DEF2_PK           INDEX                               .0625

                  Mit der Klausel SEGMENT CREATION IMMEDIATE wird sofort bei der Erstellung der Tabelle auch je ein Segment für Tabelle und Index angelegt.

                  Und jetzt zu den ersten Inserts:

                  set timing on
                  insert into test_def1 values(1);

                  1 row created.
                  Elapsed: 00:00:00.18

                  insert into test_def2 values(1);

                  1 row created.
                  Elapsed: 00:00:00.01

                  insert into test_def1 values(2);

                  1 row created.
                  Elapsed: 00:00:00.01

                  insert into test_def2 values(2);

                  1 row created.
                  Elapsed: 00:00:00.01
                   

                  Wie erwartet dauert der erste Insert länger, weil die Segmente für Tabelle und Index erst angelegt werden müssen. Und hier noch der Beweis, dass die Segmente angelegt wurden:

                  select segment_name, segment_type, bytes/1024/1024 MB from user_segments where segment_name like 'TEST_DEF%';

                  SEGMENT_NAME           SEGMENT_TYPE                           MB
                  ---------------------- ------------------------------ ----------
                  TEST_DEF1              TABLE                               .0625
                  TEST_DEF1_PK           INDEX                               .0625
                  TEST_DEF2              TABLE                               .0625
                  TEST_DEF2_PK           INDEX                               .0625

                   

                  Es gibt leider in diesem Release einige Objekte auf die das neue Feature nicht angewendet werden kann:

                  • Partitionierte Tabellen
                  • Index organisierte Tabellen
                  • Geclusterte Tabellen
                  • Temporäre Tabellen (temporary tables)
                  • Externe Tabellen (external tables)
                  • Interne Tabellen (X$.., K$..)
                  • Auf Typen basierende Tabellen (typed tables)
                  • AQ Tables
                  • Tabellen der User SYS, SYSTEM, PUBLIC, OUTLN und XDB

                  Wenn eine Tabelle einmal gefüllt war, bleibt das Segment bestehen, auch wenn ein Truncate oder Alter Table Kommando abesetzt wurde.

                  Letztendlich bietet diese neue Art der Speicherplatzverwaltung Vorteile, vor allem wenn es sich um große Softwareinstallationen handelt, weil kein unnötiger Platz im Tablespace verschwendet wird.

                  Mehr Informationen zu den neuen Features von Oracle erhalten Sie auch in unserer Schulungen.



                  Weitere Interessente Artikel zum Thema:



                  Empfohlene Schulungen zum Thema:


                  Oracle Live SQL

                  Bereich:DBA:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.11.2018

                  Keywords:SQL lernen, SQL live nutzen, SQL Testserver, SQL in der Cloud

                  Viele wollen SQL lernen, sind aber abgeschreckt vor dem Aufwand, sich etwas zu installieren. Eigentlich ist das gar nicht schlimm, siehe auch unser Tipp zur Installation von Oracle XE 18c. Wer es aber noch einfacher haben möchte, geht in die Cloud. Oracle bietet einen kostenlosen Zugriff auf einen Server an, wo auch SQL gelernt werden kann. Der nachfolgende Tipp soll das Vorgehen kurz erläutern.

                  1. Man besucht die Webseite https://livesql.oracle.com und klickt dann auf "Sign In".

                   

                  Live SQL Screen I

                   

                  2. Dann wird man auf die Seite zum Registrieren weitergeleitet.

                   

                  Live SQL Screen II v2

                   

                  3. Nun gibt man die geforderten Daten ein und klickt dann auf "Account erstellen".

                   

                  Live SQL Screen III v2

                   

                  4. Dann wird einem die Bestätigungsmail zugeschickt, diese muss vor dem Nutzen des Accounts bestätigt werden.

                   

                  5. Danach meldet man sich mit den eingegebenen Daten auf der Webseite an.

                   

                  6. In der Suchleiste kann man nach "Skripten und Tutorials" suchen.

                   

                  Live SQL Screen IV v2

                   

                  7. Klickt man auf "View Scripts and Tutorials" findet man die Code Library (Code Bibliothek).

                   

                  Live SQL Screen 5 v2

                   

                  8. Drückt man auf "Start Coding Now" gelangt man zum SQL Worksheet (Arbeitsblatt), wo man Commands (Befehle) eingeben kann.

                   

                  Live SQL Screen VI v2

                   

                  9. Klickt man auf "My Session" kann man seine SQL Statements abrufen.

                  • Im Unterreiter "Previous Sessions" kann man die vorherigen Sessions abrufen.

                   

                  Live SQL Screen VIII v2

                   

                  • Im anderen Unterreiter "Utilization" findet man die Nutzungsübersicht.

                   

                  Live SQL Screen VIIII v2

                   

                  10. Drückt man auf "Schema" findet man ein einfaches Schema zum Üben von SQL.

                   

                  Live SQL Screen X v2

                   

                  11. Unter "Design" kann man ein Schema schnell entwerfen.

                   

                  Live SQL Screen XI v2

                   

                  12. Klickt man auf "My Scripts" kann man alle Skripte und Sessions abrufen, die gespeichert sind.

                   

                  Live SQL Screen XII

                   

                  13. Der letzte Punkt ist die "Code Library" (Code Bibliothek), dort findet man Beispiele und Tutorials.

                   

                  Live SQL Screen VII v2

                   

                  Wir wünschen viel Spaß bei der Benutzung und viel Erfolg beim Üben. In einem unserer Kurse, kann man das gelernte dann gerne vertiefen.



                  Weitere Interessente Artikel zum Thema:



                  Empfohlene Schulungen zum Thema:


                  Sessions Transaktionen und Sperren

                  Bereich:DBA:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 12.12.2018

                  Keywords:DBA , SQL

                  Zusammenspiel zwischen Sessions, Transaktionen und Sperren

                  Ist es Ihnen schon häufiger passiert, dass Sie Änderungen an Ihren Daten vornehmen wollten und dabei in bestehende Tabellen- oder Zeilensperren gelaufen sind? Im unangenehmsten Fall „hängte“ sich Ihre Session solange auf, bis die gesperrten Zeilen wieder freigegeben wurden. Und selbst wenn Sie vor dem Durchführen Ihrer Änderung festgestellt haben, dass eine bereits vorhandene Sperre die Manipulation verhindert, hatte es Sie sehr viel Aufwand gekostet, die „störende“ Session auszumachen.

                  Der folgende Beitrag soll Ihnen beim Auffinden von Sperren behilflich sein und die Zuordnung der Sperren zu den jeweiligen Transaktionen, den Benutzern und den gesperrten Objekten erleichtern.

                  Ausgangssituation

                  Zu Demonstrationszwecken werden die Benutzer in diesem Beitrag mit TEST01, TEST02, ... bezeichnet. Die Benutzer greifen alle auf die selben Tabellen EMP und DEPT zu und versuchen diese zu manipulieren.

                   

                  Fall 1: (Default-Einstellung bei Oracle): User TEST01 sperrt durch seine Transaktion einzelne Zeilen einer Tabelle und User TEST02 läuft mit seiner Änderung in die Sperre hinein. Die Session von TEST02 ist blockiert und der User muss warten, bis die Transaktion des Users TEST01 beendet ist.

                   

                  Fall 2: (SELECT ... FOR UPDATE NOWAIT): User TEST01 startet erneut eine Transaktion:

                  UPDATE scott.dept SET loc='BERLIN' WHERE deptno=40;

                   

                  TEST02 möchte parallel den Namen der Abteilung 40 ändern, prüft allerdings vorher, ob es bereits eine Sperre auf diesem Datensatz gibt:

                  SELECT * FROM scott.dept WHERE deptno=40 FOR UPDATE NOWAIT;

                   

                  Und erhält folgende Fehlermeldung:

                  ORA-00054: Versuch, mit NOWAIT eine bereits belegte Ressource anzufordern.

                   

                  Nun weiß TEST02 zwar, dass er momentan nicht in der Lage ist, diesen Datensatz zu verändern, er weiß jedoch nicht, welcher Benutzer ihn in seiner Tätigkeit behindert. Dies gilt es im weiteren Verlauf des Beitrags zu ermitteln.

                  Die wichtigsten Data Dictionary Views zu Benutzerprozessen, Transaktionen und Sperren

                  Zum Aufruf der folgenden Views benötigen Sie mindestens das Leserecht aller Data Dictionary Views (z. B. über die Rolle SELECT_CATALOG_ROLE).

                  • V$SESSION: erfasst alle Benutzer- und Hintergrundprozesse, die auf dem Datenbankserver laufen. Hier sehen Sie, welche Benutzer von welchem Rechner aus über welches Programm auf die Datenbank zugreifen und welche Benutzer eine Transaktion gestartet haben. Dazu führen Sie folgenden SELECT durch:
                  SELECT s.sid, s.serial#, s.username, s.taddr, s.last_call_et, s.program, s.machine
                  FROM v$session s
                  WHERE s.username IS NOT NULL;
                   
                   
                  • V$LOCKED_OBJECT: erfasst alle von einer Transaktion betroffenen (gesperrten) Objekte. Durch einen Join mit der View DBA_OBJECTS erhalten Sie die genauen Namen der gesperrten Objekte:
                  SELECT l.session_id, l.oracle_username, l.object_id, o.owner, o.object_name
                  FROM v$locked_object l, dba_objects o
                  WHERE l.object_id = o.object_id;

                   

                  • V$TRANSACTION: listet alle aktuellen Transaktionen und die zugewiesenen Undo-Segmente auf. Ein Join mit V$SESSION gibt an, von welchem Benutzer eine Transaktion wann gestartet wurde:
                  SELECT t.addr, t.xidusn, s.sid, s.serial#, s.username, t.start_time
                  FROM v$transaction t, v$session s
                  WHERE t.addr = s.taddr;

                   

                  • DBA_DML_LOCKS: gibt alle Objekte aus, die aufgrund einer DML-Anweisung von einer Sperre betroffen sind:
                  SELECT l.session_id, l.owner, l.name
                  FROM dba_dml_locks l;

                   

                  • DBA_WAITERS: enthält alle Sessions, die von anderen Sessions blockiert (gesperrt) werden.
                  SELECT w.waiting_session, w.holding_session, w.mode_held
                  FROM dba_waiters w
                  WHERE w.mode_held <> 'None';

                   

                  Ermittlung der blockierenden Sessions

                  Der folgende SELECT ermittelt die blockierende Session, in deren Sperre Sie gelaufen sind.

                   
                  SELECT  s.sid,
                               s.serial#,
                               s.username,
                               w.holding_session
                  FROM    v$session s,
                                 dba_waiters w
                  WHERE    w.waiting_session(+) = s.sid
                            AND      s.username = user
                            AND      s.taddr is not null
                            AND      w.mode_held(+) <> 'None'
                  UNION
                  SELECT  s.sid,
                               s.serial#,
                               s.username,
                               NULL
                  FROM    v$session s
                  WHERE    s.sid IN (SELECT w.holding_session
                                               FROM dba_waiters w
                                              WHERE w.mode_held <> 'None');

                         SID    SERIAL# USERNAME HOLDING_SESSION
                  ---------- ---------- -------- ---------------
                           9         33 TEST01
                          16         23 TEST02                 9

                   

                  Zurück zu Fall 2 unseres Ausgangsproblems. TEST02 hat mittels SELECT ... FOR UPDATE NOWAIT festgestellt, dass der zu ändernde Datensatz bereits gesperrt ist. Um festzustellen, welcher Benutzer für die Sperre verantwortlich ist, setzt er nun folgenden Befehl ab:

                   
                  SELECT  l.session_id,
                               s.serial#,
                               l.oracle_username,
                               o.object_name,
                               c.sql_text
                  FROM    v$locked_object l,
                                 v$session s,
                                 dba_objects o,
                                 v$open_cursor c
                  WHERE    l.object_id = o.object_id
                            AND    s.sid = l.session_id
                            AND    c.address(+) = s.sql_address
                            AND      o.owner = 'SCOTT'
                            AND      o.object_name = 'DEPT';

                  SESSION_ID  SERIAL# ORACLE_USERNAME OBJECT_NAME SQL_TEXT
                  ---------- -------- --------------- ----------- --------
                           9       33 TEST01          DEPT        UPDATE scott.dept SET loc='BERLIN' WHERE deptno=40

                   

                  Hinweis:

                  Dieses Beispiel ist für den Beitrag etwas idealisiert worden. Im Normalfall sehen Sie nicht zwingend, welche Session genau Ihre zu ändernde Zeile sperrt. Die Spalte SQL_TEXT gibt lediglich den zuletzt abgesetzten Befehl eines Benutzers an, dies muss aber nicht unbedingt der Befehl sein, mit dem Ihre Zeile(n) gesperrt worden ist. Haben mehrere Benutzer unterschiedliche Zeilen einer Tabelle gesperrt und Sie wollen feststellen, welche Session Sie blockiert, müssen Sie wohl oder übel in die Sperre laufen und über den zuvor aufgeführten SELECT die blockierende Session ausfindig machen.

                  Den Abschluss dieses Beitrags bildet ein SELECT, der Ihnen alle Benutzersessions auflistet, die eine Transaktion gestartet haben bzw. in eine Transaktionssperre gelaufen sind. Im konkreten Fall könnten dies mehrere Benutzer sein, die hintereinander in die selbe Zeilensperre laufen und damit alle blockiert sind. Nun gilt es herauszufinden, in welcher Reihenfolge die „hängengebliebenen“ Sessions wieder freigegeben werden. Als zusätzliche Information lassen Sie sich dazu die Zeit (LAST_CALL_ET), die seit dem Absetzen des letzten Befehls in einer Session verstrichen ist ausgeben.

                   
                  SELECT  s.sid,
                               s.serial#,
                               s.username,
                               w.holding_session,
                               s.taddr,
                               s.lockwait,
                               s.last_call_et AS time,
                               o.owner,
                               o.object_name,
                               c.sql_text
                  FROM    v$session s,
                                 dba_waiters w,
                                 v$locked_object l,
                                 dba_objects o,
                                 v$open_cursor c
                  WHERE    w.waiting_session(+) = s.sid
                           AND    s.sid = l.session_id
                           AND    l.object_id = o.object_id
                           AND    c.address(+) = s.sql_address
                           AND      s.username is not null
                           AND      s.taddr is not null
                           AND      w.mode_held(+) <> 'None'
                  GROUP BY    w.holding_session,
                                       s.last_call_et,
                                       s.sid,
                                       s.serial#,
                                       s.username,
                                       s.taddr,
                                       s.lockwait,
                                       o.owner,
                                      o.object_name,
                                       c.sql_text
                  ORDER BY    w.holding_session DESC,
                              s.last_call_et DESC;
                  SID SERIAL# USERNAME HOLDING_SESSION TADDR    LOCKWAIT TIME OWNER  OBJECT_NAME SQL_TEXT
                  --- ------- -------- --------------- -------- -------- ---- ------ ----------- ---------------------------------------------------------
                     10   213 TEST01                   6A874D30           262 SCOTT  DEPT       update scott.dept set loc='MONTEREY' where deptno=40
                     17    14 TEST04                   6A8520F4            90 SCOTT  EMP         update scott.emp set sal=sal*2 where empno=7900
                     19    37 TEST05                17 69FF8088 69B1CEB4   60 SCOTT  EMP        update emp set sal=sal-200 where empno=7900
                      9    33 TEST02                10 6A860DAC 69B1C920  211 SCOTT  DEPT       update scott.dept set dname='HEAD_QUARTER' where deptno=40
                     16    23 TEST03                10 6A87494C 69B1C974  171 SCOTT  DEPT       update scott.dept set loc='SAN DIEGO' where deptno=40

                  Als Ausgabe erhalten Sie eine Übersicht über alle gestarteten Transaktionen (TADDR) und welche Session in eine bereits bestehende Sperre (LOCKWAIT) einer anderen Session (HOLDING_SESSION) gelaufen ist.

                  Fazit

                  Die in diesem Beitrag angesprochenen Data Dictionary Views sind lediglich die wichtigsten rund um das Thema Sessions, Transaktionen und Sperren. Mit Hilfe der hier aufgeführten Befehls-Beispiele soll es Ihnen möglich sein, sich die gewünschten Informationen übersichtlich anzeigen zu lassen.



                  Weitere Interessente Artikel zum Thema:



                  Empfohlene Schulungen zum Thema:


                  20 CREATE TABLE Beispiele für Oracle (Version 10.2 bis 21c)

                  Bereich:SQL, Version: ab RDBMS 12.x:RDBMS 20.1, Letzte Überarbeitung: 14.06.2021

                  Keywords:CREATE Table, Tabellenerstellung, External Table, Collation, Primary Key, Foreign Key, Temporäre Tabelle

                  In unserem heutigen Tipp widmen wir uns dem wichtigsten Konzept einer Datenbank überhaupt: Der Tabelle!

                  Nachdem ich immer wieder nach Beispielen zu speziellen CREATE TABLE Befehlen für Oracle Datenbanken suchen muss, war es mal an der Zeit eine eigene Zusammenfassung zu erstellen.

                  Fall 1: Eine Tabelle soll mit den gängigsten numerischen Datentypen erstellt werden:

                  CREATE TABLE t (
                    c1 BINARY_DOUBLE,
                    c2 BINARY_FLOAT,
                    c3 DEC,
                    c4 DECIMAL,
                    c5 FLOAT,
                    c6 INT,
                    c7 INTEGER,
                    c8 NUMBER(9,2),
                    c9 NUMERIC );


                  Fall 2: Als nächstes nehmen wir die text und sonstigen Datentypen:

                  CREATE TABLE t (
                    c1 char(1),
                    c2 char,
                    c3 varchar2(4000),
                    c4 varchar(4000),
                    c5 BLOB,
                    c6 CLOB,
                    c7 BFILE,
                    c8 RAW(2000),
                    c9 date,
                    c10 timestamp,
                    c11 rowid );


                  Fall 3: Die Tabelle soll mit der neuen LOB Speichertechnik (ab 11.1), den Securefiles, erstellt werden:

                  CREATE TABLE t (
                    c1 NUMBER,
                    c2 CLOB,
                    CONSTRAINT t_pk PRIMARY KEY (c1) )
                    LOB(c2) STORE AS SECUREFILE my_lob_tbs;


                  Fall 4: Die Tabelle soll erst dann physisch angelegt werden, wenn die erste Zeile eingetragen wurde:

                  CREATE TABLE t (
                    c1 NUMBER )
                    SEGMENT CREATION DEFERRED;


                  Fall 5: Die Tabelle soll mit dem Attribut NOLOGGING erstellt werden:

                  CREATE TABLE t (
                    c1 INT,
                    c2 VARCHAR2(4000) )  -- AB 12.2 bis 32767 möglich
                    NOLOGGING;


                  Nachträglich kann die Tabelle wieder in LOGGING bzw. NOLOGGING geändert werden:

                  ALTER TABLE t LOGGING;
                  ALTER TABLE t NOLOGGING;


                  Fall 6: Sie wollen einen speziellen Tablespace für die Tabelle angeben:

                  CREATE TABLE t (
                    c1 INT,
                    c2 VARCHAR2(4000) )
                    TABLESPACE user_tbs;


                  Fall 7: Die Tabelle soll einen Primärschlüssel besitzen:

                  CREATE TABLE t (
                    c1 INT PRIMARY KEY );


                  Fall 8: Die Tabelle soll einen Fremdschlüssel (Foreign Key) besitzen:

                  CREATE TABLE t (
                    c1 INT,
                    c2 INT,
                    c3 INT,
                    CONSTRAINT fk_cons FOREIGN KEY (c1, c2,c3) REFERENCES t2(c1, c2,c3);


                  Fall 9: Die Tabelle soll eine spezielle Sortierung für eine Spalte eingestellt bekommen. Diese Funktion steht erst ab 12.2 zur Verfügung. Außerdem muss der Parameter max_string_size= EXTENDED gesetzt sein.

                  CREATE TABLE t (
                    c1 NUMBER,
                    c2 VARCHAR2(2000 CHAR) COLLATE GERMAN_CI );


                  Fall 10: Die Tabelle soll nur die Daten temporär aufbewahren (bis zum Sessionende):

                  CREATE GLOBAL TEMPORARY TABLE t (
                    c1 INT,
                    c2 VARCHAR2(4000) )
                    ON COMMIT PRESERVE ROWS;


                  Fall 11: Die Tabelle soll nur die Daten temporär aufbewahren (bis zum Transaktionsende):

                  CREATE GLOBAL TEMPORARY TABLE t (
                    c1 INT,
                    c2 VARCHAR2(4000) )
                    ON COMMIT DELETE ROWS;


                  Fall 12: Ab Version 18 kann eine temporäre Tabelle erzeugt werden, die zum Transaktionsende samt Definition gelöscht wird.
                  Hinweis: Sie muss ein Prefix (ora$ptt) im Namen aufweisen. Dies kann jedoch über den Initialisierungsparameter PRIVATE_TEMP_TABLE_PREFIX geändert werden.

                  CREATE PRIVATE TEMPORARY TABLE ora$ptt_t (
                    c1 NUMBER,
                    c2  VARCHAR2(20) )
                    ON COMMIT DROP DEFINITION;


                  Fall 13: Auch erst ab Version 18 verfügbar, ist die zweite Variante der privaten temporären Tabelle, die erst zum Sessionende gelöscht wird:

                  CREATE PRIVATE TEMPORARY TABLE ora$ptt_t (
                    c1 NUMBER,
                    c2  VARCHAR2(20) )
                    ON COMMIT PRESERVE DEFINITION;


                  Fall 14: Die Tabelle soll sich die Änderungs-SCN pro Zeile und nicht nur pro Block merken:

                  CREATE TABLE t (
                    c1 INT,
                    c2 VARCHAR2(4000) )
                    ROWDEPENDENCIES;


                  Fall 15: Die Tabelle soll auf einer Datei basieren, die ausserhalb der Datenbank liegt:

                  Als Benutzer mit DBA Rechten ausführen:

                  CREATE DIRECTORY utl_dir as 'C:\temp';
                  GRANT read,write ON DIRECTORY utl_dir TO scott;


                  Als Benutzer SCOTT ausführen:

                  CREATE TABLE scott.emp_ext (
                    EMPNO     NUMBER(4),
                    ENAME     VARCHAR2(10),
                    JOB       VARCHAR2(9),
                    MGR       NUMBER(4),
                    HIREDATE  DATE,
                    SAL       NUMBER(7,2),
                    COMM      NUMBER(7,2),
                    DEPTNO    NUMBER(2) )
                  ORGANIZATION EXTERNAL
                    (
                      TYPE ORACLE_LOADER
                      DEFAULT DIRECTORY UTL_DIR
                      ACCESS PARAMETERS
                        (
                          RECORDS DELIMITED BY NEWLINE
                          FIELDS TERMINATED BY ','
                          MISSING FIELD VALUES ARE NULL
                            (
                              empno,
                              ename,
                              job,
                              mgr,
                              hiredate   CHAR(21) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS",
                              sal,
                              comm,
                              deptno
                            )
                         )
                       LOCATION ('emp.txt')
                     )
                    PARALLEL 1
                    REJECT LIMIT UNLIMITED;

                   

                  Fall 16: Blockchain Table (Erst ab Version 21c)

                  CREATE BLOCKCHAIN TABLE bc_tab1 ( 
                  bank VARCHAR2(128), 
                  d_date DATE,   
                  d_amount NUMBER )
                   NO DROP UNTIL 25 DAYS IDLE
                   NO DELETE UNTIL 31 DAYS AFTER INSERT
                   HASHING USING "SHA2_512" VERSION v1;


                  Fall 17: Interval Range-Partitionierte Tabelle (1 Monatsintervall)

                  CREATE TABLE t
                  (id        NUMBER(6)    NOT NULL,
                   datum     DATE         NOT NULL)
                  PARTITION BY RANGE (datum)
                  INTERVAL (numtoyminterval(1,'MONTH'))
                  ( PARTITION p2021 VALUES LESS THAN (to_date('01.01.2021','dd.mm.yyyy')));


                  Fall 18: Interval Range-Partitionierte Tabelle (1 Tagesintervall)

                  CREATE TABLE t
                  (id        NUMBER(6)    NOT NULL,
                   datum     DATE         NOT NULL)
                  PARTITION BY RANGE (datum)
                  INTERVAL (numtodsinterval(1,'DAY'))
                  ( PARTITION p2021 VALUES LESS THAN (to_date('01.01.2021','dd.mm.yyyy')));


                  Fall 19: Interval Range-Partitionierte Tabelle (1 Jahresintervall)

                  CREATE TABLE t
                  (id        NUMBER(6)    NOT NULL,
                   datum     DATE         NOT NULL)
                  PARTITION BY RANGE (datum)
                  INTERVAL (numtoyminterval(1,'YEAR'))
                  ( PARTITION p2021 VALUES LESS THAN (to_date('01.01.2021','dd.mm.yyyy')));


                  Fall 20: Range Partitioninierung mit eigenem Intervall

                  CREATE TABLE scott.emp_part (
                  empno         NUMBER(4),
                  ename         VARCHAR2(20),
                  hiredate      DATE)
                  PARTITION BY RANGE (hiredate)(
                  partition year2019 VALUES LESS THAN (to_date('01.01.2020','DD.MM.YYYY')),
                  partition year2020 VALUES LESS THAN (to_date('01.01.2021','DD.MM.YYYY')),
                  partition year2021 VALUES LESS THAN (to_date('01.01.2022','DD.MM.YYYY'))
                  );


                  Weitere Interessente Artikel zum Thema:


                  Empfohlene Schulungen zum Thema:


                  Die WITH-Klausel

                  Bereich:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 18.12.2018

                  Keywords:WITH-Klausel

                  Die WITH-Klausel ist ja eines der bestgehütesten Geheimnisse der Sprache SQL. In unseren Schulungen ernten wir immer wieder Erstaunen, wenn wir die Funktion vorstellen. Dabei ist sie schon seit einigen Oracle Versionen verfügbar.

                  Richtig spannend wurde es jedoch erst ab Version 12.x, doch dazu später mehr.

                  Schauen wir uns erst einmal die einfachste WITH-Klausel an:

                  WITH s as (SELECT 'SCOTT' as myuser FROM dual)
                  SELECT * FROM s;


                  Die WITH-Klausel ermöglicht uns, eine Ergebnismenge vorab zu aggregieren, um sie dann evtl. sogar mehrmals in einem SELECT wieder einzusetzen.

                  Hier wird die Query unter dem Namen "S" in Form einer Tabelle zur Verfügung gestellt. Nur hat die Ergebnismenge im ersten Fall halt nur eine Spalte und eine Zeile.

                  Ein Beispiel mit der EMP Tabelle darf natürlich nicht fehlen:

                  WITH s as (SELECT * FROM scott.emp)
                  SELECT * FROM s;


                  Ab Version 12.x können nun auch Proceduren und Funktionen in der WITH-Klausel verwendet werden. Erstaunlicherweise muss man dazu noch nicht mal ein "CREATE PROCEDURE" Recht besitzen. Ein Schelm, wer böses damit anstellt.

                  Manchmal möchte man auch einen Aufruf in SQL starten, der eigentlich nur in PL/SQL funktioniert,das ist mit der WITH-Klausel kein Problem.

                  Beispiel: Sie haben eine Fehlernummer von Oracle erhalten, aber nicht den Text. Der Versuch in SQL:

                  SELECT sqlerrm(-1*abs(error_id)) FROM dual;
                  ORA-00904: "SQLERRM": ungültige ID


                  Mit der WITH-Klausel:

                  WITH
                      FUNCTION errm(error_id IN NUMBER) RETURN varchar2 IS         
                      BEGIN
                          return sqlerrm(-1*abs(error_id));
                      END;
                  SELECT errm(1017) from dual;


                  Oder mal eine komfortablere Datumskonvertierungsfunktion ...

                  WITH FUNCTION       to_date2(p_date IN VARCHAR2 ) RETURN DATE IS     
                      v_month VARCHAR2(30);
                      V_DAY   VARCHAR2(30);
                      v_year  VARCHAR2(30);
                      v_dummy VARCHAR2(30);
                      BEGIN
                      V_DUMMY:=TRANSLATE(P_DATE,'/.,\-;#+*=','..........'); -- Trennzeichen in . wandeln
                      V_DAY:=REGEXP_REPLACE(V_DUMMY,'([[:digit:]]{1,2}).([[:alnum:]]{1,12}).([[:digit:]]{1,4})','\1');
                      v_month:=REGEXP_REPLACE(V_DUMMY,'([[:digit:]]{1,2}).([[:alnum:]]{1,12}).([[:digit:]]{1,4})','\2');
                      V_YEAR:=REGEXP_REPLACE(V_DUMMY,'([[:digit:]]{1,2}).([[:alnum:]]{1,12}).([[:digit:]]{1,4})','\3');
                      
                      -- Monatsnamen ersetzen durch Zahlen
                      IF UPPER(v_month) IN ('JAN')        THEN v_month:='01'; END IF;
                      IF UPPER(v_month) IN ('FEB')        THEN v_month:='02'; END IF;
                      IF UPPER(v_month) IN ('MAR','MÄR')  THEN v_month:='03'; END IF;
                      IF UPPER(v_month) IN ('APR')        THEN v_month:='04'; END IF;
                      IF UPPER(v_month) IN ('MAI','MAY')  THEN v_month:='05'; END IF;
                      IF UPPER(v_month) IN ('JUN')        THEN v_month:='06'; END IF;
                      IF UPPER(v_month) IN ('JUL')        THEN v_month:='07'; END IF;
                      IF UPPER(v_month) IN ('AUG')        THEN v_month:='08'; END IF;
                      IF UPPER(v_month) IN ('SEP')        THEN v_month:='09'; END IF;
                      IF UPPER(v_month) IN ('OKT','OCT')  THEN v_month:='10'; END IF;
                      IF UPPER(v_month) IN ('NOV','NOV')  THEN v_month:='11'; END IF;
                      IF UPPER(v_month) IN ('DEZ','DEC')  THEN v_month:='12'; END IF;
                      --DBMS_OUTPUT.PUT_LINE('#'||v_month); return sysdate;
                      -- Prüfen ob das Jahr im aktuellen Jahrhundert (bis 2050) oder im letzten Jahrhundert (ab 1951) liegt
                      IF TO_NUMBER(V_YEAR)<=50 THEN
                        V_YEAR:=SUBSTR(TO_CHAR(SYSDATE,'YYYY'),1,2)||V_YEAR;
                      ELSE
                        IF not length(v_year)=4 THEN -- Nur wenn Jahr nicht vierstellig ist, wird ergänzt
                          V_YEAR:=TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE,'YYYY'),1,2)-1)||V_YEAR;
                        END IF;
                      END IF;
                      RETURN TO_DATE(v_day||'.'||v_month||'.'||v_year,'DD.MM.YYYY');
                      END;
                  SELECT to_date2('01.JAN.2000') as datum FROM dual
                  /


                  Das geht auch mit Datumswerten wie: '01.01.01', '01-Mai.99', '30/DeC.2001', '02-jan:1932'. Das versuchen Sie mal mit der to_date Funktion ...

                  Auch mehrere WITH-Klauseln sind möglich:

                  WITH
                  a as ( SELECT 1 as col FROM dual),
                  b as ( SELECT 2 as col FROM dual)
                  SELECT a.col,b.col
                    FROM a, b;


                  Sie müssen sie nur mit einem Komma trennen.

                  Zum Schluss noch ein etwas anspruchsvolleres Beispiel, das wir gerne in den Kursen zeigen:
                  Es soll ein Skript angelegt werden, das einen Benutzer mit all seinen zugewiesenen Rechten anzeigt.

                  WITH U AS (SELECT 'SCOTT' AS NAME FROM DUAL)(
                  SELECT TO_CHAR(DBMS_METADATA.GET_DDL('USER',U.NAME)) FROM U,DUAL
                  UNION ALL
                  SELECT CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',u.name)) end
                    FROM u,(select count(*) as cnt from u,all_tab_privs where grantee=u.name)
                  UNION ALL
                  SELECT CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',u.name)) end
                    FROM u,(select count(*) as cnt from u,dba_sys_privs where grantee=u.name)
                  UNION ALL
                  SELECT  CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',u.name)) end
                    FROM u,(SELECT count(*) as cnt  FROM u,dba_role_privs where grantee=u.name)
                  UNION ALL
                  SELECT to_char(dbms_metadata.get_ddl('SYNONYM',synonym_name,owner))
                    FROM u,dba_synonyms where table_owner=u.name);


                  Der Vorteil hier ist, dass man den Schemanamen in der WITH-Klausel, quasi wie eine Variable definieren kann, die man dann an mehreren Stellen weiter unten im SQL Statement wieder verwenden kann.

                  Nur kann es passieren, dass einer der Teil-Selects keine Ergebnismenge zurückbringt. Dieser ist dann so nett, mit dem Fehler ORA-31608 abzustürzen. Damit stürzt dann aber auch gleich das komplette SQL-Statement ab. Deswegen haben wir oben mit der COUNT Funktion geprüft, ob überhaupt Rechte vorhanden sind.

                  Ab 12c können wir aber auch das schöner lösen:

                  WITH
                    FUNCTION get_ddl(ddl_typ in VARCHAR2, uname IN VARCHAR2) RETURN VARCHAR2 IS
                    BEGIN
                     DBMS_METADATA.SET_TRANSFORM_PARAM(
                     DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
                     IF ddl_typ like '%GRANT' THEN
                      RETURN to_char(DBMS_METADATA.GET_GRANTED_DDL(ddl_typ,uname));
                     ELSE
                      RETURN to_char(DBMS_METADATA.GET_DDL(ddl_typ,uname));
                    END IF;
                      EXCEPTION WHEN OTHERS THEN
                        IF sqlcode=-31608 THEN RETURN NULL; ELSE RAISE; END IF;
                    END;
                    u AS (SELECT 'SCOTT'  as name FROM dual)
                  SELECT get_ddl('USER',u.name) from u
                  UNION ALL
                  SELECT get_ddl('OBJECT_GRANT',u.name) from u
                  UNION ALL
                  SELECT get_ddl('SYSTEM_GRANT',u.name) from u
                  UNION ALL
                  SELECT get_ddl('ROLE_GRANT',u.name) from u

                  oder wollten Sie immer schon mal viele DDL befehle in einem Rutsch ausführen?
                  Achtung die folgende Funktion löscht sofort alle invaliden Synonyme der Benutzer MDSYS und CTXSYS:

                  WITH  FUNCTION   eval (cmd IN VARCHAR2)
                  RETURN VARCHAR2
                  IS
                  PRAGMA AUTONOMOUS_TRANSACTION; -- Damit ist die Funktion eigenständig und beeinflusst niemanden :-)
                  BEGIN
                  EXECUTE IMMEDIATE cmd; -- Befehl ausführen
                  RETURN cmd||'; --OK'; -- Befehl erfolgreich ausgeführt
                  EXCEPTION WHEN OTHERS THEN
                   RETURN cmd||' --'||sqlerrm;
                  END;
                  select eval('DROP PUBLIC SYNONYM "'||object_name||'"') from dba_objects
                  where status<>'VALID'
                  and object_type='SYNONYM'
                  and owner IN ('MDSYS','CTXSYS');
                  ;


                  Na, auf den Geschmack gekommen? Weitere Beispiele erhalten Sie z.B. in unserem PL/SQL Kurs. Besuchen Sie uns doch mal im schönen Unterhaching bei München.



                  Weitere Interessente Artikel zum Thema:



                  Empfohlene Schulungen zum Thema:


                  Umbennen von Oracle intervall partitionierten Tabellen (Nach Datum)

                  Bereich:DBA:SQL, Version: ab RDBMS 18.1:RDBMS 19.3:RDBMS 21.1, Letzte Überarbeitung: 14.06.2021

                  Keywords:partitionierte Tabelle, Umbennen

                  Wenn man eine range intervall partitionierte Tabelle anlegt, vergibt Oracle nicht sehr besonders sprechende Namen.
                  So heißen Partitionen schon mal SYS_P140, SYS_P141, ...
                  Dass muss aber nicht so bleiben, sie können natürlich jede Partitiion umbenennen mittels:
                   

                  ALTER TABLE <owner>.<table> RENAME PARTITION <partionsname> TO <neuer_name>;


                  Was aber für 300 Partitionen sehr mühsam ist. warum also nicht das ganze etwas automatisieren?
                  Die Informationen, welche Partition welchen Bereich abdeckt steht in der Spalte high_value der View USER_TAB_PARTITIONS.
                  Wenn die Tabelle nicht Ihnen gehört, Sie aber Rechte darauf besitzen, können Sie in ALL_TAB_PARTITIONS nachsehen.

                  Nur ist das Problem, das es sich bei dieser Spalte um eine Long Spalte handelt, die nicht normal gefiltert werden kann. Deswegen gehen wir einen kleinen Umweg via XML
                  um aus der Tabelle einen Teil herausschneiden zu können. Wir haben uns in diesem Beispiel auf Range Partitionierung nach dem Datum konzentriert, was in der Praxis wohl am häufigsten vorkommt.
                  Es ist auch leicht auf andere Range Datentypen anpassbar.
                   

                  WITH tab as (select 'TRANS_PART' as name FROM dual)
                  SELECT 'ALTER TABLE '||tab_name||' RENAME PARTITION '||partition_name||' TO '||tab_name||'_'||replace(substr(high_value,11,10),'-','_') ||';'
                  from (
                    SELECT tab.name as tab_name,PARTITION_NAME,
                      extractvalue
                        ( dbms_xmlgen.getxmltype
                          ( 'select high_value
                             from USER_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
                          '//text()' ) as high_value
                    FROM tab,USER_TAB_PARTITIONS t
                    WHERE TABLE_NAME = tab.name);


                  Diese Ausgabe erzeugt nun die SQL Befehle, die Sie dann nur noch ausführen müssen.

                  PS: Wenn Sie Beratung zum Thema Oracle Partitionierung, melden Sie sich doch einfach bei uns. Wir bieten Schulungen und Consulting zu vielen Oracle Bereichen...



                  Weitere Interessente Artikel zum Thema:


                  Empfohlene Schulungen zum Thema:


                  Oracle Dokumentationsübersicht von Version 11.2 bis Oracle 21c

                  Bereich:DBA:Monitoring:PL/SQL:SQL, Version: ab RDBMS 11.2:RDBMS 12.x:RDBMS 18.1:RDBMS 18.3:RDBMS 19.1:RDBMS 19.3:RDBMS 20.1, Letzte Überarbeitung: 05.07.2023

                  Keywords:Oracle Doku, Oracle Doc

                  Haben Sie auch schon einmal verzweifelt nach der richtigen Oracle Dokumentation Ihrer Oracle Datenbank gesucht.
                  Leider hat Oracle im Gegensatz zu Postgres keinen einheitlichen Doc Server, sondern legt die Dokumenation immer auf einen neuen Server...
                  Die folgende Matrix soll Ihnen die Suche nach der richtigen Version für Ihre Oracle Datenbank helfen

                   

                   11.212.112.218c19c21c23c
                  Oracle HauptseiteDokuDokuDokuDokuDokuDokuDoku
                  SQL DocDokuDokuDokuDokuDokuDokuDoku
                  PL/SQL DocDokuDokuDokuDokuDokuDokuDoku
                  PL/SQL PackagesDokuDokuDokuDokuDokuDokuDoku
                  Oracle Datenbank AdministrationDokuDokuDokuDokuDokuDokuDoku
                  Oracle Upgrade GuideDokuDokuDokuDokuDokuDokuDoku
                  Oracle ReferenceDokuDokuDokuDokuDokuDokuDoku
                  Oracle ConceptsDokuDokuDokuDokuDokuDokuDoku
                  Oracle Database UtilitiesDokuDokuDokuDokuDokuDokuDoku
                  Oracle Backup & RecoveryDokuoracleDokuDokuDokuDokuDoku
                  Oracle Doc Server


                  Und für alle Oracle APEX Fans:

                  APEX Release18.219.119.220.120.221.222.122.223.123.2
                  Oracle APEX HauptseiteDokuDokuDokuDokuDokuDokuDokuDokuDoku 
                  APEX Install GuideDokuDokuDokuDokuDokuDokuDokuDokuDoku 
                  APEX App Builder User GuideDokuDokuDokuDokuDokuDokuDokuDokuDoku 
                  APEX API ReferenceDokuDokuDokuDokuDokuDokuDokuDokuDoku 


                  Nun kommen Sie bei Ihrer Doku-Suche schneller zum Ziel. Wer die Dokumentation nicht so gerne liest, kann natürlich auch gerne in eine von unseren Schulungen kommen.



                  Weitere Interessente Artikel zum Thema:


                  Empfohlene Schulungen zum Thema:


                  Visual Studio Code für Oracle SQL und PL/SQL

                  Bereich:PL/SQL:SQL, Version: ab RDBMS 8.x, Letzte Überarbeitung: 14.06.2019

                  Keywords:MS Visual Studio für Oracle

                  In den letzten Jahren wird von vielen Entwicklern verstärkt das Tool Visual Studio Code bei der Entwicklung verwendet.
                  Wir haben das Tool in Verbindung mit Oracle SQL und PL/SQL mal näher angesehen und beschreiben in diesem Tipp die Einrichtung

                  Netterweise gab es zu dem Thema schon einen Blog von Morten Braten, der aber noch die Version 0.1.0 verwendet, aktuelle Version ist 2.0.0 (Stand Juni 2019). Wir haben ein paar Sachen ergänzt und korrigiert, damit es unter der aktuellen Visual Studio Code Version läuft

                  Sie können Visual Studio Code hier herunterladen.
                  Zusätzlich sollten Sie folgende Extensions installieren:

                  • Language PL/SQL von xyz
                  • Notepad++ keymap
                  • SQL Server (mssql) (falls Sie mal für den SQL Server tätig werden möchten)

                  VisualStudioCode

                  Extensions werden installiert durch Klick auf das zweite Icon von unten, links im Hauptfenster (Screenshot (1)). Danach im Bereich "Search..." den gesuchten Namen eintragen, Suche starten und bei der gewünschten Extension auf "Install" klicken.
                  Danach legen Sie bitte einen Ordner im Betriebssystem an (z.B. c:\temp\VC)
                  Gehen Sie nun auf File/Open Folder und wählen Sie den Ordner aus. Der Inhalt des Ordners wird Ihnen links (Screenshot (2)) angezeigt.
                  Wenn Sie auf eine Datei (links) klicken, wird der Inhalt im Fenster (Screenshot (3)) angezeigt.
                  Nun müssen wir einige Konfigdateien anlegen, die Visual Studo Code sagen, was zu tun ist.
                   

                  Datei 1: _run_sqlplus.bat

                  REM Setup sqlplus Umgebung für Unicode
                  REM Dieses Skript setzt Voreinstellungen für Unicode (Multibyte Zeichen)
                  set NLS_LANG=.AL32UTF8
                  chcp 65001
                  cls

                  REM Fehler werden mittels problemMatcher angezeigt
                  echo exit | echo @_show_errors.sql | sqlplus -s %1 %2
                  color a0


                  Datei 2: _show_errors.sql

                  set pagesize 9999
                  set linesize 9999
                  set heading off
                  set trim on

                  HOST color c0

                  select lower(attribute) -- error oder warning
                    || ' '
                    || line || '/' || position -- Zeile und Spalte
                    || ' '
                    || lower(name) -- Dateiname
                    || case -- Datei-extension
                      when type = 'PACKAGE' then '.pks'
                      when type = 'PACKAGE BODY' then '.pkb'
                      else '.sql'
                    end
                    || ' '
                    || replace(text, chr(10), ' ') -- Zeilenbrüche entfernen
                    as user_errors
                  from user_errors
                  where attribute in ('ERROR', 'WARNING')
                  order by type, name, line, position;

                   
                    und die wichtigste, Datei 3: task.json im Unterordner .vscode

                  {
                      "version": "2.0.0",

                      // sqlplus startet über Batch-Datei
                      "windows": {
                          "command": "./_run_sqlplus.bat"
                      },
                      "osx": {
                          "command": "./_run_sqlplus.sh"
                      },
                      "linux": {
                          "command": "./_run_sqlplus.sh"
                      },

                      "presentation": {
                          "reveal": "always", // never,silent,always
                          "panel": "shared"  // new,shared,dedicated
                      },

                      //         $1= Datenbank Connect String mit Benutzer/PWD@TNS-String   $2=Dateiname
                      "args": ["scott/tiger@172.30.30.30:1521/xe.muniqsoft-training.de", "@${file}"],

                      // Wenn Kompilierungsfehler auftreten, werden diese im "Problems" Reiter (unten Links angezeigt)
                      "problemMatcher": {
                          "owner": "plsql",
                          "fileLocation": ["relative", "${fileDirname}"],
                          "pattern": [
                            {
                              "regexp": "(.*) (\\d*)\/(\\d*) (.*?) (.*)",
                              "severity": 1,
                              "line": 2,
                              "column": 3,
                              "file": 4,
                              "message": 5,
                              "loop": true
                          }
                        ]
                      }
                  }


                  Passen Sie bitte in dieser Datei in der Zeile "args" ihre Daten bzgl. Benutzername/Passwort und Datenbank Daten an.

                  Nun können wir loslegen:
                  Wir öffnen den Ordner (File/Open Folder), bei uns war das der Ordner c:\temp\VC
                  In diesen Ordner legen Sie eine bereits existierende SQL bzw. PL/SQL Datei (in unserem Screenshot(2)) z.B: die Datei vc_test.sql
                  Wenn Sie eine neue Datei erstellen möchten, klicken Sie mit der rechten Maustaste in den Dateibereich, Menue: New File. Geben Sie einen Namen an, dann wird die Datei im Ordner angelegt.
                  Klicken Sie also auf die gewünschte Datei, die dann im rechten Fenster angezeigt wird.
                  Ändern Sie ggf noch etwas ab und speichern Sie Datei (z.B. durch STRG-S)
                  Drücken Sie nun STRG-Shift-B (für Build) und ein kleines Fenster öffnet sich oben.
                  Wählen Sie dort ./_run_sqlplus.bat aus und die Datei wird in Oracle SQL*Plus ausgeführt.
                  Die Ausgabe wird im Reiter "TERMINAL" unten ausgegeben (Screenshot(4)). Sollte in der Select Liste dort nicht _run_sqlplus stehen, dann bitte ändern.
                  Mit dem Mülleimer Symbol (Screenshot(5)) kann das Fenster wieder geleert werden.

                  Mit Klick auf das Dateiensymbol (Screenshot(6)) werden die aktuellen Dateien im Ordner angezeigt, ein nochmaliger Klick öffnet den Code als Vollbild.

                  Wer es etwas kürzer mag (aber unflexibler), kann als tasks.json Datei auch die folgende verwenden:

                  {
                      "version": "2.0.0",
                      "tasks": [
                          {
                              "taskName": "build",
                              "type": "shell",
                              "command": "sqlplus -s scott/tiger@172.30.30.30:1521/xe.muniqsoft-training.de @${file}",
                          },     
                      ]
                  }


                  Dann kann man sich auch die Dateien _run_sqlplus.bat und _show_errors.sql sparen hat dann aber

                  • keine schönen Fehlermeldungen in einem eigenen Reiter (man sollte dann unter jeden PL/SQL im Skript "show errors" schreiben)
                  • keine Umstellung auf ein Unicode Terminal
                  • keine Möglichkeit einfache Anpassungen für Windows/Unix/Max durchzuführen.


                  Weitere tolle Tipps erfahren Sie in einem unserer vielen Entwicklungskurse...

                   



                  Weitere Interessente Artikel zum Thema:



                  Empfohlene Schulungen zum Thema:


                  Übersicht der wichtigsten SQL-Befehle

                  Bereich:SQL, Version: ab RDBMS 11.x, Letzte Überarbeitung: 12.07.2023

                  Keywords:sql, sql-befehle

                  Der Oracle SELECT Befehl

                   

                  INFO

                   

                  		ERKLÄRUNGBEISPIEL
                  SELECT * FROM tabellen_namewählt alle Spalten der Tabelle ausSELECT *           
                  FROM emp;
                  ausdruck as "..."Spalten-Aliasnamen verwendenSELECT ename as "Name"           
                  FROM emp;
                  ... || ...VerkettungsoperatorSELECT ename||job           
                  FROM emp;
                  ... ||'...'||...Zeichenkette verwendenSELECT 'Hallo '||user||' wie geht`s?'           
                  FROM dual;
                  DISTINCT spalteschließt doppelte Zeilen ausSELECT DISTINCT job FROM emp;
                  ORDER BY spalte [desc/asc]sortiert in bestimmter ReihenfolgeSELECT ename, sal           
                  FROM emp           
                  ORDER BY sal desc;
                  ORDER BY alias_spaltesortiert nach AliasnamenSELECT ename, job beruf, sal           
                  FROM emp           
                  ORDER BY beruf, ename;

                   

                   

                  Die Oracle WHERE Bedingung

                  INFOERKLÄRUNGBEISPIEL
                  WHERE bedingungWHERE bedingung(en)SELECT * FROM emp           
                  WHERE ename = 'SMITH';
                  WHERE ... >, >, = <,<>, =VergleichsoperatorenSELECT ename FROM emp           
                  WHERE deptno <> 10;
                  WHERE ... BETWEEN ... ANDzeigt Zeilen auf einem Wertebereich basierend anSELECT * FROM emp           
                  WHERE sal BETWEEN 1000 AND 2000;
                  WHERE ... IN(...,...,...)teste auf Werte in einer ListeSELECT * FROM emp           
                  WHERE mgr IN (7902, 7782, 7839);
                  WHERE ... LIKE '%... %'sucht nach gültigen ZeichenfolgenSELECT * FROM emp           
                  WHERE ename LIKE 'A%';
                  WHERE... IS [NOT] NULLtestet auf (nicht) Null-WerteSELECT * FROM emp           
                  WHERE comm IS NOT NULL;
                  WHERE... AND ...setzt voraus das beide Bedingungen wahr sindSELECT * FROM emp           
                  WHERE sal = 1300 AND deptno = 10;
                  WHERE... OR...setzt voraus das mind. eine der Bedingungen war istSELECT * FROM emp           
                  WHERE sal>2000 OR job LIKE 'SAL%';
                  WHERE... NOT...setzt voraus das eine Bedingung nicht erfüllt istSELECT * FROM emp           
                  WHERE ename NOT BETWEEN 'CLARK' AND 'SCOTT';
                  WHERE(... OR...) AND...Klammern verwenden um Priorität durchzusetzenSELECT *           
                  FROM emp           
                  WHERE (deptno = 10 OR depnto = 20)           
                  AND sal>2500;

                   

                   

                  Die Oracle CASE Bedingung

                  INFOBESCHREIBUNGBEISPIEL
                  CASE... WHEN... THEN...Das Pendant zum If-Then-Else Statement in PL/SQLSELECT emp,           
                  CASE           
                  WHEN sal>3000 THEN 'Das Gehalt ist größer als 3000'           
                  WHEN sal<2500 THEN 'Das Gehalt ist kleiner als 2500'           
                  ELSE 'ERROR'           
                  END;           
                  FROM emp;
                  CASE [exp] WHEN... THEN...Man kann Spalte auch schon beim Wort CASE mit angebenSELECT emp,           
                  CASE owner           
                  WHEN 'SYS' THEN 'Der Owner ist SYS'           
                  WHEN 'SYSTEM' THEN 'Der Owner ist SYSTEM'           
                  END;           
                  FROM all_tables;

                   

                  Oracle Tabellenverknüpfung durch Joins

                  INFOBESCHREIBUNGBEISPIEL
                  Equijoinidentische Werte in 2 Spalten verschiedener TabellenSELECT ename, e.deptno, dname, loc           
                  FROM emp e JOIN dept d;
                   alte SyntaxSELECT ename, e.deptno, dname, loc           
                  FROM emp e, dept d, WHERE e.deptno = d.deptno;
                   USING-KlauselSELECT ename, deptno, dname, loc           
                  FROM emp JOIN dept USING(deptno);
                   Natural Join, Verknüpfung erfolgt durch gleiche Spalten
                  SELECT empno, deptno, dname, loc           
                  FROM emp natural JOIN dept;
                  Self Joinverknüpft eine Tabelle mit sich selbstSELECT t1.ename "Hiwi", t2.ename "Chef"           
                  FROM emp t1, emp 2 WHERE t1.mgr = t2.empno;
                   alte SyntaxSELECT t1.ename "Hiwi", t2.ename "Chef"           
                  FROM emp t1 JOIN emp t2           
                  ON (t1.mgr = t2.empno);
                  Non-Equijoinkeine Spalte einer Tabelle entspricht direkt einer Spalte einer anderen TabelleSELECT ename, job, sal grade           
                  FROM emp e, salgrade s           
                  WHERE sal BETWEEN s.local and s.hisal;
                  Outer Joinzeigt auch Zeilen an die nicht der Join Bedingung entsprechen 
                   {LEFT|RIGHT|FULL} Outer JoinSELECT empno, ename, dname, d.deptno           
                  FROM emp e RIGHT OUTER JOIN dept d           
                  ON (e.deptno = d.deptno);
                   alte SyntaxSELECT empno, ename, dname, d.deptno           
                  FROM emp e, dept d           
                  WHERE e.deptno(+) = d.deptno;
                  Cross Joinentspricht dem KreuzproduktSELECT empno, ename, sal, dname           
                  FROM emp CROSS JOIN dept;

                   



                  Weitere Interessente Artikel zum Thema:


                  Empfohlene Schulungen zum Thema:


                  Übersicht der wichtigsten DML Befehle

                  Bereich:SQL, Version: ab RDBMS 11.x, Letzte Überarbeitung: 12.07.2023

                  Keywords:Oracle DDL Befehle

                  Data Manipulation Language (DML)

                  INFOBESCHREIBUNGBEISPIEL
                  INSERT INTO tabelle[(spalte,...)]  
                  VALUES (value, ..)                  		fügt neue Zeilen in eine Tabellen hinzuINSERT INTO emp (empno, ename, job, deptno)  
                  VALUES  
                  (8000,'HUBER', 'TRAINEE', 40);  
                  INSERT INTO dept  
                  VALUES  
                  (50, 'HEAD_QUARTER', null);
                  INSERT INTO tabelle [(spalte, ..)]
                   unterabfrage                  		kopiert Zeilen aus einer anderen TabelleINSERT INTO emp_copy  
                  SELECT * FROM emp WHERE job = 'MANAGER';
                  UPDATE tabelle  
                  SET spalte = value  
                  [WHERE condition]
                  ändert vorhandene Zeilen  
                  ändert bestimmte Zeilen                  		UPDATE emp SET sal = 2000  
                  WHERE deptno = 10;  

                  UPDATE emp SET  
                  sal = 1111,  
                  comm = 1000,  
                  deptno = 50  
                  WHERE ename = 'HUBER';  
                  UPDATE emp  
                  SET (deptno, sal, comm) =  
                  (SELECT deptno, sal, comm FROM emp WHER empno = 7499)  
                  WHERE ename = 'MILLER';
                  DELETE FROM tabelle  
                  [WHERE condition]                  		löscht Zeilen aus einer TabelleDELETE FROM emp  
                  WHERE job = 'TRAINEE';  
                  DELETE FROM emp  
                  WHERE deptno = (SELECT min(deptno) FROM emp);
                  MERGE INTO tabelle  
                  USING tabelle  
                  ON(condition)  
                  WHEN MATCHED THEN UPDATE  
                  SET(condition)  
                  WHEN NOT MATCHED THEN INSERT  
                  (condition)                  		Kombination von INSERT- und UPDATE-Anweisungen (ab 9i)MERGE INTO annsal a USING emp e  
                  ON (a.empno = e.empno)  
                  WHEN MATCHED THEN UPDATE  
                  SET a.peryear = (e.sal*12)  
                  WHEN NOT MATCHED THEN INSERT  
                  (a.empno, a.peryear)  
                  VALUES (e.empno, e.sal*12);

                   

                  Transaktionen

                  INFOBESCHREIBUNG
                  COMMIT;Datenänderungen werden permanent festgeschrieben
                  ROLLBACK;alle Änderungen werden zurückgesetzt
                  ROLLBACK TO SAVEPOINT test;alle vorgenommenen Änderungen werden bis zum  
                  SAVEPOINT zurückgesetzt

                   

                  Sperrmechanismen

                  INFOBESCHREIBUNG
                  SET TRANSACTION READ COMMITTED;/th>Standarteinstellung
                  SET TRANSACTION READ ONLYerlaubt nur Lesen der Daten, kein ändern


                  Weitere Interessente Artikel zum Thema:


                  Empfohlene Schulungen zum Thema:



                    Übersicht der wichtigsten DDL-Befehle

                    Bereich:SQL, Version: ab RDBMS 11.x, Letzte Überarbeitung: 25.03.2020

                    Keywords:Oracle DDL Befehle

                    Data Manipulation Language (DML)


                    Fügt neue Zeilen in eine Tabelle hinzu
                      Syntax:

                      INSERT INTO tabelle [(spalte, ...)] VALUES (value, ...);
                      Beispiel:
                      INSERT INTO emp
  (empno, ename, job, deptno)
  VALUES
  (8000,'HUBER','TRAINEE',40);
  INSERT INTO dept
  VALUES
  (50, 'HEAD_QUARTER', null);

                    Kopiert Zeilen aus einer anderen Tabelle
                      Syntax:

                      INSERT INTO  tabelle [(spalte ...)]
               unterabfrage


                    Beispiel:

                      INSERT INTO emp_copy
  SELECT * FROM emp WHERE job = 'MANAGER';

                     

                    Syntax:

                    UPDATE tabelle
SET spalte = value  ändert vorhandene Zeilen
[WHERE condition]  ändert bestimmte Zeilen 


                    Beispiel:

                    
  UPDATE emp SET sal = 2000
  WHERE deptno = 10;
  UPDATE emp
  SET sal = 1111, comm = 1000, deptno = 50
  WHERE ename =  'HUBER';
  UPDATE emp
  SET (deptno, sal, comm) =
      (SELECT deptno, sal, comm
       FROM emp WHERE empno = 7499)
  WHERE ename =  'MILLER';



                    Löscht Zeilen aus einer Tabelle
                      Syntax:

                      DELETE FROM  tabelle
              [WHERE condition]


                      Beispiel:

                      DELETE FROM emp
  WHERE job = 'TRAINEE';

  DELETE FROM emp
  WHERE deptno = (SELECT MIN(deptno)FROM emp);


                    Kombination von INSERT- und UPDATE-Anweisungen (ab 9i) 
                      Syntax:

                    MERGE INTO   tabelle                   
               USING tabelle
               ON (condition) 
WHEN MATCHED THEN UPDATE
               SET (condition) 
WHEN NOT MATCHED THEN INSERT
              (condition) 


                      Beispiel:

                       MERGE INTO annsal a USING emp e
   ON (a.empno = e.empno)
   WHEN MATCHED THEN UPDATE
   SET a.peryear = (e.sal*12)
   WHEN NOT MATCHED THEN INSERT
   (a.empno, a.peryear)
   VALUES (e.empno, e.sal*12);

                    Transaktionen

                     

                       COMMIT;                                               → Datenänderungen werden permanent festgeschrieben
                       ROLLBACK;                                          → alle Änderungen werden zurückgesetzt
                       ROLLBACK TO SAVEPOINT test;    → alle vorgenommenen Änderungen werden bis zum SAVEPOINT zurückgesetzt
                     

                    Sperrmechanismen

                       SET TRANSACTION READ COMMITTED;  → (Standardeinstellung)
                       SET TRANSACTION READ ONLY;          → erlaubt nur Lesen der Daten, kein Ändern
                     

                    Data Definition Language (DDL)

                    Erstellt Tabelle mit Spalten und -attributen 
                      Syntax:    

                    
   CREATE TABLE tabellenname   
               (spalte type(n), ...)


                      Beispiel:

                    CREATE TABLE kollegen (
   k_nr number(3) NOT NULL,
   nname varchar2(20),
   ort varchar2(20) DEFAULT 'MÜNCHEN');


                    Datentypen
                        ⇒z.B. number[(p,s)], varchar2(n), char[(n)], date

                    Erstellt Tabelle mittels Unterabfrage aus anderer Tabelle 
                      Syntax: 

                    CREATE TABLE tabellenname 
          AS   subquery


                      Beispiel:

                    CREATE TABLE dept10 AS 
   SELECT empno, ename, sal*12 j_gehalt,
   hiredate FROM emp WHERE deptno = 10;


                    Fügt eine neue Spalte in eine Tabelle 
                     Syntax: 

                    ALTER TABLE  tabellenname
          ADD  (spalte type(n), ...)


                     Beispiel:

                     ALTER TABLE kollegen ADD (email char(30));

                     

                    Ändert eine vorhandene Spalte
                      Syntax:

                    ALTER TABLE  tabellenname
            Modify (spalte type(n), ...)


                      Beispiel:

                    ALTER TABLE kollegen 
   MODIFY (email varchar2(35));


                    Löscht eine Spalte aus einer Tabelle
                      Syntax:

                          DROP COLUMN spalte


                      Beispiel:

                    ALTER TABLE emp DROP COLUMN job;
   ALTER TABLE dept DROP COLUMN deptno
   CASCADE CONSTRAINTS;



                    Löscht die gesamte Tabelle unwiderruflich 
                     Syntax:

                    DROP TABLE  tabellenname;

                    Beispiel:
                     

                    DROP TABLE kollegen;
DROP TABLE dept CASCADE CONSTRAINTS;


                    Benennt eine Tabelle um
                      Syntax:

                    RENAME  table TO table_neu


                     Beispiel:     

                    RENAME kollegen TO ex_kollegen;

                     

                    Leert eine Tabelle
                      Syntax:
                    TRUNCATE TABLE tabellenname

                      Beispiel:     
                    TRUNCATE TABLE ex_kollegen;

                    Views

                    Bettet Unterabfrage in die Anweisung 'CREATE VIEW' mit ein
                      Syntax:

                    CREATE VIEW  viewname    
                AS subquery 


                      Beispiel:

                    CREATE OR REPLACE VIEW empvu20 (nr, nname,
   beruf) AS SELECT empno, ename, job FROM emp
   WHERE deptno = 20 WITH CHECK OPTION;

                     

                    Löscht eine View
                      Syntax:

                    DROP VIEW  viewname 


                      Beispiel:

                    DROP VIEW empvu20;



                    Fügt Daten einer View hinzu
                      Syntax:

                     INSERT INTO  view [(spalte, ...)]  
                                VALUES (value, ...) 


                      Beispiel:

                    INSERT INTO emp_view (empno, ename, deptno)
   VALUES (9000,'HUGO',20);


                    Benutzen einer Inline View als Datenquelle
                     Syntax:

                    SELECT  spalte, spalte  
FROM tabelle alias,
(inline view) alias 
bedingung


                      Beispiel:

                    SELECT a.ename, a.sal, a.deptno, b.maxsal  
   FROM emp a, (SELECT deptno, max(sal) maxsal       
                FROM emp GROUP BY deptno) b
   WHERE a.deptno = b.deptno
   AND a.sal<b.maxsal;


                    Top-N-Analysen ermitteln die größten bzw. kleinsten Werte einer Spalte
                      Syntax:

                    SELECT rownum alias, spalte, ...   
   FROM (inline view   
   ORDER BY spalte)
   bedingung 


                      Beispiel:

                    SELECT rownum rang, ename, sal
   FROM (SELECT ename, sal FROM emp
         ORDER BY sal desc)
   WHERE rownum< = 5;


                    Weitere Interessente Artikel zum Thema:


                    Empfohlene Schulungen zum Thema:


                    Übersicht der wichtigsten DDL-Befehle (Teil 2)

                    Bereich:SQL, Version: ab RDBMS 11.x, Letzte Überarbeitung: 19.03.2020

                    Keywords:Oracle DDL Befehle

                    Constraints

                    Stellt sicher, dass keine Null-Werte in die Spalte eingetragen werden können

                     Beispiel:

                    CREATE TABLE t (
name varchar2(50) CONSTRAINT cons1 NOT NULL,
vname varchar2(20));
ALTER TABLE t MODIFY (vname NOT NULL);


                    Setzt voraus, dass jeder Wert einer Spalte eindeutig ist 

                     Beispiel:

                    CREATE TABLE kunden (
nr number, sub_nr number, name varchar2(50),
CONSTRAINT kdn_nr_uk UNIQUE (nr,sub_nr));
ALTER TABLE kunden
ADD CONSTRAINT kunden_uk UNIQUE (nr);


                    UNIQUE KEY + NOT NULL

                     Beispiel:

                    CREATE TABLE kunden2 (
nr number, sub_nr number, CONSTRAINT   kunden_pk PRIMARY KEY (nr,sub_nr));
  
ALTER TABLE kunden2
ADD CONSTRAINT kunden_pk PRIMARY KEY (nr);



                    Legt Beziehung zu Primärschlüssel in derselben oder anderer
                    Tabelle fest    

                     Beispiel: 

                    CREATE TABLE rechnung (
kd_nr number(2) CONSTRAINT kunden_fk
REFERENCES kunden(kd_nr)
ON DELETE cascade);
ALTER TABLE emp   
ADD CONSTRAINT emp_deptno_fk FOREIGN KEY
(deptno) REFERENCES dept(deptno); 



                    Definiert eine Beziehung, die jede Zeile erfüllen muss
                     
                     Beispiel:

                    CREATE TABLE kunden3 (
kd_nr number CONSTRAINT kunden_ch
CHECK (kd_nr>0 AND kd_nr<1000);
ALTER TABLE kunden4
ADD CONSTRAINT kunden_geschlecht
CHECK (geschlecht IN ('M','W'))); 



                    Löscht bestehende Constraints
                      Syntax:
                       ALTER TABLE  tabelle
                       DROP PRIMARY KEY | UNIQUE (spalte) CONSTRAINT constraint [cascade]   
                      Beispiel:   

                    ALTER TABLE kunden3 DROP CONSTRAINT kunden_ch;


                     Sequenzen, Indizes und Synonyme

                    Legt eine Sequenz an
                      Syntax: 
                       CREATE SEQUENCE name   
                      [optionen]
                      Beispiel:  

                    CREATE SEQUENCE emp_empno
INCREMENT BY 10
START WITH 2500
MAXVALUE 9990; 



                    Verwendet beim Einfügen neuer Daten eine Sequenz
                     Syntax:
                         INSERT INTO tabelle [(spalte, ...)] 
                                   VALUES
                                                (name.NEXTVAL,  
                                   value,...) 
                      Beispiel: 

                    INSERT INTO emp (empno, ename, job, sal) VALUES
(emp_empno.NEXTVAL,'HUBER','CLERK',2500);



                    Legt einen Index auf Spalte einer Tabelle an
                     Syntax:
                       CREATE [UNIQUE] INDEX name
                       ON tabelle (spalte)
                       [tablespace idx_tbs] 
                      Beispiel:  

                    CREATE UNIQUE INDEX dept_deptno_idx ON dept     
(deptno) TABLESPACE indx; 


                    Legt ein Synonym für ein DB-Objekt an
                      Syntax:
                       CREATE [PUBLIC] SYNONYM name
                       FOR object_name  
                      Beispiel:  

                    CREATE PUBLIC SYNONYM emp FOR scott.emp;

                    Benutzerverwaltung und Data Contol Language (DCL)

                    Legt einen neuen Benutzer mit Passwort an
                      Syntax:
                         CREATE USER  user_name IDENTIFIED BY password [optionen];
                      Beispiel:

                    CREATE USER hugo IDENTIFIED BY lion  
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 1G ON users;


                    Ändert das Passwort eines Benutzers
                      Syntax:
                       ALTER USER username IDENTIFIED BY password         
                      Beispiel:

                    ALTER USER hugo IDENTIFIED BY cat;


                    Gewährt Zugriffsrechte auf die DB und darin enthaltene Strukturen  
                      Syntax:
                         GRANT  objekt_priv  
                              [(spalten)]   
                       ON objekt  
                       TO {user|role|public}
                       [WITH GRANT OPTION]
                      Beispiel:

                       GRANT CREATE SESSION, CREATE TABLE TO huber;

   GRANT CONNECT, RESOURCE TO hugo;
   GRANT SELECT, INSERT ON freunde
   TO hugo, heidi WITH GRANT OPTION;
   GRANT UPDATE (job, mgr, deptno) 
   ON emp TO buchhaltung; 

                     
                    Entzieht Zugriffsrechte
                      Syntax:
                       REVOKE  {privileg, ...|all}
                       ON objekt
                       FROM {user, ...|role|public}
                       [cascade constraints]
                     Beispiel:

                    REVOKE SELECT, INSERT ON freunde FROM hugo; 

                    SQL*Plus-Befehle

                    DESC  table                       zeigt Tabellenstruktur an
                                                                  DESC emp

                    COL  spalte                       ändert Spaltenformat
                    FORMAT [a]...            COL ename FORMAT a20
                                           COL sal FORMAT 99,999.99

                    CONNECT  user/pwd            stellt Verbindung zu einem anderen Benutzer her
                                                                  CONNECT system/manager

                    DEFINE                                 zeigt definierte Variablen an
                                            DEFINE

                    SET  parameter         ändert Parameter für die Sitzung
                                           SET lines 1000 pages 100 head off

                    SHOW  parameter               zeigt Parameter der laufenden Sitzung an                                               
                                           SHOW linesize
                                           SHOW all

                    EDIT  [dateiname]      ruft Editor auf (Standard: afiedt.buf)
                                                                  ED
                                           ED c:\oracle\ora81\rdbms\admin\scott.sql

                    EXIT                                      beendet SQL*Plus
                                                                  EXIT  

                    GET  dateiname                 schreibt / holt Inhalt einer gespeicherten Datei in SQL-Puffer
                                           GET c:\oracle\ora81\bin\date

                    SAVE  dateiname                 speichert aktuellen Inhalt des SQL-Puffers in Datei
                                            SAVE date
                                           SAVE c:\temp\test.sql

                    SPOOL  dateiname       speichert Abfrageergebnisse in einer Datei
                                           SPOOL c:\temp\sicherung.lst

                    START  dateiname              führt gespeicherte Befehlsdatei aus
                                                                  START c:\oracle\ora81\bin\date
                    @  dateiname                      führt gespeicherte Befehlsdatei aus
                                                                  è @ date

                    CL[EAR]                 option nimmt Löschoperation vor
                                           CL SCR

                    L[IST] [n] [m n] &

                    LISTAGG Alternative mit CLOB Datentyp (und damit 128TB Maximallänge)

                    Bereich:PL/SQL:SQL, Version: ab RDBMS 10.x:RDBMS 12.x:RDBMS 19.3:RDBMS 21.1, Letzte Überarbeitung: 11.01.2021

                    Keywords:listagg clob

                    Wer kennt es nicht, immer wenn man mal listagg benötigt, reciht der Speicherplatz nicht, weil listagg maximal 32767 Bytes zurückgeben kann.

                    Mit einem kleinen Trick klappt es auch mit CLOB als Rückggaabetyp. Dazu verwenden wir die XML Funktion XMLELEMENT.
                    Keine Angst, sie müssen kein XML dazu beherschen:-)

                    Wir haben das ganze in eine WITH Funktion gekappselt, für den Fall, dass Sie kein CREATE PROCEDURE RECHT haben.
                    Die Funktion hat bis zu 3 Parameter,
                    column_name ist der Name der Spalte, die zusammengefasst werden soll
                    table_name ist der Name der Tabelle, in der die Spalte enthalten ist
                    where_cond ist Optional und beinhaltet eine Filterklausel Default 1=1 also alles)
                    order_by Optional kann auch ein Sortierreihenfolge bei der Ausgabe definiert werden (Default: Sortiert nach der Ausgabespalte)
                    delimiter ist auch Optional und gibt das Trennzeichen zwischen der Ausgabe der Spaltenwerte an (Default ',' )

                    WITH
                    FUNCTION listagg_clob (
                    column_name IN VARCHAR2,
                    table_name  IN VARCHAR2,
                    where_cond  IN VARCHAR2 DEFAULT NULL,
                    order_by    IN VARCHAR2 DEFAULT NULL,
                    delimiter   IN VARCHAR2 DEFAULT ',')
                    RETURN CLOB
                    IS
                    ret_clob CLOB;
                    BEGIN
                    EXECUTE IMMEDIATE q'!select replace(replace(XmlAgg(
                                      XmlElement("a", !' || column_name ||')
                                      order by ' ||nvl(order_by,'1') ||
                                      q'!)
                                      .getClobVal(),
                                  '<a>', ''),
                                '</a>','!'|| delimiter ||q'!') as aggname
                       from !' || table_name || q'!
                      where  !' || nvl(where_cond,' 1=1') INTO ret_clob;
                    RETURN ret_clob;
                    END;
                    SELECT listagg_clob('table_name','all_tables') FROM dual;



                    Wenn Sie das Ganze als eigenständige Funktion anlegen möchten:

                    CREATE OR REPLACE FUNCTION listagg_clob (
                    column_name IN VARCHAR2,
                    table_name  IN VARCHAR2,
                    where_cond  IN VARCHAR2 DEFAULT NULL,
                    order_by    IN VARCHAR2 DEFAULT NULL,
                    delimiter   IN VARCHAR2 DEFAULT ',')
                    RETURN CLOB
                    IS
                    ret_clob CLOB;
                    BEGIN
                    EXECUTE IMMEDIATE q'!select replace(replace(XmlAgg(
                                      XmlElement("a", !' || column_name ||')
                                      order by ' ||nvl(order_by,'1') ||
                                      q'!)
                                      .getClobVal(),
                                  '<a>', ''),
                                '</a>','!'|| delimiter ||q'!') as aggname
                       from !' || table_name || q'!
                      where  !' || nvl(where_cond,' 1=1') INTO ret_clob;
                    RETURN ret_clob;
                    END;
                    /
                     


                    Testcases:

                    SELECT listagg_clob('table_name','all_tables')
                    FROM dual;
                    SELECT listagg_clob('table_name','all_tables',order_by=>'table_name desc nulls last')
                    FROM dual;

                    SELECT length(listagg_clob(q'!owner||'.'||table_name!','all_tables', where_cond=>q'!table_name<>'MARCO'!'))
                    FROM dual;
                    =>60962

                    SELECT listagg_clob('table_name','all_tables',order_by=>'table_name desc nulls last', delimiter=>':')
                    FROM dual;
                     


                    Damit können Sie nun (fast) unbegrenzt die Werte einer Spalte zusammenfassen und weiterverarbeiten.



                    Weitere Interessente Artikel zum Thema:


                    Empfohlene Schulungen zum Thema:
                    2024.05.19