15. Dezember 2009

SecureFiles und mehr ... Veranstaltung im Februar 2010

Das letzte Blog-Posting für dieses Jahr gibt es nur auf Deutsch: Ich möchte gerne auf eine Oracle-Veranstaltung, die meine Kollegin Ulrike Schwinn und ich im Februar 2010 durchführen, hinweisen: Im Oracle Developer Day "SecureFiles und das Database Filesystem" wird das Thema unstrukturierte Daten in der Oracle-Datenbank näher betrachtet. Im Detail sind dies Informationen über die in Oracle11g neuen SecureFiles und das in 11g Release 2 neue Database Filesystem. Aber auch verwandte Themen wie die Oracle XML DB, Oracle TEXT (Volltextrecherche) oder einfache Bildbearbeitung (MultiMedia) kommen nicht zu kurz. Wer immer mit CLOB oder BLOB-Datentypen zu tun hat (und wer hat das nicht), sollte mit diesen Informationen etwas anfangen können ...
Die Teilnahme ist kostenlos - also gleich anmelden. Vielleicht sehen wir uns. Ansonsten wünsche ich schon mal frohe Weihnachten und einen Guten Rutsch nach 2010!
The last blog posting in 2009 is only available in german since it is about an event in germany in german language.
Season's greetings ... and see you next year!

8. Dezember 2009

Umlaute "normalisieren": NLSSORT

English title: normalizing "umlauts": The NLSSORT function

Bereits vor zwei Jahren (beim Bloggen vergeht die Zeit doch recht schnell) hatte ich ein Posting über Case- und Umlaut-"insensitive" Suche veröffentlicht. Darin ging es im wesentlichen um die Verwendung der Session-Parameter NLS_SORT und NLS_COMP. Damit ist linguistischen Suche möglich. Heute möchte ich (in einem kurzen Blog-Posting) zeigen, wie man mit expliziter Verwendung der Funktion NLSSORT (diese wird bei der linguistischen Suche implizit genutzt) Strings "normalisieren" kann. Ein Beispiel:
Two years ago (time passes by quickly) I had a posting about Case- and Umlaut-"insensitive" Searches. It was basically about the session-parameters NLS_SORT and NLS_COMP which allow linguistic search capabilities within SQL queries. Today I have a very short blog posting about the explicit usage of the NLSSORT function (the linguistic search uses it implicitly). With NLSSORT you can "normalize" a string value: The string is being converted to lowercase and all umlauts are converted to their base letter. An example:
select nlssort('München á la Carte - and ...: ê,á,ß,ö', 'nls_sort=''binary_ai''') NORMALIZED
from dual
/

NORMALIZED
------------------------------------------------------------------------------
6D756E6368656E2061206C61206361727465202D20616E64202E2E2E3A20652C612C73732C6F00
Die Funktion NLSSORT gibt einen RAW zurück; um zu sehen, was da drin steckt, wandeln wir es mit UTL_RAW.CAST_TO_VARCHAR2 wieder um.
Since NLSSORT returns a RAW value we cast it to a VARCHAR2:
select utl_raw.cast_to_varchar2(
  nlssort('München á la Carte - and ...: ê,á,ß,ö', 'nls_sort=''binary_ai''') 
) NORMALIZED
from dual
/

NORMALIZED
----------------------------------------------
munchen a la carte - and ...: e,a,ss,o
Ich konnte das in einem konkreten Fall schon brauchen - vielleicht auch für euch ...
This was useful for me in a specfic situation - perhaps it is also for you ...

23. November 2009

In ein Datafile "hineingucken": GET_TABLESPACE_MAP

English title: Looking into a datafile: GET_TABLESPACE_MAP

Gerade auf Entwicklermaschinen (bspw. meiner) kommt es immer wieder vor, dass durch das häufige Anlegen und Droppen von Tabellen die Datafiles sehr stark anwachsen. Wenn dann noch ein paar größere Tabellen dabei sind, wird das Datafile so groß, dass ich es nach dem Droppen der großen Tabelle gerne "shrinken" würde. Aber das bekannte ALTER DATABASE DATAFILE '...' RESIZE XM klappt dann nicht, weil sich am Ende des Datafile immer noch Objekte befinden - ich habe dann ein Datafile mit Objekten "an Anfang", "am Ende" und jeder Menge freien Platz in der Mitte. Man könnte die Tabellen am Ende nun mit einem ALTER TABLE ... MOVE verschieben und den Platz so freibekommen - dazu müsste man aber mal wissen, welche das sind.
Sometimes - after dropping and creating tables I am in the situation that I have a huge datafile for my (say: USERS) tablespace which has huge sparse areas. Unfortunately these areas are not at the end of the datafile so I cannot shrink it using ALTER DATABASE DATAFILE ... RESIZE. So I have a datafile (which I want to shrink) but which I cannot shrink because there some some objects at the end. I could move them (of course) but I need to know which objects these are.
Einfache Abfragen auf die DBA_SEGMENTS-Tabellen sind meistens ziemlich unübersichtlich, die Datendateien sind häufig mal was größer - die DBA_SEGMENTS hat demnach für ein Datafile ziemlich viele Einträge. Werkzeuge muss man immer erstmal starten und viele Tablespace Maps finde ich persönlich sehr unübersichtlich, da sie versuchen, alle Details auf einmal anzuzeigen ... Ich habe mir daher mal eine Funktion geschrieben, die mir erlaubt, zunächst einen "groben" Überblick über das File zu bekommen und darüber hinaus die Möglichkeit bietet, in die Details hineinzugehen - quasi ein "Drill Down" ins Datafile. Hier ist nun der SQL und PL/SQL-Code der Funktion GET_TABLESPACE_MAP.
Simple queries on the DBA_SEGMENTS table are most often difficult to read - when there are many objects this view has many rows ... Tools always have to be started first and many tools only provide a view at the very detail level. So I decided to code a little function which allows me to look into a datafile using a free level of detail. I can start with an overview (e.g. 10 percentiles) and then drill down to see more and more details. The final level of detail would be the individual segments. Here is the code:
CREATE OR REPLACE TYPE  "STORAGE_MAP_T" as object(
  owner          varchar2(30),
  segment_name   varchar2(30),
  start_block    number,
  size_bytes     number,
  size_blocks    number,
  alloc_bytes    number,
  alloc_blocks   number,
  segment_count  number
)
/

CREATE OR REPLACE TYPE  "STORAGE_MAP_CT" as table of storage_map_t
/


create or replace function get_tablespace_map (
  p_datafile_name  in varchar2,
  p_percentiles in number,
  p_start_block in number default null,
  p_end_block   in number default null
) return storage_map_ct authid current_user pipelined is
  v_start_block number := null;
  v_end_block   number := null;
  v_ts_blocks   number := null;
  v_ts_blocksize number := null;
  v_ts_name     varchar2(30) := null;
  v_perc_size   number := null;
  v_last_endblock number := null;
