使用DBLINK迁移表结构

DECLARE 
CURSOR c_get_table_sql IS SELECT a.table_name FROM dba_tables@dblink a WHERE a.owner = 'GXKF' AND ROWNUM = 1; --源数据 从DBLINK中找出
i                NUMBER(10);
v_tab_name       VARCHAR2(100);
v_create_tab_sql VARCHAR2(500);
v_sql            VARCHAR2(500);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM dba_tables WHERE table_name = '''||v_tab_name||'''';
  OPEN c_get_table_sql;
  i := 0;
  LOOP 
    FETCH c_get_table_sql INTO v_tab_name;
    v_create_tab_sql := 'CREATE TABLE '||v_tab_name||' AS SELECT * FROM GXKF.'||v_tab_name||' WHERE 1 = 0';
    dbms_output.put_line(v_tab_name);
    EXIT WHEN c_get_table_sql%NOTFOUND;
    EXECUTE IMMEDIATE v_sql INTO i;
    IF i = 0 THEN 
      dbms_output.put_line(v_create_tab_sql);
      EXECUTE IMMEDIATE v_create_tab_sql;
    ELSE 
      i := 0;
    END IF;
  END LOOP;
  CLOSE c_get_table_sql;
END;