对存在的数据做PARTITION,情况是这样的,有一个表里面的数据是从其他服务器的DMP文件IMP导入,
现在的数据是已经存在的,想做PARTITION 处理。数据量有
COUNT(*)
----------
11695929
这么多,是2年的数据,想按月多分区处理
数据库是Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
现在的数据是已经存在的,想做PARTITION 处理。数据量有
COUNT(*)
----------
11695929
这么多,是2年的数据,想按月多分区处理
数据库是Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
方法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表进行了分区。
方法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表进行了分区。
PLS-00306: wrong number or types of arguments in call to 'CAN_REDEF_TABLE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored报错误
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
大概这样操作: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;