表的大小

. . exported "ECARD"."RECORDS_YX"                        1.020 GB 28966552 rows
rows

參考分區方法
partition by range (INTIME)
(
  partition P20090501 values less than (TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

'NLS_CALENDAR=GREGORIAN'))
    tablespace TBS_ECARD
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 4M
      next 2M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),

  partition P20110501 values less than ('20110501')
    tablespace TBS_SFCDATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 2M
      next 2M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
開始建立

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as system

1，測試
SQL> exec dbms_redefinition.can_redef_table('ECARD','RECORDS_YX');

PL/SQL procedure successfully completed
2
中建表
輔助建立
/*create table ECARD.RECORDS_YX_T
(
  IDX       NUMBER(10) not null,
  SNO       NUMBER(10) not null,
  DEVICEMAC VARCHAR2(8),
  INTIME    DATE,
  GETIME    DATE default sysdate
)
tablespace TBS_ECARD
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  )
partition by range(INTIME)
( PARTITION P201001 values less than (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
  PARTITION P201101 values less than (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
  PARTITION P201201 values less than (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
  PARTITION P201206 values less than (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
  PARTITION P201211 values less than (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
  PARTITION P201304 values less than (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),
  PARTITION P201309 values less than (TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
);
3,步骤三、开始进行重定义
SQL> select count(*) from ECARD.RECORDS_YX;

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

SQL> execute dbms_redefinition.start_redef_table('ECARD','RECORDS_YX','RECORDS_YX_T',null,2);

PL/SQL procedure successfully completed

SQL> select count(*) from ECARD.RECORDS_YX_T;

  COUNT(*)
----------
  29028541
步骤四、保证数据的一致性：
SQL> execute dbms_redefinition.sync_interim_table('ECARD','RECORDS_YX','RECORDS_YX_T');

PL/SQL procedure successfully completed
步骤五、开始重定义之后，完成重定义之前，需要在中间表上创建与源表对应的索引、外键、触发器等，而中

间表与源表对应的主键，如果需要按照主键重定义，需要再开始redefine之前创建，如果按照rowid进行重定义

，对应主键呢？
alter table ECARD.RECORDS_YX_T
  add constraint PK_RECORDS_YXX_T primary key (IDX)
  using index
  tablespace NDX_ECARD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );*/
-- Create/Recreate indexes
create index ECARD.INDEX_RECORDS_YXX_01_T on ECARD.RECORDS_YX_T (SNO)
  tablespace NDX_ECARD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index ECARD.INDEX_RECORDS_YXX_02_T on ECARD.RECORDS_YX_T (INTIME)
  tablespace TBS_ECARD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

步骤六、完成重定义

----完成重定义
SQL> execute dbms_redefinition.finish_redef_table('ECARD','RECORDS_YX','RECORDS_YX_T');

PL/SQL procedure successfully completed

SQL> select count(*) from ECARD.RECORDS_YX;

  COUNT(*)
----------
  29028541
步骤七：刪除中間表
SQL> DROP TABLE ECARD.RECORDS_YX_T;

Table dropped
步骤七：檢查表索引是否生效
 
  select * from dba_indexes where wner='ECARD' and table_name='RECORDS_YX';