error in partition table [message #400175] |
Sun, 26 April 2009 23:14 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
create table a_OUTPUT
(
A_ID VARCHAR2(20) not null,
A_DATE DATE DEFAULT SYSDATE)
PARTITION BY RANGE(A_DATE)
(PARTITION (TO_CHAR(SYSDATE,'dd-Mon-yyyy'))
values less than (TO_DATE(to_char(sysdate, 'MM/DD/YYYY') ,'MM/DD/YYYY')))
(PARTITION (TO_CHAR(SYSDATE -7,'dd-Mon-yyyy'))
values less than (TO_DATE(to_char(sysdate - 7, 'MM/DD/YYYY') ,'MM/DD/YYYY')))
(PARTITION (TO_CHAR(SYSDATE -14,'dd-Mon-yyyy'))
values less than (TO_DATE(to_char(sysdate - 14 , 'MM/DD/YYYY') ,'MM/DD/YYYY')))
(PARTITION (TO_CHAR(SYSDATE -21,'dd-Mon-yyyy'))
values less than (TO_DATE(to_char(sysdate - 21 , 'MM/DD/YYYY') ,'MM/DD/YYYY')))
)
tablespace a1
)
tablespace b1
;
Quote: | ORA-14006: invalid partition name
|
I get this error
my requirement is to have a generic partition name baseed on the table created date.
again if i replace the above partition names with constant names i get this error
Quote: | ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
|
[Updated on: Mon, 27 April 2009 06:36] by Moderator Report message to a moderator
|
|
|
|
|
Re: error in partition table [message #400221 is a reply to message #400180] |
Mon, 27 April 2009 03:39 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
create table a_OUTPUT
(
A_ID VARCHAR2(20) not null,
ALO_ENTRY_DATE DATE DEFAULT SYSDATE)
PARTITION BY RANGE(ALO_ENTRY_DATE)
(
PARTITION ALO_APR2009 VALUES LESS THAN (TO_DATE(to_char(sysdate,'DD-MON-YYYY'),'DD-MON-YYYY')),
PARTITION ALO_HISTORY VALUES LESS THAN (TO_DATE(to_char(sysdate-32,'DD-MON-YYYY'),'DD-MON-YYYY'))
)
SQL> /
PARTITION ALO_APR2009 VALUES LESS THAN (TO_DATE(to_char(sysdate,'DD-MON-YYYY'),'DD-MON-YYYY')),
*
Quote: | ERROR at line 59:
ORA-14019: partition bound element must be one of: string, datetime or interval
literal, number, or MAXVALUE
|
I need to keep a month's data in one partition and rest in another
kindly advice here
|
|
|
|
Re: error in partition table [message #400256 is a reply to message #400226] |
Mon, 27 April 2009 06:07 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
create table a_OUTPUT
(
A_ID VARCHAR2(20) not null,
ALO_ENTRY_DATE DATE DEFAULT SYSDATE)
PARTITION BY RANGE(A_ENTRY_DATE)
(
PARTITION ALO_HISTORY VALUES LESS THAN (TO_DATE('sysdate','DD-MM-YYYY')),
PARTITION ALO_APR2009 VALUES LESS THAN (TO_DATE('sysdate-32','DD-MM-YYYY'))
)
error again
|
|
|
|
|
Re: error in partition table [message #400265 is a reply to message #400260] |
Mon, 27 April 2009 06:50 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
sorry for a_entry_date
as that is alo_
so its fine.
but without sysdate how can i make a partition of one month(generic code)
and rest in one more??
|
|
|
|
Re: error in partition table [message #400303 is a reply to message #400268] |
Mon, 27 April 2009 08:15 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
SQL> select high_value from user_tab_partitions where table_name='A_OUTPUT';
HIGH_VALUE
--------------------------------------------------------------------------------
TO_DATE(' 2009-04-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TO_DATE(' 2009-05-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TO_DATE(' 2009-05-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TO_DATE(' 2009-05-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
How to know the newest partition from high_value
so that i can add latest partition +7 to create a new partition?
|
|
|
|
|
|
Re: error in partition table [message #400316 is a reply to message #400313] |
Mon, 27 April 2009 09:05 |
ajitpal.s
Messages: 204 Registered: November 2006
|
Senior Member |
|
|
The code below will give a good idea how partitions can be created dynamically. I did not attached some procedure call, but you should be able to understand it if you go through the procedure througly.
CREATE OR REPLACE PROCEDURE ADD_TAB_PART
IS
--
-- Purpose: Briefly explain the functionality of the procedure
--
-- Add table Partitions
TYPE v_table_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
v_table v_table_type;
C_SPROGRAM_NAME CONSTANT M_AUDIT_TRAILS.PROGRAM_NAME%TYPE := ADD_TAB_PART';
v_part_name VARCHAR2(9);
v_date VARCHAR2(15);
v_pcheck NUMBER DEFAULT 0;
v_ts VARCHAR2(20);
V_SSEQ_ID VARCHAR(20);
V_NSEQ_ID NUMBER(9); -- sequence id for batch processing
V_AS_OF_DATE DATE;
v_date_char VARCHAR2(7);
v_retain_no NUMBER;
BEGIN
-- partition table name
v_table(1) := 'TABLE1';
v_table(2) := 'TABL2';
-- Capture the current load month end date as the partition name and max date
SELECT TO_CHAR((prev_load_month_end + 1),'MONYYYY'),
O_CHAR(ADD_MONTHS(TRUNC(prev_load_month_end)+1,1),'DD-MM-YYYY')
INTO v_part_name,v_date
FROM M_SETUP_CONTROLS
WHERE load_type = 1;
M_Utilities.M_AUDIT(C_SPROGRAM_NAME,'S',
V_SSEQ_ID || '1. Adding Table Partition ' || v_part_name ||
' started.',NULL);
DBMS_OUTPUT.PUT_LINE('Adding Table Partitions for the month '||v_part_name);
FOR i IN v_table.FIRST .. v_table.LAST LOOP
SELECT COUNT(*) INTO v_pcheck FROM all_tab_partitions
WHERE table_name = UPPER(v_table(i))
AND table_owner = 'EDW'
AND partition_name = v_part_name;
IF v_pcheck > 0 THEN
M_Utilities.M_AUDIT(C_SPROGRAM_NAME,'I',
V_SSEQ_ID || '2. Table Partition already Exist ',NULL);
EXECUTE IMMEDIATE 'alter table '||v_table(i)||
' truncate partition '||v_part_name||' drop storage ';
ELSE
-- get tablespace name and add new partition to table
SELECT tablespace_name INTO v_ts FROM all_tab_partitions
WHERE table_name = UPPER(v_table(i))
AND table_owner = 'EDW'
AND ROWNUM = 1;
EXECUTE IMMEDIATE 'alter table '||v_table(i)||' add partition '||v_part_name||
' values less than (to_date('''||v_date||''',''DD-MM-YYYY''))'||
' tablespace '||v_ts;
v_pcheck := 0;
END IF;
END LOOP;
M_Utilities.M_AUDIT(C_SPROGRAM_NAME,'E',
V_SSEQ_ID || 'Adding table partition for ' || v_part_name ||' ended.',NULL);
EXCEPTION
WHEN OTHERS THEN
M_Utilities.M_AUDIT( C_SPROGRAM_NAME,'A',
V_SSEQ_ID || 'Adding table partition for ' || v_part_name ||
' aborted due to undefined exception.',SQLERRM(SQLCODE));
END ADD_TAB_PART;
/
[Updated on: Mon, 27 April 2009 09:08] by Moderator Report message to a moderator
|
|
|
Re: error in partition table [message #400364 is a reply to message #400316] |
Mon, 27 April 2009 21:33 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
many thanks.
i will go through and get back in case of any doubt but i guess the procedurte call is not req in my case
please find the current situation below.
create table a_OUTPUT
(
A_ID VARCHAR2(20) not null,
A_DATE DATE DEFAULT SYSDATE)
PARTITION BY RANGE(A_DATE)
(
PARTITION ALO_HISTORY VALUES LESS THAN (TO_DATE('2009-04-10 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')),
PARTITION ALO_APR2009 VALUES LESS THAN (TO_DATE('2009-05-10 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'))
)
My requirement is to build a partitioned table which will have 2 partitions one will hold the current month data
say(Apr 10th 09 to may 10th 09) and the other will hold the history data for all other dates.(i.e before apr 10th 09)
and each month i will add one partition (say for ex from may 11th to june 11th) and so on.
For the first stpe i am failing to create the table.
Can you please advice how if i have created it in the right way then i will try to proceed for adding the partitions dynamically in a generic way
many thanks
|
|
|