Flashback

Eine ganz kurze Demonstration zur Flashback-Funktionalität in 10g:

create table test1(a number);

insert into test1 values (1);

commit;

drop table test1;

select * 
  from test1;

  from test1
       *
FEHLER in Zeile 2:
ORA-00942: Tabelle oder View nicht vorhanden

flashback table test1 to before drop;

select * 
  from test1;

         A
----------
         1

Web-Adresse der DB Console

Die Adresse der db console einer Instanz kann auf mehreren Wegen ermittelt werden. Während der Einrichtung der console durch den emca werden logs geschrieben:

• unter %OracleHome%\cfgtoollogs wird in 10.1 im Rahmen der Installation eine Datei emca.log.0 abgelegt, in der die Adresse zu finden ist. Hier bekommt anscheinend die erste Installation die 5500 und weitere Installationen dann die folgenden ports (5501, 5502 etc.).
• In 10.2 ist das Verzeichnis cfgtoollogs aufgeräumt worden. Dort hat dann jedes Tool sein eigenes Verzeichnis mit Unterverzeichnissen für die zugehörigen Instanzen - also %OracleHome%\cfgtoollogs\emca\<sid> - und darin liegen dann emca_<datum>_<uhrzeit>.log-Dateien. In 10.2 scheint die Portvergabe nicht mehr bei 5500 zu beginnen (jedenfalls nicht in meinen Installationen).

Mit dem Kommando <emctl start dbconsole> kann man – nach vorheriger Setzung der ORACLE_SID - die console neu starten und dabei auch ihre Webadresse erfahren.

Windows Memory Limit

Unter Windows (32-Bit) kann ein Prozess üblicherweise nicht mehr als 2 GB RAM verwenden. Dieses Limit lässt sich über eine Einstellung in der boot.ini erhöhen. Näheres dazu z.B. unter http://www.brianmadden.com/content/content.asp?ID=69.

Outer Join mit OR-Verknüpfung

Laut Oracle Database SQL Reference (für 10g) gilt: "A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator."

Die Datenbank behauptet das gleiche:

SQL> r
1 select *
2   from test2 t2, test3 t3
3  where t2.a = t3.a(+)
4*    or t3.b = 3

where t2.a = t3.a(+)
*ERROR at line 3:

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Es gibt allerdings ab Oracle 9 die folgende Möglichkeit:

select * 
  from test2 t2 
  left outer join 
       test3 t3 
    on (t2.a = t3.a or t3.b = 3);

Das Problem liegt also beim (+)-Operator (dessen Verwendung Oracle auch nicht mehr empfiehlt - obwohl er mir immer besser gefallen hat als die verbose "left outer join on blabla"-Variante - was angesichts solcher Einschränkungen allerdings einleuchtet).

Nachtrag 27.07.2011: Wolfgang Breitling (den Link muss ich schuldig bleiben) und andere (z.B. der Herr Lewis) haben gelegentlich darauf hingewiesen, dass Oracle (in diesem Fall also der CBO) mit der traditionellen (und über Jahrzehnte hinweg eingesetzten) Join-Syntax besser zurecht kommt.

database cloning

Da das Thema <cloning> offenbar häufiger angeschnitten wird, hier ein Link auf die passenden Threads bei AskTom:

• http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:634671569269
• http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:318216852435

(die wiederum MetaLink-Artikel enthalten; die verwendete Version ist jeweils 8i, aber mit 9 sollte es ähnlich funktionieren)

Nachtrag 19.10.2012: die Links verweisen dieser Tage ins Nirvana, aber man findet beim Herrn Kyte natürlich immer noch viel Relevantes zu diesem Thema, z.B. hier.

MVs für Postgres

Ausnahmsweise mal etwas zu Postgres. Der folgende Link beschreibt sehr detailliert, wie man in Postgres Materialisierte Views anlegen kann: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html.

Tablespaceerweiterung

Die Vergrößerung einer Datendatei eines Tablespaces kann auf mehreren Wegen erfolgen:

alter database datafile 'pfadangabe_für_die_datendatei' autoextend on maxsize 1000M;

Dabei haben die Optionen folgende Wirkung:

• autoextend on: schaltet die automatische file-Erweiterung ein
• maxsize: ist (man ahnt es schon) die maximale Größe des Datenfiles.

Wer die automatische Erweiterung nicht mag, kann eine Datei bei Bedarf manuell vergrößern:

alter database datafile 'pfadangabe_für_die_datendatei' resize 1000M;

(was allerdings abhängig von der Größe etwas dauern kann)

