create table tp1
(
....
sTime date
)
TABLESPACE 表空间名
PARTITION BY RANGE (sTime)
(
........
PARTITION PART_2008_04_01 VALUES LESS THAN (TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION PART_2008_05_01 VALUES LESS THAN (TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION PART_2008_06_01 VALUES LESS THAN (TO_DATE(' 2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION PART_2008_07_01 VALUES LESS THAN (TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION PART_2008_08_01 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION PART_2008_09_01 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
/
rename 原表 to bak_xxxx
/
rename tp1 to 原表
/
alter table 原表 nologging
/
alter session enable parallel dml
/
insert /*+ append */ into 原表 select * from bak_xxxx
/
CREATE INDEX IDX_xxxxxx_Time ON 原表 (STIME)
noLOGGING
local
TABLESPACE indx
/
commit
/
alter table 原表 logging;
begin dbms_redefinition.can_redef_table('jxc','jxc_art'); end;
ORA-12089: cannot online redefine table "JXC"."JXC_ART" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
begin DBMS_REDEFINITION.CAN_REDEF_TABLE('jxc_art'); end;
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CAN_REDEF_TABLE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
方法1:停应用,然后exp;rename table;创建分区表;imp进分区.
方法2:在线重定义(不需要停应用),下面是个例子.
create table test as select * from dba_users;
alter table TEST add primary key (USERNAME);
create index I_TEST on TEST (USER_ID);
CREATE TABLE TEST1 PARTITION BY RANGE(USER_ID)
(PARTITION P1 VALUES LESS THAN(20) TABLESPACE NNC_DATA01,
PARTITION P2 VALUES LESS THAN(40) TABLESPACE NNC_DATA02,
PARTITION P3 VALUES LESS THAN(MAXVALUE)TABLESPACE NNC_DATA03) enable row movement AS
SELECT * FROM TEST;
alter table TEST1 add primary key (USERNAME);
create index I_TEST1 on TEST (USER_ID);
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TEST');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TEST', 'TEST1');
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'TEST', 'TEST1');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TEST', 'TEST1');
drop table test1;
通过上面的方法就把test表进行了分区。
两个方法:
方法1:停应用,然后exp;rename table;创建分区表;imp进分区.
方法2:在线重定义(不需要停应用),下面是个例子.
create table test as select * from dba_users;
alter table TEST add primary key (USERNAME);
create index I_TEST on TEST (USER_ID);
CREATE TABLE TEST1 PARTITION BY RANGE(USER_ID)
(PARTITION P1 VALUES LESS THAN(20) TABLESPACE NNC_DATA01,
PARTITION P2 VALUES LESS THAN(40) TABLESPACE NNC_DATA02,
PARTITION P3 VALUES LESS THAN(MAXVALUE)TABLESPACE NNC_DATA03) enable row movement AS
SELECT * FROM TEST;
alter table TEST1 add primary key (USERNAME);
create index I_TEST1 on TEST (USER_ID);
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TEST');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TEST', 'TEST1');
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'TEST', 'TEST1');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TEST', 'TEST1');
drop table test1;
通过上面的方法就把test表进行了分区。