its-people Blog – Der Oracle Blog neben Beiträgen aus den Bereichen: Datenbanken, Business Intelligence & Datawarehouse, IT-Management Service & ERP-Beratung

DBMS_METADATA – Flexibler Umgang mit DDL

Die Definitionen von Schemadaten über mehrere Systeme hinweg synchron zu halten und zu versionieren ist schwerer als den berühmten Sack Flöhe zu hüten.

DBMS_METADATA erweist sich für diese Aufgaben als gut versteckte aber schlecht dokumentierte Perle, die weit mehr kann, als man ihr auf den ersten Blick ansieht.

Das Paket dient als Basis für die Datapump und kann weit mehr, als nur DDLs im Klartext für Tabellendefinitionen zu erzeugen:
Unter anderem kann zwischen Script- und XML-Format gewählt, Filter gesetzt und Transformationen durchgeführt werden.
Die Skripte für abhängige Objekte können gleich mit exportiert werden.
Während die Entwickler oftmals nur die Differenzskripte zwischen ihren Releases pflegen und liefern, erzeugt dbms_metadata die Initialskripte automatisch, um die Konfigurationsmanager zufrieden zu stellen.

Wurzeln

Das Package dbms_metadata gehört seit Oracle 9i zum Standardlieferumfang der Datenbank.
Es wird beständig weiterentwickelt und im Funktionsumfang verbessert.
Seit Oracle 10g ist dbms_metadata das Fundament der Datapump. So ist es kaum verwunderlich, dass sich Parameter und Optionen der beiden Features sehr ähnlich sich.
read more…

Oracle – Tabellen eines Tablespaces anzeigen

Es war einmal wieder so weit:
Neulich kam eine Mail, dass der Tablespace dabei ist wegen Überfüllung die Arbeit einzustellen. Man sollte doch bitte prüfen, welche Tabellen von dem Tablespace gelöscht werden können.

Zunächst setzt dies natürlich voraus, dass man die Tabelle sys.dba_segments sehen darf.
Sofern man das darf, lässt man sich zunächst die Tabellen nach Relevanz, in diesem Falle die Größe, anzeigen und prüft mit gesundem Augenmaß, welche Tabelle gelöscht werden kann:
read more…

Welches genaue Datum ergibt eigentlich der Aufruf von to_date nur mit Jahreszahl als Parameter???

Neulich stoplerte ich über folgendes Code-Fragment:

v_date := to_date ( ’2012′, ‘YYYY’);

Welches genaue Datum erhält man damit? Eine spontane Umfrage unter zufällig anwesenden Kollegen ergab folgendes Ergebnis:

• 40% waren der Meinung ’01.01.2012′
• 60% wollten sich lieber nicht festlegen und haben fluchartig das Büro auf der Suche nach Kaffee verlassen

Mit folgendem einfachen 1-Zeiler ist das Ergebnis schnell ermittelt:

select to_char ( to_date ( ’2012′, ‘YYYY’), ‘DD.MM.YYYY’ ) from dual;

Das Ergebnis: ’01.09.2012′ – im August war es noch der ’01.08.2012′.

Man erhält also immer den ersten des aktuellen Monats. Die Antwort hat keiner in unserer zugegebenermassen nicht repräsentativen Umfrage gegeben. Aber gut zu wissen – für’s nächste Mal…

Benutzer-Wechsel in einem Package

Immer wieder möchte man aus einem PL/SQL-Package heraus einen Befehl absetzen, der nicht unter dem aktuellen sondern unter einem anderen Benutzer abgesetzt wird. Das ist so ohne weiteres nicht möglich, da ein Package einen Reconnect oder Benutzer-Wechsel nicht unterstützt. Hier leistet der Oracle Scheduler wertvolle Hilfe. Man kann mit ihm aus einem Package heraus einen synchronen Job aufrufen, der unter einem beliebigen Benutzer gestartet werden kann.

Hierzu ein Beispiel:

create or replace package my_utilities

is

--

-- Ausführen eines beliebigen SQL-Statements

procedure exec_with_other_user(p_sql in varchar2);

--

-- Erstellen eines Scheduler Jobs

procedure exec_sql(p_user in varchar2

                  ,p_sql  in varchar2

                  );

/

end my_utilities;

One Filter to Rule them All: Dynamische Regeln für das Business

