SQL>
SQL> create table TBILLSIGN
2 (
3 BNO VARCHAR2(12) not null,
4 DELI VARCHAR2(12) not null,
5 INPUTOPR VARCHAR2(12) not null,
6 INPUTTIMESTAMP DATE not null,
7 MONEYTYPE NUMBER(4)
8 )
9 partition by range (INPUTTIMESTAMP)
10 (
11 partition TBILLSIGN_M200704 values less than (TO_DATE(' 2007-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
12 tablespace TBILLSIGN200704
13 pctfree 10
14 initrans 2
15 maxtrans 255
16 storage
17 (
18 initial 1M
19 minextents 1
20 maxextents unlimited
21 ),
22 partition TBILLSIGN_M200705 values less than (TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
23 tablespace TBILLSIGN200705
24 pctfree 10
25 initrans 2
26 maxtrans 255
27 storage
28 (
29 initial 1M
30 minextents 1
31 maxextents unlimited
32 ),
33 partition TBILLSIGN_M200706 values less than (TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
34 tablespace TBILLSIGN200706
35 pctfree 10
36 initrans 2
37 maxtrans 255
38 storage
39 (
40 initial 1M
41 minextents 1
42 maxextents unlimited
43 ),
44 partition TBILLSIGN_M200707 values less than (TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
45 tablespace TBILLSIGN200707
46 pctfree 10
47 initrans 2
48 maxtrans 255
49 storage
50 (
51 initial 1M
52 minextents 1
53 maxextents unlimited
54 )
55 );Table createdSQL> select * from user_part_tables;TABLE_NAME PARTITIONING_TYPE SUBPARTITIONING_TYPE PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT SUBPARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME DEF_PCT_FREE DEF_PCT_USED DEF_INI_TRANS DEF_MAX_TRANS DEF_INITIAL_EXTENT DEF_NEXT_EXTENT DEF_MIN_EXTENTS DEF_MAX_EXTENTS DEF_PCT_INCREASE DEF_FREELISTS DEF_FREELIST_GROUPS DEF_LOGGING DEF_COMPRESSION DEF_BUFFER_POOL
------------------------------ ----------------- -------------------- --------------- ---------------------- ---------------------- ------------------------- ------------------------------ ------------ ------------ ------------- ------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------- ------------------- ----------- --------------- ---------------
TBILLSIGN RANGE NONE 4 0 1 0 USERS 10 1 255 DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT 0 0 NONE NONE DEFAULT
我们可以看到这个表按时间分为4个区
SQL> select * from user_tab_partitions;TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
TBILLSIGN NO TBILLSIGN_M200707 0 TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 4 TBILLSIGN200707 10 40 2 255 1048576 1 2147483645 1 1 YES DISABLED DEFAULT NO NO
TBILLSIGN NO TBILLSIGN_M200706 0 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 3 TBILLSIGN200706 10 40 2 255 1048576 1 2147483645 1 1 YES DISABLED DEFAULT NO NO
TBILLSIGN NO TBILLSIGN_M200705 0 TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 2 TBILLSIGN200705 10 40 2 255 1048576 1 2147483645 1 1 YES DISABLED DEFAULT NO NO
TBILLSIGN NO TBILLSIGN_M200704 0 TO_DATE(' 2007-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 1 TBILLSIGN200704 10 40 2 255 1048576 1 2147483645 1 1 YES DISABLED DEFAULT NO NOSQL> select add_months(sysdate,-4) from dual;ADD_MONTHS(SYSDATE,-4)
----------------------
2007-4-15 21:22:57 时间为4月份 小于5月份 所以在第一个分区。SQL> insert into TBILLSIGN values ('wanghui','wanghui','wanghui',add_months(sysdate,-4),4);1 row insertedSQL> select add_months(sysdate,-3) from dual;ADD_MONTHS(SYSDATE,-3)
----------------------
2007-5-15 21:23:22 时间为5月份 小于6月份 所以在第二个分区。SQL> insert into TBILLSIGN values ('wanghui','wanghui','wanghui',add_months(sysdate,-3),4);1 row insertedSQL> select add_months(sysdate,-2) from dual;ADD_MONTHS(SYSDATE,-2)
----------------------
2007-6-15 21:23:42 时间为6月份 小于7月份 所以在第三个分区。SQL> insert into TBILLSIGN values ('wanghui','wanghui','wanghui',add_months(sysdate,-2),4);1 row insertedSQL> select add_months(sysdate,-1) from dual;ADD_MONTHS(SYSDATE,-1)
----------------------
2007-7-15 21:23:53 时间为7月份 小于8月份 所以在第四个分区。SQL> insert into TBILLSIGN values ('wanghui','wanghui','wanghui',add_months(sysdate,-1),4);1 row inserted
SQL> select sysdate from dual;SYSDATE
-----------
2007-8-15 2 时间为8月份 超过了我们定的范围SQL>注意看下面会报错:SQL> insert into TBILLSIGN values ('wanghui','wanghui','wanghui',sysdate,4);insert into TBILLSIGN values ('wanghui','wanghui','wanghui',sysdate,4)ORA-14400: 插入的分区关键字未映射到任何分区SQL>解决办法：
SQL> ALTER TABLE TBILLSIGN ADD PARTITION TBILLSIGN_M2007max VALUES LESS THAN ( MAXVALUE) TABLESPACE TBILLSIGN200707;Table alteredSQL> insert into TBILLSIGN values ('wanghui','wanghui','wanghui',sysdate,4);1 row insertedSQL> commit;Commit complete
问题解决
SQL> select * from TBILLSIGN;BNO DELI INPUTOPR INPUTTIMESTAMP MONEYTYPE
------------ ------------ ------------ -------------- ---------
wanghui wanghui wanghui 2007-4-15 21:2 4
wanghui wanghui wanghui 2007-5-15 21:2 4
wanghui wanghui wanghui 2007-6-15 21:2 4
wanghui wanghui wanghui 2007-7-15 21:2 4
wanghui wanghui wanghui 2007-8-15 21:2 4SQL>
阅读(7500) | 评论(0) | 转发(0) |
