移动一个表到另一个schema的方法
admin
2023-04-26 05:41:29
0

可以有以下几种常用的办法:
1、expdp/impdp

2、ctas + parallel + nologin

     第二种方法要注意主键在新表是没有创建的

NOT NULL constraints that were implicitly created by Oracle Database on columns of the selected table (for example, for primary keys) are not carried over to the new table.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm

3、exchange partition

以下针对第三种方法进行测试:
创建big_table脚本来自Oracle Database 9i10g11g编程艺术深入数据库体系结构(第2版),转换方式:普通表A.A->分区表A.A_TEMP->普通表B.B
1.创建测试表:

info@PROD> create table big_table
  2  as
  3  select rownum id, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.O  3  
  3  select rownum id, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.OBJECT_ID, a.DATA_OBJECT_ID
  4    from all_objects a
  5   where 1=0
  6  /
Table created.
Elapsed: 00:00:00.09
info@PROD> alter table big_table nologging;
Table altered.
Elapsed: 00:00:00.01
info@PROD> declare
  2      l_cnt number;
  3      l_rows number := &1;
  4  begin
  5      insert /*+ append */
  6      into big_table
  7      select rownum, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.OBJECT_ID, a.DATA_OBJECT_ID
  8        from all_objects a
  9   where rownum <= &1;
 10  
 11      l_cnt := sql%rowcount;
 12  
 13      commit;
 14  
 15      while (l_cnt < l_rows)
 16      loop
 17          insert /*+ APPEND */ into big_table
 18          select rownum+l_cnt, 
 19                 OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
 20            from big_table
 21           where rownum <= l_rows-l_cnt;
 22          l_cnt := l_cnt + sql%rowcount;
 23          commit;
 24      end loop;
 25  end;
 26  /
Enter value for 1: 8000000
old   3:     l_rows number := &1;
new   3:     l_rows number := 8000000;
Enter value for 1: 8000000
old   9:  where rownum <= &1;
new   9:  where rownum <= 8000000;
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.73
info@PROD> select count(*) from big_table;
  COUNT(*)
----------
   8000000
Elapsed: 00:00:01.86
info@PROD> alter table big_table add constraint big_table_pk primary key(id);
Table altered.
Elapsed: 00:00:38.63
info@PROD> info@PROD> exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);
PL/SQL procedure successfully completed.

创建中间表:

info@PROD> CREATE TABLE big_table_temp
   2    PARTITION BY RANGE (id)
   3   (PARTITION id_1 VALUES LESS THAN (MAXVALUE))
   4    AS
   5     SELECT *
   6       FROM big_table
   7      WHERE ROWNUM <= 0;
info@PROD> alter table big_table_temp add constraint pk_big_table_temp_id primary key(id);

为pinfo用户授权:

info@PROD>  grant ALL on big_table to "PINFO";
info@PROD>  grant ALL on big_table_temp to "PINFO";

登录pinfo,创建info同名表:

info@PROD> conn pinfo/admin
Connected.
pinfo@PROD> CREATE TABLE pinfo.big_table
  2  AS
  3     SELECT *
  4       FROM info.big_table
  5      WHERE ROWNUM <= 0;

登录info,将big_table交换至big_table_temp:

pinfo@PROD> conn info/admin
info@PROD> ALTER TABLE big_table_temp EXCHANGE PARTITION id_1 WITH TABLE big_table EXCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.02
#此处使用了excludeing选项,否则会报 ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION,可以在交换完成以后手动创建索引
info@PROD> select count(*) from big_table;
  COUNT(*)
----------
   0
info@PROD> select count(*) from  info.big_table_temp;
  COUNT(*)
----------
  8000000

登录pinfo,将big_table_temp交换至big_table:

pinfo@PROD> ALTER TABLE info.big_table_temp EXCHANGE PARTITION id_1 WITH TABLE pinfo.big_table EXCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.01
pinfo@PROD> select count(*) from big_table;
  COUNT(*)
----------
   8000000
Elapsed: 00:00:02.91
pinfo@PROD> select count(*) from  info.big_table_temp;
  COUNT(*)
----------
         0

完成交换几乎是毫秒级的。

也可以反向交换回去:

pinfo@PROD> ALTER TABLE info.big_table_temp EXCHANGE PARTITION id_1 WITH TABLE pinfo.big_table EXCLUDING INDEXES WITHOUT VALIDATION;
info@PROD> conn info/admin
info@PROD> ALTER TABLE big_table_temp EXCHANGE PARTITION id_1 WITH TABLE big_table exCLUDING INDEXES WITHOUT VALIDATION;

以下内容来自asktom,转换方式:普通表A.A->分区表B.B

参考:https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:752030266230

To quickly move big tables between schemas  use EXCHANGE PARTITION feature of Oracle 8i.
for example:
SQL> connect as user "A"
SQL> create table large_table
     (
       a number,
       b char,
       c date
     )
-- just for this example only. :)
SQL> grant ALL on large_table to "B";
SQL> connect as user "B"
SQL> create table large_table 
     (
       a number,  
       b char,    
       c date
     )
     partition by range (a)
     (
       partition dummy values less than (maxvalue)
     )
Then you can use the following command to quickly move 
"A.large_table" to "B.large_table"
SQL> connect as user "B";
SQL> alter table large_table exchange partition dummy
     with table A.large_table;
And return it back to schema A:
SQL> alter table large_table exchange partition dummy
     with table A.large_table;
-- of course, it is the same SQL command


相关内容

热门资讯

美媒:中美元首会晤或讨论AI护... 据凤凰卫视援引美国媒体报道,美方希望借中美领导人会晤的机会,开启有关人工智能安全与管控的对话,并推动...
卫生间水管布置图尺寸高度 卫生间的水管布置可能比较复杂一些,因为卫生间的管道比较多。比如有热水器的通水管道,有座便器的冲水管道...
电脑尺寸在哪里看 电脑尺寸在哪里看1、可以选择手工测量法来测量电脑尺寸,这时测量一下屏幕对角线,因为显示面积都会小于显...
电脑机箱尺寸标准 常规的机箱一般是立体式,或桌面式,再就是横式。电脑机箱尺寸,目前市面上根据不同的规格有不同的方案。 ...
斯塔默发声:不会辞职 【环球时报驻英国特约记者 纪双城 环球时报特约记者 甄翔 于文】执政党工党在英国地方选举中遭遇“历史...
抽油烟机管道有异味怎么办 抽油烟机是厨房中不可或缺的设备,它能有效地排出油烟,保证室内空气的清新。但是,长时间使用后,抽油烟机...
抽油烟机管道有油垢怎么办 抽油烟机管道是我们家庭生活中必不可少的设备,但长时间的使用会导致管道内部积累大量的油垢,如不及时清理...
沈伯洋与台北市长格局有落差,叶... 海峡导报综合报道 民进党将正式拍板征召民代沈伯洋出战台北市长,并发布采访通知,预计在13日下午举行台...
核查:上世纪已有多款汉坦疫苗上... 明查员 冯梦速览- 人类感染汉坦病毒已有数十年历史。朝鲜战争期间,联合国军就曾有过大规模感染。汉坦病...
如何从“一证在手”走向“一技傍... 教员在给学员讲解理论知识。 学员在进行无人机试飞训...