How to create copy of full schema on same database in oracle

Question

I want to create full copy of the schema on the same database. What is the best technique to do that?

I have few ideas in mind:

1. Datapump Over Db link
2. IMPDP on network link
3. EXPDP and then IMPDP.

What would be the advantages and/or drawbacks of each technique?

Answer 1

You don't need a fancy network link or anything just the expdp/impdp commands. First export the schema that you want:

expdp fromUser/fromPword schemas=sourceSchema directory=DUMPDIR dumpfile=dump.dmp logfile=explog.txt

Tip: make sure that that user has the ability to write to DUMPDIR or whatever it is you call it

Then reimport the schema using the remap_schema parameter:

impdp toUser/toPword directory=DUMPDIR dumpfile=dump.dmp logfile=implog.txt remap_schema=fromUser:toUser

If you really want to use the network_link parameter (so you don't create a dumpfile for some reason) then I recommend reading this or this.
Great info on your question is found here also.

Answer 2

Try this:

Copy schema into same database

I tested and it worked. but you have to create a public link like mentioned in the comments.

If you create a .par file which remaps your tablespaces you can reuse it. With some search replace of course.

O and don't forget to put the transaform in the .par file:

TRANSFORM=oid:n

Otherwise you might get errors.

Answer 3

The steps I did to copy schema's content from schema user_a to schema user_b in Oracle: DBMS is located inside an Oracle Linux Server container.

let's assume the schemas are defined like that:

create user user_a identified by user_a_pass default tablespace tablespace_a;
grant
    create materialized view,
    create procedure,
    create sequence,
    create session,
    create table,
    create type,
    create trigger,
    create view to user_a;

create user user_b identified by user_b_pass default tablespace tablespace_a;
grant
    create materialized view,
    create procedure,
    create sequence,
    create session,
    create table,
    create type,
    create trigger,
    create view to user_b;

The important point here: the schemas have access to the same tablespace and have create session privilege.

in order to copy, data schemas' directory are required: check granted directory:

select tp.grantee, tp.table_name from all_tab_privs tp where tp.privilege = 'WRITE' and tp.type = 'DIRECTORY';

check that the directory's path phisically exists and a group dba has an access to that.

if directory is not assigned to the schema user, do that:

create or replace directory user_dir as '/opt/oracle/product/19c/dbhome_1/user_dir_name';
grant read, write on directory user_dir to user_a;
grant read, write on directory user_dir to user_b;

run export:

expdp user_a/user_a_pass@host/database schemas=user_a directory=user_dir dumpfile=user_a.dmp logfile=schema_exp.log

run import:

impdp user_b/user_b_pass@host/database directory=user_dir dumpfile=user_a.dmp logfile=schema_imp.log remap_schema=user_a:user_b