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.

Kommentare:

Anonym hat gesagt…

excellent application. pls remove schema name (testit.) from application process

Carsten Czarski hat gesagt…

Hi,

thank you for the heads-up. Done.

Best regards

-Carsten

Beliebte Postings