迁移表数据

 为了节省表空间,打算把一部分表迁移到另外的库中,由于之前没有进行过类似的实施,迁移起来把脚本弄得东一个西一个,稽核做得不到位,导致部分数据不成功,还要反复的核实,还是数据不多,720个表.

1 主要是生成建表,索引,主机脚本

2 运行生成脚本后,在非归档模式下导入数据,然后改为归档模式

3 稽核数据行数前后是否一致,一致则drop原库的表.

4 建立同义词.

 

建表脚本生成语句:

declare
  v_create_table_ddl varchar(4000);
  v_create_index_ddl varchar(4000);
  v_create_cons_ddl varchar(4000);
  v_sql1 varchar(4000);
  v_sql2 varchar(4000);
  v_sql3 varchar(4000);
  verrmsg varchar2(1024):='';
  v_tablespace varchar2(256):='BILL';
  v_owner varchar2(256):='';
  v_table_name varchar2(256):='';
  create_sts NUMBER(12);
begin
 
  insert into  houfulin.MIG_SEGMENT_INFO_INDEX (owner,Table_Name,index_NAME) select t.owner,t.table_name,a.index_name from houfulin.hfl_mid_zc t ,Dba_Indexes a where
  t.owner=a.owner and t.table_name=a.table_name;
  commit;
 
  insert into houfulin.MIG_SEGMENT_INFO_CONSTRAINT(owner,Table_Name,CONS_NAME)
select t.owner,t.table_name,t.constraint_name from dba_constraints t
where exists(select * from houfulin.MIG_SEGMENT_INFO_INDEX b where b.index_name=t.constraint_name )
and not exists (select * from houfulin.MIG_SEGMENT_INFO_CONSTRAINT c where c.cons_name=t.constraint_name);
   commit;
 
