Skip to Main Content
  • Questions
  • Create table replicate along with dependents in different schema

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: March 11, 2019 - 4:18 am UTC

Last updated: March 28, 2019 - 11:36 am UTC

Version: 18c

Viewed 10K+ times! This question is

You Asked

Hi Chris/Connor,
I have one requirement where we need to create a replica of table in different schema and different table space (schema and table space will be inputs to the scripts)in same database server..
This we need to do using plsql only so imp/exp won’t work.
I tried using dbms_metada but unfortunately it’s getting complex as table has partitions/subpartitions/indexes/ref constraints/triggers..
Could you please suggest some methods to do this using plsql?

Database we are using 18c
Thank you!

and Chris said...

You could do a network data pump import to copy the table.

By using a loopback database link (one that refers back to the same database), you can use the data pump API clone a table.

So you can build a PL/SQL wrapper that allows you to transform it as needed:

create or replace procedure copy_table ( 
  dest_schema varchar2,
  table_list  varchar2
) as
  hand number;
begin
  hand := dbms_datapump.open (
    operation   => 'IMPORT',
    job_mode    => 'TABLE',
    remote_link => 'DBS1'
  );

  dbms_datapump.add_file (
    handle    => hand,
    filename  => 'impdp_test.log',
    directory => 'TMP', 
    filetype  => dbms_datapump.ku$_file_type_log_file
  );

  dbms_datapump.metadata_filter (
    handle => hand,
    name   => 'NAME_LIST',
    value   => table_list
  );

  dbms_datapump.metadata_remap(
    handle    => hand, 
    name      => 'REMAP_SCHEMA', 
    old_value => user, 
    value     => upper ( dest_schema ) 
  ); 

  dbms_datapump.start_job ( hand );

end;
/ 

drop user u cascade;

grant unlimited tablespace to 
  u identified by u;

create table t (
  c1 int
);

insert into t values ( 1 );

create index i on t ( c1 );

exec copy_table ( 'U', '''T''' );

-- Wait a while
select object_name, object_type 
from   dba_objects
where  owner = 'U';

OBJECT_NAME   OBJECT_TYPE   
T             TABLE         
I             INDEX     

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBMS_METADAT.PUT

A reader, March 26, 2019 - 4:26 pm UTC

Hi Chris,
Thanks for your reply.
I have create an objects using dbms_metadata.put succesfully..
However when i executed dbms_metadata.put in parallel chunks using dbms_parallel_execute, it always gets rejected saying insufficient privileges for DBMS_SQL (user hase direct execute grants on DBMS_SQL).
Strangely, when i run the same dbms_metadata.put in SQL developer single session it gets created successfully..
is there any restriction of executing .put in jobs?

Chris Saxon
March 28, 2019 - 11:36 am UTC

What exactly are you doing? Show us your code!

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.