oracle表转为分区表,oracle 11.2 普通表转换为分区表的常见方法

1、模拟大表

--新建测试表

create tablespace test

datafile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\test01.DBF'

size 2000m

autoextend on

next 100m maxsize unlimited

extent management local autoallocate

segment space management auto;

create table t1

(

tid number not null primary key,

sname varchar2(10)

)

tablespace test;

--循环导入数据

declare

maxrecords constant int:=200000;

i int :=1;

begin

for i in 1..maxrecords loop

insert into t1 values(i,'ocpyang');

end loop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

/

-------方法1：在线重定义

建立中间表(分区表，你想得到的格式)

CREATE TABLE t11

(

tid number not null primary key,

sname varchar2(10)

)

PARTITION BY RANGE (tid)

(

PARTITION p1 VALUES LESS THAN (50000) tablespace test,

PARTITION p2 VALUES LESS THAN (100000) tablespace test,

PARTITION p3 VALUES LESS THAN (150000) tablespace test,

PARTITION p4 VALUES LESS THAN (300000) tablespace test

)

;

验证原表是否可以在线迁移

exec dbms_redefinition.can_redef_table('SCOTT','T1');

新开启一个会话验证当前会话没有REDO产生

select t3.sid,t2.NAME,t1.VALUE from v$sesstat t1 ,

v$statname t2 ,

(select SID from v$mystat where rownum<=1) t3

where t1.SID=t3.sid and t1.STATISTIC#=t2.STATISTIC#

and t2.name like '%redo%' and t1.VALUE<>0 ;

SID NAME VALUE

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

200 redo entries 407915

200 redo size 106889084

200 redo size for direct writes 2729848

200 redo buffer allocation retries 3

200 redo log space requests 5

200 redo log space wait time 23

200 redo ordering marks 1031

200 redo subscn max counts 17

200 redo synch time 1

200 redo synch time (usec) 6903

200 redo synch writes 5

开始联机重定义：

SQL> exec dbms_redefinition.start_redef_table('SCOTT','T1','T11');

同步数据:

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','T1','T11');

结束联机重定义：

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','T1','T11');

select t3.sid,t2.NAME,t1.VALUE from v$sesstat t1 ,

v$statname t2 ,

(select SID from v$mystat where rownum<=1) t3

where t1.SID=t3.sid and t1.STATISTIC#=t2.STATISTIC#

and t2.name like '%redo%' and t1.VALUE<>0 ;

SID NAME VALUE

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

200 redo entries 419232

200 redo size 130386624

200 redo size for direct writes 11235576

200 redo buffer allocation retries 3

200 redo log space requests 5

200 redo log space wait time 23

200 redo ordering marks 1993

200 redo subscn max counts 41

200 redo synch time 25

200 redo synch time (usec) 243493

200 redo synch writes 33

查看数据已经移动完成，索引已经迁移.

如果分区表并没有主键，我们建立主键

alter table t11 add constraint pk_t11 primary key (tid);

---方法2

使用NOLOGGING方法

CREATE TABLE t12

(

tid number not null primary key,

sname varchar2(10)

)

PARTITION BY RANGE (tid)

(

PARTITION p1 VALUES LESS THAN (50000) tablespace test nologging ,

PARTITION p2 VALUES LESS THAN (100000) tablespace test nologging,

PARTITION p3 VALUES LESS THAN (150000) tablespace test nologging,

PARTITION p4 VALUES LESS THAN (300000) tablespace test nologging

)

;

/****或对分区表修改

alter table t12 modify partition p1 nologging;

***/

select t3.sid,t2.NAME,t1.VALUE from v$sesstat t1 ,

v$statname t2 ,

(select SID from v$mystat where rownum<=1) t3

where t1.SID=t3.sid and t1.STATISTIC#=t2.STATISTIC#

and t2.name like '%redo%' and t1.VALUE<>0 ;

SID NAME VALUE

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

200 redo entries 419448

200 redo size 130464436

200 redo size for direct writes 11252032

