Home » SQL & PL/SQL » SQL & PL/SQL » error in partition table (oracle 10g)
error in partition table [message #400175] Sun, 26 April 2009 23:14 Go to next message
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 #400178 is a reply to message #400175] Sun, 26 April 2009 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#sthref7382
Re: error in partition table [message #400180 is a reply to message #400175] Sun, 26 April 2009 23:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
 *Cause:  Partition bound list contained an element of invalid type
          (i.e. not a number, non-empty string, datetime or interval literal,
          or MAXVALUE)
 *Action: Ensure that all elements of partition bound list are of valid type

Regards
Michel
Re: error in partition table [message #400221 is a reply to message #400180] Mon, 27 April 2009 03:39 Go to previous messageGo to next message
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 #400226 is a reply to message #400221] Mon, 27 April 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Partition values must be fixed.
You have to manually handle them as I said in your previous topic:
http://www.orafaq.com/forum/m/400166/102589/#msg_400166

Regards
Michel
Re: error in partition table [message #400256 is a reply to message #400226] Mon, 27 April 2009 06:07 Go to previous messageGo to next message
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 #400260 is a reply to message #400256] Mon, 27 April 2009 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course there is an error: there is no column named A_ENTRY_DATE, how could Oracle partition by it?

Regards
Michel

[Updated on: Mon, 27 April 2009 06:35]

Report message to a moderator

Re: error in partition table [message #400262 is a reply to message #400256] Mon, 27 April 2009 06:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you expect rows to smoothly move from one partition to the other as sysdate progresses?
Re: error in partition table [message #400265 is a reply to message #400260] Mon, 27 April 2009 06:50 Go to previous messageGo to next message
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 #400268 is a reply to message #400265] Mon, 27 April 2009 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
Michel Cadot wrote on Mon, 27 April 2009 10:55
Partition values must be fixed.
You have to manually handle them as I said in your previous topic:
http://www.orafaq.com/forum/m/400166/102589/#msg_400166

Regards
Michel

You can repeat it one zillion times, the answer will be the same.

Regards
Michel

Re: error in partition table [message #400303 is a reply to message #400268] Mon, 27 April 2009 08:15 Go to previous messageGo to next message
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 #400307 is a reply to message #400303] Mon, 27 April 2009 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take the value of the last partition.

Regards
Michel
Re: error in partition table [message #400311 is a reply to message #400307] Mon, 27 April 2009 08:33 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
should my query be

 select high_value from user_tab_partitions where table_name='A_OUTPUT' and rownum=1;


then how to create the new partition?
Re: error in partition table [message #400313 is a reply to message #400311] Mon, 27 April 2009 08:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
and rownum = 1
will give you a random row.

While you're in the documentation to look up partinioning, also take a look at ORDER BY
Re: error in partition table [message #400316 is a reply to message #400313] Mon, 27 April 2009 09:05 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Search the Table Name or Column Name by Column Values
Next Topic: Doubts on Normalisation
Goto Forum:
  


Current Time: Wed Dec 07 14:23:50 CST 2016

Total time taken to generate the page: 0.07465 seconds