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.
Publikationen
- Oracle 12c New Features: Inklusive Release 2
- Der Oracle DBA: Handbuch für die Administration der Oracle Database 12c
- Oracle 11g. Neue Features für Administratoren und Entwickler
- Oracle 10g Hochverfügbarkeit. Die ausfallsichere Datenbank mit RAC, Data Guard und Flashback Edition Oracle
