First create a database link;CREATE DATABASE LINK loopback CONNECT TO tomasz IDENTIFIED BY dbaora USING 'PORA12C1.dbaora.com';then create procedure ; CREATE OR REPLACE PROCEDURE pro_copy_schema ( in_srce_user IN VARCHAR2, in_dest_user IN VARCHAR2, in_dest_user_pswd IN VARCHAR2, in_paral_lvl IN NUMBER DEFAULT 8, in_netwk_link IN VARCHAR2 DEFAULT 'loopback', in_incld_rows IN NUMBER DEFAULT 1 ) AUTHID CURRENT_USER AS l_hnd NUMBER; -- job handle l_js user_datapump_jobs.state%TYPE; -- to hold job status l_q VARCHAR2(1) := chr(39); -- single quote BEGIN /* open a new schema level import job using our loopback DB link */ l_hnd := dbms_datapump.open ('IMPORT','SCHEMA', in_netwk_link); /* set parallel level */ dbms_datapump.set_parallel(handle => l_hnd, degree => in_paral_lvl); /* make any data copied consistent with respect to now */ dbms_datapump.set_parameter (l_hnd, 'FLASHBACK_SCN', dbms_flashback.get_system_change_number); /* restrict to the schema we want to copy */ dbms_datapump.metadata_filter (l_hnd, 'SCHEMA_LIST', l_q || in_srce_user || l_q); /* remap the importing schema name to the schema we want to create */ dbms_datapump.metadata_remap (l_hnd,'REMAP_SCHEMA',in_srce_user,in_dest_user); /* copy_data for each table or not 1 - yes 0 - meta data only */ dbms_datapump.data_filter (l_hnd,'INCLUDE_ROWS',in_incld_rows,NULL,NULL); /* start the job */ dbms_datapump.start_job(l_hnd); /* wait for the job to finish */ dbms_datapump.wait_for_job(l_hnd, l_js); /* change the password for new user */ EXECUTE IMMEDIATE 'ALTER USER ' || in_dest_user || ' IDENTIFIED BY ' || in_dest_user_pswd; END; /
and then execute ;
BEGIN pro_copy_schema ( in_srce_user => 'SRC_USER', in_dest_user => 'DST_USER', in_dest_user_pswd => 'dst_user_passwd', in_paral_lvl => 8, in_netwk_link => 'LOOPBACK', in_incld_rows => 1 ); END; /
Source : http://dbaora.com/oracle-clone-schema-using-plsql/
