Über unsMediaKontaktImpressum
Andrea Held 02. August 2016

Oracle Database: So können Sie Benutzer kopieren und ein Schema umbenennen

Sie arbeiten mit Oracle-Datenbanken und möchten einen Benutzer umbenennen? Oder auch nur ein Benutzer-Schema mit allen darin enthaltenen Objekten wie Tabellen, Indizes, Prozeduren und Jobs kopieren? Leider bietet Oracle keinen Befehl wie copy schema oder copy user. Ein rename schema oder rename user gibt es auch nicht.

Es gibt aber einen Trick 17: Mit Data Pump kann man einen Workaround bauen: Oracle Data Pump kann über einen Datenbank-Link einen Benutzer bzw. ein Schema einer anderen Datenbank in die eigene importieren. Diese Technik können Sie auch nutzen, um innerhalb derselben Datenbank einen Benutzer bzw. ein Schema zu kopieren. Dazu erstellen Sie einen Loopback Database-Link, der auf die eigene Datenbank verweist.

Zunächst erstellen Sie also einen passenden Datenbank-Link als Loop:

-- Erstellen des Loopback Database Link
CREATE DATABASE LINK "LOOPBACK.WORLD"
USING
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mein_service_name)))';

Achten Sie darauf, dass Sie den Parameter service_name beim Erstellen des Datenbank-Links anpassen.

Testen Sie Ihren Datenbank-Link vorab nochmals, indem Sie eine Abfrage wie

   SELECT * 
   FROM   all_tables@loopback.world
   WHERE  rownum <= 10;

absetzen. Hier darf keine Fehlermeldung auftreten. Ist der Schalter global_names Ihrer Datenbank auf TRUE gesetzt, so tritt etwa folgende Fehlermeldung auf:

   ORA-02085: database link LOOPBACK.WORLD connects to HO_DB.WORLD

In diesem Fall ist es erforderlich, dass der Name des Datenbank-Links dem der Datenbank entspricht. Ein Beispiel:

 CREATE DATABASE LINK "HO_DB.WORLD@loopback"
 USING
 '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=HO_DB.WORLD.world)))

Die Prüfung der Funktionsfähigkeit des Datenbank-Links erfolgt dann entsprechend mit:

   SELECT * 
   FROM    all_tables@HO_DB.WORLD
   WHERE   rownum <= 10;

Funktioniert der Datenbank-Link, kann die Prozedur implementiert werden. Der Benutzer, unter dessen Namen die Prozedur implementiert wird, benötigt Ausführungsrechte auf die Datenbank-Packages dbms_datapump und dbms_flashback sowie das Privileg alter user. Auf Letzteres kann dann verzichtet werden, wenn Codezeile 50 ff (Ändern des Kennwortes des neu erstellten Benutzers) entfernt oder auskommentiert wird. Gerade unter Sicherheitsaspekten ist es sinnvoll, das alter user-Privileg möglichst sparsam zu vergeben.

Die Prozedur zum Kopieren eines Schemas, kann dann in etwa wie folgt aussehen:

Oracle-Datenbanken: Schema kopieren mit Oracle Data Pump

CREATE OR REPLACE PROCEDURE copy_schema
(
   i_quell_schema   IN VARCHAR2,
   i_ziel_schema    IN VARCHAR2,
   i_neues_passwort IN VARCHAR2,
   i_database_link  IN VARCHAR2 DEFAULT 'loopback.world'
) AS
   -- Job Handle
   l_job_handle    NUMBER;                        
    -- Job Status
   l_job_status    user_datapump_jobs.state%TYPE;
   -- Single Quote
   quote           VARCHAR2(1) := chr(39);        
 
BEGIN
 
   -- Erstellen eines Import-Jobs
   l_job_handle := dbms_datapump.open  (
      'IMPORT',
      'SCHEMA',
      i_database_link);
 
   -- Um konsistent zu kopieren, wird die aktuelle
   -- System Change Number ermittelt und geesetzt
   dbms_datapump.set_parameter (
       l_job_handle,
       'FLASHBACK_SCN',
       dbms_flashback.get_system_change_number);
 
   -- Einschraenkung auf das zu kopierende Schema
   dbms_datapump.metadata_filter (
      l_job_handle,
      'SCHEMA_LIST',
      quote || i_quell_schema || quote);
 
   -- Remapping auf das neue Schema
   dbms_datapump.metadata_remap(
      l_job_handle,
      'REMAP_SCHEMA',
       i_quell_schema,
       i_ziel_schema);
 
   -- Start des Jobs
   dbms_datapump.start_job(l_job_handle);
 
   -- Auf Ausfuehrungsende warten
   dbms_datapump.wait_for_job(l_job_handle, l_job_status);
 
   -- Aendern des Passwortes fuer den neuen Schema-Benutzer
   EXECUTE IMMEDIATE
      ' ALTER USER '|| i_ziel_schema||
      ' IDENTIFIED BY '|| i_neues_passwort;