Rules Manager und Expression Filter sind zwei sehr mächtige aber weitgehend unbekannte Bestandteile der Datenbank, die mit Oracle 10g eingeführt wurden.
Metadatengesteuert – und damit ohne Programmierung – lassen sich komplexe Regelwerke und Prüfungen erstellen.
Rulesmanager setzt auf Expression Filter auf. Durch den Einsatz dessen eigener Indextypen und SQL-Operatoren lassen sich auch sehr große Datenbestände performant bearbeiten.

Flexible Geschäftsregeln
Jedes Unternehmen hat Geschäftsregeln. IT-Systeme sind gute Gehilfen, wenn es darum geht, das Umsetzen dieser Regeln zu unterstützen und das Einhalten sicher zu stellen.

In den heutigen Zeiten verändern sich Regeln allerdings häufig so schnell, dass IT-Abteilungen mit den Anpassungen und Release-Zyklen nicht mehr nachkommen. Bis die Änderungen implementiert, getestet und abgenommen sind, haben sich die Anforderungen oftmals schon wieder verändert. Workarounds und Schatten-IT sind die Folge.

Diesen Teufelskreis kann man mit dem Einsatz von Rules Manager und Expression Filter durchbrechen.
Rules Manager ist eine generische Regel-Maschine, die Regeln über Metadaten bei Eintreffen von Ereignissen auslösen. Diese Metadaten können dann z.B. auch durch die Fachabteilung gepflegt werden, wenn ihr eine entsprechende Oberfläche zur Verfügung gestellt wird.
read more…

Sinnvolle und unsinnige Festlegungen bei Passwörtern

Spätestens beim Einrichten einer neuen Datenbank steht jeder Oracle-DBA vor der Frage, was für Passwörter er zulassen soll.

In Oracle besteht die Möglichkeit, das neue Passwort eines Benutzers durch eine selbstdefinierte Prozedur zu überprüfen, damit es nicht zu trivial ausfällt und somit nicht zu einfach erraten werden kann. Aber wie soll eine solche Prüffunktion aussehen?

Zum Glück hat Oracle bereits ein Beispiel mitgeliefert. Im Verzeichnis

“ORACLE_BASE/ORACLE_HOME/RDBMS/ADMIN”

befindet sich das Skript “UTLPWDMG.SQL”. Führt man dieses Skript mit SQL*Plus als SYSDBA aus, so werden zwei Funktionen implementiert: verify_function_11G und verify_function (für Datenbanken älter als 11G). Mit dem SQL-Befehl:

ALTER PROFILE default LIMIT
PASSWORD_VERIFY_FUNCTION verify_function_11G;

wird die Prüffunktion verify_function_11G für alle Benutzer aktiviert.
Damit müssen alle Passwörter die folgenden Kriterien erfüllen:

• Das Passwort muss eine Länge zwischen 8 und 30 Zeichen (je einschließlich) haben
• Das Passwort darf nicht identisch mit dem Benutzernamen sein, oder dem Benutzernamen rückwärts geschrieben, oder dem Benutzernamen plus Zahlen
• Ebenfalls nicht zulässig als Passwort ist der Servername sowie der Servername plus Zahl zwischen 1 und 100 (je einschließlich)
• Zu triviale Passwörter wie etwa oracle1, abcdefg1, user1234 etc. sind ebenfalls nicht erlaubt
• Das Passwort muss minde*stens einen Buchstaben und mindestens eine Zahl enthalten
• Das Passwort muss sich vom vorherigen Passwort um mindestens drei Zeichen unterscheiden.

Diese Festlegungen erscheinen alle sinnvoll. Dennoch empfehle ich, das Skript UTLPWDMG.SQL an einem Punkt zu ändern. Dies betrifft nicht
die Funktionen, sondern einen Befehl, der direkt nach der Definition der Funktion verify_function_11G ausgeführt wird. Er lautet:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
[...]

Diese beiden Parameter bewirken, dass die Benutzer alle 180 Tage ein neues Passwort vergeben müssen. Der zweite setzt eine
“Gnadenfrist” (“grace time”) von sieben Tagen für die Vergabe eines neuen Passworts.

