簡單測試11g interval partition table

CREATE TABLE purchase_interval

(

REGION_ID NUMBER,

ORDER_ID NUMBER,

time_id DATE

)

PARTITION BY RANGE (time_id)

INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3)

(

PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2005', 'dd-mm-yyyy')),

PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2007', 'dd-mm-yyyy'))

);

 

desc dba_tab_partitions;

select partition_name from dba_tab_partitions where table_name=UPPER('purchase_interval');

 

PARTITION_NAME

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

P2

P1

 

truncate table purchase_interval ;

insert into purchase_interval values (1,1, TO_DATE('1-1-2005', 'dd-mm-yyyy'));

commit;

 

insert into purchase_interval values (1,1, TO_DATE('1-2-2005', 'dd-mm-yyyy'));

commit;

 

insert into purchase_interval values (1,1, TO_DATE('1-3-2005', 'dd-mm-yyyy'));

commit;

select partition_name from dba_tab_partitions where table_name=UPPER('purchase_interval');

 

PARTITION_NAME

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

P2

P1

--建了兩個table partiton

 

insert into purchase_interval values (1,1, TO_DATE('1-1-2007', 'dd-mm-yyyy'));

commit;

 

select partition_name from dba_tab_partitions where table_name=UPPER('purchase_interval');

PARTITION_NAME

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

SYS_P681  --當塞入最新資料之後(大於1-1-2007以後的新資料) , 就會自動產生一個新的partition table

P2

P1

 

set long 999999999

set pagesize 300

select dbms_metadata.get_ddl('TABLE',  UPPER('purchase_interval')) from dual;

 

  CREATE TABLE "SYS"."PURCHASE_INTERVAL"

   (    "REGION_ID" NUMBER,

        "ORDER_ID" NUMBER,

        "TIME_ID" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM"

  PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) STORE IN

("TBS1", "TBS2", "TBS3")

(PARTITION "P1"  VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM" ,

PARTITION "P2"  VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD

HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM" )

 

可以看到DDL是存放當初建出來table的語法

另外, NUMTOYMINTERVAL(1,'month') 是自動add partition table by month ,

也可以設定七天自動add partition:  NUMTOYMINTERVAL(1,'week')

 

SQL> select PARTITION_NAME, TABLESPACE_NAME, PARTITION_POSITION from dba_tab_partitions  where table_name=UPPER('purchase_interval');

 

--而且舊的table partition 如果不指定tablespace存放在哪的話, 會自動存到user's default tablespace

PARTITION_NAME

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

TABLESPACE_NAME                PARTITION_POSITION

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

P1

SYSTEM                                          1

 

P2

SYSTEM                                          2

 

SYS_P681

TBS3                                            3

 

--建立第二個表格測試看看, 狀況是一樣的.

 

CREATE TABLE purchase_interval2

PARTITION BY RANGE (time_id)

INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3)

(

PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2005', 'dd-mm-yyyy')),

PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2007', 'dd-mm-yyyy'))

)

as select * from sys.purchase_interval;

 

select partition_name from dba_tab_partitions where table_name=UPPER('purchase_interval2');

 

PARTITION_NAME

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

SYS_P707

P2

P1

 

drop table purchase_interval;

drop table purchase_interval2;