END;

Der Aufruf der Prozedur kann nun wie folgt aussehen:

Oracle-Datenbanken: Aufruf der Prozedur zur Schema-Kopie

BEGIN
   copy_schema ('SCOTT', 'noch_ein_scott', 'tricksi');
END;
/

Benutzernamen ändern: rename schema und rename user

Oracle-Datenbanken bieten keine reguläre Möglichkeit, ein Schema bzw. einen Benutzer umzubenennen. Es gibt "dreckige" Hacks des Data Dictionary. Die funktionieren auch, aber ob das mit den folgenden Releases der Datenbank so noch funktioniert, ist nicht sicher. Und wichtiger: Die Datenbank fällt – wenn Sie das Dictionary hacken – aus dem Support. Daher ist von solchen Lösungen abzuraten.

Eine andere – und reguläre Möglichkeit – ist, die obenstehende Prozedur zu erweitern. Man hängt einfach ein Drop User an. Beispielsweise:

EXECUTE IMMEDIATE 'DROP USER ' || i_quell_schema || ' cascade';

Damit wird das Quellschema entfernt.

CREATE OR REPLACE PROCEDURE rename_schema
(
   i_quell_schema   IN VARCHAR2,
   i_ziel_schema    IN VARCHAR2,
   i_neues_passwort IN VARCHAR2,
   i_database_link  IN VARCHAR2 DEFAULT 'loopback.world'
) AS
   -- Job Handle
   l_job_handle    NUMBER;                        
    -- Job Status
   l_job_status    user_datapump_jobs.state%TYPE;
   -- Single Quote
   quote           VARCHAR2(1) := chr(39);        
 
BEGIN
 
   -- Erstellen eines Import-Jobs
   l_job_handle := dbms_datapump.open  (
      'IMPORT',
      'SCHEMA',
      i_database_link);
 
   -- Um konsistent zu kopieren, wird die aktuelle
   -- System Change Number ermittelt und geesetzt
   dbms_datapump.set_parameter (
       l_job_handle,
       'FLASHBACK_SCN',
       dbms_flashback.get_system_change_number);
 
   -- Einschraenkung auf das zu kopierende Schema
   dbms_datapump.metadata_filter (
      l_job_handle,
      'SCHEMA_LIST',
      quote || i_quell_schema || quote);
 
   -- Remapping auf das neue Schema
   dbms_datapump.metadata_remap(
      l_job_handle,
      'REMAP_SCHEMA',
       i_quell_schema,
       i_ziel_schema);
 
   -- Start des Jobs
   dbms_datapump.start_job(l_job_handle);
 
   -- Auf Ausfuehrungsende warten
   dbms_datapump.wait_for_job(l_job_handle, l_job_status);
 
   -- Entfernen des Quellschemas
   EXECUTE IMMEDIATE 'DROP USER ' || i_quell_schema || ' cascade';

END;
/

Fazit

Testen Sie umfassend, bevor Sie so etwas einsetzen. Am besten nutzen Sie ein solches Rename nur in Entwicklungs- und Testumgebungen, nicht in der produktiven Datenbank.

Ich persönlich ziehe es vor, zunächst ein Copy auszuführen, danach zu prüfen, ob auch alles ordnungsgemäß ist und abschließend manuell das Originalschema zu löschen. Warum? Weil es manchmal mit Export/Import auch Probleme gibt. Der Export und Import von komprimierten lokalen Indizes auf eine Tabellenpartition hatte in früheren Releases einen Bug. Bei Sonderlocken, wie Queue-Tables für Advanced Queueing, gab es auch schon Probleme. Und dann ist es nicht schön, wenn das Original weg ist, die neue Version aber nicht funktioniert.

Für eine Entwicklungsumgebung kann die obige rename_schema-Prozedur aber eine gute Lösung sein. Erstellen Sie dennoch immer ein aktuelles Backup Ihrer Datenbank vor dem Einsatz. 

Autorin

Andrea Held

Andrea Held ist technische Architektin und Autorin von Fachartikeln und Büchern, darunter "Der Oracle DBA", "Oracle New Features" und "Oracle Hochverfügbarkeit". Ihr Schwerpunkt: Hochverfügbare Datenbanksysteme.
>> Weiterlesen
Bücher der Autorin:

botMessage_toctoc_comments_9210