Auf den ersten Blick erscheint dies eine sinnvolle Steigerung der Passwortsicherheit zu sein. In der Praxis zeigt sich
jedoch, damit jede Menge Nachteile einhergehen, vor allem in Kombination mit den obigen Bedingungen. So werden sich die
meisten Benutzer einfach zwei Passwörter ausdenken und zwischen diesen hin- und herschalten. Außerdem gewöhnen sich
Benutzer an ihr Passwort. Wenn sie nun gezwungen werden, es zu ändern, dann geschieht es oft, dass sie am nächsten
Arbeitstag das neue Passwort vergessen haben – und dann die Administratoren um Hilfe beim Zurücksetzen des Passworts
bitten müssen. Oder sie schreiben sich das neue Passwort auf – tun also genau das, vor dem seit Jahrzehnten alle
Sicherheitsratgeber warnen. Der Hauptnachteil jedoch ist, dass auch für reine Batch-User nun eine PASSWORD_LIFE_TIME
von 180 Tagen gilt, wie Robert Marz in seinem Artikel Stolpersteine bei der Migration auf Oracle 11g (R2) ausführt. Daher empfehle
ich, entweder das Skript UTLPWDMG.SQL so abzuändern, dass der Befehl lautet:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME UNLIMITED
[...]

oder nach Ausführung des Skripts den Befehl

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

als SYSDBA von Hand auszuführen.

Tabellengröße abfragen

Wie findet man die Größe mehrerer Tabellen heraus, die in mehreren Schemata liegen?

Die Anforderung lautete: “Ermitteln Sie doch bitte mal den Festplattenplatz den wir einsparen können, wenn wir die Tabelle “XY“ in Schema “AB“ und die Tabellen, die ich hier in dem Excelsheet notiert habe, löschen werden. Und schon hat man 68 Tabellennamen und 6 Schemata im Excelformat.

Man kann sie nun die Zeit nehmen und per GUI die Tabellen suchen, sich die Tabellengröße anzeigen lassen, diese in das Excelsheet eintragen und die Größen summieren.

Oder man hat das entsprechende Select-Grant auf die Tabelle dba_segments und das richtige Statement.

Zunächst erstellt man eine Tabelle, in die man die Schemanamen und die Tabellennamen einfügen kann:

– Tabelle mit den Schemanamen und Tabellennamen,

– die berechnet werden sollen

CREATE TABLE create_table_sizes
(
owner VARCHAR2(30 BYTE)
,table_name VARCHAR2 (81 Byte)
);

Anschließend erstellt man eine Tabelle, in der man die Ergebnisse speichert, falls man zu einem späteren Zeitpunkt noch mal darauf zugreifen können muss (z.B. wenn die Tabellen gelöscht wurden und das Excelsheet auf ominöse Art und Weise verloren gegangen ist ):

– Ergebnistabelle aus welcher die Daten
– jederzeit wieder ausgelesen werden können

CREATE TABLE table_sizes
(
owner VARCHAR2(30 BYTE)
,table_name VARCHAR2 (81 Byte)
,db_blocks NUMBER
,table_size NUMBER
,unit VARCHAR2 (8 BYTE)
,table_space VARCHAR2 (30 BYTE)
,created DATE
,createdby VARCHAR2 (40 byte)
);

Und dann wird es interessant: Die Ergebnistabelle wird gefüllt.
Dabei erfragen wir von Oracle den Schemanamen, den Tabellennamen, die Blöcke, die angeforderte Größe in der entsprechenden Einheit und speichern natürlich das Abfragedatum und den
Namen des Autors ab.

– Falls es interessant ist, wer die Statistik erstellt hat
– (das wann wird automatisch eingefügt):
– UNDEFINE created_by;
– DEFINE created_by := ‘SBROEMER’;

INSERT INTO table_sizes
(
SELECT dse.“owner”
, dse.“table” table_name
, dse.db_blocks
, ROUND(DECODE(SIGN(dse.“size”/1073741824 -1)
, -1, DECODE(SIGN(dse.“size”/1048576 -1)
, -1 , DECODE(SIGN(dse.“size”/1024 -1)
, -1, dse.“size”
, dse.“size”/1024)
, dse.“size”/1048576)
, dse.“size”/1073741824), 2) “TABLE_SIZE”
, DECODE(SIGN(dse.“size”/1073741824 -1)
, -1, DECODE(SIGN(dse.“size”/1048576 -1 )
, -1, DECODE(SIGN(dse.“size”/1024 -1)
, -1 ,‘ Byte’
, ‘ Kb’)
, ‘ Mb’)
, ‘Gb’ ) “Unit”
, dse.“tablespace” table_space
, SYSDATE
, ‘&created_by’
FROM (
SELECT ds.owner “owner”
, ds.segment_name “table”
, SUM(ds.BYTES) “size”
, ds.blocks db_blocks
, ds.tablespace_name “tablespace”
FROM c ds
, create_table_sizes dt
WHERE segment_type in (‘TABLE’)
AND ds.owner = dt.owner
AND ds.segment_name = dt.table_name
GROUP BY ds.owner, ds.segment_name, ds.blocks, ds.tablespace_name
UNION
SELECT ds.owner “owner”
, ds.segment_name “table”
, SUM(ds.BYTES) “size”
, SUM(ds.blocks) db_blocks
, ‘n/a’ “tablespace”
FROM dba_segments ds
, create_table_sizes dt
WHERE segment_type in (‘TABLE PARTITION’, ‘TABLE SUBPARTITION’)
AND ds.owner = dt.owner
AND ds.segment_name = dt.table_name
GROUP BY ds.owner, ds.segment_name
) dse
);

