Duplicate table over database link

The old-style approach would be CREATE TABLE T AS SELECT * FROM T@L, where T is the table you want to duplicate and L the database link pointing to the remote database

If you want to keep more info about the structure of t, the constraints, the indexes, you may use Datapump. Here it is…


SQL> var n number
SQL> exec :n := dbms_datapump.open('IMPORT','TABLE','L')

PL/SQL procedure successfully completed.

SQL> print n
N
----------
28

SQL> exec dbms_datapump.metadata_filter(:n,'SCHEMA_LIST','''SCOTT''')

PL/SQL procedure successfully completed.

SQL> exec dbms_datapump.metadata_filter(:n,'NAME_LIST','''T''')

PL/SQL procedure successfully completed.

SQL> exec dbms_datapump.start_job(:n)

PL/SQL procedure successfully completed.
SQL> desc t
Name Null? Type
----------------- -------- ------------
X NOT NULL NUMBER

This is utterly simple. If you mess up with the link name, global name, syntax, and so on, you may end up with orphan jobs in DBA_SCHEDULER_JOBS. There is some metalink note on dropping the underlying tables (Note 336914.1) but first log off, get a coffee, and they may vanish after a few minutes.

2 thoughts on “Duplicate table over database link

  1. Gary

    If you mess up with the filter criteria, you could end up sucking a few extra gigabytes into your database 🙂

    Curious why you went for a series of SQL*Plus statements rather than a single PL/SQL block

  2. Laurent Schneider Post author

    Hmm, I will take care 🙂

    A plsql makes more sense in a script. But one-line exec statements are easier to debug when using the command line, ymmv

Comments are closed.