随笔-196  评论-90  文章-8  trackbacks-0

1、创建表

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

2、插入数据

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
SQL
> COMMIT;

3、在线重定义的表自行验证,看该表是否可以重定义,

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user'T', DBMS_REDEFINITION.CONS_USE_PK);
 (如果没有定义主键会提示以下错误信息
 
begin dbms_redefinition.can_redef_table(user,'pft_party_profit_detail'); end;
 ORA
-12089: cannot online redefine table "OFSA"."PFT_PARTY_PROFIT_DETAIL" with no primary key
 ORA
-06512: at "SYS.DBMS_REDEFINITION", line 8
 ORA
-06512: at "SYS.DBMS_REDEFINITION", line 247
 ORA
-06512: at line 1
 
 出错了, 该表上缺少主键,为该表建主键。再执行验证。
 SQL
> alter table t add constraint pk_t primary key(id);
 
Table altered)

4、建个和源表表结构一样的分区表,作为中间表。按日期范围分区

 

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 
     (PARTITION P1 
VALUES LESS THAN (TO_DATE('2004-7-1''YYYY-MM-DD')), 
     PARTITION P2 
VALUES LESS THAN (TO_DATE('2005-1-1''YYYY-MM-DD')), 
     PARTITION P3 
VALUES LESS THAN (TO_DATE('2005-7-1''YYYY-MM-DD')), 
     PARTITION P4 
VALUES LESS THAN (MAXVALUE));
 这里值得注意的一个问题是:PARTITION P4 
VALUES LESS THAN (MAXVALUE))是把所有剩下的数据分在一个区里,如果你想一个月建一个分区,那最好写成这样,提前建好,目前我还不知道能不能自动建,如果哪位知道请告知我,谢谢  
 SQL
> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 
     (PARTITION P20070201 
VALUES LESS THAN (TO_DATE('2007-2-1''YYYY-MM-DD')), 
     PARTITION P20070301 
VALUES LESS THAN (TO_DATE('2005-3-1''YYYY-MM-DD')),
     PARTITION P20070401 
VALUES LESS THAN (TO_DATE('2005-4-1''YYYY-MM-DD')), 
     PARTITION P20070501 
VALUES LESS THAN (TO_DATE('2005-5-1''YYYY-MM-DD')),  
     PARTITION P20070601 
VALUES LESS THAN (TO_DATE('2005-6-1''YYYY-MM-DD')));  

 

5、执行表的在线重定义:

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER'T''T_NEW');

6、执行把中间表的内容和数据源表进行同步。

SQL>execute dbms_redefinition.sync_interim_table(user,'t','t_new');

7、执行结束在线定义过程

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user'T''T_NEW');

8、查看数据字典,可以看到改表已经成为了分区表。

SQL> SELECT COUNT(*FROM T;
  
COUNT(*)
----------
      6264
SQL
> SELECT COUNT(*FROM T PARTITION (P2);
  
COUNT(*)
----------
      6246
SQL
> SELECT COUNT(*FROM T PARTITION (P3);


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

至此普通表转为分区操作完成

9、如果执行在线重定义的过程中出错

可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行:DBMS_REDEFINITION.abort_redef_table(user't''t_new')以放弃执行在线重定义。

 

10、如果出现以下错误:

SQL> exec dbms_redefinition.can_redef_table(user't');
BEGIN dbms_redefinition.can_redef_table(user't'); END;

*
ERROR at line 
1:
ORA
-12091: cannot online redefine table "user"."t" with
materialized views
ORA
-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA
-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA
-06512: at line 1

用这句删除materialized 
view 即可继续进行
drop materialized view log on <tablename>;
drop materialized view log on t;
    OR drop materialized t;

详细原理请查看:
Oracle的在线重定义表功能:http://tb.blog.csdn.net/TrackBack.aspx?PostId=900018
Oracle的在线重定义表功能(二):http://blog.itpub.net/post/468/12962

posted on 2007-04-19 17:11 一凡 阅读(6344) 评论(4)  编辑  收藏 所属分类: DATABASE

评论:
# re: Oracle的在线重定义(将普通表转为分区表ora9i以上版本) 2013-12-14 19:19 | hubery
很好,赞一个!
老大说了看到好文要评论,更好赞一个!  回复  更多评论
  
# re: Oracle的在线重定义(将普通表转为分区表ora9i以上版本) 2013-12-15 21:41 | hubery
弱弱的问下,这个例子是在什么用户下操作的,我用sysdba用户操作了报错:
SQL> exec dbms_redefinition.can_redef_table(user,'t',dbms_redefinition.cons_use_pk);
BEGIN dbms_redefinition.can_redef_table(user,'t',dbms_redefinition.cons_use_pk); END;

*
第 1 行出现错误:
ORA-12087: 在 "SYS" 拥有的表上不允许联机重新定义
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 1
  回复  更多评论
  
# re: Oracle的在线重定义(将普通表转为分区表ora9i以上版本) 2013-12-15 21:58 | hubery
我使用其他自己创建的用户则报:
SQL> exec dbms_redefinition.can_redef_table(user,'t',dbms_redefinition.cons_use_pk);
BEGIN dbms_redefinition.can_redef_table(user,'t',dbms_redefinition.cons_use_pk); END;

*
第 1 行出现错误:
ORA-06550: 第 1 行, 第 50 列:
PLS-00201: 必须声明标识符 'DBMS_REDEFINITION'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored  回复  更多评论
  
# re: Oracle的在线重定义(将普通表转为分区表ora9i以上版本) 2013-12-15 23:34 | hubery
@hubery
OK~!我明白了,查了下资料,原来是需要给用户授予一个角色:
SQL> grant EXECUTE_CATALOG_ROLE to hubery;

授权成功。  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航: