logon trigger

keine neue Erkenntnis, sondern ein Syntaxbeispiel:

create or replace trigger scott_logon_trigger
after logon on database
begin
if user = 'SCOTT' then
execute immediate 'alter session set WORKAREA_SIZE_POLICY=Manual';
execute immediate 'alter session set sort_area_size = 100000000';
execute immediate 'alter session set hash_area_size = 100000000';
end if;
end;
/

In 10g erfolgt das PGA-Management per default automatisch. Eine Umstellung der WORKAREA_SIZE_POLICY und explizite Setzung der %_area_size-Parameter kann für kostspielige Operationen interessant sein, da die automatische Zuweisung des PGA-Memories bestimmte (prozentual bestimmte) Obergrenzen nicht überschreitet.

Ein logon trigger ist vor allem dann interessant, wenn ein Parameter für Sessions einer Applikation gesetzt werden soll, die diese Setzung selbst nicht vornehmen können.

Outer Join Syntax

Die Leute, die den Oracle Optimizer entwickeln, haben unter http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html die Unterschiede zwischen der ANSI-Syntax für Outer Join Operationen und Oracles (+)-Operator erläutert. Ausgesprochen erhellend.

sys_connect_by_path

da ich die Syntax dieser hübschen Möglichkeit, die übergeordneten Elemente in einer Hierarchie auf einfache Weise darzustellen, regelmäßig vergesse, hier ein Beispiel für die Verwendung der sys_connect_by_path-Funktion:

SQL> r
1  select empno,
2         ename,
3         job,
4         sys_connect_by_path(job, '/') job_hierarchy
5    from emp
6  connect by prior empno = mgr
7   start with mgr is null
8*

EMPNO ENAME      JOB       JOB_HIERARCHY
---------- ---------- --------- ---------------------------------
7839 KING       PRESIDENT /PRESIDENT
7566 JONES      MANAGER   /PRESIDENT/MANAGER
7788 SCOTT      ANALYST   /PRESIDENT/MANAGER/ANALYST
7876 ADAMS      CLERK     /PRESIDENT/MANAGER/ANALYST/CLERK
7902 FORD       ANALYST   /PRESIDENT/MANAGER/ANALYST
7369 SMITH      CLERK     /PRESIDENT/MANAGER/ANALYST/CLERK
7698 BLAKE      MANAGER   /PRESIDENT/MANAGER
7499 ALLEN      SALESMAN  /PRESIDENT/MANAGER/SALESMAN
7521 WARD       SALESMAN  /PRESIDENT/MANAGER/SALESMAN
7654 MARTIN     SALESMAN  /PRESIDENT/MANAGER/SALESMAN
7844 TURNER     SALESMAN  /PRESIDENT/MANAGER/SALESMAN
7900 JAMES      CLERK     /PRESIDENT/MANAGER/CLERK
7782 CLARK      MANAGER   /PRESIDENT/MANAGER
7934 MILLER     CLERK     /PRESIDENT/MANAGER/CLERK

14 Zeilen ausgewählt.

Funktioniert seit 9i.

Redo-Generierung für Indizes

Eigentlich hatte ich angenommen, die Regeln der nologging-clause einigermaßen genau zu kennen. Das war aber wohl ein Irrtum. Zu meiner Überraschung ist es nämlich offenbar nicht möglich, die redo-Generierung für Indizes bei direct-path-Inserts zu vermeiden. Hier noch mal die Details zum Mitschreiben:

• im NOARCHIVELOG-Modus erzeugt ein INSERT /*+ append */ nur minimale redo Informationen
• allerdings werden redo-Informationen für zugehörige Indizes erzeugt
• dies geschieht auch dann, wenn der Index in den nologging Modus gesetzt wurde
• nologging spielt für Indizes nur bei rebuild-Operationen eine Rolle

Damit diese Aussagen nicht reine Behauptungen bleiben, hier die zugehörigen Tests:

create table test as select * from dba_objects where 1 = 0;

Tabelle wurde erstellt.

insert into test select * from dba_objects;

54496 Zeilen wurden erstellt.