begin
  select df.blocks, ts.block_size, ts.tablespace_name
    into v_ts_blocks, v_ts_blocksize, v_ts_name
  from dba_data_files df, dba_tablespaces ts
  where df.tablespace_name = ts.tablespace_name
  and df.file_name = p_datafile_name;
 
  if p_end_block is null then
    v_end_block := v_ts_blocks;
  else
    v_end_block := p_end_block;
  end if;
  if p_start_block is null then
    v_start_block := 0;
  else
    v_start_block := p_start_block;
  end if;
 
  if p_percentiles is not null then
    v_perc_size := round((v_end_block - v_start_block) / p_percentiles);
    for c in (
      with ts as (
        select v_ts_blocksize block_size, v_ts_name tablespace_name from dual
      ), pc as (
        select level - 1 perc from dual connect by level <= p_percentiles
      ), ex as (
        select
          de.owner,
          de.segment_name,
          de.block_id                 start_block_id,
          de.block_id * ts.block_size start_bytes,
          de.block_id + de.blocks     end_block_id,
          nvl(de.blocks, 0)           blocks,
          nvl(de.bytes,0)             bytes,
          pc.perc   percentile
        from (dba_extents de join ts on (ts.tablespace_name = de.tablespace_name and  de.block_id >= v_start_block and de.block_id < v_end_block
  ))
           right outer join pc on (pc.perc = trunc((de.block_id - v_start_block) / v_perc_size))
      )
      select
        percentile,
        v_perc_size * percentile + v_start_block start_block,
        v_perc_size                                    perc_size_blocks,
        sum(bytes)                                     perc_alloc_bytes,
        sum(blocks)                                    perc_alloc_blocks,
        count(segment_name)                            segment_count
      from ex
      group by percentile
    ) loop
      pipe row (
        storage_map_t(
          null,
          c.percentile,
          c.start_block,
          c.perc_size_blocks * v_ts_blocksize,
          c.perc_size_blocks,
          c.perc_alloc_bytes,
          c.perc_alloc_blocks,
          c.segment_count
        )
      );
    end loop;
  else
    v_last_endblock := v_start_block;
    for c in (
      with ts as (
        select v_ts_blocksize block_size, v_ts_name tablespace_name from dual
      )
      select
        de.owner,
        de.segment_name,
        de.block_id                 start_block,
        de.block_id * ts.block_size start_bytes,
        de.block_id + de.blocks     end_block_id,
        nvl(de.blocks, 0)           blocks,
        nvl(de.bytes,0)             bytes
      from dba_extents de join ts on (ts.tablespace_name = de.tablespace_name)
      where de.block_id >= v_start_block and de.block_id < v_end_block
      order by 3
    ) loop
      if v_last_endblock < c.start_block then
        pipe row (
          storage_map_t(
            null,
            '- FREE -',
            v_last_endblock,
            (c.start_block - v_last_endblock) * v_ts_blocksize,
            (c.start_block - v_last_endblock),
            0,
            0,
            0
          )
        );
      end if;
      pipe row (
        storage_map_t(
          c.owner,
          c.segment_name,
          c.start_block,
          c.bytes,
          c.blocks,
          c.bytes,
          c.blocks,
          1
        )
      );
      v_last_endblock := c.start_block + c.blocks;
    end loop;
    if v_last_endblock < v_end_block then
        pipe row (
          storage_map_t(
            null,
            '- FREE -',
            v_last_endblock,
            (v_end_block - v_last_endblock) * v_ts_blocksize,
            (v_end_block - v_last_endblock),
            0,
            0,
            0
          )
        );
     end if;
  end if;
  return;
end;
Die Funktion sieht danm wie folgt aus ...
The function looks as follows ...
FUNCTION get_tablespace_map RETURNS STORAGE_MAP_CT
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_DATAFILE_NAME                VARCHAR2                IN
 P_PERCENTILES                  NUMBER                  IN
 P_START_BLOCK                  NUMBER                  IN     DEFAULT
 P_END_BLOCK                    NUMBER                  IN     DEFAULT
Dabei bedeuten ...
  • P_DATAFILE_NAME: Der Name des Datafiles, logisch
  • P_PERCENTILES: Die Anzahl "Perzentile", die angezeigt werden sollen. Die Funktion teilt den untersuchten Bereich (das ganze Datafile oder den durch die nächsten zwei Parameter festgelegten Bereich) in die hier angegebene Anzahl Perzentile auf und ermittelt für jeden Bereich den Füllgrad in Anzahl Segmente, Bytes und Blocks.
  • P_START_BLOCK und P_END_BLOCK schließlich geben an, welcher Bereich untersucht werden soll. Gibt man hier nichts an, so wird das ganze Datafile untersucht.
The parameters explained
  • P_DATAFILE_NAME: The datafiles' name
  • P_PERCENTILES: The amount of percentiles into which the examined area (full datafile or area between the next two parameters) should be divided into.
  • P_START_BLOCK und P_END_BLOCK (if given) determine the area of the datafile to be examined.
Das kann man nun wie folgt testen ...
Testing as follows ...
select
  segment_name,
  start_block,
  size_bytes / 1048576 size_mb,
  size_blocks,
  alloc_bytes / 1048576 alloc_mb,
  alloc_blocks,
  segment_count
from table(get_tablespace_map('/oracle/u02/orcl/users01.dbf', 10))
order by 1

SEGMENT_NAME                   START_BLOCK       SIZE_MB SIZE_BLOCKS      ALLOC_MB ALLOC_BLOCKS SEGMENT_COUNT
------------------------------ ----------- ------------- ----------- ------------- ------------ -------------
0                                        0      3276,797      419430      3307,000       423296       1645
1                                   419430      3276,797      419430      3250,000       416000        761
2                                   838860      3276,797      419430      3324,000       425472         69
3                                  1258290      3276,797      419430      3240,000       414720         53
4                                  1677720      3276,797      419430      3263,000       417664         53
5                                  2097150      3276,797      419430      3284,000       420352       1278
6                                  2516580      3276,797      419430      3159,063       404360       2630
7                                  2936010      3276,797      419430         0,000            0          0
8                                  3355440      3276,797      419430         0,000            0          0
9                                  3774870      3276,797      419430         0,000            0          0
Man sieht an diesem Beispiel sehr schön, dass eigentlich "gar nichts zu tun ist". Man könnte einfach hingehen und mit ALTER DATABASE DATAFILE ... RESIZE ... das File verkleinern. Der "Start"-Block eines Segments entscheidet über die Zuordnung; wenn ein Objekt also eine Perzentilgrenze überschreitet, wird es dem vorgehenden zugeordnet. Die Spalten ALLOC_MB bzw. ALLOC_BLOCKS können also durchaus größere Werte anzeigen als SIZE_MB bzw. SIZE_BLOCKS. Möchte man nun ein Drill Down bspw. in das 6. Perzentil machen, so gibt man einfach den Start- und End-Block als dritten und vierten Parameter an.
This example shows the case in which there's nothing to do - since the free area is at the end of the datafile. In this case an ALTER DATABASE DATAFILE ... RESIZE ... would do the job. There are some rows where the ALLOC-Columns are greater then the SIZE-Columns. This is because a segemnts' first block determines the percentile to which this object is being added to. If a large objects starts at the very last block of a percentile it's being added to this one even if most of the data is contained in the next percentile. To do a Drill Down we use the third and fourth parameter of the function: To get more detail for the 6th percentile we recall the function as follows.
select
  segment_name,
  start_block,
  size_bytes / 1048576 size_mb,
  size_blocks,
  alloc_bytes / 1048576 alloc_mb,
  alloc_blocks,
  segment_count
from table(get_tablespace_map('/oracle/u02/orcl/users01.dbf', 10, 25165580, 2936010))
order by 1;

