oracle数据库 快速copy

/*目前只创建了table、复制数据、sequence、view、type、procedures、function*/
declare
  v_username varchar2(100):='cpxthgapp';--目标数据库的用户名
  v_password varchar2(100):='cpxthgapp';--目标数据库的密码
  v_host varchar2(100):='192.168.100.68';--目标数据库的ip
  v_port varchar2(100):='1521';--目标数据库的端口
  v_ssid varchar2(100):='ttsx';--目标数据库的ssid
  v_dblink varchar2(100):='cpxthgapp';--link的名称
  
  v_str varchar2(3000):='';--ei执行的语句
  v_cons varchar2(3000):='';--ei执行表的约束语句
  
  type tablename is table of user_tables.TABLE_NAME%type;
  o_tablename tablename;--源数据库的table
  type indexnames is record (index_name user_ind_columns.INDEX_NAME%type,cnt user_ind_columns.CHAR_LENGTH%type);
  type indexname is table of indexnames;
  o_indexname indexname;--源数据库的index
  type consname is table of user_constraints.constraint_name%type;
  o_consname consname;--源数据库的constraint
  type v_sequences is record (createsql varchar2(30000),seqname varchar2(1000));
  type v_sequence is table of v_sequences;
  o_seqname v_sequence;--源数据库的sequence,view,type,procedures,function
