oracle在线重定义(一)普通表到分区表

于 2012-01-13 15:32:00 发布
阅读量125 收藏
点赞数
文章标签： 数据库 人工智能
本文介绍Oracle数据库中如何使用在线重定义将普通表转换为分区表的过程，包括使用dbms_redefinition包进行重定义的各个步骤及注意事项。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

oracle在线重定义(一）

(2010-09-25 21:45:09)
转载▼
分类：oracle随笔

一、废话几句：

关于pd12导出图像，

首先ctrl+A全选
然后：edit->ExportImage——>emf格式为默认，实则任选就OK了；

下午时隔4个月再次画ER图，相对的感觉要熟练很多，很多功能也都晓得了，但是不好的是对需求把握不到，粗糙，慢慢来，take iteasy.

<wbr></wbr>

二、普通表转换为分区表的做法：其中一种便是使用oracle自带的包进行重定义，简单测试一下，感觉还比较好用；但是出现了一个问题：①需要有相应的系统权限（待查，目前用的是sys用户测试）；②是否表一定要有主键才能进行重定义？还是至少需要索引？按照重定义的方式来说，默认按照主键；可选择的为rowid;

包中的定义，可以用pl/sql工具看下包dbms_redefinition的用法

<wbr><wbr>--Constants for the options_flag parameter ofstart_redef_table<br><wbr>cons_use_pk<wbr><wbr><wbr>CONSTANT PLS_INTEGER := 1;---主键重定义<br><wbr> cons_use_rowid CONSTANT PLS_INTEGER :=2;---rowid重定义</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr></wbr>

<wbr>config:</wbr>源表LOGIN_USER_DETAIL数据量考虑，需要转换为分区表；

<wbr></wbr>CREATE TABLEEDU.LOGIN_USER_DETAIL
<wbr>(<br><wbr><wbr><wbr>ID<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>NUMBER(10)<wbr><wbr><wbr>NOT NULL,<br><wbr><wbr><wbr>WEB_ID<wbr><wbr><wbr><wbr><wbr>NUMBER(8)<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>NULL,<br><wbr><wbr><wbr>TICKET<wbr><wbr><wbr><wbr><wbr>VARCHAR2(40)<wbr><wbr><wbr><wbr><wbr>NULL,<br><wbr><wbr><wbr>STATUS<wbr><wbr><wbr><wbr><wbr>NUMBER(2)<wbr><wbr><wbr><wbr>DEFAULT 0 NOT NULL,<br><wbr><wbr><wbr> CLIENT_INFOVARCHAR2(100)<wbr><wbr><wbr><wbr>NULL,<br><wbr><wbr><wbr> SERVER_NAMEVARCHAR2(40)<wbr><wbr><wbr><wbr><wbr>NULL,<br><wbr><wbr><wbr> CREATE_DATEDATE<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>NULL<br><wbr>)</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr>/</wbr>

<wbr>CREATE INDEX EDU.IDX_WEBID_TICKET_CLINFO<br><wbr>ONEDU.LOGIN_USER_DETAIL(WEB_ID,TICKET,CLIENT_INFO)</wbr></wbr>

<wbr></wbr>

步骤一、测试能否表进行在线重定义，

<wbr>begin<br><wbr>dbms_redefinition.can_redef_table('edu','LOGIN_USER_DETAIL');<br><wbr>end;</wbr></wbr></wbr>

<wbr></wbr>

出现错误

ORA-12089: cannot online redefine table"EDU"."LOGIN_USER_DETAIL" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

原因是默认为主键在线重定义的方式，需要设定参数为2，按照rowid重定义；

<wbr><wbr><wbr>begin<br><wbr><wbr><wbr>dbms_redefinition.can_redef_table('edu','LOGIN_USER_DETAIL',<strong>2</strong>);<br><wbr><wbr><wbr> end;</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr><wbr><wbr>/</wbr></wbr></wbr>

<wbr><wbr><wbr>或者</wbr></wbr></wbr>

<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr>begin
<wbr><wbr><wbr>dbms_redefinition.can_redef_table('edu','LOGIN_USER_DETAIL',<strong>dbms_redefinition.cons_use_rowid</strong>);<br><wbr><wbr><wbr> end;</wbr></wbr></wbr></wbr></wbr></wbr>

PL/SQL procedure successfully completed

<wbr></wbr>

步骤二、创建中间表

<wbr>create table edu.LOGIN_USER_DETAIL_new(<br><wbr><wbr><wbr>ID<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>NUMBER(10)<wbr><wbr><wbr>NOT NULL,<br><wbr><wbr><wbr>WEB_ID<wbr><wbr><wbr><wbr><wbr>NUMBER(8)<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>NULL,<br><wbr><wbr><wbr>TICKET<wbr><wbr><wbr><wbr><wbr>VARCHAR2(40)<wbr><wbr><wbr><wbr><wbr>NULL,<br><wbr><wbr><wbr>STATUS<wbr><wbr><wbr><wbr><wbr>NUMBER(2)<wbr><wbr><wbr><wbr>DEFAULT 0 NOT NULL,<br><wbr><wbr><wbr> CLIENT_INFOVARCHAR2(100)<wbr><wbr><wbr><wbr>NULL,<br><wbr><wbr><wbr> SERVER_NAMEVARCHAR2(40)<wbr><wbr><wbr><wbr><wbr>NULL,<br><wbr><wbr><wbr> CREATE_DATEDATE<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>NULL<br><wbr>)<br> PARTITION BY RANGE(CREATE_DATE)<br> ( PARTITION part23 VALUES LESS THAN (TO_DATE('2010-09-24','YYYY-MM-DD')),<br><wbr> PARTITION part24 VALUES LESS THAN(TO_DATE('2010-09-25', 'YYYY-MM-DD')),<br><wbr> PARTITION Part25 VALUES LESS THAN(TO_DATE('2010-09-26', 'YYYY-MM-DD')),<br><wbr> PARTITION partmax VALUES LESS THAN(MAXVALUE)<br> );</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr></wbr>

步骤三、开始进行重定义

executedbms_redefinition.start_redef_table('edu','LOGIN_USER_DETAIL','LOGIN_USER_DETAIL_new',null,2);

---------需要注意这里的输入参数选择
<wbr> PROCEDUREstart_redef_table(uname<wbr><wbr><wbr><wbr><wbr><wbr><wbr>IN VARCHAR2,----用户名<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>orig_table<wbr><wbr> INVARCHAR2,----源表名<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>int_table<wbr><wbr><wbr> INVARCHAR2,----中间表名<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>col_mapping<wbr> IN VARCHAR2 :=NULL,---源表和中间表列之间的映射，map；<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>options_flag IN BINARY_INTEGER := 1,---重定义方式<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>orderby_cols IN VARCHAR2 := NULL,---对于分区表重定义的时候，分区列名<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>part_name<wbr><wbr><wbr> INVARCHAR2 :=NULL);---对于分区表重定义的时候，需要重定义的分区。其中最后2个参数没用到，因为这里是由<strong>普通表</strong>转换为<strong>分区表</strong>；</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

start_redef_table执行时间为12.5s 数据量为80万。

<wbr></wbr>

步骤三和步骤四中间步骤：

select count(*)
from edu.LOGIN_USER_DETAIL_new；

/

select count(*)
from edu.LOGIN_USER_DETAIL;

----数据会有差别 而且与实际不符

/

并且对表进行DML操作

<wbr>delete edu.login_user_detail<br><wbr>where id=355;</wbr></wbr>

步骤四、保证数据的一致性：

execute dbms_redefinition.sync_interim_table('edu','LOGIN_USER_DETAIL','LOGIN_USER_DETAIL_new');

<wbr></wbr>

步骤五、开始重定义之后，完成重定义之前，需要在中间表上创建与源表对应的索引、外键、触发器等，而中间表与源表对应的主键，如果需要按照主键重定义，需要再开始redefine之前创建，如果按照rowid进行重定义，对应主键呢？

<wbr></wbr>

CREATE INDEX EDU.IDX_WEBID_TICKET_CLIFO
<wbr><wbr><wbr> ONEDU.LOGIN_USER_DETAIL_new(WEB_ID,TICKET,CLIENT_INFO)<br> TABLESPACE EDUCATION<br> NOLOGGING<br> PCTFREE 10<br> INITRANS 2<br> MAXTRANS 255<br> STORAGE(BUFFER_POOL DEFAULT)<br> NOPARALLEL<br> NOCOMPRESS</wbr></wbr></wbr>

<wbr></wbr>

步骤六、完成重定义

----完成重定义
executedbms_redefinition.finish_redef_table('edu','LOGIN_USER_DETAIL','LOGIN_USER_DETAIL_new');

<wbr></wbr>

desc LOGIN_USER_DETAIL
/
select table_name,index_name,index_type,status
from user_indexes
where table_name in ('LOGIN_USER_DETAIL','LOGIN_USER_DETAIL_new');

------看一下

此时的'LOGIN_USER_DETAIL的索引为EDU.IDX_WEBID_TICKET_CLIFO

而非原来EDU.IDX_WEBID_TICKET_CLINFO，

<wbr></wbr>

LOGIN_USER_DETAIL_new的索引由EDU.IDX_WEBID_TICKET_CLINFO变成EDU.IDX_WEBID_TICKET_CLIFO

表结构变成分区表的结构，而的LOGIN_USER_DETAIL_new的结构变成了redefine之前的的LOGIN_USER_DETAIL的普通表结构；

对于触发器又会是什么效果呢？

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

<wbr></wbr>

<wbr></wbr>

步骤七：

DROP TABLE edu.LOGIN_USER_DETAIL_new

<wbr></wbr>

结论：

重定义的方式有2个按照rowid、主键；

重定义的过程中可以对表进行DML操作；

重定义的速度还是挺快的；

重定义需要用户有一定系统权限（不知道是不是合适这么说）、需要一个中间表（中间表的结构、列名可以与be_organized的表不同）、重定义开始到重定义结束需要在中间表上建一系列约束；重定义完成之后的效果是：

be_organized的表结构、约束信息与中间表的互相交换，二者数据量一致。

可以普通表转为分区表；

分区表也可以重定义。


iteye_18365
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值