SEGMENT_NAME                   START_BLOCK        SIZE_MB SIZE_BLOCKS       ALLOC_MB ALLOC_BLOCKS SEGMENT_COUNT
------------------------------ ----------- -------------- ----------- -------------- ------------ -------------
0                                  2516580        327,680       41943        342,813        43880           158
1                                  2558523        327,680       41943        304,000        38912            97
2                                  2600466        327,680       41943        326,250        41760           620
3                                  2642409        327,680       41943        340,000        43520            40
4                                  2684352        327,680       41943        318,000        40704            31
5                                  2726295        327,680       41943        328,000        41984            20
6                                  2768238        327,680       41943        325,000        41600           781
7                                  2810181        327,680       41943        328,125        42000           365
8                                  2852124        327,680       41943        317,875        40688           421
9                                  2894067        327,680       41943        229,000        29312            97
Übergibt man SQL NULL für die Anzahl der Perzentile, so gibt die Funktion die Details zurück ...
Specifying SQL NULL as the amount of percentiles leads the function to return the very detail information: the individual segments.
select
  segment_name,
  start_block,
  size_bytes / 1048576 size_mb,
  size_blocks,
  alloc_bytes / 1048576 alloc_mb,
  alloc_blocks,
  segment_count
from table(get_tablespace_map('/oracle/u02/orcl/users01.dbf', null,  2700000, 2730000 ))
order by 1

SEGMENT_NAME                   START_BLOCK        SIZE_MB SIZE_BLOCKS       ALLOC_MB ALLOC_BLOCKS SEGMENT_COUNT
------------------------------ ----------- -------------- ----------- -------------- ------------ -------------
- FREE -                           2700000         56,250        7200          0,000            0             0
SPATIALTAB01                       2724608          8,000        1024          8,000         1024             1
SPATIALTAB01                       2728704          8,000        1024          8,000         1024             1
SPATIALTAB01                       2723584          8,000        1024          8,000         1024             1
SPATIALTAB01                       2729728          8,000        1024          8,000         1024             1
SPATIALTAB01                       2725632          8,000        1024          8,000         1024             1
SPATIALTAB01                       2726656          8,000        1024          8,000         1024             1
SPATIALTAB01                       2727680          8,000        1024          8,000         1024             1
MDRT_1D2E3A$                       2715392         64,000        8192         64,000         8192             1
MDRT_1D2E3A$                       2707200         64,000        8192         64,000         8192             1
In Verbindung mit APEX wäre hier eine sehr einfache Navigation - mit Drill Up und Drill Down denkbar. Eine Erweiterung könnte dann so aussehen, dass auf bestimmte Segmente per Klick einfach ein ALTER TABLE MOVE anwendet. Ich selbst konnte das schon einige Male brauchen; vielleicht ist die Funktion ja für den ein oder anderen noch hilfreich.
In combination with APEX this allows a very simple web application. Using kind of Drill Down and Drill Up one could easily navigate through a datafile and gain information about allocated and free areas. This function was sometimes useful to me - perhaps it is also for you ...

2. November 2009

DATE, TIMESTAMP und Zeitzonen in SQL und PL/SQL: Wissenswertes

English title: DATE, TIMESTAMP and time zones in SQL and PLSQL: Things to know - contd ...

Heute setze ich das vor einigen Wochen begonnene Posting zum Thema DATE und TIMESTAMP fort. Wie darin bereits erläutert, unterscheidet sich TIMESTAMP durch zwei Dinge von DATE. Erstens kann TIMESTAMP mit Sekundenbruchteilen umgehen, zweitens werden Zeitzonen unterstützt. Ein Blick in die Dokumentation zeigt nun, dass es TIMESTAMP in drei Varianten gibt:
Today I'd like to continue the blog posting about DATE and TIMESTAMP which I published a few weeks ago. As stated in this posting there are basically two differences between DATE and TIMESTAMP. Firstly, TIMESTAMP supports fractional seconds, secondly TIMESTAMP can handle time zones. The documentation states that there are three variants of TIMESTAMP:
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE
Der einfache TIMESTAMP arbeitet ohne Zeitzone (hier besteht der Unterschied zu DATE nur in der Unterstützung von Sekundenbruchteilen). Die anderen beiden Varianten können mit Zeitzonen umgehen. TIMESTAMP WITH TIME ZONE speichert dabei eine explizite Information zur Zeitzone (MEZ, PST, etc.) wogegen TIMESTAMP WITH LOCAL TIME ZONE zur Konvertierung in die "lokale" Zeitzone (die Zeitzone der Datenbanksession) vorgesehen ist (dazu unten mehr). Im letzten Blog-Posting habe ich TIMESTAMP anhand der SYSTIMESTAMP-Funktion vorgestellt. Wenn Ihr wissen möchtet, welche dieser drei Varianten SYSTIMESTAMP nun zurückliefert, könnt Ihr das einfach selbst herausfinden ...
The "simple" TIMESTAMP works without time zones - the only difference between this and a DATE value is the support of fractional seconds. TIMESTAMP WITH TIME ZONE stores the time zone information explicitly whereas TIMESTAMP WITH LOCAL TIME ZONE is suited for datetime conversions to the "local" time zone (the time zone of the database session). In the first blog posting I used the SYSTIMESTAMP function in order to present the TIMESTAMP data type. If you now like to know which of the three TIMESTAMP variants SYSTIMESTAMP returns, just create a view as follows ...
create view systimestamp_type as select systimestamp datum from dual
/

View created.

desc systimestamp_type

 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------

 DATUM                                              TIMESTAMP(6) WITH TIME ZONE
Man sieht, dass SYSTIMESTAMP vom Typ TIMESTAMP WITH TIME ZONE ist. Bei TIMESTAMP könnt Ihr (wie man sieht) auch eine Präzision für die Sekundenbruchteile angeben - der Default ist 6. Die kleinste Einheit in diesem Fall wäre also 0,000001 Sekunden. Diese Präzision kann von Null (0) bis Neun (9) reichen. Wenn Ihr also eine Tabelle mit TIMESTAMP(0) anlegt, habt Ihr effektiv ein DATE.
As you can see: SYSTIMESTAMP returns TIMESTAMP WITH TIME ZONE. And there is another "feature" of TIMESTAMP: You can specify a precision value for the fractional seconds. The default for this is 6 - the smallest unit is then 0.000001 seconds. The range for this goes from zero (0) to nine (9). So if you create a table with a TIMESTAMP(0) column, you have actually a DATE.
TIMESTAMP WITH TIME ZONE bringt nun einige Besonderheiten beim Erzeugen mit TO_TIMESTAMP und umwandeln in VARCHAR2 mit TO_CHAR mit. Zunächst wollen wir das Datum 02.11.2009 14:00 als TIMESTAMP erzeugen, aber nicht in unserer Zeitzone (MEZ), sondern als "kalifornische Zeit". Dazu kann man entweder TO_TIMESTAMP_TZ oder FROM_TZ nutzen.
Time zones must also be taken into account when constructing TIMESTAMP WITH TIME ZONE value or when converting it to VARCHAR2 using TO_CHAR. Let's create a TIMESTAMP value for November, 2nd 2009, 2:00pm - but Pacific Time not CET. You can either use TO_TIMESTAMP_TZ or FROM_TZ for this.
select to_timestamp_tz('2009-11-02 14:00:00 PST', 'YYYY-MM-DD HH24:MI:SS TZR') from dual;

TO_TIMESTAMP_TZ('2009-11-0214:00:00PST','YYYY-MM-DDHH24:MI:SSTZR')
---------------------------------------------------------------------------
02.11.09 14:00:00,000000000 PST

