DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Partitions

  1. #1
    Join Date
    Sep 2000
    Posts
    384
    In the below table my partition key is doc_type which datatype is varchar.If i do the partition with datatype as varchar i am getting an below error .But for the same with numbers it is getting created .How should i creat this table as partitioned with the same values with datatype as varchar2(5)
    SQL> @part
    SQL> CREATE TABLE PART_DOCUMENT
    2 (
    3 BATCH_NAME VARCHAR2(50) NULL,
    4 RELEASE VARCHAR2(100) NULL,
    5 FILENAME VARCHAR2(50) NULL,
    6 RELEASE_DATE DATE NULL,
    7 COMPASS_ACCT VARCHAR2(10) NULL,
    8 ACCOUNT_NUMBER VARCHAR2(26) NULL,
    9 DOC_TYPE number(5) NULL,
    10 CUST_NAME VARCHAR2(30) NULL,
    11 VIN VARCHAR2(20) NULL
    12 )
    13 TABLESPACE GENERAL_DATA
    14 PARTITION BY RANGE(DOC_TYPE)
    15 (PARTITION P1 VALUES LESS THAN (11),
    16 PARTITION P2 VALUES LESS THAN (30),
    17 PARTITION P3 VALUES LESS THAN (40),
    18 PARTITION P4 VALUES LESS THAN (50),
    19 PARTITION P5 VALUES LESS THAN (70),
    20 PARTITION P6 VALUES LESS THAN (100),
    21 PARTITION P7 VALUES LESS THAN (101),
    22 PARTITION P8 VALUES LESS THAN (102),
    23 PARTITION P9 VALUES LESS THAN (103),
    24 PARTITION P10 VALUES LESS THAN (104),
    25 PARTITION P11 VALUES LESS THAN (105),
    26 PARTITION P12 VALUES LESS THAN (106),
    27 PARTITION P13 VALUES LESS THAN (501),
    28 PARTITION P14 VALUES LESS THAN (maxvalue))
    29 /

    Table created.

    SQL> CREATE TABLE FMCCDBA.PART_DOCUMENT_a
    2 (
    3 BATCH_NAME VARCHAR2(50) NULL,
    4 RELEASE VARCHAR2(100) NULL,
    5 FILENAME VARCHAR2(50) NULL,
    6 REL_DATE DATE NULL,
    7 COMPASS_ACCT VARCHAR2(10) NULL,
    8 ACC_NUMBER VARCHAR2(26) NULL,
    9 DOC_TYPE varchar(5) NULL,
    10 CUST_NAME VARCHAR2(30) NULL,
    11 VIN VARCHAR2(20) NULL
    12 )
    13 TABLESPACE GENERAL_DATA
    14 PARTITION BY RANGE(DOC_TYPE)
    15 (PARTITION P1 VALUES LESS THAN (11),
    16 PARTITION P2 VALUES LESS THAN (30),
    17 PARTITION P3 VALUES LESS THAN (40),
    18 PARTITION P4 VALUES LESS THAN (50),
    19 PARTITION P5 VALUES LESS THAN (70),
    20 PARTITION P6 VALUES LESS THAN (100),
    21 PARTITION P7 VALUES LESS THAN (101),
    22 PARTITION P8 VALUES LESS THAN (102),
    23 PARTITION P9 VALUES LESS THAN (103),
    24 PARTITION P10 VALUES LESS THAN (104),
    25 PARTITION P11 VALUES LESS THAN (105),
    26 PARTITION P12 VALUES LESS THAN (106),
    27 PARTITION P13 VALUES LESS THAN (501),
    28 PARTITION P14 VALUES LESS THAN (maxvalue))
    29 Input truncated to 1 characters
    /
    PARTITION P5 VALUES LESS THAN (70),
    *
    ERROR at line 19:
    ORA-14037: partition bound of partition "P5" is too high


    SQL> spool off
    Radhakrishnan.M

  2. #2
    Join Date
    Apr 2001
    Posts
    219
    This will work:
    CREATE TABLE FMCCDBA.PART_DOCUMENT_a
    2 (
    3 BATCH_NAME VARCHAR2(50) NULL,
    4 RELEASE VARCHAR2(100) NULL,
    5 FILENAME VARCHAR2(50) NULL,
    6 REL_DATE DATE NULL,
    7 COMPASS_ACCT VARCHAR2(10) NULL,
    8 ACC_NUMBER VARCHAR2(26) NULL,
    9 DOC_TYPE varchar(5) NULL,
    10 CUST_NAME VARCHAR2(30) NULL,
    11 VIN VARCHAR2(20) NULL
    12 )
    13 TABLESPACE GENERAL_DATA
    14 PARTITION BY RANGE(DOC_TYPE)
    15 (PARTITION P1 VALUES LESS THAN ('11'),
    16 PARTITION P2 VALUES LESS THAN ('30'),
    17 PARTITION P3 VALUES LESS THAN ('40'),
    18 PARTITION P4 VALUES LESS THAN ('50'),
    19 PARTITION P5 VALUES LESS THAN ('70'),
    20 PARTITION P6 VALUES LESS THAN ('100'),
    21 PARTITION P7 VALUES LESS THAN ('101'),
    22 PARTITION P8 VALUES LESS THAN ('102'),
    23 PARTITION P9 VALUES LESS THAN ('103'),
    24 PARTITION P10 VALUES LESS THAN ('104'),
    25 PARTITION P11 VALUES LESS THAN ('105'),
    26 PARTITION P12 VALUES LESS THAN ('106'),
    27 PARTITION P13 VALUES LESS THAN ('501'),
    28 PARTITION P14 VALUES LESS THAN (maxvalue))

    The reason it will work is becuase you were comparing numbers to varchar2 and Oracle will not make the conversion. Also, if you leave doc_type as a varchar2 you will not get the same order as a number. So, if these two tables are tied together with in the data and you drop a partition you will violate an constraint and it will error possibly. Just food for thought.

  3. #3
    Join Date
    Sep 2000
    Posts
    384
    I tried that also the result is error ...
    Did you tried in your database ...

    SQL>
    1 CREATE TABLE FMCCDBA.PART_DOCUMENT_a
    2 (
    3 BATCH_NAME VARCHAR2(50) NULL,
    4 RELEASE VARCHAR2(100) NULL,
    5 FILENAME VARCHAR2(50) NULL,
    6 REL_DATE DATE NULL,
    7 COMPASS_ACCT VARCHAR2(10) NULL,
    8 ACC_NUMBER VARCHAR2(26) NULL,
    9 DOC_TYPE varchar(5) NULL,
    10 CUST_NAME VARCHAR2(30) NULL,
    11 VIN VARCHAR2(20) NULL
    12 )
    13 TABLESPACE GENERAL_DATA
    14 PARTITION BY RANGE(DOC_TYPE)
    15 (PARTITION P1 VALUES LESS THAN ('11'),
    16 PARTITION P2 VALUES LESS THAN ('30'),
    17 PARTITION P3 VALUES LESS THAN ('40'),
    18 PARTITION P4 VALUES LESS THAN ('50'),
    19 PARTITION P5 VALUES LESS THAN ('70'),
    20 PARTITION P6 VALUES LESS THAN ('100'),
    21 PARTITION P7 VALUES LESS THAN ('101'),
    22 PARTITION P8 VALUES LESS THAN ('102'),
    23 PARTITION P9 VALUES LESS THAN ('103'),
    24 PARTITION P10 VALUES LESS THAN ('104'),
    25 PARTITION P11 VALUES LESS THAN ('105'),
    26 PARTITION P12 VALUES LESS THAN ('106'),
    27 PARTITION P13 VALUES LESS THAN ('501'),
    28* PARTITION P14 VALUES LESS THAN (maxvalue))
    SQL> /
    PARTITION P5 VALUES LESS THAN ('70'),
    *
    ERROR at line 19:
    ORA-14037: partition bound of partition "P5" is too high


    SQL> spool off
    Radhakrishnan.M

  4. #4
    Join Date
    Apr 2001
    Posts
    219
    Oops, I forgot that 100 in Varchar2 will be a lesser value than 70 becuase of the ascii value of 1. So, your values will need to be reordered to accomidate.

  5. #5
    Join Date
    Sep 2000
    Posts
    384
    How do I do now ...and What should be done to solve this ...
    Radhakrishnan.M

  6. #6
    Join Date
    Apr 2001
    Posts
    219
    I would use a number for the doc_type field for consistancy and proper order of your partitions. Any other way will not be consistant between tables that are partitioned on the doc_type field.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Omega,
    Try this:
    CREATE TABLE PART_DOCUMENT_a
    (
    BATCH_NAME VARCHAR2(50) NULL,
    RELEASE VARCHAR2(100) NULL,
    FILENAME VARCHAR2(50) NULL,
    REL_DATE DATE NULL,
    COMPASS_ACCT VARCHAR2(10) NULL,
    ACC_NUMBER VARCHAR2(26) NULL,
    DOC_TYPE varchar(5) NULL,
    CUST_NAME VARCHAR2(30) NULL,
    VIN VARCHAR2(20) NULL
    )
    TABLESPACE USERS
    PARTITION BY RANGE (DOC_TYPE )
    (PARTITION P1 VALUES LESS THAN ('00001'),
    PARTITION P2 VALUES LESS THAN ('00020'),
    PARTITION P3 VALUES LESS THAN ('00500'),
    PARTITION P4 VALUES LESS THAN ('04000'),
    PARTITION P5 VALUES LESS THAN ('06000'),
    PARTITION P6 VALUES LESS THAN ('07000'),
    PARTITION P7 VALUES LESS THAN ('08000'),
    PARTITION P8 VALUES LESS THAN ('99999'),
    PARTITION P9 VALUES LESS THAN (MAXVALUE)
    )
    ;

    Oracle converts the partition key into binary irrespective of the column data type and value. If you are using CHAR or VARCHAR2, and the column can take NULL value, you should be aware that NULL will preceed other values. First of all a column which takes NULL value is not suitable for partition key.

    [Edited by tamilselvan on 06-22-2001 at 04:05 PM]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width