-
Hi
I am attempting to a table partition based on the month. My intension is to keep all the records based on the month irrespective of the year. For example, all the January records go into Jan_Partition, Feb records into Feb_Partition and so on..
Currently the table I am attempting, containing one varchar2 field, which contains the data like 'MMDDYYYYXXXXXXX'.
When I tryto partition this table I am getting ora-14019 error.
I tried like this:
CREATE TABLE CALLLOG3 (
CALLLOG_ID NUMBER (18) NOT NULL,
SESSION_ID VARCHAR2 (18) DEFAULT '0',
CONSTRAINT PK_CALLLOG3
PRIMARY KEY ( CALLLOG_ID )
USING INDEX
TABLESPACE INDEX_SSP1_TRANSACTION PCTFREE 10
STORAGE ( INITIAL 65536 NEXT 81920 PCTINCREASE 0 ))
TABLESPACE USERS_SSP1_TRANSACTION NOLOGGING
PARTITION BY RANGE (SESSION_ID)
(PARTITION calllog3_jan VALUES LESS THAN(substr('02212001',1,2))
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_dec VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS_SSP1_TRANSACTION)
ERROR at line 11:
ORA-14019: partition bound element must be one of: string, datetime or interval
literal, number, or MAXVALUE
Can anyone shed some light on this?
Thanks In Advance.
Nagesh
-
The only function you can use in range partitioning is TO_DATE(). Otherwise, you must use a literal value.
Jeff Hunter
-
Why do you want to use "substr('02212001',1,2)" instead of simply using "02" ??
Sanjay
-
Becuase, fields rest of the value may contain another "02". And mainly I am concerned about the month wise partitions.
Is it possible in Version 8.1.7/9i ?
Thanks In Advance
Nagesh
-
Try this...
Code:
CREATE TABLE CALLLOG3 (
CALLLOG_ID NUMBER (18) NOT NULL,
SESSION_ID VARCHAR2 (18) DEFAULT '0',
CONSTRAINT PK_CALLLOG3
PRIMARY KEY ( CALLLOG_ID )
USING INDEX
TABLESPACE INDEX_SSP1_TRANSACTION PCTFREE 10
STORAGE ( INITIAL 65536 NEXT 81920 PCTINCREASE 0 ))
TABLESPACE USERS_SSP1_TRANSACTION NOLOGGING
PARTITION BY RANGE (SESSION_ID)
(PARTITION calllog3_jan VALUES LESS THAN ('020000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_dec VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS_SSP1_TRANSACTION);
Sanjay
[Edited by SANJAY_G on 02-05-2002 at 01:44 AM]
-
What it does exactly? Is it something like partitioning on alphanumaric fields?
Thanks In Advance.
Nagesh
-
This will do exactly what you want. You can't use substr function while partitioning, you will have to provide a literal value. In your case all January records will be 01XXXX...
i.e less than 02XXXX...
Sanjay
-
Sorry Sanjay,
I tried. It does not work. Here are the results.
PPMS5_SSP1@MASSBEJ2> select * from calllog3 partition(calllog3_dec);
CALLLOG_ID SESSION_ID
---------- ------------------
123 02012001123456
124 02042002345678
125 020000000000000000
PPMS5_SSP1@MASSBEJ2> select * from calllog3 partition(calllog3_jan);
no rows selected
Please advise.
Nagesh
-
How many characters you have in the session_id column?
Is it varying or always having 14 characters. I am assuming that it has 14 chracters always.
Try replacing maxvalue by '12000000000000' in callog3_dec partition definition.
Similarly use 11...., 10.... for Nov, Oct and so on.. let me know the results.
Sanjay
-
Assuming SESSION_ID is 14 characters below is the code. If it is more than 14 then put additional 0s.
Code:
CREATE TABLE CALLLOG3 (
CALLLOG_ID NUMBER (18) NOT NULL,
SESSION_ID VARCHAR2 (18) DEFAULT '0',
CONSTRAINT PK_CALLLOG3
PRIMARY KEY ( CALLLOG_ID )
USING INDEX
TABLESPACE INDEX_SSP1_TRANSACTION PCTFREE 10
STORAGE ( INITIAL 65536 NEXT 81920 PCTINCREASE 0 ))
TABLESPACE USERS_SSP1_TRANSACTION NOLOGGING
PARTITION BY RANGE (SESSION_ID)
(PARTITION calllog3_jan VALUES LESS THAN ('02000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_feb VALUES LESS THAN ('03000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_mar VALUES LESS THAN ('04000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_apr VALUES LESS THAN ('05000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_may VALUES LESS THAN ('06000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_jun VALUES LESS THAN ('07000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_jul VALUES LESS THAN ('08000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_aug VALUES LESS THAN ('09000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_sep VALUES LESS THAN ('10000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_oct VALUES LESS THAN ('11000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_nov VALUES LESS THAN ('12000000000000')
TABLESPACE USERS_SSP1_TRANSACTION,
PARTITION calllog3_dec VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS_SSP1_TRANSACTION);
Sanjay
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|