利用dblink复制用户下所有的表

declare
  cursor cur is
    select t.TABLE_NAME, c.comments
      from user_tables@sdgdfw t, user_tab_comments@sdgdfw c
     where t.TABLE_NAME = c.table_name;

  table_count number := 0;

  cursor col_cur is
    select t.TABLE_NAME tab, c.column_name col, c.comments
      from user_tables@sdgdfw t, user_col_comments@sdgdfw c
     where t.table_name = c.table_name;
begin

  for v_tab in cur loop
    exit when cur%notfound;
    table_count := 0;
    select count(*)
      into table_count
      from user_tables t
     where t.TABLE_NAME = v_tab.table_name;
    if table_count = 0 then
      -- 建表
      execute immediate 'create table ' || v_tab.table_name ||
                        ' as select * from ' || v_tab.table_name ||
                        '@sdgdfw';
    
      --表注释
      execute immediate 'comment on table ' || v_tab.table_name || ' is ' || '''' ||
                        v_tab.comments || '''';
    else
      continue;
    end if;
    
    -- 给字段加注释
    for v in col_cur loop
      exit when col_cur%notfound;
      if v.tab = v_tab.table_name then
        --表注释
        execute immediate 'comment on column ' || v.tab || '.' || v.col ||
                          ' is ' || '''' || v.comments || '''';
      else
        continue;                    
      end if;
    end loop;
  
  end loop;
  rollback;
end;