—————————————————————————————————
— Datapump clone schema to another DB
—————————————————————————————————
—————————————————————————————————
— EXPORT:
—————————————————————————————————
CREATE OR REPLACE DIRECTORY expimp_dir AS ‚/home/oracle/dmp‘;
GRANT READ,WRITE ON DIRECTORY expimp_dir TO myschema;
grant dba to myschema;
–shell-command:
expdp myschema/myschema schemas=myschema directory=expimp_dir dumpfile=myschema_expdp.dmp logfile=myschema_expdp.log
revoke dba from myschema;
revoke READ,WRITE ON DIRECTORY expimp_dir from myschema;
—————————————————————————————————
— IMPORT on a different machine!
—————————————————————————————————
— create schema on new DB
set serveroutput on
DECLARE CONFLICT_WITH_ANOTHER_USER EXCEPTION;
PRAGMA EXCEPTION_INIT (CONFLICT_WITH_ANOTHER_USER, -01920); — -01920: user name ‚myschema‘ conflicts with another user or role name
BEGIN
EXECUTE IMMEDIATE ‚CREATE USER myschema identified by myschema‘;
EXCEPTION WHEN CONFLICT_WITH_ANOTHER_USER THEN DBMS_OUTPUT.PUT_LINE(‚OK: User/Schema already existing‘);
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/
grant create session to myschema;
CREATE OR REPLACE DIRECTORY expimp_dir AS ‚/home/oracle/dmp‘;
GRANT READ,WRITE ON DIRECTORY expimp_dir TO myschema;
grant dba to myschema;
–shell-command:
impdp myschema/myschema schemas=myschema directory=expimp_dir dumpfile=myschema_expdp.dmp logfile=myschema_impdp.log
revoke dba from myschema;
revoke READ,WRITE ON DIRECTORY expimp_dir from myschema;
— Info:
Job „myschema“.“SYS_IMPORT_SCHEMA_01″ completed with 1 error(s) at Wed Jan 7 13:10:17 2015 elapsed 0 00:00:06
comes from:
ORA-31684: Object type USER:“myschema“ already exists.
So: everything o.k.!