Use: db: Oracle DB 11g XE and sql developer.
I create db link:
CREATE DATABASE LINK db_link
CONNECT TO ROOT IDENTIFIED BY ROOT
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ***)(PORT = 1521))) (CONNECT_DATA = (SID = xe)))';
And I want copy all tables in db_link.
I found solutions, how copy one by one:
create table temp_table as select * from OLD1@DB_LINK
How to duplicate all db from db link ?
You can get a list of tables with:
select * from all_tables@db_link;
And create copies with
begin
for k in (select * from all_tables@db_link)
loop
execute immediate 'create table '||k.table_name||' as
select * from '||k.table_name||'@db_link';
end loop;
end;
/
but this code may vary if there are more than one schema, if there are indexes, etc.
You may want to try SqlDeveloper's Database Copy tool which can be found in Tools menu in sqldeveloper.
