[20120922]快速修改表的schema.txt

[20120922]快速修改表的schema.txt

如果想快速交换表的schema,常规方法是exp/imp,expdp/impdp或者ctas,在线重定义表等等,如果表很小,方法还可以.如果表大,就不是很好.

我以前写过一篇:

http://space.itpub.net/?uid-267265-action-viewspace-itemid-741154

http://www.akadia.com/services/ora_exchange_partition.html

通过底层修改直接修改sys.obj$对象.当然这种修改存在一定的风险!

实际上可以通过alter table命令变成分区表的方法来实现,自己做了一个测试:

1.测试环境

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

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

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

grant dba to test1 identified by test1;

grant dba to test2 identified by test2;

connect test1/test1

create table t as select rownum id,'test' name from dual connect by level 

create unique index i_t_id on t(id);

create table t_swap ( id number,name char(4)) partition by range (id) (partition pt1 values less than (maxvalue));

create unique index i_t_swap_id on t_swap(id) local;

--注意索引一定加local,不然swap时出现:

SQL> alter table t_swap exchange partition pt1 with table t including indexes  without validation;

alter table t_swap exchange partition pt1 with table t including indexes  without validation

                                                     *

ERROR at line 1:

ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

SQL> host oerr ora 14098

14098, 00000, "index mismatch for tables in ALTER TABLE EXCHANGE PARTITION"

// *Cause:  The two tables specified in the EXCHANGE have indexes which are

//          not equivalent

// *Action: Ensure that the indexes for the two tables have indexes which

//          follow this rule

//          For every non partitioned index for the non partitioned table,

//          there has to be an identical LOCAL index on the partitioned

//          table and vice versa. By identical, the column position, type

//          and size have to be the same.

2.交换schema=test1的表t为分区表:

--先查看一些相关信息:

SQL> column object_name format a20

SQL> column owner format a20

SQL> SELECT owner, object_name, subobject_name, object_id, data_object_id FROM dba_objects WHERE owner LIKE 'TEST_' ;

OWNER                OBJECT_NAME          SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID

-------------------- -------------------- ------------------------------ ---------- --------------

TEST1                T                                                       108465         108465

TEST1                I_T_ID                                                  108466         108466

TEST1                T_SWAP                                                  108475

TEST1                T_SWAP               PT1                                108476         108476

TEST1                I_T_SWAP_ID                                             108478

TEST1                I_T_SWAP_ID          PT1                                108479         108479

6 rows selected.

SQL> column segment_name format a20

SQL> column partition_name format a20

SQL> SELECT owner, segment_name, partition_name, header_file, header_block  FROM dba_segments WHERE owner LIKE 'TEST_';

OWNER                SEGMENT_NAME         PARTITION_NAME       HEADER_FILE HEADER_BLOCK

-------------------- -------------------- -------------------- ----------- ------------

TEST1                T                                                   4         1898

TEST1                T_SWAP               PT1                            4         1914

TEST1                I_T_ID                                              4         1906

TEST1                I_T_SWAP_ID          PT1                            4        13922

SQL> alter table t_swap exchange partition pt1 with table t including indexes  without validation;

Table altered.

--再来看看一些相关信息:

SQL> SELECT owner, object_name, subobject_name, object_id, data_object_id FROM dba_objects WHERE owner LIKE 'TEST_' ;

OWNER                OBJECT_NAME          SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID

-------------------- -------------------- ------------------------------ ---------- --------------

TEST1                T                                                       108465         108476

TEST1                I_T_ID                                                  108466         108479

TEST1                T_SWAP                                                  108475

TEST1                T_SWAP               PT1                                108476         108465

TEST1                I_T_SWAP_ID                                             108478

TEST1                I_T_SWAP_ID          PT1                                108479         108466

6 rows selected.

SQL> SELECT owner, segment_name, partition_name, header_file, header_block  FROM dba_segments WHERE owner LIKE 'TEST_';

OWNER                SEGMENT_NAME         PARTITION_NAME       HEADER_FILE HEADER_BLOCK

-------------------- -------------------- -------------------- ----------- ------------

TEST1                T                                                   4         1914

TEST1                T_SWAP               PT1                            4         1898

TEST1                I_T_ID                                              4        13922

TEST1                I_T_SWAP_ID          PT1                            4         1906

--注意看看data_object_id字段可以发现发生了交换,^_^,实际上是交换了名字!以及dba_segments的HEADER_BLOCK也可以看出!从这里也可以看出swap很快的,产生的redo也很少.

SQL> select count(*) from t;

  COUNT(*)

----------

         0

SQL> select count(*) from t_swap;

  COUNT(*)

----------

      1000

3.这里仅仅交换为分区表,在换回普通表才完成任务,而且schema并没有变:

connect test1/test1

drop table t purge;

connect test2/test2

create table t as select rownum id,'test' name from dual where 1=2;

create unique index i_t_id on t(id) ;

SQL> alter table test1.t_swap exchange partition pt1 with table test2.t including indexes  without validation;

Table altered.

--为了避免错误,我加入了表的schema.

--OK转化完成,这个过程仅仅需要一个分区表作为中间的转换.

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

  COUNT(*)

----------

         0

SQL> select count(*) from test2.t ;

  COUNT(*)

----------

      1000

--查询相关信息:

SQL> SELECT owner, object_name, subobject_name, object_id, data_object_id FROM dba_objects WHERE owner LIKE 'TEST2' ;

OWNER                OBJECT_NAME          SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID

-------------------- -------------------- ------------------------------ ---------- --------------

TEST2                T                                                       108491         108465

TEST2                I_T_ID                                                  108492         108466

SQL> SELECT owner, segment_name, partition_name, header_file, header_block  FROM dba_segments WHERE owner LIKE 'TEST2';

OWNER                SEGMENT_NAME         PARTITION_NAME       HEADER_FILE HEADER_BLOCK

-------------------- -------------------- -------------------- ----------- ------------

TEST2                T                                                   4         1898

TEST2                I_T_ID                                              4         1906

--摘要对比开头的信息:

OWNER                OBJECT_NAME          SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID

-------------------- -------------------- ------------------------------ ---------- --------------

TEST1                T                                                       108465         108465

TEST1                I_T_ID                                                  108466         108466

OWNER                SEGMENT_NAME         PARTITION_NAME       HEADER_FILE HEADER_BLOCK

-------------------- -------------------- -------------------- ----------- ------------

TEST1                T                                                   4         1898

TEST1                I_T_ID                                              4         1906

--可以发现仅仅schema不同罢了.