begin
    --避免output overflow
    dbms_output.enable(null);
    execute immediate 'CREATE DATABASE LINK '||v_dblink||' CONNECT TO '||v_username||
          ' IDENTIFIED BY '||v_password||' USING ''(DESCRIPTION =(ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = '||v_host||')(PORT = '||v_port||')))
          (CONNECT_DATA = (SERVICE_NAME = '||v_ssid||')))''';
      dbms_output.put_line('读取源数据库所有表名称……');
    v_str:='select table_name from user_tables@'||v_dblink;
    execute immediate v_str bulk collect into o_tablename;
    dbms_output.put_line('读取源数据库所有表数据成功,开始创建table、index、constraint');
    --1.创建表
    for j in o_tablename.FIRST .. o_tablename.LAST loop
        dbms_output.put_line('创建表:'||o_tablename(j)||'……');
        v_str:='create table '||o_tablename(j)||' as select * from '||o_tablename(j)||'@'||v_dblink;--查询源数据库所有表
        execute immediate v_str;
        dbms_output.put_line('创建表:'||o_tablename(j)||'成功!检查该表是否存在索引和约束……');
        --是否存在index
        v_str:='select INDEX_NAME,0 as cnt from user_ind_columns@'||v_dblink||' where table_name='''||o_tablename(j)||'''';
        execute immediate v_str bulk collect into o_indexname;
        --是否存在constraint
        v_str:='select constraint_name from user_constraints@'||v_dblink||' where table_name='''||o_tablename(j)||''' and constraint_type in (''P'',''R'',''U'')';
        execute immediate v_str bulk collect into o_consname;
        --创建索引
        if o_indexname.count = 0 then
            dbms_output.put_line('表'||o_tablename(j)||'没有索引,跳过……');
            continue;
        else
            for m in o_indexname.first .. o_indexname.last loop
                v_str:='select count(*) from user_ind_columns where index_name='''||o_indexname(m).index_name||'''';
                execute immediate v_str into o_indexname(m).cnt;--判断是否存在同名的index
                if o_indexname(m).cnt >= 1 then continue; end if;
                dbms_output.put_line('表'||o_tablename(j)||'索引:'||o_indexname(m).index_name||'创建中……');
                v_str:='SELECT DBMS_LOB.SUBSTR@'||v_dblink||'(DBMS_METADATA.GET_DDL@'||v_dblink||'(''INDEX'','''||o_indexname(m).index_name||''')) FROM dual@'||v_dblink;
                execute immediate v_str into v_cons;--index创建语句
                v_cons:=substr(v_cons,0,instr(v_cons,')'));--去掉一些无用的数据（STORAGE）
                v_cons:=replace(v_cons,'"'||upper(v_username)||'"."','"');--去掉表空间
                execute immediate v_cons;
                dbms_output.put_line('表'||o_tablename(j)||'索引:'||o_indexname(m).index_name||'创建成功!');
            end loop;
        end if;
        --创建约束
        if o_consname.count = 0 then
            dbms_output.put_line('表'||o_tablename(j)||'没有约束,跳过……');
            continue;
        else
            for k in o_consname.FIRST .. o_consname.LAST loop
                dbms_output.put_line('表'||o_tablename(j)||'约束:'||o_consname(k)||'创建中……');
                v_str:='SELECT DBMS_LOB.SUBSTR@'||v_dblink||'(DBMS_METADATA.GET_DDL@'||v_dblink||'(''CONSTRAINT'','''||o_consname(k)||''')) FROM dual@'||v_dblink;
                execute immediate v_str into v_cons;--index创建语句
                v_cons:=substr(v_cons,0,instr(v_cons,')'));--去掉一些无用的数据（STORAGE）
                v_cons:=replace(v_cons,'"'||upper(v_username)||'"."','"');--去掉表空间
                execute immediate v_cons;
                dbms_output.put_line('表'||o_tablename(j)||'约束:'||o_consname(k)||'创建成功!');
            end loop;
        end if;
    end loop;
    dbms_output.put_line('创建表及对应索引和约束完成,其中
        表:'||o_tablename.count||'个、
        索引:'||o_indexname.count||'个,
        约束:'||o_consname.count||'个,开始创建sequence……');
    --2.创建sequence
    v_str:='select DBMS_LOB.SUBSTR@'||v_dblink||'(DBMS_METADATA.GET_DDL@'||v_dblink||'(''SEQUENCE'',u.object_name)),u.object_name from  user_objects@'||v_dblink||' u where  object_type = ''SEQUENCE''';
    execute immediate v_str bulk collect into o_seqname;
    if o_seqname.count = 0 then
        dbms_output.put_line('未检测到用户存在sequence,跳过……');
    else
        for n in o_seqname.FIRST .. o_seqname.LAST loop
            o_seqname(n).createsql:=replace(o_seqname(n).createsql,'"'||upper(v_username)||'"."','"');--去掉表空间
            execute immediate o_seqname(n).createsql;
            dbms_output.put_line('创建sequence:'||o_seqname(n).seqname||'成功!');
        end loop;
        dbms_output.put_line('创建sequence完成!共'||o_seqname.count||'个');
    end if;
    
    --3.创建views,如果源数据库的view是错误的，可能会异常，导致报错
    v_str:='select DBMS_LOB.SUBSTR@'||v_dblink||'(DBMS_METADATA.GET_DDL@'||v_dblink||'(''VIEW'',u.object_name)),u.object_name from  user_objects@'||v_dblink||' u where  object_type = ''VIEW''';
    execute immediate v_str bulk collect into o_seqname;
    if o_seqname.count = 0 then
        dbms_output.put_line('未检测到用户存在view,跳过……');
    else
        for n in o_seqname.FIRST .. o_seqname.LAST loop
            o_seqname(n).createsql:=replace(o_seqname(n).createsql,'"'||upper(v_username)||'"."','"');--去掉表空间
            execute immediate o_seqname(n).createsql;
            dbms_output.put_line('创建view:'||o_seqname(n).seqname||'成功!');
        end loop;
        dbms_output.put_line('创建view完成!共'||o_seqname.count||'个');
    end if;
    --4.创建type,在procedures和function前面创建，避免在procedures和function中使用
    v_str:='select DBMS_LOB.SUBSTR@'||v_dblink||'(DBMS_METADATA.GET_DDL@'||v_dblink||'(''TYPE'',u.object_name)),u.object_name from  user_objects@'||v_dblink||' u where  object_type = ''TYPE''';
    execute immediate v_str bulk collect into o_seqname;
    if o_seqname.count = 0 then
        dbms_output.put_line('未检测到用户存在type,跳过……');
    else
        for n in o_seqname.FIRST .. o_seqname.LAST loop
            o_seqname(n).createsql:=replace(o_seqname(n).createsql,'"'||upper(v_username)||'"."','"');--去掉表空间
            execute immediate o_seqname(n).createsql;
            dbms_output.put_line('创建type:'||o_seqname(n).seqname||'成功!');
        end loop;
        dbms_output.put_line('创建type完成!共'||o_seqname.count||'个');
    end if;
    --5.创建procedures
    v_str:='select DBMS_LOB.SUBSTR@'||v_dblink||'(DBMS_METADATA.GET_DDL@'||v_dblink||'(''PROCEDURE'',u.object_name)),u.object_name from  user_objects@'||v_dblink||' u where  object_type = ''PROCEDURE''';
    execute immediate v_str bulk collect into o_seqname;
    if o_seqname.count = 0 then
        dbms_output.put_line('未检测到用户存在procedures,跳过……');
    else
        for n in o_seqname.FIRST .. o_seqname.LAST loop
            o_seqname(n).createsql:=replace(o_seqname(n).createsql,'"'||upper(v_username)||'"."','"');--去掉表空间
            execute immediate o_seqname(n).createsql;
            dbms_output.put_line('创建view:'||o_seqname(n).seqname||'成功!');
        end loop;
        dbms_output.put_line('创建view完成!共'||o_seqname.count||'个');
    end if;
    --6.创建function
    v_str:='select DBMS_LOB.SUBSTR@'||v_dblink||'(DBMS_METADATA.GET_DDL@'||v_dblink||'(''FUNCTION'',u.object_name)),u.object_name from  user_objects@'||v_dblink||' u where  object_type = ''FUNCTION''';
    execute immediate v_str bulk collect into o_seqname;
    if o_seqname.count = 0 then
        dbms_output.put_line('未检测到用户存在function,跳过……');
    else
        for n in o_seqname.FIRST .. o_seqname.LAST loop
            o_seqname(n).createsql:=replace(o_seqname(n).createsql,'"'||upper(v_username)||'"."','"');--去掉表空间
            execute immediate o_seqname(n).createsql;
            dbms_output.put_line('创建function:'||o_seqname(n).seqname||'成功!');
        end loop;
        dbms_output.put_line('创建function完成!共'||o_seqname.count||'个');
    end if;
    --清除对象
    execute immediate 'commit';
    v_str:='alter session close database link '||v_dblink;
    execute immediate v_str;
    v_str:='drop database link '||v_dblink;
    execute immediate v_str;
end;