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.
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
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