select from_tz(to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PST') from dual;

FROM_TZ(TO_TIMESTAMP('2009-11-0214:00:00','YYYY-MM-DDHH24:MI:SS'),'PST')
---------------------------------------------------------------------------
02.11.09 14:00:00,000000000 PST
In beiden Fällen kommt ein TIMESTAMP WITH TIME ZONE heraus; die Information "PST" steckt da also explizit drin - und das können wir nun nutzen, um den Wert in unsere Zeitzone umzurechnen. Dazu bietet TO_CHAR zusätzliche Schlüsselwörter an:
Both functions return TIMESTAMP WITH TIME ZONE; the information that this is "pacific time" is now explicity stored within the TIMESTAMP value. So we now can convert this value to other time zones. The TO_CHAR function has addional keywords for that ...
select 
 to_char(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) at time zone 'CET','DD.MM.YYYY HH24:MI:SS'
 ) as "ZEIT_IN_MEZ" from dual;

ZEIT_IN_MEZ
-------------------
02.11.2009 23:00:00
"Offsets" gehen natürlich auch ...
You can -of course- also work with timezone offsets ...
select 
 to_char(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) at time zone '+01:00','DD.MM.YYYY HH24:MI:SS'
 ) as "ZEIT_IN_MEZ" from dual;

ZEIT_IN_MEZ
-------------------
02.11.2009 23:00:00
Wenn Ihr in die lokale Zeitzone der Session umrechnen wollt, könnt Ihr den Datentypen TIMESTAMP WITH LOCAL TIME ZONE verwenden; das ist nützlich, wenn mehrere Nutzer in unterschiedlichen Zeitzonen mit dem System arbeiten.
If you like to convert to the "local" time zone (the time zone of the database session), you might use TIMESTAMP WITH LOCAL TIME ZONE. This es particularly useful if your application has to deal with different client time zones.
SQL> alter session set time_zone='CET';

Session altered.

select 
 cast(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) as TIMESTAMP with local time zone
 ) as "LOCAL_TIME" 
from dual;

LOCAL_TIME
---------------------------------------------------------------------------
02.11.09 23:00:00,000000

Session altered.

SQL> alter session set time_zone='EST';

select 
 cast(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) as TIMESTAMP with local time zone
 ) as "LOCAL_TIME" 
from dual;

LOCAL_TIME
---------------------------------------------------------------------------
02.11.09 17:00:00,000000
Wenn Ihr nun wissen möchtet, was "PST" nun konkret bedeutet, kann die Funktion TZ_OFFSET weiterhelfen. Sie gibt wieder, wieviele Stunden eine gegebene Zeitzone von UTC (GMT) abweicht.
If you like to know what "PST" means (how many hours), you might use the TZ_OFFSET function. It returns the offset of a given time zone to UTC.
select tz_offset ('PST') from dual;

TZ_OFFS
-------
-07:00
Man kann also sagen, dass man mit TIMESTAMP (und hier: TIMESTAMP WITH TIME ZONE) arbeiten sollte, wenn es an das Speichern von Zeiten geht. Denn die Wahrscheinlichkeit, dass man verschiedene Zeitzonen unterstützen muss, dürfte (für jedwede Anwendung) in Zukunft eher steigen. Arbeitet man dagegen mit DATE, sollte man zumindest alle Zeiten auf eine Zeitzone normalisieren (am besten UTC, also die "Greenwich-Zeit") und dann bei Bedarf umrechnen.
In summary one should work with the TIMESTAMP (and more accurate: TIMESTAMP WITH TIME ZONE) data type when it's about storing datetime values. In the future it will be more and more likely that an application must support multiple time zones - so you should be better off when anticipating this from the very beginning. If you are working with DATE you should at least "normalize" all values to a specific time zones (say: "UTC") and convert them as needed.

22. Oktober 2009

Neu in Oracle 11.2 und praktisch: Der IGNORE_ROW_ON_DUPKEY_INDEX Hint

English title: Oracle 11.2: The IGNORE_ROW_ON_DUPKEY_INDEX Hint

Heute will ich mich mal dem Thema Oracle11g Release 2 widmen. Im Rahmen der deutschen APEX-Community gab es ja bereits Anfang September einen ersten Einblick in die neuen Features. Dieses Blog-Posting widmet sich einem Feature, das nicht auf der Liste ist: Den neuen Optimizer-Hints zum Handling von Duplicate Key Violations bei SQL INSERT-Kommandos. Die Ausgangssituation:
Today I'll start writing about the new database release 11.2. For german readers the APEX community website contains some information about new features since 11.2 was released in september. This blog posting is about a feature which is not on that list: There are new optimizer hints which can help handling duplicate key violations on SQL insert commands. An Example:
create table test_tab (
  id   number(10) primary key,
  name varchar2(20)
)
/

insert into test_tab values (1, 'Oracle')
/

commit
/
Möchte man nun nochmals eine Zeile mit der ID 1 einfügen, so gibt es eine Fehlermeldung - das ist bekannt. Wenn man nun mehrere Zeilen auf einmal einfügen möchte (und man nicht weiss, ob diese teilweise Unique Key Violations auslösen würden), so bieten sich bislang zwei Varianten an:
  • Man baut eine PL/SQL-Schleife, geht darin durch die einzufügenden Zeilen durch (SELECT-Loop), probiert mit jeder einzelnen den INSERT und fängt etwaige Exceptions ab. Das muss allerdings codiert werden, ist langsam und fehleranfällig
  • Man nutzt das MERGE-Kommando
If you now try to insert another row with the ID value of 1 you get an error message - this is clear. If you now want to insert multiple rows into that table (and you don't know whether there will be unique key violations) there are two approaches:
  • Use PL/SQL to build a loop on the SELECTed rows, try an INSERT for each row and catch the exception. This has to be coded, is errornous and slow.
  • A better approach is the MERGE command
In Oracle 11.2 gibt es eine dritte Variante per Optimizer-Hint (IGNORE_ROW_ON_DUPKEY_INDEX): Und das geht so:
Oracle 11.2 now has a third variant: The optimizer hint IGNORE_ROW_ON_DUPKEY_INDEX.
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(test_tab(id))*/ into test_tab values (1, 'MySQL')
/

0 rows inserted.
Wie man sieht, wird zwar nichts eingefügt, es wird aber auch keine Fehlermeldung ausgelöst. Der Hint funktioniert nur bei Single Table-INSERT-Kommandos. Laut Dokumentation ist dies trotz Hint-Syntax ein sog. Mandate, wird vom Optimizer also auf jeden Fall umgesetzt. Normalerweise ist ein Hint - wie der Name schon sagt - ein Hinweis an den Optimizer, den dieser auch ignorieren darf. Ich finde das eine ganz nette Variante, da es mir die PL/SQL-Loops erspart und doch ein wenig einfacher als MERGE ist. Mehr Infos zu 11.2 in den nächsten Blog-Postings - Stay tuned ...
As you can see: There is no row being inserted but also no error message - the statement succeeds. This hint does only work for Single Table INSERT statements. The documentation states that this hint is indeed a mandate - so for the optimizer it is a mandatory command. Unlike a classic hint it cannot choose between following or not following it. IMHO this is a nice and easy alternative to a MERGE command - PL/SQL loops are still not necessary and it's much easier to use. I'll do more blog postings about 11.2 features in the near future - Stay tuned ...

12. Oktober 2009

LIKE-Abfragen und Indizes: Geht das ..:?

English title: LIKE queries and indexes ...

Dass man mit dem LIKE-Operator eine Wildcard-Suche durchführen kann, ist hinreichend bekannt. Interessant wird es, wenn man sich fragt, wie das denn mit der Indizierung aussieht. Dazu ein Beispiel anhand der Tabelle EMP:
Everyone knows that the LIKE operator allows wildcard searches within SQL. The interesting thing is when it's about indexes. We'll start with an example based on the EMP table.
SQL> create index idx_ename on emp (ename);

Index created.

SQL> select * from emp where ename like 'K%';

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7839 KING       PRESIDENT       17.11.81  5000           10
Hierfür kann ein Index genutzt werden, wie der Ausführungsplan zeigt
This query can be executed using an index - as the execution plan indicates.
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ENAME |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME" LIKE 'K%')
       filter("ENAME" LIKE 'K%')
Das ist klar, denn die Wildcard steht am Ende. Da der Anfang des Suchstrings feststeht, kann der Index genutzt werden. Probieren wir es mal andersherum ...
That's abvious since the beginning of the search string is a literal - the wildcard is at the end. So the optimizer chooses an index range scan to speed up query execution. The next example is the other way around: The wildcard is now at the beginning of the search string.
SQL> select * from emp where ename like '%G';

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7839 KING       PRESIDENT       17.11.81  5000           10

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor());

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME" LIKE '%G')
Naja und auch das ist klar; da der Anfang des Suchstrings nicht bekannt ist, kann die Datenbank den Index gar nicht nutzen. Ein Full-Table-Scan ist das einzige, was bleibt ...
And that's also obvious: When the search string starts with a wildcard the optimizer just cannot use the index - where should it enter the index tree ...? So a full table scan is the only option.
Wirklich das einzige? Es gäbe noch die Moglichkeit, mit einem function-based Index zu arbeiten. Dazu brauchen wir zunächst eine Funktion, die uns den String "umdreht".
Really? Function Based indexes might be another approach. For this we firstly need a function which "reverses" a string.
create or replace function string_reverse (
  p_string in varchar2
) return varchar2 deterministic is
  v_revstring varchar2(32767);