---生成创建表语句
  for rec1 in (SELECT t.*,t.rowid vrowid from  houfulin.hfl_mid_zc  t  where t.create_sql is null and t.create_sts<>1) loop
    begin
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false);
      v_create_table_ddl:= replace(dbms_metadata.get_ddl('TABLE', upper(rec1.index_name), upper(rec1.owner)),
                     '"',
                     '');
      v_sql2 := 'update  houfulin.hfl_mid_zc a set a.create_sql=''' || v_create_table_ddl || ''' where  rowid='''||rec1.vrowid||'''';
      execute immediate v_sql2;
      update houfulin.hfl_mid_zc t set t.create_sts=1 where rowid=rec1.vrowid;
      update houfulin.hfl_mid_zc t set t.remark='ok' where rowid=rec1.vrowid;
      update houfulin.hfl_mid_zc t set t.create_sql=replace(create_sql,'"','')||v_tablespace;
      commit;
    exception
      when others then
        verrmsg:=sqlerrm;
        update houfulin.MIG_SEGMENT_INFO_INDEX t set t.remark=verrmsg where rowid=rec1.vrowid;
        commit;
    end;
  end loop;
  commit;
  -----获取创建索引语句
  for rec2 in (
      select t.*,t.rowid vrowid from  houfulin.MIG_SEGMENT_INFO_INDEX t  where t.index_ddl is null
  ) loop
    begin
      v_create_index_ddl:= replace(dbms_metadata.get_ddl('INDEX', upper(rec2.index_name), upper(rec2.owner)),
                     '"',
                     '');
      v_sql3 := 'update houfulin.MIG_SEGMENT_INFO_INDEXa set a.index_ddl=''' || v_create_index_ddl || ''' where  rowid='''||rec2.vrowid||'''';
      execute immediate v_sql3;
      update houfulin.MIG_SEGMENT_INFO_INDEX t set t.remark='ok' where rowid=rec2.vrowid;
       update houfulin.MIG_SEGMENT_INFO_INDEX t set t.index_ddl=replace(index_ddl,'"','') where rowid=rec2.vrowid;
      commit;
    exception
      when others then
        verrmsg:=sqlerrm;
        update houfulin.MIG_SEGMENT_INFO_INDEX t set t.remark=verrmsg where rowid=rec2.vrowid;
        commit;
    end;
  end loop;
  commit;
 
  -----获取主键创建语句

  for rec3 in (
      select t.*,t.rowid vrowid from houfulin.MIG_SEGMENT_INFO_CONSTRAINT t where t.cons_ddl is null
  ) loop
    begin
      v_create_cons_ddl:= replace(dbms_metadata.get_ddl('CONSTRAINT', rec3.cons_name, 'ZG'),
                     '"',
                     '');
      v_sql3 := 'update houfulin.MIG_SEGMENT_INFO_CONSTRAINT a set a.cons_ddl=''' || v_create_cons_ddl || ''' where  rowid='''||rec3.vrowid||'''';
      execute immediate v_sql3;
      update houfulin.MIG_SEGMENT_INFO_CONSTRAINT t set t.remark='ok' where rowid=rec3.vrowid;
      update houfulin.MIG_SEGMENT_INFO_CONSTRAINT t set t.cons_ddl=replace(cons_ddl,'"','') where rowid=rec3.vrowid;
      commit;
    exception
      when others then
        verrmsg:=sqlerrm;
        update houfulin.MIG_SEGMENT_INFO_CONSTRAINT t set t.remark=verrmsg where rowid=rec3.vrowid;
        commit;
    end;
  end loop;
  commit;
end;

 

 

2导入数据脚本:

DECLARE
  V_SQL_CONS   VARCHAR2(4000);
  V_SQL_INSERT VARCHAR2(4000);
  V_SQL_INDEX  VARCHAR2(4000);
  V_SQL_ALTER  VARCHAR2(4000);
  V_SQL_CREATE VARCHAR2(4000);
  VERRMSG      VARCHAR2(4000) := '';
  VROWCOUNT    NUMBER := 0;
  V_DBLINK     VARCHAR2(256) := '@dbacct.world';
  STATUS       NUMBER(2);
BEGIN
  ----查找生成成功的建表语句 create_sts=1   其他库建表成功更新为create_sts=2

  FOR REC_CREATE IN (SELECT T.*, T.ROWID VROWID
                       FROM [e-mail] T
                      WHERE T.CREATE_STS = 1) LOOP
     BEGIN
      V_SQL_CREATE := REC_CREATE.CREATE_SQL;
      EXECUTE IMMEDIATE V_SQL_CREATE;
      UPDATE [e-mail] T
         SET T.CREATE_STS = 2
       WHERE ROWID = REC_CREATE.VROWID;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        VERRMSG := SQLERRM;
        UPDATE [e-mail] T
           SET T.REMARK = VERRMSG
         WHERE ROWID = REC_CREATE.VROWID;
        COMMIT;
    END;
  END LOOP;
  ----status=1 插入成功的表（非归档模式）
  FOR REC_INSERT IN (SELECT T.OWNER, T.TABLE_NAME
                       FROM [e-mail] T
                      WHERE T.STATUS <> 1
                        AND CREATE_STS = 2) LOOP
    BEGIN
      -- execute immediate rec_insert.index_dll;
      V_SQL_INSERT := 'insert into  /*+append nologging */ ' ||
                      REC_INSERT.OWNER || '.' || REC_INSERT.TABLE_NAME ||
                      ' select /*+parallel (a,16)*/* from ' ||
                      REC_INSERT.OWNER || '.' || REC_INSERT.TABLE_NAME ||
                      V_DBLINK;
      EXECUTE IMMEDIATE V_SQL_INSERT;
      VROWCOUNT := SQL%ROWCOUNT;
      UPDATE [e-mail] T
         SET T.STATUS = 1
       WHERE TABLE_NAME = REC_INSERT.TABLE_NAME
         AND OWNER = REC_INSERT.OWNER;
      UPDATE [e-mail] T
         SET T.NEW_ROWS = VROWCOUNT
       WHERE TABLE_NAME = REC_INSERT.TABLE_NAME;
      COMMIT;
    END;
  END LOOP;
  -----改回归档模式
  FOR REC_INSERT IN (SELECT * FROM [e-mail] WHERE STATUS = 1 ) LOOP
    BEGIN
        V_SQL_ALTER := 'alter table ' || REC_INSERT.OWNER ||'.'||
                       REC_INSERT.TABLE_NAME || ' nologging';
            dbms_output.put_line(V_SQL_ALTER);
        EXECUTE IMMEDIATE V_SQL_ALTER;
        UPDATE [e-mail] T
           SET T.REMARK = T.REMARK || ' nologging'
         WHERE TABLE_NAME = REC_INSERT.TABLE_NAME
           AND OWNER = REC_INSERT.OWNER;
        UPDATE [e-mail] T
           SET T.STATUS = 2
         WHERE TABLE_NAME = REC_INSERT.TABLE_NAME
           AND OWNER = REC_INSERT.OWNER;
        COMMIT;
        RETURN;
    EXCEPTION
      WHEN OTHERS THEN
        VERRMSG := SQLERRM;
        UPDATE [e-mail] T
           SET T.REMARK = VERRMSG
         WHERE TABLE_NAME = REC_INSERT.TABLE_NAME;
        COMMIT;
    END;
  END LOOP;
  ------创建表索引
  FOR REC_INDEX IN (SELECT T.OWNER, T.TABLE_NAME, T.INDEX_DDL
                      FROM [e-mail] T,
                           [e-mail]             A
                     WHERE T.INDEX_DDL IS NOT NULL
                       AND A.STATUS = 1
                       AND T.TABLE_NAME = A.TABLE_NAME
                       AND T.OWNER = A.OWNER) LOOP
    BEGIN
      V_SQL_INDEX := REC_INDEX.index_ddl;
      EXECUTE IMMEDIATE V_SQL_INDEX;
      UPDATE [e-mail] T
         SET T.REMARK = 'index is ok'
       WHERE TABLE_NAME = REC_INDEX.TABLE_NAME
         AND OWNER = REC_INDEX.OWNER;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        VERRMSG := SQLERRM;
        UPDATE [e-mail] T
           SET T.REMARK = VERRMSG
         WHERE TABLE_NAME = REC_INDEX.TABLE_NAME
           AND OWNER = REC_INDEX.OWNER;
        COMMIT;
    END;
  END LOOP;
  ------创建主键
  FOR REC_CONS IN (SELECT T.OWNER, T.TABLE_NAME, T.CONS_DDL
                     FROM [e-mail] T,
                           [e-mail]                  A
                    WHERE T.CONS_DDL IS NOT NULL
                      AND T.TABLE_NAME = A.TABLE_NAME
                      AND T.OWNER = A.OWNER
                      AND A.STATUS = 1) LOOP
    BEGIN
      V_SQL_CONS := REC_CONS.CONS_DDL;
      EXECUTE IMMEDIATE V_SQL_CONS;
      UPDATE [e-mail] T
         SET T.REMARK = 'OK'
       WHERE TABLE_NAME = REC_CONS.TABLE_NAME;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        VERRMSG := SQLERRM;
        UPDATE [e-mail] T
           SET T.REMARK = VERRMSG
         WHERE TABLE_NAME = REC_CONS.TABLE_NAME;
        COMMIT;
    END;
  END LOOP;
END;
3 稽核行数:

 

declare
  v_ddl VARCHAR2(4000);
  v_sql_truncate VARCHAR2(4000);
  v_sql VARCHAR2(4000);
  verrmsg varchar2(4000):='';
 v_old_rows NUMBER(12);
  v_dblink VARCHAR2(256):='@dbacct.world';
  BEGIN
  for rec3 in (
      select t.owner,t.table_name from houfulin.hfl_mid_zc  t ) loop
    begin
     -- execute immediate rec3.index_dll;
    v_sql:=' SELECT COUNT(*)  FROM '||rec3.owner||'.'||rec3.table_name;
   execute immediate v_sql INTO v_old_rows;
   dbms_output.put_line('rows'||v_old_rows);
     update  houfulin.hfl_mid_zc t SET t.old_rows=v_old_rows where table_name=rec3.table_name;
     update  houfulin.hfl_mid_zc t set t.remark='oldrows is ok' where table_name=rec3.table_name;
     COMMIT;
       exception
      when others then
        verrmsg:=SQLERRM;
         update  houfulin.hfl_mid_zc t set t.remark=verrmsg where table_name=rec3.table_name;
        commit;
    end;
  end loop;
end;