[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不同罢了.
时间： 2024-09-10 23:43:09