ITPub博客

ORACLE中修改表的Schema的总结

转载 作者:eddy0lion 时间:2018-07-14 15:52:00 0 删除 编辑

前阵子遇到一个案例,需要将数据库中的几个表从USER A 移动到USER B下面,在ORACLE中,这个叫做更改表的所有者或者修改表的Schema。其实遇到这种案例,有好几种解决方法。下面我们通过实验来测试、验证一下。首先准备简单测试数据,如下所示:

 

 

SQL> CREATE TABLE TEST.KKK
( ID   INT   ,
  NAME VARCHAR2(12) ,
  CONSTRAINT PK_KKK PRIMARY KEY(ID)
);
 

Table created.
 

SQL> INSERT INTO TEST.KKK
  2  VALUES(1000, 'kerry');
 

1 row created.
 

SQL> commit;
 

Commit complete.

 

 

方法1: 常规方法,在目标用户下创建表,并拷贝数据过去。

 

 

SQL> CREATE TABLE TEST1.KKK( ID  INT, NAME VARCHAR2(12) ,CONSTRAINT PK_KKK PRIMARY KEY (ID));
 

Table created.
 

SQL> INSERT INTO TEST1.KKK
  2  SELECT * FROM TEST.KKK;
 

1 row created.
 

SQL> COMMIT;
 

Commit complete.
 

SQL> DROP TABLE TEST.KKK;
 

Table dropped.
 

SQL>

 

 

 

当然也可以使用CREATE TABLE TEST1.KKK AS SELECT * FROM TEST.KKK; 但是使用这种方式需要注意,索引和约束都无法Copy过去。所以一定要慎用CREATE TABLE AS (CTAS)这种语法。

 

 

 

方法2: 使用expdp/impdp,导出表然后导入表修改表的Schema。使用exp/imp方式也是差不多,在此不做介绍。

 

 

 

[oracle@DB-Server dpdump]$ expdp system/xxxx tables=test.kkk directory=data_pump_dir dumpfile=kkk.dmp logfile=kkk.log;

 

Export: Release 11.2.0.1.0 - Production on Mon Jul 10 15:57:22 2017

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.    All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":    system/******** tables=test.kkk directory=data_pump_dir dumpfile=kkk.dmp logfile=kkk.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

. . exported "TEST"."KKK"                                   5.421 KB         1 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

   /u01/app/oracle/admin/gsp/dpdump/kkk.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 15:57:26

 

 

 

[oracle@DB-Server dpdump]$ impdp system/xxxx tables=test.kkk directory=data_pump_dir remap_schema=test:test1 dumpfile=kkk.dmp logfile=import.log

 

Import: Release 11.2.0.1.0 - Production on Mon Jul 10 15:58:07 2017

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.    All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_01":    system/******** tables=test.kkk directory=data_pump_dir remap_schema=test:test1 dumpfile=kkk.dmp logfile=import.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TEST1"."KKK"                                 5.421 KB         1 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 15:58:09

 

 

注意上面方法无法移动表到其它表空间,所以,如果你也必须移动表到对应的表空间,那么就必须使用参数remap_tablespace, 如下所示:

 

 

impdp system/xxxxx tables=test.kkk directory=data_pump_dir remap_schema=test:test1 remap_tablespace=tbs_test_data:tbs_test1_data dumpfile=kkk.dmp logfile=import.log

 

 

 

 

 

 

 

方法3:修改系统表obj$、sys.con$等 (这个仅仅作为实验测试而已,不可应用于生产环境),ASK TOM里面强烈不建议使用这种方法,原文如下,不过不妨碍我们在测试环境玩一玩,了解一下。

 

 

 

clip_image001

 

 

SQL> select obj#, owner#, name, namespace from obj$ where name='KKK';
 

      OBJ#     OWNER# NAME                            NAMESPACE
---------- ---------- ------------------------------ ----------
     93220         85 KKK                                     1
 

SQL> select user_id , username from dba_users where username in ('TEST', 'TEST1');
 

   USER_ID USERNAME
---------- ------------------------------
        86 TEST1
        85 TEST
 

SQL>  update obj$ set owner#=86 where obj#=93220;
 

1 row updated.
 

SQL> commit;
 

Commit complete.
 

SQL>

 

clip_image002

 

 

 

如下所示,更新了系统表obj$后,你会发现TEST.KKK与TEST1.KKK两个表都存在,这个时候可以执行ALTER SYSTEM FLUSH SHARED_POOL命令后,TEST.KKK就不存在了。

 

 

SQL> select * from v$mystat where rownum=1;
 

       SID STATISTIC#      VALUE
---------- ---------- ----------
        12          0          0
 

SQL> SELECT * FROM TEST.KKK;
 

        ID NAME
---------- ------------
      1000 kerry
 

SQL> SELECT * FROM TEST1.KKK;
 

        ID NAME
---------- ------------
      1000 kerry
 

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
 

System altered.
 

SQL>  SELECT * FROM TEST.KKK;
 SELECT * FROM TEST.KKK
                    *
ERROR at line 1:
ORA-00942: table or view does not exist
 

 

SQL>  SELECT * FROM TEST1.KKK;
 

        ID NAME
---------- ------------
      1000 kerry
 

SQL>

 

 

 

clip_image003

 

 

 

另外,更新了系统表obj$后,你会发现索引、约束信息还没有变化,如下所示,OWNER依然为TEST,索引也可以在obj$中更新对应记录,但是约束就必须继续查找

 

 

 

SQL>  SELECT OWNER ,INDEX_NAME , TABLE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='KKK';
 

OWNER                          INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           PK_KKK                         KKK
 

SQL> COL OWNER FOR A20;
SQL> SELECT OWNER, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='KKK';
 