COMMIT;

Zum guten Schluss lässt sich das Ergebnis schön sortiert nach Schema und Tabelle ausgeben:

SELECT *
FROM table_sizes
ORDER BY owner ASC, table_name ASC;

Partitionen einer Tabelle in Oracle auslesen

Für meinen ersten Block hier wollte ich erstmal ein einfaches Thema ansprechen:
Das Auslesen von vorhanden Partitionen einer Tabelle.

Mir passierte es, dass ich bei einem Kunden saß und mir mit Toad die Partitionen einer Tabelle anschauen wollte.
Also erstmal über die GUI die Tabelle im entsprechenden Schema suchen und sich dann die Description anzeigen lassen. Anschließend ganz einfach auf den Tab “Partitions” wechseln.
Eigentlich ganz einfach. Wenn nun aber die GUI Probleme hat wieder ins Leben zurückzukehren und aufzutauen, weil bereits so viele Partitionen existieren, hört die Einfachheit auf.

Was tut man also in so einer Situation?
Richtig! SQLPLUS öffnen und per Hand abfragen:

– Für den User:
SELECT table_name
, partition_name
, partition_position
, tablespace_name
FROM user_tab_partitions
WHERE TABLE_NAME = ‘&P_TABLE’
ORDER BY PARTITION_POSITION DESC;

– Mit Blick auf die dba_tab_partitions:
SELECT table_owner
, table_name
, partition_name
, partition_position
, tablespace_name
FROM dba_tab_partitions
WHERE TABLE_NAME = ‘&P_TABLE’
ORDER BY PARTITION_POSITION DESC;

listagg

In Oracle 11gRel2 gibt es eine neue Gruppierungsfunktion das LISTAGG. Mit dieser Funktion ist man in der Lage, Spaltenwerte einer Wiederholungsgruppe in einer Zeile zusammenzufassen getrennt durch ein definiertes Trennzeichen.

Beispiel:

Inhalt der Tabelle Team:

GRUPPE               NAME

==================== ============ 

Team Blau            Harald

Team Blau            Robert

Team Blau            Thomas

Team Gelb            Alex

Team Gelb            Sabine

Team Rot             Angelika

Team Rot             Uwe

 Erzeugen einer Mitgliederliste pro Team:

