Duplizieren eines Oracle-Schemas mit Datapump

Sie möchten einen Datenbank-Benutzer inklusive seiner Objekte und Daten kopieren? Verwendet man Oracle Export / Import für das Kopieren eines Datenbank-Benutzers, so wird zunächst eine Datei ins Dateisystem geschrieben, die anschließend in ein neues Schema importiert wird. Ein Beispiel finden Sie undefinedhier.

Ab Oracle Database 10g Release 1 ist der Zwischenschritt über den Export in eine Datei nicht mehr erforderlich. Mit Oracle Data Pump kann über einen Datenbank Link ein Schema einer anderen Datenbank in die eigene importiert werden. Diese Technik können Sie auch nutzen, um innerhalb derselben Datenbank ein Schema zu kopieren. Dazu erstellen Sie einen Loopback Database Link, der auf die eigene Datenbank verweist. Die Prozedur lehnt sich im Wesentlichen an die von Externer LinkChristopher Poole beschriebene an.

Zunächst erstellen Sie einen Datenbank Link:

Oracle-Datenbanken: Erstellen eines Datenbank Links 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 Database 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:

Oracle-Datenbanken: Erstellen eines Datenbank Links
 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@loopback
   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 Prigvileg alter user. Auf Letzteres kann dann verzichtet werden, wenn Codezeile 50 ff. (Ändern des Kennwortes des neu erstellten Benutzers) entfernt oder auskommentiert wird.

Oracle-Datenbanken: Schema kopieren mit Oracle Datapump
  1. CREATE OR REPLACE PROCEDURE kopiere_schema
  2. (
  3.    i_quell_schema   IN VARCHAR2,
  4.    i_ziel_schema    IN VARCHAR2,
  5.    i_neues_passwort IN VARCHAR2,
  6.    i_database_link  IN VARCHAR2 DEFAULT 'loopback.world'
  7. ) AS
  8.    -- Job Handle
  9.    l_job_handle    NUMBER;                        
  10.     -- Job Status
  11.    l_job_status    user_datapump_jobs.state%TYPE;
  12.    -- Single Quote
  13.    quote           VARCHAR2(1) := chr(39);        
  14.  
  15. BEGIN
  16.  
  17.    -- Erstellen eines Import-Jobs
  18.    l_job_handle := dbms_datapump.open  (
  19.       'IMPORT',
  20.       'SCHEMA', 
  21.       i_database_link);
  22.  
  23.    -- Um konsistent zu kopieren, wird die aktuelle
  24.    -- System Change Number ermittelt und geesetzt
  25.    dbms_datapump.set_parameter (
  26.        l_job_handle,
  27.        'FLASHBACK_SCN',
  28.        dbms_flashback.get_system_change_number);
  29.  
  30.    -- Einschraenkung auf das zu kopierende Schema
  31.    dbms_datapump.metadata_filter (
  32.       l_job_handle,
  33.       'SCHEMA_LIST', 
  34.       quote || i_quell_schema || quote);
  35.  
  36.    -- Remapping auf das neue Schema
  37.    dbms_datapump.metadata_remap(
  38.       l_job_handle,
  39.       'REMAP_SCHEMA',
  40.        i_quell_schema,
  41.        i_ziel_schema);
  42.  
  43.    -- Start des Jobs
  44.    dbms_datapump.start_job(l_job_handle);
  45.  
  46.    -- Auf Ausfuehrungsende warten
  47.    dbms_datapump.wait_for_job(l_job_handle, l_job_status);
  48.  
  49.    -- Aendern des Passwortes fuer den neuen Schema-Benutzer
  50.    EXECUTE IMMEDIATE 
  51.       ' ALTER USER '|| i_ziel_schema||
  52.       ' IDENTIFIED BY '|| i_neues_passwort;
  53. END;
  54. /

 

Der Aufruf der Prozedur kann nun wie folgt aussehen:

Oracle-Datenbanken: Aufruf der Prozedur zur Schema-Kopie
BEGIN
   kopiere_schema ('SCOTT', 'noch_ein_scott', 'tricksi');
END;
/