begin
  for i in reverse 1..length(p_string) loop
    v_revstring := v_revstring || substr(p_string, i, 1);
  end loop;
  return v_revstring;
end;
/
Wichtig ist das Schlüsselwort deterministic. Damit deklarieren wir explizit, dass diese Funktion bei gleichen Eingabeparametern das gleiche Ergebnis zurückliefert. Das ist ein wichtig für den Optimizer. Als nächstes kommt nun ein funktionsbasierter Index auf die Spalte ENAME im Zusammenspiel mit dieser Funktion.
The keyword deterministic is important here. This declared explicitly that the function returns the equal values for equal input parameters. This is important for the optimizer. The next step is to create the function based index on the ENAME column using this new function.
SQL> create index idx_ename_rev on emp (string_reverse(ename));

Index created.
Nun probieren wir das aus. Die obige Abfrage kann den Index nicht nutzen, man kann sie aber nun sinngemäß wie folgt umschreiben ... demnach ist ...
Now it's about testing this setup. The above query still cannot use the index but we could now rewrite the query - so the above query ...
SQL> select * from emp where ename like '%G';
... das gleiche wie ...
... is the same as this one ...
SQL> select * from emp where string_reverse(ename) like string_reverse('%G');
Letzteres kann allerdings nun einen Index nutzen, wie der Ausführungsplan zeigt:
And the latter one can use the new function based index - as the execution plan shows.
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |  2089 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |  2089 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ENAME_REV |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SCOTT"."STRING_REVERSE"("ENAME") LIKE "STRING_REVERSE"('%G'))
       filter("SCOTT"."STRING_REVERSE"("ENAME") LIKE "STRING_REVERSE"('%G'))