select    distinct

          gruppe

         ,listagg(name,’;')

          within group (order by name asc)

          over (partition by gruppe) as mitglieder

from      teams

order by  gruppe

;

Ergebnis:

GRUPPE               MITGLIEDER

==================== ==============================

Team Blau            Harald;Robert;Thomas

Team Gelb            Alex;Sabine

Team Rot             Angelika;Uwe

Die Wiederholungsgruppe wird über die Klausel PARTITION BY <COLUMN> angegeben, die Sortierung innerhalb der Liste über die Klausel ORDER BY <COLUMN>. In der Funktion LISTAGG bestimmt die erste Angabe die Spalte, die als Liste zusammengefasst werden soll, die zweite Angabe das Trennzeichen.

SQL Zeilengenerator

Letzte Woche bin ich während eines Vortrages über folgenden Code-Schnipsel gestolpert:

select rownum ident
  from dba_objects
 where rownum <=500;

Mal eben ein paar Zeilen erzeugen ist eine Standardanforderung, der sich jeder SQL-Entwickler von Zeit zu Zeit stellen muss. Egal ob ein paar Demo-Zeilen erzeugt werden müssen oder für die Basis eines Outerjoins die vollständige Liste aller Tage in einem Zeitraum benötigt werden, Anwendungsmöglichkeiten gibt es viele.

Erschreckend häufig werden zu diesem Zweck Abfragen auf System-Views durchgeführt.

Schauen wir es uns einmal genauer an:
Dieses Statement liefert 500 Zeilen zurück. Das ist aber auch schon alles, was man ihm positives unterstellen kann.

Zunächst einmal wird hier auf eine DBA-View zugegriffen, auf die ein normales Schema ohne DBA-Berechtigungen gar keinen Zugriff hat. Das Verwenden von ALL_OBJECTS hilft dem zwar ab, aber kann man sich immer sicher sein, dass jeder Benutzer mehr als 500 Objekte im Zugriff hat?

Außerdem ist DBA_OBJECTS eine System-View, die auf verschiedene andere Views und Tabellen zugreift. Wenn man mal nachschaut, was man der Datenbank für diese einfache Aufgabe abverlangt, bekommt man das bestätigt:

select rownum id
  from dba_objects
 where rownum <=500;

-----------------------------------------------------------------------------------------------

| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |             |   500 |       |     8  (13)| 00:00:01 |

|*  1 |  COUNT STOPKEY                  |             |       |       |            |          |

|   2 |   VIEW                          | DBA_OBJECTS |   500 |       |     8  (13)| 00:00:01 |

|   3 |    UNION-ALL                    |             |       |       |            |          |

|*  4 |     FILTER                      |             |       |       |            |          |

|*  5 |      HASH JOIN                  |             |   533 |   154K|     7  (15)| 00:00:01 |

|   6 |       INDEX FULL SCAN           | I_USER2     |    87 |   348 |     1   (0)| 00:00:01 |

|*  7 |       HASH JOIN                 |             |   533 |    97K|     6  (17)| 00:00:01 |

|   8 |        INDEX FULL SCAN          | I_USER2     |    87 |  1914 |     1   (0)| 00:00:01 |

|*  9 |        TABLE ACCESS FULL        | OBJ$        |   534 | 44322 |     4   (0)| 00:00:01 |

|* 10 |      TABLE ACCESS BY INDEX ROWID| IND$        |     1 |     8 |     2   (0)| 00:00:01 |

|* 11 |       INDEX UNIQUE SCAN         | I_IND1      |     1 |       |     1   (0)| 00:00:01 |

|  12 |      NESTED LOOPS               |             |     1 |    29 |     2   (0)| 00:00:01 |

|* 13 |       INDEX FULL SCAN           | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |

|* 14 |       INDEX RANGE SCAN          | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |

|  15 |     NESTED LOOPS                |             |     1 |    17 |     1   (0)| 00:00:01 |

|  16 |      INDEX FULL SCAN            | I_LINK1     |     1 |    13 |     0   (0)| 00:00:01 |

|* 17 |      INDEX RANGE SCAN           | I_USER2     |     1 |     4 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Dabei liefert Oracle-SQL doch einen eigenen Generator mit:

select rownum id
  from dual
connect by rownum <= 500;

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT                        |      |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Diese wenig bekannte Anwendung der hierarchischen Abfrage ist schlank, schön und vor allem viel billiger.
Funktionieren tut das seit mindestens Oracle 9i – frühere Datenbankversionen zum Überprüfen habe ich leider zur Zeit nicht zur Hand.

Leider hat es die Oracle-Schreibweise für hierarchische Abfragen nicht in den ANSI-Standard geschafft, sodass man diese Art Abfragen in den meisten anderen Datenbanken nicht ausführen kann.

Für hierarchische Abfragen sieht ANSI SQL-99 rekursives “subquery refactoring with with-clause” vor.

Komplexe SQL-Abfragen durch with-Blöcke zu vereinfachen, beherrscht Oracle schon seit mindestens Oracle 9i. Die rekursive Aufruf-Erweiterung ist ein neues Feature der 11gR2, sodass man hierarchische Abfragen jetzt auch ANSI-konform formulieren kann:

with
 generator (id)
 as ( select 1 id from dual
      union all
      select id+1 from generator
      where id < 500 )
select id from generator;

--------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |      |     2 |    26 |     4   (0)| 00:00:01 |
|   1 |  VIEW                                     |      |     2 |    26 |     4   (0)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|      |       |       |            |          |
|   3 |    FAST DUAL                              |      |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    RECURSIVE WITH PUMP                    |      |       |       |            |          |
--------------------------------------------------------------------------------------------------

Das ist fast so billig und beinahe genauso schön wie die Connect-by-Variante.

Durch das für die Rekursion nötige UNION ALL schränkt man bei dieser Variante auf “< 500″ ein, im Gegensatz zu den “<= 500″ bei dem Connect-by