Wenn Sie mal wieder eine Migration vorhaben und möchten das mittels Oracle Export und Import (expdp / impdp) durchführen haben sie die Alternativen, einen Full Export oder einen Schema Export durchzuführen.
Wenn Sie einen Schema Export durchführen:
expdp userid=system/Marco13# full=y directory=data_pump_dir dumpfile=full_exp_befor_mig.dmp consistent=y INCLUDE=SCHEMA:\"IN \(\'SCOTT\',\'HR\',\'PROD_SCHEMA\'\)\"
Man beachte die lustige Syntax (Entwerten der Hochkommata und Klammern) für eine Linux Installation.
Das Problem ist, das Oracle die Erstellungsbefehle für den Benutzer (CREATE USER) nicht in den Export mit integriert.
Aber das bekommen wir hin:
Wir lassen auf der Quelldatenbank den folgenden Befehl laufen:
Ab Version 12c:
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
WITH
FUNCTION get_ddl(ddl_typ in VARCHAR2, uname IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
IF ddl_typ like '%GRANT' THEN
RETURN to_char(DBMS_METADATA.GET_GRANTED_DDL(ddl_typ,uname));
ELSE
RETURN to_char(DBMS_METADATA.GET_DDL(ddl_typ,uname));
END IF;
EXCEPTION WHEN OTHERS THEN
IF sqlcode=-31608 THEN RETURN NULL; ELSE RAISE; END IF;
END;
u AS (SELECT 'SCOTT' as name FROM dual) --<======####### Bitte ändern
SELECT get_ddl('USER',u.name) from u
UNION ALL
SELECT get_ddl('OBJECT_GRANT',u.name) from u
UNION ALL
SELECT get_ddl('SYSTEM_GRANT',u.name) from u
UNION ALL
SELECT get_ddl('ROLE_GRANT',u.name) from u;
Bitte setzen sie an der Stelle mit SCOTT Ihre jeweiligen Benutzernamen an.
Vor Version 12c hat man ein Problem, wenn der Benutzer keine Rollen, Objektrechte, oder keine Systemrechte besessen hat, weil dann stürzt der Select ab.
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
WITH U AS (SELECT 'MUNIQ' AS NAME FROM DUAL)(
SELECT TO_CHAR(DBMS_METADATA.GET_DDL('USER',U.NAME)) FROM U,DUAL
UNION ALL
SELECT CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',u.name)) end
FROM u,(select count(*) as cnt from u,all_tab_privs where grantee=u.name)
UNION ALL
SELECT CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',u.name)) end
FROM u,(select count(*) as cnt from u,dba_sys_privs where grantee=u.name)
UNION ALL
SELECT CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',u.name)) end
FROM u,(SELECT count(*) as cnt FROM u,dba_role_privs where grantee=u.name)
UNION ALL
select to_char(dbms_metadata.get_ddl('SYNONYM',synonym_name,owner))
from u,dba_synonyms where table_owner=u.name);
Bei einer Fehlermeldung ist die Lösung hier, den Select um den Bereich zu kürzen, der keine Ergebnismenge zurückgeliefert hat.
Wenn der Benutzer noch Public Synonyme hatte, kann man die auch wieder aus der Datenbank herausholen.
SELECT s.*,dbms_metadata.get_ddl('SYNONYM',synonym_name,'PUBLIC')
FROM dba_synonyms s
where owner='PUBLIC'
AND table_owner in ('SCOTT');
Wenn der Benutzer auch Database Links besitzt, können auch diese wieder als Skript erzeugt werden:
SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link)
FROM user_db_links; -- als Eigentümer des DB Links
oder als SYS:
SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link, owner)
FROM dba_db_links
WHERE owner IN ('SCOTT','HR');
Da kommt dann z.B. raus:
CREATE DATABASE LINK "TEST_DB_LINK.MUNIQSOFT.DE"
CONNECT TO "SCOTT" IDENTIFIED BY VALUES ':1'
USING '173.20.20.1:1521/o19c';
Das Passwort wird aus Sicherheitsgründen seit einiger Zeit hier nicht mehr mit ausgegeben und muss von Hand eingetragen werden (an der Stelle :1).