OWNER                CONSTRAINT_NAME
-------------------- ------------------------------
TEST                 PK_KKK
 

SQL> select obj#, owner#, name, namespace from obj$ where name='PK_KKK';
 

      OBJ#     OWNER# NAME                            NAMESPACE
---------- ---------- ------------------------------ ----------
     93221         85 PK_KKK                                  4
 

SQL> update obj$ set owner#=86 where obj#=93221;
 

1 row updated.
 

SQL> commit;
 

Commit complete.
 

SQL> SELECT OWNER ,INDEX_NAME , TABLE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='KKK';
 

OWNER                INDEX_NAME                     TABLE_NAME
-------------------- ------------------------------ ------------------------------
TEST1                PK_KKK                         KKK
 

SQL> SELECT OWNER, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='KKK';
 

OWNER                CONSTRAINT_NAME
-------------------- ------------------------------
TEST                 PK_KKK
 

SQL>
 

 

 

 

而DBA_CONSTRAINTS的定义位于 cdcore.sql中($ORACLE_HOME/rdbms/admin)中,具体定义如下所示所示(本文测试环境为Oracle 11g),从其定义可以知道,我们必须更新sys.con$中的记录。

 

 

create or replace view DBA_CONSTRAINTS
    (OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE,
     TABLE_NAME, SEARCH_CONDITION, R_OWNER,
     R_CONSTRAINT_NAME, DELETE_RULE, STATUS,
     DEFERRABLE, DEFERRED, VALIDATED, GENERATED,
     BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME,
     INVALID, VIEW_RELATED)
as
select ou.name, oc.name,
       decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
              4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
              10, 'F', 11, 'F', 13, 'F', '?'),
       o.name, c.condition, ru.name, rc.name,
       decode(c.type#, 4,
              decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'),
              NULL),
       decode(c.type#, 5, 'ENABLED',
              decode(c.enabled, NULL, 'DISABLED', 'ENABLED')),
       decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),
       decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),
       decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),
       decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),
       decode(bitand(c.defer,16),16, 'BAD', null),
       decode(bitand(c.defer,32),32, 'RELY', null),
       c.mtime,
       decode(c.type#, 2, ui.name, 3, ui.name, null),
       decode(c.type#, 2, oi.name, 3, oi.name, null),
       decode(bitand(c.defer, 256), 256,
              decode(c.type#, 4,
                     case when (bitand(c.defer, 128) = 128
                                or o.status in (3, 5)
                                or ro.status in (3, 5)) then 'INVALID'
                          else null end,
                     case when (bitand(c.defer, 128) = 128
                                or o.status in (3, 5)) then 'INVALID'
                          else null end
                    ),
              null),
       decode(bitand(c.defer, 256), 256, 'DEPEND ON VIEW', null)
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
     sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
     sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
  and oc.con# = c.con#
  and c.obj# = o.obj#
  and c.type# != 8        /* don't include hash expressions */
  and (c.type# < 14 or c.type# > 17)    /* don't include supplog cons   */
  and (c.type# != 12)                   /* don't include log group cons */
  and c.rcon# = rc.con#(+)
  and c.enabled = oi.obj#(+)
  and oi.owner# = ui.user#(+)
  and rc.owner# = ru.user#(+)
  and c.robj# = ro.obj#(+)
/

 

 

那么我们必须更新sys.con$中记录,如下所示:

 

 

 

SQL> select owner#,name, con# from sys.con$ where name='PK_KKK';
 

    OWNER# NAME                                 CON#
---------- ------------------------------ ----------
        85 PK_KKK                              19023
 

SQL> update sys.con$ set owner#=86 where name='PK_KKK';
 

1 row updated.
 

SQL> commit;
 

Commit complete.
 

SQL> COL OWNER FOR A32;
SQL> SELECT OWNER, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='KKK';
 

OWNER                            CONSTRAINT_NAME
-------------------------------- ------------------------------
TEST1                            PK_KKK
 

SQL>

 

 

clip_image004

clip_image005

 

 

 

OK,到此基本的几种方法我们已经介绍了,实际场景当中,那些需要修改Schema的表可能会非常大,生产环境完全不能使用方法3这种捷径。如果使用CTAS这种方法,即使使用NOLOGGING和开启并行,也是非常消耗资源和时间的。使用expdp/impdp 稍微好一些,但是也不能避免一些资源开销。 还有一种方法就是使用exchange partition,exchange只是在ORACLE的数据字典中修改了分区和表的结构。数据并未发生任何修改,因此速度很快.对于大表来说,非常高效!如下测试案例所示:

 

 

SQL> create table test.big_table
  2  as
  3  select object_id, object_name from dba_objects;
 

Table created.
 

SQL>
SQL>
SQL> create table test1.big_table
  2  (     object_id    number,
  3        object_name  varchar2(128)
  4  )
  5  partition by range( object_id)
  6  (
  7     partition big_table_par values less than(maxvalue)
  8  );
 

Table created.
 

SQL> alter table test1.big_table exchange partition big_table_par
  2  with table test.big_table;
 

Table altered.
 

SQL> select count(*) from test1.big_table;
 

  COUNT(*)
----------
     72329
 

SQL> select count(*) from test.big_table;
 

  COUNT(*)
----------
         0
 

SQL>

 

如上所示,我们假设test.big_table是一个非常大的表,那么使用exchange partition就能快速、高效地将一个大表修改Schema,唯一不足的是,需要将普通表修改为分区表,当然,很多时候,大表很可能已经是分区表,使用这种方式不会有任何修改。另外,我们上面都是测试普通表,其实如果是分区表,使用exchange partition则是最佳选择!

 

 

参考资料:

 

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


下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-11-05

  • 博文量
    43
  • 访问量
    30220