25. November 2013

Remote Debugging für Java Stored Procedures

Remote Debugging Java Stored Procedures
Auf diesem Blog habe ich ja schon etwas öfter mit Java in der Datenbank gearbeitet. Auf der DOAG2013 erreichte mich nun nochmal die Frage, ob (und wenn ja, wie) man Java Stored Procedures (die in der Datenbank laufen) mit einem Java-Werkzeug remote debuggen kann. In der Oracle-Dokumentation (Java Developers' Guide) steht drin, dass es geht, aber nicht, wie. Im Internet gibt es das eine oder andere Blog-Posting zum Thema, meist sind diese aber schon etwas älter und passen nicht mehr zu den heute verfügbaren Werkzeugen. Daher gibt es heute eine Schritt-für-Schritt-Anleitung zum Remote-Debugging einer Java Stored Procedure.

Voraussetzungen für das Remote-Debugging

Zunächst braucht es den Java Code, der in der Datenbank als Java Stored Procedure laufen soll und in dem wir nun Breakpoints setzen und ein wenig debuggen wollen.
public class DebugTest {
  public static String cnt(int iTimes) {
    String s = "";
    for (int i=0;i<iTimes;i++) {
      s = s + ".";
    }
    return s;
  }
}
Außerdem braucht es ein Werkzeug, mit dem man überhaupt debuggen kann. Mit dem SQL Developer geht es leider nicht - denn der kann zwar PL/SQL, aber kein Java debuggen. Wir müssen also den JDeveloper hernehmen - der lässt sich, wie immer, aus dem OTN herunterladen. Für dieses Beispiel könnt Ihr die kleinere Java Edition nehmen; mit 146MB ist der Download wesentlich schlanker als die 1,8G der vollständigen Edition. Und zum Debuggen einer Java Stored Procedure reicht das aus.

Bevor es weitergeht, eine wichtige Voraussetzung: Es muss eine Netzwerkverbindung zwischen der Datenbank und dem installierten JDeveloper möglich sein. Mehr noch: Für das Remote Debugging wird die Datenbank eine Netzwerkverbindung zum JDeveloper hin öffnen - der JDeveloper (auf dem PC des Entwicklers) ist also dann der "Server", der von der Datenbank kontaktiert wird. Das muss vom Netzwerk her möglich sein und darf nicht durch Firewalls blockiert werden - am besten überprüft Ihr das vorher (ping, putty, etc.).

JDeveloper Projekt einrichten

Nach dem Herunterladen, Auspacken und Starten des JDeveloper erzeugt Ihr eine Application, darin ein Projekt und darin eine neue Java-Klasse mit obigem Code.
Nun müsst Ihr das Debugging für euer Projekt einschalten. Dazu öffnet Ihr im Baum links oben das Kontextmenü und navigiert zu den Project Properties.
Dort navigiert Ihr zuerst zum Abschnitt Compiler. Setzt dort das Häkchen bei Full Debug Info und setzt die JDK Version Compatibility auf die Version, die in der Datenbank aktiv ist. Für eine 10.2-Datenbank muss das auf 1.4 gesetzt werden, für eine 11g-Datenbank auf 1.5 und für 12c muss es 1.6 sein.
Danach geht es zum Bereich Run/Debug. Ihr seht dort eine Konfiguration namens Default - die wollen wir ändern, klickt dazu den Button Edit auf der rechten Seite.
Es öffnet sich ein weiteres Fenster. Im Bereich der Launch Settings aktiviert Ihr mit einem Häkchen das Remote Debugging ...
... und dann navigiert Ihr zum Abschnitt Debugger -> Remote. Dort wird bei Protokoll Listen for JPDA ausgewählt. Wenn Ihr möchtet, könnt Ihr noch einen TCP/IP Port vergeben, es kann aber auch bei der voreingestellten 4000 bleiben. Danach speichert Ihr mit Klick auf OK eure Änderungen ab - solange, bis alle Property-Fenster weg sind.

Java Stored Procedure kompilieren und in die Datenbank laden

Als nächstes kompiliert Ihr den Quellcode eures Java Programms. Ihr könnt das entweder manuell (auf der Kommandozeile) oder mit dem JDeveloper machen. Wenn Ihr auch mit dem JDeveloper kompiliert, wird eine Java-Klassendatei für die eingestellte Java-Version (hier: 1.5 für Oracle11g) mitsamt Debug-Einstellungen generiert und im JDeveloper-Projektverzeichnis abgelegt.
Alternativ könnt Ihr manuell (auf der Kommandozeile) kompilieren.
$ javac -target 1.5 -g DebugTest.java
$ ls
DebugTest.java DebugTest.class
Die entstandene .class-Datei muss nun mit Hilfe des Oracle-Werkzeugs loadjava in die Datenbank geladen werden. loadjava ist (ebenso wie SQL*Plus) Teil einer Oracle-Datenbankinstallation.
Es gibt drei Wege, Java-Code in die Datenbank zu laden: Wenn Ihr Java in der Datenbank remote debuggen möchtet, müsst Ihr die Kompilate, also die .class-Dateien, mit loadjava in die Datenbank laden. Wenn Ihr dagegen Java-Quellcode ladet oder CREATE JAVA SOURCE verwendet, kompiliert Ihr in der Datenbank selbst. Dort wird aber keine Debug-Information dazukompiliert, was dazu führt, dass das Remote-Debugging nicht geht. Also: loadjava verwenden und den Bytecode laden.
$ loadjava -u scott/tiger -o -r -v DebugTest.class
arguments: '-u' 'scott/***' '-o' '-r' '-v' 'DebugTest.class'
identical: DebugTest
skipping : class DebugTest
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 1
Synonyms Created: 0
Errors: 0
$
Nun ist der Java-Code in die Datenbank geladen. Damit er aufgerufen werden kann, braucht es noch etwas PL/SQL-Code:
create or replace function debugtest (p_cnt in number) return varchar2
is language java name 'DebugTest.cnt(int) return java.lang.String';
/

alter function debugtest compile debug
/
Die Prozedur generiert einen aus Punkten zusammengesetzten String - der Parameter legt die Anzahl der Punkte und damit die Länge fest. Ein kurzer Test der Prozedur empfiehlt sich ...
SQL> select debugtest(50) from dual;

DEBUGTEST(50)
--------------------------------------------------------------------------------
..................................................

1 Zeile wurde ausgewählt.
Außerdem braucht das Datenbankschema, in dem sich die Prozeduren befinden, noch das zum Remote-Debugging nötige Systemprivileg DEBUG CONNECT SESSION.
SQL> grant debug connect session to scott;

Benutzerzugriff (Grant) wurde erteilt.

Debugging-Sitzung im JDeveloper starten

Im JDeveloper setzt Ihr nun einen Breakpoint (sinnvollerweise innerhalb der Schleife); dann öffnet Ihr, im Baum auf der linken Seite, das Kontextmenü zu eurer Java-Klasse. Wählt Debug aus.
Dann öffnet sich ein kleines Fenster, in dem Ihr den TCP/IP-Port des Remote-Debuggers nochmals ändern könnt. Klickt auf OK. Es passiert dann nichts weiter, als dass links oben ein Fenster Processes geöffnet wird, in dem die Meldung erscheint, dass der Remote-Debugger des JDeveloper nun auf Verbindungen wartet. Lasst den JDeveloper nun offen ...
Der JDeveloper agiert nun also tatsächlich als "Server". Der Debugging-Vorgang muss nun aus der Datenbank heraus gestartet werden - denn dort soll das Debugging ja stattfinden. Daher ist es so wichtig, dass die Datenbank vom Netzwerk her in der Lage ist, eine Verbindung zum JDeveloper hin zu öffnen. Und um das möglich zu machen, brauchen wir als nächstes die IP-Adresse des Rechners, auf dem der JDeveloper läuft; typischerweise ist das ein Arbeitsplatz-PC.
D:\>ipconfig

Windows IP Configuration

Ethernet adapter Local Area Connection:

   Connection-specific DNS Suffix  . : mycompany.com
   IPv4 Address. . . . . . . . . . . : 10.1.1.100
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . : 10.1.1.1

Debugging aus der Datenbank heraus durchführen

Nun geht es wieder zur Datenbank. Öffnet eine SQL Session als Eigentümer der Java Stored Procedure. Als erstes muss sich die Datenbank mit dem JDeveloper verbinden. Die IP-Adresse habt Ihr mit ipconfig herausgefunden - den Port im JDeveloper eingestellt (oder bei 4000 belassen).
SQL> execute dbms_debug_jdwp.connect_tcp('10.1.1.100', 4000); 

PL/SQL-Prozedur erfolgreich abgeschlossen.
Nun sollte sich im JDeveloper etwas getan haben; im Fenster Processes solltet Ihr nun sehen, dass sich eine Datenbank mit dem Remote-Debugger verbunden hat.
Und jetzt geht's los: Startet eure Java Stored Procedure.
SQL> select debugtest(50) from dual;
Im Gegensatz zum Normalfall bleibt der Prozeduraufruf "hängen"; im JDeveloper könnt Ihr nun aber sehen, dass der Remote Debugger aktiv ist und die Ausführung beim Breakpoint angehalten hat. Und die immer beim Debuggen könnt Ihr nun im Einzelschrittmodus durch das Programm gehen, die Inhalte der Variablen verfolgen und sogar ändern.
Wenn das Debugging abgeschlossen ist, endet der Prozeduraufruf; und das Ergebnis wird an die SQL bzw. PL/SQL Engine zurückgegeben. Man sieht deutlich, dass die im Debugger veränderte Variable sich tatsächlich auf das Prozedurergebnis ausgewirkt hat - so wie es sein soll.
SQL> select debugtest(50) from dual;

DEBUGTEST(50)
--------------------------------------------------------------------------------
>>>>..................................................

1 Zeile wurde ausgewählt.
Fehlt zum Abschluss noch die Abmeldung der Datenbank vom Debugger im JDeveloper ...
SQL> execute dbms_debug_jdwp.disconnect;
Obwohl der Java an einem etwas exotischen Ort abläuft (in der Oracle-Datenbank), und aus einer SQL-Abfrage heraus gestartet wird, kann man ihn dennoch mit einem Standardwerkzeug remote debuggen - und man kann während des Debuggings vom JDeveloper aus sogar Java-Variablen in der Oracle-Datenbank ändern. Das finde ich schon recht interessant. Das Protokoll JPDA für das Remote-Debugging ist übrigens nicht auf den JDeveloper beschränkt - es ist ein Standardprotokoll, welches auch von anderen Werkzeugen beherrscht wird. Mit Eclipse sollte die Vorgehensweise also analog möglich sein.
I mentioned Java Stored Procedures in several blog postings in the past. During the DOAG2013 conference I got (again) the question, whether it is possible to use a Java IDE like JDeveloper to remote debug Java Stored Procedures in the database. And if that is possible: How to do this? The Oracle documentation (the Java Developers Guide) tells us, that remote debugging does work, but it does not describe what to do. There are also some blog postings showing the process with old JDeveloper versions - I got no one to work. So I decided to create a current step-by-step guide for remote debugging a Java Stored Procedure.

Prerequisites

First, we need some Java code to debug. It's a rather simple example, but it can be loaded into the database, we can set breakpoints and therefore we can perform some debugging.
public class DebugTest {
  public static String cnt(int iTimes) {
    String s = "";
    for (int i=0;i<iTimes;i++) {
      s = s + ".";
    }
    return s;
  }
}
Furthermore, we need a tool, which allows us to remote debug Java Code in the database. SQL Developer will not help, since it can only remote debug PL/SQL, but not Java in database. So we go for Oracle's JDeveloper, which can be downloaded from OTN. You only need the small Java Edition - it's download footprint is 146M, which is much more convenient than the "full" edition with 1.8G. And for debugging the Java Stored Procedure, this Java Edition is sufficient.

The next prerequisite is very important: The database must be able to open a network connection to JDeveloper (which is typically installed on the developer's PC). The network connection will be initiated by the database - JDeveloper will be the "listener" - this setup is rather uncommon, so you might check whether there are really no network firewalls making this impossible. It's a good idea to test this in advance (ping, ssh, putty, etc.)

Create and configure a JDeveloper Project

After downloading, unpacking and starting JDeveloper, create a new Application, within this a new Project and within that a new java class containing the above code.
Now it's about to enable debugging: Open the context menu in the navigation tree on the left and navigate to Project Properties.
First, go to the Compiler Section, check the Full Debug Info and set the JDK Version Compatibility to the Java Version in the database you are using. For a 10.2 database, this must be 1.4, for 11.1 and 11.2 set to 1.5 and for 12.1, use 1.6.
Afterwards, proceed to Run/Debug. There is one Run Configuration named Default. We'll change this now, so click the Edit button.
Another Window will open. Within the Launch Settings, activate Remote Debugging ...
... then go to the Debugger -> Remote section. Choose Listen for JPDA within the Protocol select list. If you like, change the default TCP/IP port. After doing this, save your changes by clicking OK, until all windows have been closed.

Compile and load the Java Stored Procedure into the database

Now compile the java source code. You can do this using JDeveloper or manually using the command line. After compiling with JDeveloper, the Java Binary Code is being placed into the project directory; you'll need it for the next step.
As said: Compiling on the command line is the alternative. Note to add the -target and -g switches.
$ javac -target 1.5 -g DebugTest.java
$ ls
DebugTest.java DebugTest.class
The resulting .class file must now be loaded into the database. We use the loadjava utility for this. As e.g. SQL*Plus, loadjava is part of an Oracle database installation.
There are three ways to load java into the database: For remote debugging, you must load the .class file into the database with loadjava. Loading Java sources (either with loadjava oder with SQL "CREATE JAVA SOURCE") will not work, since the Compiler inside the Oracle Database will not add debugger-specific information.
$ loadjava -u scott/tiger -o -r -v DebugTest.class
arguments: '-u' 'scott/***' '-o' '-r' '-v' 'DebugTest.class'
identical: DebugTest
skipping : class DebugTest
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 1
Synonyms Created: 0
Errors: 0
$
Now, as we have loaded the Java code into the database, we need some PL/SQL in order to actually execute the Java Stored Procedure.
create or replace function debugtest (p_cnt in number) return varchar2
is language java name 'DebugTest.cnt(int) return java.lang.String';
/

alter function debugtest compile debug
/
This procedure will generate a string by concatenating point characters. The parameter determines the amount of points. For the records, a short test ...
SQL> select debugtest(50) from dual;

DEBUGTEST(50)
--------------------------------------------------------------------------------
..................................................

1 row selected.
And finally, the database user needs the system privilege DEBUG CONNECT SESSION. With this privilege, the session is able to perform the communication with the remote debugger inside JDeveloper.
SQL> grant debug connect session to scott;

Grant succeeded.

Open the debugging session in JDeveloper

Back to JDeveloper: Set a breakpoint in your code. After that, go to your java code within the navigation tree on the left and open the context menu with a right click. Choose Debug.
Now, another window will open in which you can change the TCP/IP port for the remote debugger (if you like). Click on OK. Now JDeveloper starts a listener process for the debugger - which you can see in the Processes window. Leave the JDeveloper window open ...
As you can see, JDeveloper is now the "server". It's now on the database to start the debugging process. And that is the reason, why it is so important that there is a clear network between the database and the PC JDeveloper is running on. Before going back to the database, we need the IP address of the JDeveloper PC - on windows, ipconfig will help (use ifconfig on linux).
D:\>ipconfig

Windows IP Configuration

Ethernet adapter Local Area Connection:

   Connection-specific DNS Suffix  . : mycompany.com
   IPv4 Address. . . . . . . . . . . : 10.1.1.100
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . : 10.1.1.1

Perform Remote Debugging

Now we come back to the database. Open a session as the owner of the Java Stored Procedure (say: with SQL*Plus). First we need the database to connect to JDeveloper - we have determined the IP address and we have set the TCP/IP port within JDeveloper.
SQL> execute dbms_debug_jdwp.connect_tcp('10.1.1.100', 4000); 

PL/SQL procedure completed successfully.
You should see something within JDeveloper - the Processes window should indicate, that the database actually has connected to the debugger.
And then: Let's get started: Run your Java Stored Procedure.
SQL> select debugtest(50) from dual;
You should notice that your call seems to "hang". But in JDeveloper the debugger is active and has stopped that your breakpoint. And as always, you can step through the java program, monitor and even change the variable contents.
After completing the walk through the java code, the Java Stored Procedure finishes and returns a result to the SQL or PL/SQL engine. And as we'd expect it: the variable content we have changed within the debugger, really influenced the functions' result.
SQL> select debugtest(50) from dual;

DEBUGTEST(50)
--------------------------------------------------------------------------------
>>>>..................................................

1 row selected.
Finally, disconnect the database from JDeveloper.
SQL> execute dbms_debug_jdwp.disconnect;
Although this Java Stored Procedure runs in an exotic environment (inside the Oracle Database) and is being invoked with a SQL Query, we can use a standard java IDE in order to perform remote debugging. As with every other Java debugger, we can step through the progran, monitor and even change the variables. And since the JPDA protocol is not restricted to JDeveloper, a similar setup should be possible with other Java Development tools.

11. November 2013

Die Oracle-Datenbank und HTML5: Eine "Tablespace Map"

The Oracle Database and HTML5: A "Tablespace Map"
Auf meiner "Test-, Entwicklungs- und Ausprobier"-Datenbank kommt es, da ich immer wieder Objekte anlege und lösche, gerne mal vor, dass meine Datafiles größer und größer werden. Dann versucht man, die Datei mit ALTER DATABASE DATAFILE ... RESIZE ... zu schrumpfen - und stellt fest, dass es nicht geht ...
SQL> alter database datafile '/opt/oracle/oradata/orcl/users01.dbf' resize 1G;

alter database datafile '/opt/oracle/oradata/orcl/users01.dbf' resize 1G
*
FEHLER in Zeile 1:
ORA-03297: Datei enthält benutzte Daten über angeforderten RESIZE-Wert hinaus
... und das ist dann der Punkt, ab dem ich gerne wissen würde, wie es eigentlich in diesem Tablespace aussieht. Im Enterprise Manager gibt es sowas wie eine Tablespace Map, aber man hat ja nicht immer Zugriff auf einen Enterprise Manager.
Diese Herausforderung wollte ich ausnutzen, um mich ein wenig mehr mit der immer wichtiger werdenden HTML5-Technologie auseinanderzusetzen. Ziel ist es, eben mit HTML5, im Browser eine grafische Tablespace-Map bereitzustellen - und das mit wenig Aufwand. Die Download-URL zur fertigen APEX-Anwendung findet Ihr am Ende des Blog-Postings. Doch damit genug der Vorrede - jetzt geht's los. Technisch wird die Tablespace Map wie folgt arbeiten
  • Ausgangspunkt ist eine Webseite (der Einfachheit halber eine APEX-Anwendung)
  • Diese Webseite sendet (mit JavaScript) einen AJAX-Request an die Datenbank und ruft damit eine PL/SQL-Prozedur - GET_EXTMAP auf
  • Die PL/SQL Prozedur stellt anhand der Dictionary View DBA_EXTENTS die Informationen über die belegten und nicht belegten Extents des Tablespace zusammen und gibt das Ganze als JSON-Objekt zurück.
  • Das auf dem Server generierte JSON wird mit JavaScript ausgelesen und dann mit Hilfe eines HTML5 "Canvas" Objektes grafisch visualisiert.
Zur PL/SQL-Prozedur GET_EXTMAP. Da diese Prozedur aus APEX heraus aufgerufen werden soll, erfolgt die Ausgabe mit dem Web-Package HTP. Natürlich könnte man sie auch so umschreiben, dass das JSON als CLOB ausgeliefert wird.
create or replace procedure get_extmap(p_tablespace_name in dba_extents.segment_name%type)
is
  l_firstrow boolean := true;
  l_blocks   number  := 0;
begin
  select f.bytes / t.block_size into l_blocks
  from dba_tablespaces t, dba_data_files f
  where f.tablespace_name = t.tablespace_name and t.tablespace_name=p_tablespace_name;

  htp.prn('{"map": [');
  for i in (
    select owner, segment_name, stype, blocks, strt, send, (next_extent-send-1) as free_after from (
      select
        owner,
        extent_id,
        case
         when segment_type in ('TABLE', 'TABLE SUBPARTITION', 'TABLE PARTITION', 'NESTED TABLE') then 'TABLE'
         when segment_type in ('INDEX', 'INDEX SUBPARTITION', 'INDEX PARTITION') then 'INDEX'
         when segment_type in ('LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION') then 'LOB'
         when segment_type in ('CLUSTER') then 'CLUSTER'
         else 'SYSTEM'
        end as stype,
        segment_name,
        segment_type,
        blocks,
        block_id as strt,
        block_id + blocks - 1 as send,
        lead(block_id, 1) over (order by block_id) next_extent
      from dba_extents
      where tablespace_name=p_tablespace_name order by block_id
    )
  ) loop
    if (l_firstrow) then
      htp.p('{"t": "FREE", "n": "-", "c": '||i.strt||'},');
      l_firstrow := false;
    end if;
    htp.p('{"t": "'||i.stype||'", "n": "'||i.owner||'.'||i.segment_name||'", "c": '||i.blocks||'},');
    if nvl(i.free_after,(l_blocks - i.send)) != 0 then
      htp.p('{"t": "FREE", "n": "-", "c": '||nvl(i.free_after,(l_blocks - i.send))||'},');
    end if ;
  end loop;
  htp.p('{"t": "FREE", "n": "-", "c": 0}');
  htp.prn('], ');
  htp.prn('"total": '||l_blocks);
  htp.prn('}');
end;
Diese Prozedur generiert dann in etwa die folgende Ausgabe. Dieses JSON-Format kann im Browser mit der Funktion JSON.parse sehr einfach in ein Objekt konvertiert werden, in das man danach als Programmierer frei "hineingreifen" kann.
{
 "map": [
  {"t": "FREE",  "n": "-", "c": 128},
  {"t": "TABLE", "n": "TESTIT.TAB_GEOTAG_IMAGES", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000314943C00004$$", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_IL0000314943C00004$$", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000314943C00005$$", "c": 8},
  {"t": "TABLE", "n": "TESTIT.DEMO_PRODUCT_INFO", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_PRODUCT_INFO_PK", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_PRODUCT_INFO_UK", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000193713C00007$$", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_IL0000193713C00007$$", "c": 8},
  {"t": "TABLE", "n": "TESTIT.DEMO_CUSTOMERS", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_CUSTOMERS_PK", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_CUSTOMERS_UK", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_CUST_NAME_IX", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000309371C00009$$", "c": 128},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000309371C00009$$", "c": 128},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000309371C00009$$", "c": 128},
  {"t": "FREE",  "n": "-", "c": 72},
  {"t": "TABLE", "n": "TESTIT.APEX$_WS_WEBPG_SECTIONS", "c": 8},
  {"t": "INDEX", "n": "TESTIT.IDX_ENAME", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_IL0000290211C00008$$", "c": 8},
  {"t": "FREE",  "n": "-", "c": 5473},
  {"t": "FREE",  "n": "-", "c": 0},
  :
  ], 
 "total": 57600
}
Im Browser wird der folgende JavaScript Code hinterlegt. Zur Darstellung einer Tablespace Map wird die Funktion drawTsMap aufgerufen. Diese führt alle nötigen Schritte aus.
// Definition of colors for the various DB object types
var gCol = {
  "TABLE":   {"r": 255, "g": 32, "b": 32},
  "LOB":     {"r": 255, "g": 128, "b": 64},
  "INDEX":   {"r": 64, "g": 64, "b": 255},
  "FREE":    {"r": 255, "g": 255, "b": 255},
  "CLUSTER": {"r": 255, "g": 32, "b": 255},
  "SYSTEM":  {"r": 128, "g": 128, "b": 128},
  "END":    {"r": 0, "g": 0, "b": 0}
};

// function to "draw" the "blocks" for a specific DB object into
// the extent map

function drawCells(pArray, px, py, pType, pAmount) {
 var x = px;
 for (var i=0;i<pAmount;i++) {
   pArray.data[4*(pArray.width * py+x+i)+0] = gCol[pType].r;
   pArray.data[4*(pArray.width * py+x+i)+1] = gCol[pType].g;
   pArray.data[4*(pArray.width * py+x+i)+2] = gCol[pType].b;
   pArray.data[4*(pArray.width * py+x+i)+3] = 255;
 }
}

// main function

function drawTsMap(pRegionId, pTablespace, pWidth, pXZoom, pYZoom) {

  // Step 1: Execute AJAX request and retrieve JSON from server
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=getExtentMap',$v('pFlowStepId'));
  get.addParam("x01", pTablespace);
  var ltsMap = JSON.parse(get.get());

  // Step 2: Initialize HTML DIV containers for Tablespace Map
  var inf = document.createElement("div");
  inf.setAttribute("id", "inf_"+pRegionId);
  inf.setAttribute("class", "info");
  inf.style.display="none";
  try {
    $("#cv_"+pRegionId).remove();
    $("#inf_"+pRegionId).remove();
  } catch (e) {}
  var c = document.createElement("canvas");
  c.setAttribute("id", "cv_"+pRegionId);
  c.setAttribute("class", "tsmap");
  c.width = pWidth;
  c.height = Math.ceil(ltsMap.total / pWidth) + 5;
  c.style.width = (pWidth * pXZoom)+"px";
  c.style.height = (c.height * pYZoom)+"px";
  document.getElementById(pRegionId).appendChild(c);
  document.getElementById(pRegionId).appendChild(inf);

  // Step 3: Initialize HTML5 Canvas object
  var ctx = c.getContext("2d");
  ctx.fillStyle="#cccccc";
  ctx.fillRect(0,0, c.width, c.height);  
  var ida = ctx.getImageData(0,0,pWidth, c.height);

  // Step 4: Start drawing into the canvas
  var xc = 0, yc = 0;
  var xf = pWidth;
  var xtd , xd;

  for (var i=0;i<ltsMap.map.length; i++) {
    xtd = ltsMap.map[i].c;
    while (xtd > 0) {
      if (xtd > xf) {
        xd = xf; xtd = xtd - xd; xf = pWidth;
        drawCells(ida, xc, yc, ltsMap.map[i].t, xd);
        xc = 0; yc++;
      } else {
        xd = xtd; xtd = xtd - xd; xf = xf - xd;
        drawCells(ida, xc, yc, ltsMap.map[i].t, xd);
        xc = xc + xd;
      }
    }
  }

  // Step 5: Display Tablespace Map and add Mouse event handlers
  drawCells(ida, xc, yc, 'END',1);
  ctx.putImageData(ida,0,0);
  c.addEventListener("click", function (e) {handleMouseOver(e, c, inf, ltsMap);});
  c.addEventListener("dblclick", function (e) {inf.style.display="none";});
}

// Mouse Event Handling function
function handleMouseOver(e, cv, inf, ida) {
  var x = Math.round((e.pageX - cv.offsetLeft - 1) * cv.width / $(cv).width());
  var y = Math.round(((e.pageY - cv.offsetTop) - 4) * cv.height / $(cv).height());
  var n="- None -";
  var f=false;
  var i=0, b=0;
  while (i<ida.map.length && !f) {
    b = b + ida.map[i].c;
    if (b >= (y * cv.width + x)) {n = ida.map[i].n; f = true;} 
    i++;
  }
  inf.style.top=e.pageY +"px";
  inf.style.left=e.pageX +"px";
  inf.style.display="";
  inf.innerHTML = "BLOCK #: <b>" + (y * cv.width + x) + "</b><br/>Object: <b>"+ n + "</b><br/>Type: <b>"+ida.map[i-1].t+"</b><br/>Size: <b>"+ida.map[i-1].c+" blocks</b>"; 
}
Zum Abschluß wird das alles in eine APEX-Anwendung eingebunden. Die APEX-Anwendung benötigt (logischerweise) die Leseprivilegien auf DBA_EXTENTS und, zur Darstellung einer Auswahlliste der vorhandenen Tablespaces, auch auf DBA_TABLESPACES. Sobald ein Tablespace in der Auswahlliste gewählt wurde (onChange-Event), läuft der JavaScript-Code los - und generiert folgende Ansicht.
Die fertige APEX-Anwendung kann heruntergeladen und ausprobiert werden. Übrigens funktioniert HTML5 auch hervorragend auf mobilen Endgeräten.
HTML5 ist eine hochinteressante Technologie, die sich sehr gut zur Kombination mit einer Oracle-Datenbank eignet. Mit APEX lassen sich die nötigen Technologie-Bausteine sehr einfach zusammenbringen. Es muss nicht zwingend eine "Tablespace Map" sein; auch andere Dinge lassen sich auf diese Art und Weise darstellen. Viel Spaß beim Ausprobieren.
On the database, I do my daily work on (testing, developing, trying out customer scenarios), the datafiles typically are growing and growing. Then, of course, objects are being deleted and then I try to shrink the tablespace using the ALTER DATABASE DATAFILE ... RESIZE ... command - which does not work.
SQL> alter database datafile '/opt/oracle/oradata/orcl/users01.dbf' resize 1G;

alter database datafile '/opt/oracle/oradata/orcl/users01.dbf' resize 1G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Being at this point, I'd like to know how it looks inside the tablespace. Most often, there are only a few objects at the end of the database - and after moving them, the file can be shrinked without issues. In Enterprise Manager, I remember that there was a "tablespace map" - but we not always have access to an Enterprise Manager instance.
So I used this challenge in order to get more familiar with the upcoming HTML5 technology. Using Application Express, some JavaScript, AJAX and HTML5 Canvas, I was able to build a website showing the contents of a tablespace graphically. In this blog posting, I'll describe, how this will work - at the end of the posting you'll find a URL to download the "ready-to-use" APEX application:
  • The starting point will be a website - or in our case: The page of an APEX application
  • This page will execute an AJAX-Request, using JavaScript, which actually calls a PL/SQL procedure inside the database (GET_EXTMAP).
  • This PL/SQL Procedure performs a query on DBA_EXTENTS. The information is being rendered in JSON format and then returned to the browser.
  • The JavaScript code within the browser will finally process the JSON and render a graphical tablespace map using HTML5 "Canvas".
The PL/SQL procedure GET_EXTMAP will be called with an AJAX request executed by some JavaScript running in the browser. So it will write its output using the HTP package from Oracle's Web Toolkit. Of course, one can easily rewrite it to return a CLOB instead.
create or replace procedure get_extmap(p_tablespace_name in dba_extents.segment_name%type)
is
  l_firstrow boolean := true;
  l_blocks   number  := 0;
begin
  select f.bytes / t.block_size into l_blocks
  from dba_tablespaces t, dba_data_files f
  where f.tablespace_name = t.tablespace_name and t.tablespace_name=p_tablespace_name;

  htp.prn('{"map": [');
  for i in (
    select owner, segment_name, stype, blocks, strt, send, (next_extent-send-1) as free_after from (
      select
        owner,
        extent_id,
        case
         when segment_type in ('TABLE', 'TABLE SUBPARTITION', 'TABLE PARTITION', 'NESTED TABLE') then 'TABLE'
         when segment_type in ('INDEX', 'INDEX SUBPARTITION', 'INDEX PARTITION') then 'INDEX'
         when segment_type in ('LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION') then 'LOB'
         when segment_type in ('CLUSTER') then 'CLUSTER'
         else 'SYSTEM'
        end as stype,
        segment_name,
        segment_type,
        blocks,
        block_id as strt,
        block_id + blocks - 1 as send,
        lead(block_id, 1) over (order by block_id) next_extent
      from dba_extents
      where tablespace_name=p_tablespace_name order by block_id
    )
  ) loop
    if (l_firstrow) then
      htp.p('{"t": "FREE", "n": "-", "c": '||i.strt||'},');
      l_firstrow := false;
    end if;
    htp.p('{"t": "'||i.stype||'", "n": "'||i.owner||'.'||i.segment_name||'", "c": '||i.blocks||'},');
    if nvl(i.free_after,(l_blocks - i.send)) != 0 then
      htp.p('{"t": "FREE", "n": "-", "c": '||nvl(i.free_after,(l_blocks - i.send))||'},');
    end if ;
  end loop;
  htp.p('{"t": "FREE", "n": "-", "c": 0}');
  htp.prn('], ');
  htp.prn('"total": '||l_blocks);
  htp.prn('}');
end;
The JSON output generated by the procedure is shown here. The advantage of JSON is that it can be consumed in JavaScript with the JSON.parse function. The developer then gets an object which they can very easy navigate in.
{
 "map": [
  {"t": "FREE",  "n": "-", "c": 128},
  {"t": "TABLE", "n": "TESTIT.TAB_GEOTAG_IMAGES", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000314943C00004$$", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_IL0000314943C00004$$", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000314943C00005$$", "c": 8},
  {"t": "TABLE", "n": "TESTIT.DEMO_PRODUCT_INFO", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_PRODUCT_INFO_PK", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_PRODUCT_INFO_UK", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000193713C00007$$", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_IL0000193713C00007$$", "c": 8},
  {"t": "TABLE", "n": "TESTIT.DEMO_CUSTOMERS", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_CUSTOMERS_PK", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_CUSTOMERS_UK", "c": 8},
  {"t": "INDEX", "n": "TESTIT.DEMO_CUST_NAME_IX", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000309371C00009$$", "c": 128},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000309371C00009$$", "c": 128},
  {"t": "LOB",   "n": "TESTIT.SYS_LOB0000309371C00009$$", "c": 128},
  {"t": "FREE",  "n": "-", "c": 72},
  {"t": "TABLE", "n": "TESTIT.APEX$_WS_WEBPG_SECTIONS", "c": 8},
  {"t": "INDEX", "n": "TESTIT.IDX_ENAME", "c": 8},
  {"t": "LOB",   "n": "TESTIT.SYS_IL0000290211C00008$$", "c": 8},
  {"t": "FREE",  "n": "-", "c": 5473},
  {"t": "FREE",  "n": "-", "c": 0},
  :
  ], 
 "total": 57600
}
The following represents the JavaScript code which is part of the application running in the browser. To render a tablespace map, the function drawTsMap must be called. This function then executes all necessary steps: Issue the AJAX request, retrieve the JSON response, process it and render the tablespace map in an HTML5 canvas object.
// Definition of colors for the various DB object types
var gCol = {
  "TABLE":   {"r": 255, "g": 32, "b": 32},
  "LOB":     {"r": 255, "g": 128, "b": 64},
  "INDEX":   {"r": 64, "g": 64, "b": 255},
  "FREE":    {"r": 255, "g": 255, "b": 255},
  "CLUSTER": {"r": 255, "g": 32, "b": 255},
  "SYSTEM":  {"r": 128, "g": 128, "b": 128},
  "END":    {"r": 0, "g": 0, "b": 0}
};

// function to "draw" the "blocks" for a specific DB object into
// the extent map

function drawCells(pArray, px, py, pType, pAmount) {
 var x = px;
 for (var i=0;i<pAmount;i++) {
   pArray.data[4*(pArray.width * py+x+i)+0] = gCol[pType].r;
   pArray.data[4*(pArray.width * py+x+i)+1] = gCol[pType].g;
   pArray.data[4*(pArray.width * py+x+i)+2] = gCol[pType].b;
   pArray.data[4*(pArray.width * py+x+i)+3] = 255;
 }
}

// main function

function drawTsMap(pRegionId, pTablespace, pWidth, pXZoom, pYZoom) {

  // Step 1: Execute AJAX request and retrieve JSON from server
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=getExtentMap',$v('pFlowStepId'));
  get.addParam("x01", pTablespace);
  var ltsMap = JSON.parse(get.get());

  // Step 2: Initialize HTML DIV containers for Tablespace Map
  var inf = document.createElement("div");
  inf.setAttribute("id", "inf_"+pRegionId);
  inf.setAttribute("class", "info");
  inf.style.display="none";
  try {
    $("#cv_"+pRegionId).remove();
    $("#inf_"+pRegionId).remove();
  } catch (e) {}
  var c = document.createElement("canvas");
  c.setAttribute("id", "cv_"+pRegionId);
  c.setAttribute("class", "tsmap");
  c.width = pWidth;
  c.height = Math.ceil(ltsMap.total / pWidth) + 5;
  c.style.width = (pWidth * pXZoom)+"px";
  c.style.height = (c.height * pYZoom)+"px";
  document.getElementById(pRegionId).appendChild(c);
  document.getElementById(pRegionId).appendChild(inf);

  // Step 3: Initialize HTML5 Canvas object
  var ctx = c.getContext("2d");
  ctx.fillStyle="#cccccc";
  ctx.fillRect(0,0, c.width, c.height);  
  var ida = ctx.getImageData(0,0,pWidth, c.height);

  // Step 4: Start drawing into the canvas
  var xc = 0, yc = 0;
  var xf = pWidth;
  var xtd , xd;

  for (var i=0;i<ltsMap.map.length; i++) {
    xtd = ltsMap.map[i].c;
    while (xtd > 0) {
      if (xtd > xf) {
        xd = xf; xtd = xtd - xd; xf = pWidth;
        drawCells(ida, xc, yc, ltsMap.map[i].t, xd);
        xc = 0; yc++;
      } else {
        xd = xtd; xtd = xtd - xd; xf = xf - xd;
        drawCells(ida, xc, yc, ltsMap.map[i].t, xd);
        xc = xc + xd;
      }
    }
  }

  // Step 5: Display Tablespace Map and add Mouse event handlers
  drawCells(ida, xc, yc, 'END',1);
  ctx.putImageData(ida,0,0);
  c.addEventListener("click", function (e) {handleMouseOver(e, c, inf, ltsMap);});
  c.addEventListener("dblclick", function (e) {inf.style.display="none";});
}

// Mouse Event Handling function
function handleMouseOver(e, cv, inf, ida) {
  var x = Math.round((e.pageX - cv.offsetLeft - 1) * cv.width / $(cv).width());
  var y = Math.round(((e.pageY - cv.offsetTop) - 4) * cv.height / $(cv).height());
  var n="- None -";
  var f=false;
  var i=0, b=0;
  while (i<ida.map.length && !f) {
    b = b + ida.map[i].c;
    if (b >= (y * cv.width + x)) {n = ida.map[i].n; f = true;} 
    i++;
  }
  inf.style.top=e.pageY +"px";
  inf.style.left=e.pageX +"px";
  inf.style.display="";
  inf.innerHTML = "BLOCK #: <b>" + (y * cv.width + x) + "</b><br/>Object: <b>"+ n + "</b><br/>Type: <b>"+ida.map[i-1].t+"</b><br/>Size: <b>"+ida.map[i-1].c+" blocks</b>"; 
}
Finally, all this will be combined in an APEX application. In order to run it, the APEX workspace will need SELECT privileges on DBA_EXTENTS and DBA_TABLESPACES. The application page contains a select list (to choose a tablespace) and an empty HTML DIV region to hold the canvas object with the tablespace map. After the end user has chosen a tablespace (onChange Event Handler), the JavaScript code will generate the following result.
You can also download the ready-to-use APEX application from here. It will also run (it's HTML5) on mobile devices.
The HTML5 technology with its visualization capabilities suits very well in combination with the Oracle Database. APEX makes it very easy to integrate the necessary building blocks. HTML5 allows to do things which, in earlier times, required very complex software. Have fun trying it out.

25. Oktober 2013

JSON und die Oracle-Datenbank ...? Mehr auf der DOAG2013!

This event is about the DOAG2013, a german-language conference and therefore in german only.
Heute geht es nochmal um die in etwa 4 Wochen stattfindende DOAG-Konferenz. Seit heute steht fest, dass auf der DOAG2013 ein hochinteressanter Vortrag stattfinden wird, den ich der Entwicklercommunity hiermit wärmstens ans Herz legen möchte: JSON and the Oracle Database: Storage, Query and more ... von Mark Drake, Produktmanager für die Oracle XML DB aus den USA.
Im Vortrag, der am ersten Konferenztag (19.11.2013) um 12:00 Uhr im Raum "Hong Kong" stattfindet, geht es um neueste Entwicklungen und Pläne des Development-Teams für das native Zusammenspiel von JSON und der Oracle-Datenbank. Auf der diesjährigen Oracle Open World war SQL/JSON ebenfalls ein Thema, wie man im Blog Posting von Marco Gralike nachlesen kann. Wer also wissen möchte, was sich bei JSON und der Oracle-Datenbank tun wird ... DOAG2013.

23. Oktober 2013

Webseminare im Herbst und ... DOAG2013!

This blog posting is about upcoming events in German language and therefore in German only.
Der Herbst ist, wie jedes Jahr, die Zeit der Veranstaltungen. Hier ist eine kleine Zusammenstellung der in Kürze stattfindenden Veranstaltungen, an denen ich mitwirken darf ...
  • Das Thema HTML5 tritt bei meiner Arbeit mit APEX- und PL/SQL Kunden immer häufiger auf. Und die Möglichkeiten, die sich mit HTML5 für die Gestaltung von Webanwendungen ergeben, sind in der Tat sehr vielfältig und interessant. Daher führe ich am 8. November 2013 um 11:00 Uhr ein Webseminar zum Thema APEX und HTML5: Anwendungen der nächsten Generation durch. Im Webseminar werde ich vorstellen, welche Features des HTML5 Standards sich in APEX recht einfach und schnell nutzen lassen. Die Teilnahme ist kostenlos - mehr Informationen finden sich hier.
  • Dass die Oracle-Datenbank gerade dem Entwickler mehr bietet als Tabellen, SELECT- und DML-Operationen, ist doch den meisten bekannt. Aber den kompletten Überblick über die Möglichkeiten, Dinge direkt in der Datenbank zu erledigen, zu behalten, ist extrem schwierig. Ein wenig dabei helfen soll das Webseminar Datenbankfunktionen für PL/SQL und APEX-Entwickler: Inkl. Oracle12c Update am 4. Dezember 2013, ebenfalls um 11:00 Uhr.
  • Schon nächste Woche, am 30. Oktober 2013, stellt mein Kollege die seit kurzer Zeit verfügbare APEX-Anwendung für das Self Service Provisioning von Oracle12c Pluggable Databases vor. Es gibt einen Überblick über das neue Oracle12c Multitenant Feature und einen Einblick in die Self-Service Applikation, die übrigens auch aus dem OTN heruntergeladen werden kann.
Und wie jedes Jahr findet am 19. bis zum 21. November die DOAG2013 statt. Dort werde ich ebenfalls mit Vorträgen vertreten sein. Und ich freue mich jetzt schon auf das Wiedersehen, die Gespräche und den Austausch mit der Community.

7. Oktober 2013

Oracle12c: Identity Columns und Sequence By Default

Oracle12c Identity Columns and Sequence By Default
Heute geht es nochmals um ein neues Feature in Oracle 12.1: Die neue Datenbankversion bringt neue Möglichkeiten mit, eine Primärschlüsselspalte automatisch mit Werten zu befüllen. Bislang blieb einem da nur das manuelle Erzeugen einer Sequence und eines Triggers, wie folgt.
create table tab_trigger_sql(
  id    number 
)
/

create sequence seq_trigger_sql
/

create trigger tr_trigger_sql 
before insert on tab_trigger_sql
for each row
begin
  select seq_trigger_sql.nextval into :new.id from dual;
end;
/
sho err
Oracle11g erlaubte es dann, das select ... into im Trigger durch eine einfache PL/SQL-Zuweisung zu ersetzen - schneller war das aber, wie wir noch sehen werden, eigentlich nicht; sieht im Code aber zumindest besser aus.
create table tab_trigger_plsql(
  id    number 
)
/

create sequence seq_trigger_plsql
/

create trigger tr_trigger_plsql 
before insert on tab_trigger_plsql
for each row
begin
  :new.id := seq_trigger_plsql.nextval;
end;
/
sho err
Ab Oracle 12.1 kann man die Sequence der Tabellenspalte als Default zuweisen. Der Trigger fällt weg.
create sequence seq_default
/

create table tab_default(
  id    number default seq_default.nextval
)
/
Wie immer, gibt das Data Dictionary Auskunft über den hinterlegten Default.
SQL> select column_name, data_default from user_tab_columns where table_name='TAB_DEFAULT'

COLUMN_NAME     DATA_DEFAULT
--------------- ----------------------------------------
ID              "IDENT"."SEQ_DEFAULT"."NEXTVAL"
Noch weiter geht das Identity Column Feature. Hier wird auch die Sequence nicht mehr gebraucht - Oracle erstellt sie automatisch.
create table tab_identity(
  id number generated always as identity
)
/

create table tab_identity(
  id number generated always as identity start with 1 increment by 1 maxvalue 999999999 nocycle
)
/
Wie man sieht, wird die Definition der Sequence direkt ins CREATE TABLE Kommando übernommen - mit der bekannten Syntax werden die Details des automatisch generierten Wertes bestimmt. Identity Columns werden im Data Dictionary mit eigenen Views abgebildet.
SQL> select * from USER_TAB_IDENTITY_COLS

TABLE_NAME   COLUMN_NAME GENERATION IDENTITY_OPTIONS
------------ ----------- ---------- ------------------------------
TAB_IDENTITY ID          ALWAYS     START WITH: 1, INCREMENT BY: 1
                                    , MAX_VALUE: 999999999, MIN_VA
                                    LUE: 1, CYCLE_FLAG: N, CACHE_S
                                    IZE: 20, ORDER_FLAG: N

1 Zeile wurde ausgewählt.
Nun stellt sich natürlich die Frage, ob da nicht doch eine Sequence im Hintergrund entstanden ist .. ein Blick in USER_SEQUENCES liefert den "Anfangsverdacht" ...
 
SQL> select * from USER_SEQUENCES;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
--------------- ---------- ---------- ------------ - - ---------- ----------- --------------- - -
ISEQ$$_91957             1  999999999            1 N N         20           1                 N N

1 Zeile wurde ausgewählt.
.. und ein Blick in DATA_DEFAULT in USER_TAB_COLUMNS bestätigt diesen.
SQL> select table_name, column_name, data_default from user_tab_columns;

TABLE_NAME   COLUMN_NAME DATA_DEFAULT
------------ ----------- ----------------------------------------
TAB_IDENTITY ID          "IDENT"."ISEQ$$_91957".nextval

1 Zeile wurde ausgewählt.
Technisch ist eine Identity Column als eine Sequence By Default, welche jedoch im CREATE TABLE Kommando definiert und im Dictionary separat verwaltet wird. Das Erzeugen der Sequence-Objekte wird der Datenbank überlassen. Das bedeutet auch, dass die Datenbank die Sequences löscht, nachdem die Tabelle gelöscht wurde (probiert es aus). Von der Performance her sollten sich Sequences By Default und Identity Columns also ähnlich verhalten. Und einen solchen Performance-Test wollen wir nun machen - in alle vier Tabellen werden 10.000 Werte eingefügt. Die Zahlen hängen natürlich stark vom verwendeten System ab - aber eine Indikation bekommt man.
begin
  for i in 1..10000 loop
    insert into {table-name} values (default);
  end loop;
end;
/
Es ergeben sich folgende Zahlen ...
*** Table with Trigger and sequence assigned with SQL

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:09.36
*** Table with Trigger and sequence assigned with PL/SQL

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:10.04
*** Table with Sequence by Default feature

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:06.16
*** Table with Sequence by Identity Column

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:05.97
Im Beispiel sind die beiden Varianten mit Trigger in etwa gleich schnell. Bei wiederholten Tests ergab es sich, dass mal der Trigger mit dem SELECT ... INTO schneller war, mal war die PL/SQL Zuweisung schneller - ein klares Bild ergibt sich hier nicht. Sehr deutlich erkennbar ist jedoch der Vorteil des Sequence By Default - die Tatsache, dass kein Trigger mehr aufgerufen wird (und kein Context Switch zu PL/SQL mehr nötig ist), macht sich deutlich bemerkbar. Und wie vermutet, sind die Identity Columns in etwa ähnlich schnell ...
Alles in allem halte ich dieses neue Feature für eines, welches wohl sehr schnell adaptiert werden wird. Wer die Sequence-Objekte noch selbst kontrollieren möchte, wählt dann eher ein Sequence By Default, andere überlassen mit der Definition einer Identity Column gleich alles der Datenbank.
This blog posting is about another new Feature in Oracle 12.1: The new release allows to populate (Primary Key) columns automatically. In the past, we needed to create explicit sequence and trigger objects for this, as follows ...
create table tab_trigger_sql(
  id    number 
)
/

create sequence seq_trigger_sql
/

create trigger tr_trigger_sql 
before insert on tab_trigger_sql
for each row
begin
  select seq_trigger_sql.nextval into :new.id from dual;
end;
/
sho err
Starting with Oracle11g, it was possible to replace the select ... into statement by a simple PL/SQL assignment within the trigger. This cleans up the code, but (as we'll see) it will not run much faster.
create table tab_trigger_plsql(
  id    number 
)
/

create sequence seq_trigger_plsql
/

create trigger tr_trigger_plsql 
before insert on tab_trigger_plsql
for each row
begin
  :new.id := seq_trigger_plsql.nextval;
end;
/
sho err
The new release, Oracle 12.1, allows to assign the sequence's next value as the column default (Sequence By Default). The trigger is no longer needed.
create sequence seq_default
/

create table tab_default(
  id    number default seq_default.nextval
)
/
And as always, we can look into the data dictionary in order to get information ...
SQL> select column_name, data_default from user_tab_columns where table_name='TAB_DEFAULT'

COLUMN_NAME     DATA_DEFAULT
--------------- ----------------------------------------
ID              "IDENT"."SEQ_DEFAULT"."NEXTVAL"
The Identity Column Feature goes a step further: An explicit sequence is no longer needed - we just declare the "identity column". If needed, the sequence details can be directly added to the column definition.
create table tab_identity(
  id number generated always as identity
)
/

create table tab_identity(
  id number generated always as identity start with 1 increment by 1 maxvalue 999999999 nocycle
)
/
The syntax itself does not change: The definition, which was previously part of the CREATE SEQUENCE command, is now part of CREATE TABLE. The data dictionary contains a new view with information about identity columns.
SQL> select * from USER_TAB_IDENTITY_COLS

TABLE_NAME   COLUMN_NAME GENERATION IDENTITY_OPTIONS
------------ ----------- ---------- ------------------------------
TAB_IDENTITY ID          ALWAYS     START WITH: 1, INCREMENT BY: 1
                                    , MAX_VALUE: 999999999, MIN_VA
                                    LUE: 1, CYCLE_FLAG: N, CACHE_S
                                    IZE: 20, ORDER_FLAG: N
Of course, we now want to know, if there is kind of a "hidden" or "system" sequence object ... and after having a look into USER_SEQUENCES, there is an "initial suspicion" ...
 
SQL> select * from USER_SEQUENCES;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
--------------- ---------- ---------- ------------ - - ---------- ----------- --------------- - -
ISEQ$$_91957             1  999999999            1 N N         20           1                 N N
... which will be confirmed after looking into DATA_DEFAULT of USER_TAB_COLUMNS.
SQL> select table_name, column_name, data_default from user_tab_columns;

TABLE_NAME   COLUMN_NAME DATA_DEFAULT
------------ ----------- ----------------------------------------
TAB_IDENTITY ID          "IDENT"."ISEQ$$_91957".nextval
From a technical point of view, an Identity Column is a Sequence By Default. It allows to simplify DDL scripts by having the sequence details in the CREATE TABLE statement. Developers does not have to care about sequence objects any more: The database will maintain them automatically - which includes dropping them after the table has been dropped (try it). Regaring performance, both variants should behave similar - and finally we'll do a (very) simple performance test by inserting 10.000 values into the table. The exact numbers will depend on the system, but the indication we'll get, should apply in general.
begin
  for i in 1..10000 loop
    insert into {table-name} values (default);
  end loop;
end;
/
And here are the results:
*** Table with Trigger and sequence assigned with SQL

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.64
*** Table with Trigger and sequence assigned with PL/SQL

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.01
*** Table with Sequence by Default feature

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.14
*** Table with Sequence by Identity Column

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.91
In this example, the performance of the trigger variants is roughly equal. Some times the trigger with the PL/SQL assignment was faster, other times, the SELECT .. INTO was - there was no clear picture. But Identity Columns or columns having Sequence as Default are significantly faster - we can clearly see the cost of calling the Trigger and having the context switch from SQL to PL/SQL.
Summarized, I think that this new feature will be adopted rather quickly - DDL scripts can be much cleaner with Oracle12c. Those who still want to control the "number generator", can use Sequence By Default and create their sequence objects manually. The others might use Identity Columns, handing sequence maintenance completely over to the database.

Beliebte Postings