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