/*
-- This tip was inspired by a posting on the OTN Database General forum,
-- where I am known to hang out and attempt to answer questions.
--
-- Creating new users is a common DBA requirement and sometimes a user needs to be created
-- as a clone of another user. The easiest way to do this is using the export and import
-- utilities by doing a fromuser export and a touser import. Naturally the downside of this
-- technique is the writing of the export file to disk which takes up space and wastes time.
-- The thread starter wanted to know if there was another way to do this.
--
-- However in 10g the new Datapump utility that replaces the old fashioned exp/imp has the
-- ability to do imports over a database link, which means that no file needs to be written to.
--
-- The trick to the technique is knowing that a database link can connect to the same database
-- the user is connected to; this technique is known as a loopback database link.
-- An import Datapump job can then be opened over the link and then it's just a matter of
-- specifying the remapping of the schema name. The import flows through the link requiring no
-- dump file to be written. Since it's optional to even create a log file for the operation,
-- no files need to be written at all. The end result is a much faster technique that requires
-- no external diskspace.
--
-- Testing showed that the user created was an exact replica, right down to the password hash,
-- requiring a change of password as the new user would not be able to login.
--
-- The code below is just a demonstration illustrating a minimal stored procedure
-- implementation that can be executed to clone a user from another user.
-- The code works as intended and has been tested on 10.1.0.5 and 10.2.0.3 but does not
-- include:
-- 1) input sanity checking (Datapump produces generic unhelpful messages
-- if any of the inputs isn't valid or the database link does not work)
-- 2) exception handling (exceptions generated during the import are also generic and
-- unhelpful, details must be obtained using dbms_datapump.get_status)
-- 3) checking for successful completion after the job has finshed
-- 4) atomicity (if the import is not successful the user should really be dropped)
-- Lastly the procedure should be invokers rights not definers as befits a utility.
-- All this is left as an exercise for the reader!
--
-- With thanks to Jim Phillips, the poster of the thread that inspired the code and
-- this tip.
--
*/

create public database link loopback
using
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&your_DB_service_name)))'
;




CREATE OR REPLACE PROCEDURE copy_schema (
source_schema in varchar2,
destination_schema in varchar2,
new_password in varchar2,
network_link in varchar2 default 'loopback'
) as
h number; -- job handle
js user_datapump_jobs.state%type; -- to hold job status
q varchar2(1) := chr(39); -- single quote

BEGIN

/* open a new schema level import job using our loopback DB link */
h := dbms_datapump.open (
'IMPORT',
'SCHEMA',
network_link
);

/* make any data copied consistent with respect to now */
dbms_datapump.set_parameter(h, 'FLASHBACK_SCN', dbms_flashback.get_system_change_number);

/* restrict to the schema we want to copy */
dbms_datapump.metadata_filter (
h,
'SCHEMA_LIST',
q||source_schema||q
);

/* remap the importing schema name to the schema we want to create */
dbms_datapump.metadata_remap(h,'REMAP_SCHEMA',source_schema,destination_schema);

/* start the job */
dbms_datapump.start_job(h);

/* wait for the job to finish */
dbms_datapump.wait_for_job(h, js);

/* change the password as the new user has the same password hash as the old user,
which means the new user can't login! */
execute immediate 'alter user '||destination_schema||' identified by '||new_password;

end;
/