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

Shrink temporary tablespace

Der schnellste Weg, einen temporären Tablespace zu verkleinern, ist, einen neuen temporären TS anzulegen, alle User des alten auf den neuen umzuleiten und den alten zu droppen. Da die Extents eines temporären Tablespaces erst beim Herunterfahren der Instanz freigegeben werden, läuft ein SHRINK oder RESIZE in der Regel auf Fehler.

Also funktioniert in 10g folgendes:

-- Anlage eines neuen temporären TS
create temporary tablespace temp1 tempfile …;

-- Generierung von Queries, mit denen allen DB-User 
-- der neue temporären TS zugeordnet wird
select 'alter user ' || username || ' temporary tablespace temp1;'
  from dba_users
 where temporary_tablespace = 'TEMP';

-- den neuen temporären TS als default TS setzen
alter database default temporary tablespace temp1;

-- alle Sessions beenden, die noch Einträge in v$sort_usage halten

-- jetzt kann man den alten temporären TS wegwerfen
drop tablespace temp;

alter tablespace temp1 rename to temp;

Nachtrag 16.05.2011: In 11.2.0.1 funktioniert das Vorgehen immer noch (was nicht allzu sehr überrascht)

Nachtrag 23.03.2012: Tom Kyte weist dieser Tage darauf hin, dass das Verkleinern in 11g deutlich einfacher geworden ist - es genügt ein "alter tablespace temp_xyz shrink space".

Connect by level

zu Dokumentierungszwecken:

select rownum
  from dual
connect by level <= 10
/

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 Zeilen ausgewählt.

Als Nummerngenerator verwendbar.

Letzte Tabellenänderungen

Das Datum der letzten Strukturänderung findet man in user_objects, also z.B.:

select object_name
     , last_ddl_time
  from user_objects  
 where object_name = 'NAME_DER_TABELLE'    
   and object_type = 'TABLE';

Die letzte Änderung eines Datensatzes kann man sich in 10g mit Hilfe der Pseudocolumn ORA_ROWSCN anzeigen lassen. Diese Pseudocolumn liefert die SCN der letzten Änderung des Blocks, in dem sich der Datensatz befindet (theoretisch kann man auch die Änderung des Einzelsatzes berücksichtigen, aber dazu muss die Tabelle mit der Option <rowdependencies> erstellt worden sein). Diese SCN kann man sich über eine Funktion in einen Timestamp umwandeln lassen (was aber nur in einem bestimmten Zeitraum funktioniert, da das Mapping SCN - Timestamp offenbar nicht dauerhaft gespeichert wird):

select ora_rowscn
     , scn_to_timestamp(ora_rowscn)
  from NAME_DER_TABELLE;

Für Versionen vor 10g fällt mir keine so simple Methode ein, um Datensatzänderungen zu ermitteln. Da könnte man vielleicht mit Audit, dem Logminer oder gruseligen Triggern arbeiten, aber das alles wäre jedenfalls deutlich aufwändiger.

Nachtrag 04.05.2011: da dieser Eintrag offenbar relativ häufig aufgerufen wird, hier noch der Verweis auf Tanel Poders lastchanged.sql-Script, das noch ein paar Schritte über die hier gezeigten Möglichkeiten hinaus geht. Der Link zum download des Scripts auf der angesprochenen Seite scheint aktuell nicht zu funktionieren, aber möglicherweise nur temporär, da der Herr Poder schreibt: "I plan to fix the broken links some time between now and my retirement." In der Zwischenzeit empfiehlt er, die komplette Script-Sammlung zu laden - und das lohnt sich in diesem Fall ganz gewiß.