Home » SQL & PL/SQL » SQL & PL/SQL » weekly interval partitioning
weekly interval partitioning [message #413090] Tue, 14 July 2009 06:08 Go to next message
behi
Messages: 12
Registered: June 2009
Junior Member
Hi all,

I need to implement automatic table partitioning in Oracle 11g version, but partitioning interval should be on weekly basis(For every week).

I was able to perform this for Monthly and Yearly but not on weekly basis. Please suggest me how to perform this.

Thanks,
Re: weekly interval partitioning [message #413100 is a reply to message #413090] Tue, 14 July 2009 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post (copy and paste your session without forgetting to format it as explained in Forum Guide) what you did for both cases that worked and not worked.

Regards
Michel

[Updated on: Tue, 14 July 2009 06:46]

Report message to a moderator

Re: weekly interval partitioning [message #413119 is a reply to message #413090] Tue, 14 July 2009 07:58 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Partitioning Examples

regards,
Delna
Re: weekly interval partitioning [message #413232 is a reply to message #413100] Tue, 14 July 2009 22:31 Go to previous messageGo to next message
behi
Messages: 12
Registered: June 2009
Junior Member
This is my code, it is works for Month:
 CREATE TABLE TEST 
   ( ID NUMBER(4,0), 
     C_DATE DATE
   ) 
  PARTITION BY RANGE (C_DATE) 
  INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))  
 (PARTITION TEST_P1  VALUES LESS THAN 
(TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

if we replace interval statement as following, it will work for day too. but how I should define weekly partitions?
INTERVAL (NUMTODSINTERVAL(7,'day'))

[Updated on: Wed, 15 July 2009 01:30] by Moderator

Report message to a moderator

Re: weekly interval partitioning [message #413278 is a reply to message #413232] Wed, 15 July 2009 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create the first partition with a limit at the first week day then with an interval of 7 days you have a partition for each week.

Regards
Michel
Re: weekly interval partitioning [message #413362 is a reply to message #413278] Wed, 15 July 2009 07:00 Go to previous message
behi
Messages: 12
Registered: June 2009
Junior Member
Thanks a lot Michel. It solved my problem.
Previous Topic: eliminating group by
Next Topic: Something Like a loop
Goto Forum:
  


Current Time: Thu Dec 08 08:14:30 CST 2016

Total time taken to generate the page: 0.30905 seconds