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.

Beliebte Postings