16

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?

Chris Farmer
  • 24,974
  • 34
  • 121
  • 164
user3209595
  • 171
  • 1
  • 1
  • 3

3 Answers3

28

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.

Community
  • 1
  • 1
mmmmmpie
  • 2,908
  • 1
  • 18
  • 26
  • That worked for me, although I had some struggle with permissions that I eventually resolved by running the commands with a sysdba login - the schemas / remap_schema parameters still tell what to export/import. The directory param can be omitted when the default is ok for you, see http://www.orafaq.com/wiki/Datapump#Create_database_directories. – Matthias Sep 16 '16 at 20:56
  • @Matthias Do not EXP/IMP as SYS. :) – mmmmmpie Sep 17 '16 at 12:26
  • In the above example where you run the impdp command is it assumed that you have already created the new 'toUser' schema? Or will the command take care of that? – u123 Jul 09 '19 at 12:56
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.

tstenner
  • 10,080
  • 10
  • 57
  • 92
Rooie3000
  • 459
  • 5
  • 9
1

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
PavelPraulov
  • 589
  • 6
  • 18