使用存储过程批量复制表

--1.在数据库上创建到其他远端数据库的db link

 

--2.若是当前用户创建其他用户的表，则须赋予create any table和drop any table的权限

 

--3.创建批量创建并复制表的存储过程

 

----p_createtb过程是在schame下无表的情况使用

 

create or replace procedure p_createtb
(
     v_owner varchar2   
)
as
        tbname varchar2(100);
        sqlstr varchar2(1000);
        cursor c1 is select table_name from ALL_TABLES@db_link where wner=v_owner;     
begin
   open c1;
   
    loop 
       fetch c1 into tbname;
       exit when c1%notfound;                  
           sqlstr :='create table ' || v_owner||'.'||tbname|| ' as select * from '|| v_owner||'.'||tbname||'@db_link;
           dbms_output.put_line(sqlstr);      ---输出创建表的sql语句，若在执行过程中出现了 ORA-20000:ORU-10027:buffer overflow,limit of 1000000 bytes 的错误，注释此行
           execute immediate sqlstr;
    end loop;   
        close c1;     
--        exception
--        when others then
--                dbms_output.put_line('碰到others错误:'|| sqlcode || ','|| sqlerrm);
--        raise;
end;
/

 

----p_cretb_test ：已经存在相关的表，先drop，在create表

 

create or replace procedure p_cretb_test
(
     v_owner1 varchar2,
     v_owner2 varchar2   
)
as
        tbname varchar2(100);
        v_localtbname varchar2(100);
        sqldrop varchar2(1000);
        sqlcreate varchar2(1000);
        cursor c1 is select table_name from ALL_TABLES where wner=v_owner2;   ---查询v_owner2上的表名赋给游标c1
       
        cursor c2 is select table_name from all_tables where wner=v_owner1;   ---查询v_owner1上的表名赋给c2

BEGIN
   OPEN c1;
   OPEN c2;

   LOOP
      FETCH c2 INTO v_localtbname;                           ---抓取v_owner1上的表名
      FETCH c1 INTO tbname;                                  ---抓取v_owner2上的表名
      EXIT WHEN c1%NOTFOUND;

      IF c2%FOUND
      THEN                                               ----如果v_owner1有表存在则删除
         sqldrop := 'drop table ' || v_owner1 || '.' || tbname; ---删除 v_owner1上的相同表名
         DBMS_OUTPUT.put_line (sqldrop);
        -- execute immediate sqldrop;     ---立即执行drop语句，drop用户下的所有的表
      else                                               ----否则创建表
         sqlcreate := 'create table '|| v_owner1|| '.'|| tbname|| ' as select * from '|| v_owner2|| '.'||tbnam;
                          ---在v_owner1创建并复制v_owner2上的表
         DBMS_OUTPUT.put_line (sqlcreate);
         execute immediate sqlcreate;     ---立即执行drop语句，复制并创建表
      end if;
   END LOOP;
   CLOSE c1;
   close c2;
--        exception
--        when others then
--                dbms_output.put_line('碰到others错误:'|| sqlcode || ','|| sqlerrm);
--        raise;
END;
/

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23604335/viewspace-774177/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23604335/viewspace-774177/