--> 5982892  redo size

-- nach rollback:
insert /*+ append */ into test select * from dba_objects;

54496 Zeilen wurden erstellt.

--> 2328  redo size

--> dabei spielt das logging-Attribut der Tabelle im vorliegenden NOARCHIVELOG-Modus keine Rolle

-- nach rollback:
create index test_idx1 on test(object_name);

Index wurde erstellt.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 3799568  redo size

alter index test_idx1 nologging;

Index wurde geändert.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 3799568  redo size

--> der nologging-Modus des Index spielt im Fall des INSERTs keine Rolle
--> Änderungen am Index werden immer geloggt
--> für größere Änderungsoperationen ist demnach ein kompletter Neuaufbau des Index vorzuziehen

-- nach rollback:
alter index test_idx1 unusable;

Index wurde geändert.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 1888  redo size

alter index test_idx1 rebuild;

Index wurde geändert.

--> 60180 redo size

--> minimale redo Generierung für das rebuild eines Index im nologging-Modus

Advanced Query Rewrite

während alle ernsthaften Blogger sich mit Features aus Version 11 beschäftigen, entdecke ich immer noch hübsche Dinge in 10g. So z.B. das Package DBMS_ADVANCED_REWRITE, das es ermöglicht, eine abgesetzte Query intern durch ein anderes Statement zu ersetzen. Einerseits kann man damit natürlich ungeheuren Blödsinn anstellen, andererseits eröffnet dieses Package ziemlich umfangreiche Möglichkeiten für die Definition einer intelligenten Rewrite-Logik im DataWarehouse-Bereich. Hier ein ganz simples Syntax-Beispiel:

-- als sys:
SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO kettle;

Benutzerzugriff (Grant) wurde erteilt.

-- als kettle:
SQL> create table test_rewrite(a varchar2(64));

Tabelle wurde erstellt.

SQL> insert into test_rewrite(a)
2 values ('aus der Tabelle test_rewrite');

1 Zeile wurde erstellt.

SQL> commit;

SQL> begin
2    SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence
3        (name             => 'test_rewrite',
4         source_stmt      => 'SELECT * FROM test_rewrite',
5         destination_stmt => 'SELECT ''aus dual'' from dual',
6         validate         => FALSE);
7    end;
8    /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT * FROM test_rewrite;

A
------------------------------------
aus der Tabelle test_rewrite

-- das System muss erst davon überzeugt werden, dass unser Rewrite vertrauenswürdig ist
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session wurde geändert.

SQL> SELECT * FROM test_rewrite;

A
--------
aus dual

SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence 
2         (name => 'test_rewrite');

PL/SQL-Prozedur erfolgreich abgeschlossen.

Execution Plan

es wird behauptet, manchmal sei es besser nichts zu schreiben als nicht zu schreiben, daher hier ein paar Hinweise, die man an ca. 10000 anderen Orten finden kann:

In 10g lassen sich Zugriffspläne sehr komfortabel über das dbms_xplan-Package darstellen, also z.B.:

SQL> r
1  SELECT plan_table_output
2*   FROM TABLE( dbms_xplan.display_cursor ( 'a5ks9fhw2v9s1', 0))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

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


13 Zeilen ausgewählt.

Dabei ist der erste Parameter die sql_id und der zweite cursor_child_no (beide findet man in v$sql). Wenn ich jetzt nichts durcheinander werfe, ist der Formatierungsmechanismus der gleiche der bei autotrace verwendet wird.

Flashback-Query

Ein ganz simples Beispiel dieser praktischen Funktionalität, mit deren Hilfe man sich ansehen kann, wie eine Tabelle zu einem bestimmten Zeitpunkt in der Vergangenheit gefüllt war:

-- Anlage der Tabelle test1, die mit fünf Sätze gefüllt wird:
create table test1 (a number);

insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);
insert into test1 values (4);
insert into test1 values (5);

commit;

-- ein Blick auf die Uhr:
select to_char(sysdate, 'hh24:mi:ss') from dual;

