ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用存储过程批量复制表

使用存储过程批量复制表

原创 Linux操作系统 作者:bingmin259 时间:2013-10-11 17:10:24 0 删除 编辑
--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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: silent安装
请登录后发表评论 登录
全部评论

注册时间:2010-03-27

  • 博文量
    9
  • 访问量
    18430