Vorraussetzung für die Operation sind die erforderlichen Systemprivilegien (die beispielsweise in der DBA-Rolle enthalten sind). Bedenken sollte man bei autoextend, dass auch genügend Platz dafür auf der Platte vorhanden sein muss (damit die Platte nicht vollständig gefüllt wird). Darüber hinaus gibt es dann noch OS- und Blocksize-abhängige Limits für die Größe der Datendateien.

Alternativ kann man auch mehrere data files zum Tablespace hinzufügen (was ab einer gewissen Größe der files allein schon aus Gründen der Handhabbarkeit des Backups sinnvoller sein dürfte als der Einsatz eines sehr großen files).

Materialized View: Fast Refresh on Commit

Hier mal eine minimale Definition einer MV mit Fast Refresh on Commit:

create table test1 (a number);

alter table test1 add constraint test1pk primary key (a);

CREATE MATERIALIZED VIEW LOG ON test1 WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES;

create materialized view test1_mv 
refresh fast on commit 
as 
select * 
  from test1;

select * 
  from test1;

no rows selected

select * 
  from test1_mv;

no rows selected

insert into test1 values (1);

1 row created.

select * 
  from test1;

A
----------
1

1 row selected.

select * 
  from test1_mv;

no rows selected

commit;

Commit complete.

select * 
  from test1_mv;

A
----------
1

1 row selected.

Beim Commit werden die Änderungen an die MV propagiert. Erforderlich sind ein Primary Key (da könnte man natürlich auch einen synthetischen Key verwenden) und ein View Log, das alle Änderungen der Basis-Tabelle protokolliert.    

Länge von Long-Spalten anzeigen

Vom Datentyp LONG rät Oracle ziemlich entschieden ab und empfiehlt stattdessen den Einsatz von LOBs. Für LONG-Spalten gibt es meines Wissens keine einfache Möglichkeit, die Länge zu bestimmen. Allerdings gibt es eine Funktion TO_LOB, mit der man LONGs in LOBs umwandeln kann, aber leider kann diese Funktion nur innerhalb einer Unterabfrage für ein INSERT verwendet werden:

create table test (a long);
insert into test values ('bllllllllllllllllllllllaaaaaaaaaaaaaaaaa');
select to_lob(a) 
  from test;

ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got LONG

create table test2 (b clob);
insert into test2(b)
select to_lob(a) 
  from test;

select dbms_lob.getlength(b) from test2;

DBMS_LOB.GETLENGTH(B)
---------------------
40

Zur Bestimmung der Länge bräuchte man also eine Hilfstabelle, in die man den LONG-Wert einfügen könnte, um seine Länge dann mittels dbms_lob.getlength zu ermitteln - was natürlich eher umständlich ist.

Alternativ kann man die Länge auch in einem PL/SQL-Block bestimmen:

DECLARE
long_var LONG;
BEGIN
SELECT a 
  INTO long_var
  FROM test;

DBMS_OUTPUT.PUT_LINE('LOB_LENGTH:' || LENGTH(long_var));
end;
/
LOB_LENGTH:40

PL/SQL-Prozedur erfolgreich abgeschlossen.

Aber eine Funktion, die aus einem SQL-Statement heraus aufgerufen werden kann, wird daraus leider nicht, weil solche Funktionen keine LONGs als Parameter annehmen.   

ntile

ntile ist eine der vielen netten (meiner Erinnerung nach in 8i eingeführten) analytischen Funktionen, die das Reporting dramatisch vereinfachen. Die Funktion unterteilt eine Ergebnismenge in n buckets und vergibt die entsprechende bucket number an alle zugehörigen rows.

select OBJECT_NAME,
       OBJECT_ID,
       ntile(5) over (order by OBJECT_ID) range
  from dba_objects
 where rownum < 20
 order by OBJECT_ID

OBJECT_NAME           OBJECT_ID      RANGE
-------------------- ---------- ----------
I_OBJ#                        3          1
I_TS#                         7          1
I_FILE#_BLOCK#                9          1
UET$                         13          1
UNDO$                        15          2
FILE$                        17          2
IND$                         19          2
ICOL$                        20          2
PROXY_ROLE_DATA$             25          3
I_PROXY_ROLE_DATA$_1         26          3
CON$                         28          3
C_COBJ#                      29          3
I_OBJ3                       38          4
I_IND1                       39          4
I_FILE1                      41          4
I_USER1                      44          4
I_CON1                       48          5
I_CDEF2                      51          5
I_CDEF4                      53          5

19 rows selected.

Vorsichtige Eröffnung

Möglicherweise werde ich an dieser Stelle zukünftig über Oracle Datenbanken sprechen. Sicher ist das allerdings noch nicht. Voraussetzung ist, dass ich die Zeit dafür finde. Und dass meine Motivation dazu ausreicht. Und dass mir etwas zum Thema einfällt, das darzustellen die Mühe lohnt.