TO_CHAR(
--------
10:30:29

-- und auf den Inhalt der Tabelle:
select * from test1;

A
----------
1
2
3
4
5

5 Zeilen ausgewählt.

-- jetzt löschen wir drei Zeilen:
delete from test1 where a > 2;

3 Zeilen wurden gelöscht.

commit;

-- 5 - 3 = 2
select * from test1;

A
----------
1
2

2 Zeilen ausgewählt.

-- mit Hilfe der flashback query können wir sehen, was vor der Löschung in der Tabelle enthalten war
SELECT *
  FROM Test1
  AS OF TIMESTAMP ( TO_TIMESTAMP( '30.05.07 10:30:30', 'DD.MM.YY HH24:MI:SS') )

A
----------
1
2
3
4
5

5 Zeilen ausgewählt.

Quizfrage

Bei Dominic Delmolino (http://www.oraclemusings.com/?p=57) gab's kürzlich eine interessante SQL-Aufgabe. Hier meine (nicht besonders hübsche) Lösung dazu:

SQL> r
select case when lag_col is null then min_col
when substr(min_col, 1, 1) <> substr(lag_col, 1, 1)
then substr(min_col, 1, 1)
when substr(min_col, 1, 1) = substr(lag_col, 1, 1)
and substr(min_col, 1, 2) <> substr(lag_col, 1, 2) then substr(min_col, 1, 2)
when substr(min_col, 1, 2) = substr(lag_col, 1, 2)
and substr(min_col, 1, 3) <> substr(lag_col, 1, 3) then substr(min_col, 1, 3)
when substr(min_col, 1, 3) = substr(lag_col, 1, 3)
and substr(min_col, 1, 4) <> substr(lag_col, 1, 4) then substr(min_col, 1, 4)
when substr(min_col, 1, 4) = substr(lag_col, 1, 4)
and substr(min_col, 1, 5) <> substr(lag_col, 1, 5) then substr(min_col, 1, 5)
when substr(min_col, 1, 5) = substr(lag_col, 1, 5)
and substr(min_col, 1, 6) <> substr(lag_col, 1, 6) then substr(min_col, 1, 6)
when substr(min_col, 1, 6) = substr(lag_col, 1, 6)
and substr(min_col, 1, 7) <> substr(lag_col, 1, 7) then substr(min_col, 1, 7)
when substr(min_col, 1, 7) = substr(lag_col, 1, 7)
and substr(min_col, 1, 8) <> substr(lag_col, 1, 8) then substr(min_col, 1, 8)
when substr(min_col, 1, 8) = substr(lag_col, 1, 8)
and substr(min_col, 1, 9) <> substr(lag_col, 1, 9) then substr(min_col, 1, 9)
when substr(min_col, 1, 9) = substr(lag_col, 1, 9)
and substr(min_col, 1, 10) <> substr(lag_col, 1, 10) then substr(min_col, 1, 10)
when substr(min_col, 1, 10) = substr(lag_col, 1, 10)
and substr(min_col, 1, 11) <> substr(lag_col, 1, 11) then substr(min_col, 1, 11)
when substr(min_col, 1, 11) = substr(lag_col, 1, 11)
and substr(min_col, 1, 12) <> substr(lag_col, 1, 12) then substr(min_col, 1, 12)
else null end
|| ' thru ' ||
case when lead_col is null then max_col
when substr(max_col, 1, 1) <> substr(lead_col, 1, 1)
then substr(max_col, 1, 1)
when substr(max_col, 1, 1) = substr(lead_col, 1, 1)
and substr(max_col, 1, 2) <> substr(lead_col, 1, 2) then substr(max_col, 1, 2)
when substr(max_col, 1, 2) = substr(lead_col, 1, 2)
and substr(max_col, 1, 3) <> substr(lead_col, 1, 3) then substr(max_col, 1, 3)
when substr(max_col, 1, 3) = substr(lead_col, 1, 3)
and substr(max_col, 1, 4) <> substr(lead_col, 1, 4) then substr(max_col, 1, 4)
when substr(max_col, 1, 4) = substr(lead_col, 1, 4)
and substr(max_col, 1, 5) <> substr(lead_col, 1, 5) then substr(max_col, 1, 5)
when substr(max_col, 1, 5) = substr(lead_col, 1, 5)
and substr(max_col, 1, 6) <> substr(lead_col, 1, 6) then substr(max_col, 1, 6)
when substr(max_col, 1, 6) = substr(lead_col, 1, 6)
and substr(max_col, 1, 7) <> substr(lead_col, 1, 7) then substr(max_col, 1, 7)
when substr(max_col, 1, 7) = substr(lead_col, 1, 7)
and substr(max_col, 1, 8) <> substr(lead_col, 1, 8) then substr(max_col, 1, 8)
when substr(max_col, 1, 8) = substr(lead_col, 1, 8)
and substr(max_col, 1, 9) <> substr(lead_col, 1, 9) then substr(max_col, 1, 9)
when substr(max_col, 1, 9) = substr(lead_col, 1, 9)
and substr(max_col, 1, 10) <> substr(lead_col, 1, 10) then substr(max_col, 1, 10)
when substr(max_col, 1, 10) = substr(lead_col, 1, 10)
and substr(max_col, 1, 11) <> substr(lead_col, 1, 11) then substr(max_col, 1, 11)
when substr(max_col, 1, 11) = substr(lead_col, 1, 11)
and substr(max_col, 1, 12) <> substr(lead_col, 1, 12) then substr(max_col, 1, 12)
else null end spine, min_col, max_col
from (select ntile_range,
min_col,
max_col,
lead( min_col) over (order by ntile_range) lead_col,
lag( max_col) over (order by ntile_range) lag_col
from (select ntile_range,
min(column_name) min_col,
max(column_name) max_col
from (select column_name,
ntile(15) over(order by column_name) ntile_range
from ac1
)
group by ntile_range order by ntile_range
)
) t

SPINE MIN_COL MAX_COL
------------------------- ------------------------------ ------------------------------
A thru BITMAP A BITMAP
BITMAPP thru C_ BITMAPPED C_OBJ#
C1 thru De C1 Default
D1 thru ERR_ D1 ERR_NUM
ERRO thru GENL ERRORS GENLINKS
GENO thru I_ GENOPTION I_AGREE
IN thru LOB_ INTRO LOB_COL_NAME
LOBI thru MV_QUA LOBINDEX MV_QUANTITY_SUM
MV_QUE thru Op MV_QUERY_GEN_MISMATCH Option
OS thru P_ OSHST P_REF_TIME
P1 thru R_ P1 R_CONSTRAINT_NAME
R1 thru SHORT_WAITS R1 SHORT_WAITS
SHORT_WAIT_ thru Si SHORT_WAIT_TIME_MAX Size
SU thru T_ SUMGROSSTURNOVER T_PER_EXEC
T1 thru ZERO_RESULTS T1OBJID ZERO_RESULTS

15 Zeilen ausgewählt.

case insensitive Suche unter Oracle

Bei Tom Kyte (wo sonst?) findet man folgende hübsche Möglichtkeit, in 10g case eine insensitive Konditionsprüfung zu ermöglichen. Relevant wäre diese Option beispielsweise dann, wenn man die Statements einer Applikation nicht ändern - und die Prüfung z.B. nicht mit einem UPPER beeinflussen - kann.

create table t ( data varchar2(20) );

insert into t values ( 'Hello' );
insert into t values ( 'HeLlO' );
insert into t values ( 'HELLO' );

-- wie zu erwarten liefert die folgende Query
-- zunächst kein Ergebnis
select * 
  from t
 where data = 'hello'

 Es wurden keine Zeilen ausgewählt

alter session set nls_comp=ansi;

Session wurde geändert.

alter session set nls_sort=binary_ci;

Session wurde geändert.

-- Nach Anpassung der beiden NLS-Parameter 
-- wird die Bedingung case insensitive behandelt
select * 
  from t
 where data = 'hello'

DATA
--------------------
Hello
HeLlO
HELLO

3 Zeilen ausgewählt.

set autot trace
select * 
  from t
 where data = 'hello'

3 Zeilen ausgewählt.

Ausführungsplan
--------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 |
--------------------------------------------------

-- durch die Anlage eines function based index kann man den
-- Zugriff dann auch noch optimieren.
create index t_idx on t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );

select * 
  from t
 where data = 'hello';

3 Zeilen ausgewählt.

Ausführungsplan
-----------------------------------------------------
Plan hash value: 470836197
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 |
-----------------------------------------------------

Zu Prüfen wäre in einem solchen Fall nur, welche weiteren Auswirkungen die Änderung der NLS-Parameter auf eine Applikation haben könnte. Zusätzliche Details zum Thema findet man im AskTom-Thread.

OracleDBConsole-Dienst neu erzeugen

Der OracleDBConsole-Dienst hat (zumindest bei mir) einen gewissen Hang dazu, den Geist aufzugeben. Um ihn neu zu konfigurieren, kann man in 10.2 folgenden Befehl verwenden:

C:\> emca -config dbcontrol db -repos recreate

STARTED EMCA um 30.04.2007 09:43:02
EM-Konfigurationsassistent, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved. Alle Rechte vorbehalten.

Geben Sie folgende Informationen ein:
Datenbank-SID: ...
(hier werden dann noch diverse Passwörter und Mail-Adressen abgefragt)

Die Installation wird in einer Log-Datei protokolliert und dauert ein paar Minuten.

In 10.1 konnte man zu diesem Zweck noch die Variante emca -r verwenden.

Nachtrag 16.05.2011: in 11.2 funktioniert das Verfahren ebenfalls, allerdings ergaben sich auf meinem Windows7-Test-Rechner diverse Rechte-Probleme, die z.T. durch Vergabe von Schreibrechten an meinen User und z.T. durch Ausführung des Befehls in einer als Administrator geöffneten Eingabeaufforderung gelöst werden konnten.

Logging fehlerhafter Sätze bei direct path inserts

Tim Hall erläutert unter http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php, wie man beim Bulk-Insert alle Sätze, die Constraint-Bedingungen widersprechen, in einer mit Hilfe von dbms_errlog erzeugten Log-Tabelle vermerken kann. Die Beispiele sind sehr ausführlich und hübsch präsentiert (wie's beim Herrn Hall üblich ist).

Verkleinerung von LOB-Segmenten

Nachtrag am 19.10.2010: inzwischen muss ich von der hier vorgestellten Operation abraten, da sie einen recht häßlichen Bug auf den Plan rufen kann.

In Oracle 10g können Tabellen über ein ALTER TABLE ... SHRINK im laufenden Betrieb reorganisiert und verkleinert werden. Durch die CASCADE-Option werden auch abhängige Objekte wie Indizes und LOBs verkleinert. Voraussetzung für den Einsatz dieses Features ist ein Tablespace, für den ASSM (Automatic Segment Space Management) aktiviert ist (das ist bei uns und per default der Fall), außerdem muss für die entsprechende Tabelle das <row movement> aktiviert werden, so dass die physikalische Adresse der Einträge geändert werden kann:

select segment_name
     , bytes
  from user_segments
 where segment_name IN (select segment_name
                          from user_lobs
                         where table_name = 'SIMPLENODECONTENT')
    or segment_name = 'SIMPLENODECONTENT';

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_LOB0000109571C00002$$       855638016
SIMPLENODECONTENT                 7340032

alter table simplenodecontent enable row movement;

Tabelle wurde geändert.

-- diese Operation kann längere Zeit in Anspruch nehmen:
alter table simplenodecontent shrink space cascade;

Tabelle wurde geändert.

Abgelaufen: 00:02:34.96

alter table simplenodecontent disable row movement;

Tabelle wurde geändert.

select segment_name
     , bytes
  from user_segments
 where segment_name IN (select segment_name
                          from user_lobs
                         where table_name = 'SIMPLENODECONTENT')
    or segment_name = 'SIMPLENODECONTENT';

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_LOB0000109571C00002$$       158466048
SIMPLENODECONTENT                 1769472