200 redo buffer allocation retries 3

200 redo log space requests 5

200 redo log space wait time 23

200 redo ordering marks 1998

200 redo subscn max counts 41

200 redo synch time 25

200 redo synch time (usec) 244362

200 redo synch writes 37

然后开启新会话进行INSERT APPEND

insert /* +append */ into t12 select * from t1;

select t3.sid,t2.NAME,t1.VALUE from v$sesstat t1 ,

v$statname t2 ,

(select SID from v$mystat where rownum<=1) t3

where t1.SID=t3.sid and t1.STATISTIC#=t2.STATISTIC#

and t2.name like '%redo%' and t1.VALUE<>0 ;

SID NAME VALUE

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

200 redo entries 434385

200 redo size 155328372

200 redo size for direct writes 11260260

200 redo buffer allocation retries 5

200 redo log space requests 7

200 redo log space wait time 29

200 redo ordering marks 2775

200 redo subscn max counts 50

200 redo synch time 28

200 redo synch time (usec) 273343

200 redo synch writes 39

alter table t12 rename to t1; --修改分区表为T1的表名即可

---方法3

直接使用CREATE TABLE NOLOGGING方式

select t3.sid,t2.NAME,t1.VALUE from v$sesstat t1 ,

v$statname t2 ,

(select SID from v$mystat where rownum<=1) t3

where t1.SID=t3.sid and t1.STATISTIC#=t2.STATISTIC#

and t2.name like '%redo%' and t1.VALUE<>0 ;

SID NAME VALUE

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

200 redo entries 434385

200 redo size 155328372

200 redo size for direct writes 11260260

200 redo buffer allocation retries 5

200 redo log space requests 7

200 redo log space wait time 29

200 redo ordering marks 2775

200 redo subscn max counts 50

200 redo synch time 28

200 redo synch time (usec) 273343

200 redo synch writes 39

CREATE TABLE t13

PARTITION BY RANGE (tid)

(

PARTITION p1 VALUES LESS THAN (50000) tablespace test nologging ,

PARTITION p2 VALUES LESS THAN (100000) tablespace test nologging,

PARTITION p3 VALUES LESS THAN (150000) tablespace test nologging,

PARTITION p4 VALUES LESS THAN (300000) tablespace test nologging

)

as

select * from t1;

select t3.sid,t2.NAME,t1.VALUE from v$sesstat t1 ,

v$statname t2 ,

(select SID from v$mystat where rownum<=1) t3

where t1.SID=t3.sid and t1.STATISTIC#=t2.STATISTIC#

and t2.name like '%redo%' and t1.VALUE<>0 ;

SID NAME VALUE

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

200 redo entries 434699

200 redo size 155392656

200 redo size for direct writes 11262224

200 redo buffer allocation retries 5

200 redo log space requests 7

200 redo log space wait time 29

200 redo ordering marks 2782

200 redo subscn max counts 50

200 redo synch time 28

200 redo synch time (usec) 273797

200 redo synch writes 40

alter table t13 rename to t1; ----修改分区表为T1的表名即可

-------方法4：交换分区

SQL> select constraint_name from user_constraints where table_name='T1';

CONSTRAINT_NAME

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

SYS_C0025179

SYS_C0025180

ALTER TABLE t1 DROP PRIMARY KEY;

CREATE TABLE t14

(

tid number not null,

sname varchar2(10)

)

PARTITION BY RANGE (tid)

(

PARTITION p1 VALUES LESS THAN (300000) tablespace test nologging ,

PARTITION p2 VALUES LESS THAN (400000) tablespace test nologging,

PARTITION p3 VALUES LESS THAN (500000) tablespace test nologging,

PARTITION p4 VALUES LESS THAN (600000) tablespace test nologging

)

;

select count(*) from t1;

COUNT(*)

----------

200000

select count(*) from t14 partition (p1);

COUNT(*)

----------

0

alter table t14 exchange partition p1 with table t1;

alter table t14

add constraint pk_t14 primary key(tid);

alter table t14 rename to t1;