Man sieht also, dass man auch LIKE-Abfragen mit Wildcards am Anfang recht einfach beschleunigen kann. Was allerdings nicht mehr geht, wäre eine Wildcard am Anfang und am Ende (LIKE '%IN%'). Wenn Ihr sowas in großen Datenmengen braucht, wäre eher ein Volltextindex angedacht - dazu findet Ihr im Blog oracle-text-de.blogspot.com mehr Informationen.
So LIKE queries can also use an index - with function based indexes. The next step would then be a LIKE query having wildcards at the beginning as well as at the end (e.g. LIKE '%IN%'. But this case cannot be solved with this technology: If you need index support for such queries on larger data sets you might consider Oracle TEXT - the blog oracle-text-de.blogspot.com (in german) contains more information about this technology.

1. Oktober 2009

Oracle 11g Release 2 kennenlernen: Launch-Event in München, Stuttgart, Köln und Hamburg

Oracle11g Release 2 ist bekanntlich seit einem Monat für Linux-Plattformen verfügbar. Wenn Ihr genauer wissen möchtet, was das Release mit sich bringt und im Oktober einen Tag Zeit habt, dann ist Oracle11g Release 2 Launch-Veranstaltung (mehr Information) vielleicht genau das Richtige: Die Teilnahme ist kostenlos! Wenn Ihr also Zeit habt, meldet euch gleich an. Vielleicht sieht man sich ja dort: In München, Stuttgart und Köln stelle ich die neuen Features für Entwickler vor ...

21. September 2009

DATE und TIMESTAMP in SQL und PL/SQL: Wissenswertes ...

English title: DATE and TIMESTAMP in SQL and PLSQL: Things to know ...

Heute möchte ich einige Fragen und Antworten zum Thema "Umgang mit Datumswerten" zusammenfassen. Man hat ja immer wieder damit zu tun. Zunächst stellt sich ja recht häufig die Frage, ob man DATE oder TIMESTAMP verwenden sollte. TIMESTAMP ist ja der "jüngere" und mächtigere Datentyp. Andererseits kommt es immer noch häufig vor, dass Werkzeuge nicht richtig mit TIMESTAMP umgehen können. Aus diesem Grund wird DATE immer noch recht häufig (selbst auf 10g oder 11g-Datenbanken) genutzt. Das ist jedoch erstmal kein Problem - solange man keine speziellen "Features" des TIMESTAMP benötigt, sind beide Typen gleichwertig. Zunächst sollte man also diese Featues des TIMESTAMP herausarbeiten.
  • Die kleinste Einheit eines DATE ist die Sekunde, ein TIMESTAMP kommt auf die Millisekunde herunter.
  • TIMESTAMP kann mit Zeitzonen umgehen - es gibt den Datentypen TIMESTAMP WITH TIME ZONE und SQL-Funktionen wie FROM_TZ, die recht hilfreich beim Konvertieren zwischen Zeitzonen sind.
Today I'd like to post brief summary about the handling of datetime values within the Oracle database. This is a topic which is relevant for every developer. The first question is whether to use DATE or TIMESTAMP. TIMESTAMP is the more recent datatype for datetime values with more functionality. But there are many tools or programs which cannot handle TIMESTAMP values properly - therefore DATE is still more often being used (even on 10g or 11g systems). But this is not really a problem - DATE might be as suitable as TIMESTAMP. First we should work out the differences between DATE and TIMESTAMP, or expressed otherwise: What are the "features" of the TIMESTAMP datatype?
  • The smallest unit of a DATE value is a second. TIMESTAMP instead handles Milliseconds.
  • TIMESTAMP can handle time zones. There is timezone-aware variant: TIMESTAMP WITH TIME ZONE. And there are SQL functions like FROM_TZ which are useful for converting datetime values between timezones.
Weder DATE noch TIMESTAMP speichern das Datum intern als Zahl (wie Unix es macht). Das kann man mit der DUMP-Funktion auch einfach selbst nachsehen (Man sieht aber bereits an den Bytes, dass TIMESTAMP intern mit UTC arbeitet):
Neither DATE nor TIMESTAMP store their value internally as a NUMBER. This can be easily observed as follows:
select sysdate, dump(sysdate) bytes from dual
/

SYSDATE             BYTES
------------------- ----------------------------------------
21.09.2009 12:13:50 Typ=13 Len=8: 217,7,9,21,12,13,50,0

select systimestamp, dump(systimestamp) bytes from dual
/

SYSTIMESTAMP                        BYTES
----------------------------------- ------------------------------------------------------------------------------
21.09.2009 12:15:21,413578 +02:00   Typ=188 Len=20: 217,7,9,21,10,15,21,0,16,179,166,24,2,0,5,0,0,0,0,0
"217,7" wird hexadezimal als "D9","07" ausgedrückt. Als 16bit-Zahl wäre das "07D9" und das wiederum ist in dezimaler Schreibweise 2009. Man sieht also, dass Jahre, Monate, Tage, Stunden, Minuten und Sekunden explizit im DATE bzw. TIMESTAMP-Wert drinstecken.
The hexadecimal notation for "217,7" is "D9","07". This as a 16bit number would be "07D9" and this converted back to decimal is "2009". We see that TIMESTAMP and DATE store the individual information for years, months, days, hours, minutes and seconds.
DATE und TIMESTAMP erlauben Datumsarithmetik. Und beide erlauben das Arbeiten mit numerischen Werten und mit Intervall-Datentypen. Die meisten von euch arbeiten sicherlich mit numerischen Werten: Dabei ist die "Einheit", mit der man arbeitet, 1 Tag. Ein "DATE" ist also "ein Tag" (für TIMESTAMP gilt das gleiche). Eine Stunde ist demnach 1/24 DATE, eine Minute ist 1/1440 DATE und so weiter. Möchte man also wissen, welche Zeitspanne zwischen zwei DATE-Werten liegt, so zieht man sie zunächst mal voneinander ab ...
DATE allows datetime arithmetic as well as TIMESTAMP. One can work with numeric values as well as with interval types. The latter ones are (as we will see) more recommended. The "Unit" of DATE and TIMESTAMP is a "day", so "one DATE" means "one day". One hour is therefore 1/24 "DATE" and a minute is 1/1440 "DATE". If you like to know the datetime interval between two DATE values you just subtract one from the other.
select 
  to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
  to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
-----------------
     62,302083333
Bei DATE-Werten wird das Ergebnis in einen numerischen Wert konvertiert. Die Einheit ist "DATE", also Tage. Es kommen also 62 und ein knapper "Drittel-Tag" heraus. Verwendet man TIMESTAMP, so bekommt man sofort einen Intervall-Datentypen (hier: Day-To-Second) ...
When using the DATE datatype, the result is being converted into a NUMBER value; the UNIT is (as seen above) "days". So this result means: a bit more then 62 days. When using the TIMESTAMP datatype, the result is being expressed as an interval data type (here: Day-To-Second).
select 
  to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
  to_timestamp('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
------------------------------------------------
+000000062 07:15:00.000000000
Den numerischen Wert kann man allerdings auch in einen Intervall-Datentypen konvertieren ...
You can explicitly convert the NUMBER value into an interval datatype ...
select 
  numtodsinterval(
    to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
    to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') 
   ,'DAY'
  ) DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
-----------------------------------------------
+000000062 07:14:59.999999999
... allerdings zeigt sich hier ein kleines Rundungsproblem. Besser wäre es also, sich das Ergebnis der Datumsarithmetik auch bei Verwendung von DATE direkt als Intervallwert zurückgeben zu lassen. So vermeidet man den Umweg über NUMBER:
... but there is a rounding problem now. It would be much better to omit this roundtrip to the NUMBER value. The following syntax retrieves the result of the datetime arithmetic directly as an interval datatype - The DATE subtraction now returns the same result as the TIMESTAMP subtraction.
select 
 (
   to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
   to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
 ) day(9) to second DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
------------------------------------------
+000000062 07:15:00.000000000
Man kommt also sowohl mit DATE als auch mit TIMESTAMP zu einem in SQL*Plus gut lesbaren Ergebnis. Aber man arbeitet ja nicht immer in SQL*Plus und manchmal benötigt man eben strukturierten Zugriff auf die einzelnen Elemente: Mit der EXTRACT-Funktion kommt man an die einzelnen Elemente des Intervalls heran:
So in SQL*Plus we can retrieve the results in a readable format - for DATE as well as for TIMESTAMP values. But application developers often need structured access to the information in order to retrieve the individual information elements. The EXTRACT function allows this:
select 
  extract(
    HOUR from
    (
      to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
      to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
    ) day(9) to second
  ) INTERVAL_HOURS
from dual
/

INTERVAL_HOURS
--------------
             7
Minuten gehen dann analog ...
A similiar call extracts the Minutes ...
select 
  extract(
    MINUTE from
    (
      to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
      to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
    ) day(9) to second
  ) INTERVAL_MINUTES
from dual
/

INTERVAL_MINUTES
----------------
              15
Geht es ums Addieren oder Subtrahieren von einem vorhandenen Zeitstempel, so bieten sich auch wieder die numerischen Datentypen als auch Intervall-Datentypen an. Wir möchten 12 Stunden zu einem Datum addieren ... Erstmal numerisch ...
When it's about adding or subtracting from a datetime value, there is -again- the choice between using numeric (fractional) or interval values. So if we'd like to add 12 hours to a datetime value we could use the fractional method ...
select 
  to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + (12/24) NEW_TIME
from dual
/

NEW_TIME
-------------------
22.09.2009 02:45:00

select 
  to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + (12/24) NEW_TIME
from dual
/

NEW_TIME
-------------------
22.09.2009 02:45:00
... welche sowohl mit DATE als auch mit TIMESTAMP funktioniert. Aber Achtung: In letzterem Fall hat die Datenbank implizit in ein DATE konvertiert. Und wenn man mit der DUMP-Funktion nachsieht, stellt man fest, dass man auch einen DATE zurückbekommt ... Außerdem kann man sich nun schon denken, dass es hier Rundungsdifferenzen geben kann, besonders wenn man anfängt, einzelne Minuten oder Sekunden zu addieren oder abzuziehen. Besser sind also die Intervall-Datentypen:
... which works with DATE as well as with TIMESTAMP. But in the latter case there was an implicit conversion from TIMESTAMP to DATE (check with the DUMP function). And when it's about adding minutes or seconds it's obvious that we'll run into rounding issues again. So adding interval values is the better choice.
select 
  to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + interval '12' hour NEW_TIME
from dual
/

NEW_TIME
-----------------------------------
22.09.09 02:45:00,000000000
Das geht übrigens auch in PL/SQL - allerdings kann man, möchte man die Anzahl Stunden dynamisch gestalten, dieses SELECT nicht genauso in einem PL/SQL-Block verwenden. Man muss hier ein wenig anders arbeiten:
This also works within PL/SQL - but if you like to have the amount of hours in a variable you cannot use the above function call. In PL/SQL, the code must be a bit different:
declare
  v_zeit timestamp; 
  v_hrs  interval day(9) to second;
begin
  -- 0 days, 12 hours, 0 minutes, 0 seconds
  v_hrs := '0 12:00:00'; 
  v_zeit := to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + v_hrs;
  dbms_output.put_line(v_zeit);
end;
/
Intervalle können auch einfach aus Zahlenwerten heraus berechnet werden. Möchte man bspw. wissen, was 2,65 Tage genau sind, dann lässt sich das wie folgt ermitteln:
You can also determine interval values from numeric (fractional) values. If you like to know what 2.65 days exactly mean, try the following:
select
 numtodsinterval(2.65, 'DAY') interval
from dual
/

INTERVAL
----------------------------------------------------
+000000002 15:36:00.000000000
Die Funktion lässt sich noch anders nutzen: Angenommen, es sind keine 2,65 Tage, sondern 2,65 Stunden gemeint. Auch das lässt sich interpretieren ...
Lets assume that these are not 2.65 days but 2.65 hours. What does that mean ...?
select
 numtodsinterval(2.65, 'HOUR') interval
from dual
/

INTERVAL
----------------------------------------------------
+000000000 02:39:00.000000000
Passend dazu gibt es neben dem Day-To-Second-Intervall auch das Year-To-Month-Intervall. Angenommen, wie haben 4,55 Jahre: Was bedeutet das eigentlich ...?
Day-To-Second is not the only interval datatype: There is also Year-To-Month. So we have 4.55 Years: What does that mean ...?
select
 numtoyminterval(4.55, 'YEAR')     intervall
from dual
/

INTERVALL
----------------------------------------------------
+000000004-06
Also 4 Jahre und 6 Monate. Nun wenden wir das auf ein Beispiel mit zwei Datumswerten an:
4 Years, 6 Months. We use this example again to interpret the interval between to DATE values.
select 
 (
   to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
   to_date('2007-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
 ) year(9) to month DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
-------------------
+000000002-02
Nun wäre es ja schön, wenn man das kombinieren könnte. Wir möchten also wissen, wieviele Jahre, Monate, Tage, Stunden, Minuten und Sekunden zwischen zwei Datumswerten vergangen sind. Wie haben den Intervalltypen Year-To-Month für Jahre und Monate und Day-To-Second für Tage bis Sekunden. Dies gilt es nun zu kombinieren: Dazu ermittelt man zunächst das Intervall Year-To-Month, zieht dieses vom späteren "Enddatum" ab und ermittelt dann vom Rest das Intervall Day-To-Second.
Now, as the last step we'd like to combine the two: We want to know how many years, months, days, hours, minutes and seconds are between two datetime values. For years and months we use the Year-To-Month interval type, we subtract this from the least datetime value and finally we use the Day-To-Second interval for the remainder. Check out the following PL/SQL code.
declare
  v_enddate   date := to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS');
  v_startdate date := to_date('2007-07-15 07:30', 'YYYY-MM-DD HH24:MI:SS');
  v_ym        interval year(9) to month;
  v_ds        interval day(9) to second;
begin
  -- Jahre und Monate ermitteln
  v_ym := (v_enddate - v_startdate) year to month;
  -- Jahr und Monate vom Enddatum abziehen
  v_enddate := v_enddate - v_ym;
  -- Tage, Stunden, Minuten und Sekunden ermitteln
  v_ds := (v_enddate - v_startdate) day(9) to second;

  dbms_output.put_line('Years:   '||extract(YEAR from v_ym));
  dbms_output.put_line('Months:  '||extract(MONTH from v_ym));
  dbms_output.put_line('Days:    '||extract(DAY from v_ds));
  dbms_output.put_line('Hours:   '||extract(HOUR from v_ds));
  dbms_output.put_line('Minutes: '||extract(MINUTE from v_ds));
  dbms_output.put_line('Seconds: '||extract(SECOND from v_ds));
end;
/

Years:   2
Months:  2
Days:    6
Hours:   7
Minutes: 15
Seconds: 0
Ich denke, das wäre es dann für heute; wie man mit TIMESTAMP und Zeitzonen umgehen kann, verschiebe ich aufs nächste Blog-Posting.
This should be enough datetime blogging for today ... how to handle timezones will then be topic of the next blog posting.

10. September 2009

Erweiterungen für den SQL Developer: APEX, XML DB, Dateisystem und mehr ...

English title: APEX and XML DB extensions for SQL Developer

In den letzten Tagen habe ich noch ein wenig mit den Erweiterungsmöglichkeiten des SQL Developer gespielt. Die Erweiterungen, die dabei herausgekommen sind, möchte ich euch nicht vorenthalten ...
During the last few days I played around with SQL Developer extensibility. I produced some extensions which I'd like to share ...
Der SQL Developer ist von Haus aus erweiterbar - das wissen die meisten. Nicht so bekannt ist die Tatsache, dass man dazu nicht unbedingt Java programmieren muss. Vor allem SQL und PL/SQL-gestützte Erweiterungen können auch mit XML-Dateien, die dann einfach in den SQL Developer eingeklinkt werden, realisiert werden. Wie man das macht und wie man die XML-Dateien erstellt, ist unter anderem in diesen Artikeln beschrieben:
SQL Developer is extensible by default - this is known widely. But only a few know that it is not required to implement java code for this. Database-centric (SQL and PL/SQL-based) extensions can be implemented in a declarative manner with by XML files. The following articles describe the process.
Ladet einach mal das ZIP-Archiv herunter und probiert es mal aus - Einfach im SQL Developer zu Extras, Voreinstellungen und dort zu Datenbank und Bnutzerdefinierte Erweiterungen navigieren. Dort fügt Ihr die Dateien aus dem ZIP-Archiv hinzu. Der Dateiname enthält einen Hinweis, ob die Datei als NAVIGATOR, EDITOR oder ACTION zu registrieren ist. Anschließend SQL Developer neu starten und ansehen ...
Just download the zip archive and try the extensions - just navigate in SQL Developer to Tools then to preferences. In the preference window go to database and user defined extensions. Register the files in the downloaded ZIP archive there. The filename contains a hint whether to register the file as NAVIGATOR, EDITOR or ACTION. After that, restart SQL Developer and have a look ...
  • APEX Workspace Management Extension
    Im Navigationsbaum des SQL Developer findet Ihr anschließend den Eintrag APEX Workspaces; Ihr müsst aber als SYS verbunden sein. Die Erweiterung habe ich mit APEX 3.1 und APEX 3.2 getestet.
  • Extension für Package FILE_TYPE
    Dies ist eine Erweiterung für das Package zum Zugriff auf das Server-Dateisystem.
  • Nutzer der Oracle XML DB finden auch eine Erweiterung zur Steuerung des XML DB Repository
  • Wer das Skript zum Bestimmen der tatsächlichen Größe einer Tabelle nutzt, findet im Archiv auch eine Erweiterung hierfür.
  • APEX Workspace Management Extension
    The navigation tree contains now an entry APEX Workspaces; you have to be connected as SYS. I tested this extension with APEX 3.1 and APEX 3.2. Note that there are also some features in the navigator tree entries' context menus.
  • Extension für Package FILE_TYPE
    This is an extension for my package for retrieving directory tree listings and file contents.. It's basically a server filesystem viewer. Read the packages' install notes before using this.
  • Oracle XML DB users might find the extension for managing the XML DB repository useful.
  • Users of the SQL script to determine the actual size of a table can embed this into SQL Developer with the fourth contained extension.
Viel Spaß beim Ausprobieren - und gebt mir Feedback!
Have fun trying it - and tell me if you like it (or not).

1. September 2009

Oracle11g Release 2 ist freigegeben!

English title: Hot News: Oracle11g Release 2 is available

Oracle 11g Release 2 ist verfügbar. Für Linux kann die neue Datenbankversion aus dem Oracle Technet heruntergeladen werden.
Oracle 11g Release 2 is available. You can download the linux version from OTN.
Eine Übersicht über interessante neue Features findet sich für Entwickler in der APEX Community, für DBAs in der DBA Community. Schaut einfach mal rein.
Want to know what's new? Have a look into the Oracle11g Release 2 microsite.

26. August 2009

Wie groß ist eine Tabelle ...? Skript nun auch für Oracle10g verfügbar!

English title: How big is a table in Oracle10g ...?

Vor einiger Zeit (im Januar) hatte ich ein SQL-Skript geposted, mit dem man die Größe einer Tabelle inklusive aller Segemnte (Partitionen, LOB, Index) ermitteln kann. Das Skript hatte für viele Leser nur einen Nachteil: Es läuft nur auf 11g. Dem möchte ich nun Abhilfe schaffen: Hier ist das Skript für Oracle 10g.
Some time ago (January 2009) I posted a SQL script which determines the size of a table - including all segments (LOB, Index, Partitions). For many readers the only disadvantage was that the script was written for Oracle11g and did not run in 10g. So here is the version for Oracle10g:
Ausprobieren ...
Test it ...
SQL> desc dokument_tab
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------
 ID                                        NOT NULL NUMBER(10)
 NAME                                      NOT NULL VARCHAR2(100)
 BESCHREIBUNG                                       VARCHAR2(4000)
 MIMETYPE                                  NOT NULL VARCHAR2(100)
 DOKUMENT                                  NOT NULL BLOB

SQL> select * from table(get_space_info('DOKUMENT_TAB'));

SEGMENT_NAME                COLUMN_NAME        PARTITION_NAME   SEGMENT_TYPE   ALLOC_BYTES FREE_BYTES
--------------------------- ------------------ ---------------- -------------- ----------- ----------
DOKUMENT_TAB                                                    TABLE                65536          0
PK_DOKUMENT_TAB             ID                                  INDEX                65536      32768
SYS_LOB0000153809C00005$$   DOKUMENT                            LOBSEGMENT        10485760     516096
SYS_IL0000153809C00005$$                                        LOBINDEX             65536      32768
Viel Spaß beim Experimentieren mit der Funktion ...
Have fun!

4. August 2009

DDL generieren: mit Datenbank und Browser - sonst nichts

English title: Retrieving DDL scripts per Browser - with just the database

Das PL/SQL Paket DBMS_METADATA, mit dem man DDL-Skripte für alle Datenbankobjekte erzeugen kann, ist hinreichend bekannt. Und es wird ja auch von vielen Werkzeugen verwendet - so nutzen der SQL Developer und Application Express dieses Paket, um SQL für Datenbankobjekte zu generieren. Und auch in SQL*Plus kommt man mit der GET_DDL-Funktion schon recht weit.
The PL/SQL PackageDBMS_METADATA which can generate DDL scripts for all object types in the database is widely known. And many tools actually use it: SQL Developer, APEX and others generate SQL scripts using DBMS_METADATA. And if you're working with SQL*Plus the GET_DDL function is a very useful helper for "quick DDL" generation.
Ich selbst hätte recht häufig jedoch gerne einfach eine Datei - ohne lang ein Werkzeug starten zu müssen. APEX ist auch nicht in jeder Datenbank enthalten. Speziell wenn man mal das PL/SQL eines Packages braucht, ist die Arbeit mit SQL*Plus und dem SPOOL-Kommando etwas mühsam. Also hatte ich den Gedanken, dass es möglich sein müsste, ein DDL Skript genauso einfach generieren zu können, wie das mit XML schon lange geht ...
In the past there were many situations where I just wanted to have the SQL script in a file. I didn't want to start a specfic tool ... and working with SQL*Plus and the spool command is kind of cumbersome (particularly when retrieving DDL for PL/SQL packages). So I wondered if DDL generation could be as easy as XML generation: Just with the database and the browser ...
Und das wollen wir nun tun: Stellt zunächst sicher, dass der HTTP-Listener eurer Datenbank aktiviert ist - das XML-Blog-Posting enthält einige Hinweise dazu. Anschließend spielt als DBA folgende Objekte ein.
First you might check whether the HTTP endpoint of your database is active - the XML generation blog posting contains some hints about that. After that connect as SYS and create the following objects.
Zunächst benötigen wir eine PL/SQL-Funktion, die (passend zu einer einfachen Browser-URL mit wenigen Parametern) das DDL-Skript generiert. Ich habe hier einige für mich passende Einstellungen gemacht (bspw. lasse ich das FORCE-Kommando beim CREATE VIEW weg); das könnt Ihr nach Gusto ändern. Die PL/SQL-Funktion könnt Ihr auch in SQL*Plus testen ...
First we need a PL/SQL function which uses DBMS_METADATA as needed by the browser. Since the browser interface should be easy to use we don't want to expose all DBMS_METADATA capabilities. For example I generally omit the FORCE keyword in CREATE VIEW statements (you might adjust this to your needs). You can test the PL/SQL function in SQL*Plus .. but thats not the main purpose ...
Die Funktion wird AUTHID CURRENT_USER erzeugt; Ihr müsst euch um das Einspielen als SYS und das anschließende GRANT an PUBLIC also keine Sorgen machen. Jeder Nutzer kann DDL nur für die Objekte erzeugen, an welchen er Rechte hat.
Don't worry about the GRANT to PUBLIC: The function will be created with the AUTHID CURRENT_USER clause which means that it will be executed with the privileges of the invoker. No one can generate DDL for objects he does not have privileges on.
Anschließend wird ein Java Servlet in die Datenbank eingespielt. Ja das ist richtig: Die Datenbank ist mit einer Servlet-Engine ausgestattet: Das bedeutet nun aber nicht, dass man mit der Datenbank einen J2EE-Server ersetzen sollte - diese Möglichkeit ist für ausgesuchte Fälle gedacht; und für unsere Metadaten-Anforderung ist das perfekt geeignet. Der Java-Code wird wie die PL/SQL-Funktion als SYS mit den Rechten des Aufrufers (AUTHID CURRENT_USER) eingespielt.
After this we create the java servlet in the database. That is right: The database contains a servlet engine. But that does not mean that Oracle likes to replace J2EE servers with the database - the servlet engine is intended for those cases where a servlet is best-placed in the database. And for the metadata genaration it suits perfectly.
Und schließlich muss das Servlet registriert werden ...
Finally register the servlet ...
Fertig. Ihr könnt das nun testen ... bevor das Servlet antwortet, müsst Ihr euch mit einem Datenbankuser einloggen.

Aller Code läuft mit den Privilegien des Users ab, als der Ihr euch im Browser anmeldet.
  • DDL für die Tabelle EMP im Schema SCOTT
    http://{db-host}:{http-port}/metadata/SCOTT/TABLE/EMP
  • DDL für alle Tabellen des Users SCOTT:
    http://{db-host}:{http-port}/metadata/SCOTT/TABLE
  • PL/SQL-Packages des Users SCOTT:
    http://{db-host}:{http-port}/metadata/SCOTT/PACKAGE
And that's it. You might now test the servlet. After typing the URL the browser prompts you for a database login ...

As stated above, all code runs with invokers's privileges. So it depends on your browser login, for which objects you can generate DDL.
  • DDL script for the table SCOTT.EMP
    http://{db-host}:{http-port}/metadata/SCOTT/TABLE/EMP
  • DDL script all tables in the SCOTT schema
    http://{db-host}:{http-port}/metadata/SCOTT/TABLE
  • DDL script all PL/SQL packages in the SCOTT schema
    http://{db-host}:{http-port}/metadata/SCOTT/PACKAGE
Viel Spaß beim Ausprobieren ...
Have fun!

Beliebte Postings