Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2042769
  • 博文数量: 519
  • 博客积分: 10070
  • 博客等级: 上将
  • 技术积分: 3985
  • 用 户 组: 普通用户
  • 注册时间: 2006-05-29 14:05
个人简介

只问耕耘

文章分类

全部博文(519)

文章存档

2016年(1)

2013年(5)

2011年(46)

2010年(220)

2009年(51)

2008年(39)

2007年(141)

2006年(16)

我的朋友

分类: Oracle

2010-04-13 11:03:02

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>
阅读(7493) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~