Home » SQL & PL/SQL » SQL & PL/SQL » drop oldest partition (oracle 10g)
drop oldest partition [message #400600] Wed, 29 April 2009 01:29 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
here cant i drop the oldest partition from the partition name as it has a date part in it.this will make life a lot easer.

kindly advice


SQL> 
SQL>          SELECT   table_name, partition_name, high_value, tablespace_name
  2               FROM user_tab_partitions
  3              WHERE table_name ='ACE2_LIST_OUTPUT'
  4           ORDER BY partition_position DESC;
 
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                       TABLESPACE_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
ACE2_LIST_OUTPUT               ALO_042011                     TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_032011                     TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_022011                     TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_012011                     TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_122010                     TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_112010                     TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_102010                     TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_092010                     TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_082010                     TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_072010                     TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_062010                     TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_052010                     TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_042010                     TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_032010                     TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_022010                     TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_012010                     TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_122009                     TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_112009                     TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_102009                     TO_DATE(' 2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_092009                     TO_DATE(' 2009-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
 
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                       TABLESPACE_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
ACE2_LIST_OUTPUT               ALO_082009                     TO_DATE(' 2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_072009                     TO_DATE(' 2009-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_062009                     TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_052009                     TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
ACE2_LIST_OUTPUT               ALO_042009                     TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS_SVEN_DATA
 
25 rows selected


Re: drop oldest partition [message #400604 is a reply to message #400600] Wed, 29 April 2009 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The "oldest" has position 1, hasn't it.

Regards
Michel
Re: drop oldest partition [message #400610 is a reply to message #400604] Wed, 29 April 2009 02:04 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I NEED TO PUT THAT CODE IN A PROC WHICH WILL AUTOMATICALLY do it after 2 years

i.e when the table partition reaches 25 months it will drop the april2009 partition

this is a test table

but currently the partitions will increase monthly with one getting added each month.

so i need to make it generic

kindly advice
Re: drop oldest partition [message #400628 is a reply to message #400610] Wed, 29 April 2009 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anyway the oldest will always have the first position.

Regards
Michel
Re: drop oldest partition [message #400644 is a reply to message #400600] Wed, 29 April 2009 03:43 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
swas_fly wrote on Wed, 29 April 2009 08:29
here cant i drop the oldest partition from the partition name as it has a date part in it.this will make life a lot easer.

kindly advice

If "oldest partition" means the partition with the lowest date in it: what about convert the date part in the partition name to the format, in which it is sorted by date?
SQL> with part as ( select 'ALO_042011' partition_name from dual )
  2  select substr( partition_name, -4 )||substr( partition_name, -6, 2 ) one_way,
  3         to_char( to_date( substr (partition_name, -6), 'mmyyyy' ), 'yyyymmdd' ) another_one
  4  from part;

ONE_WA ANOTHER_
------ --------
201104 20110401

SQL> 

[Edit: corrected the second expression]

[Updated on: Wed, 29 April 2009 03:44]

Report message to a moderator

Previous Topic: sql server procedure into oracle
Next Topic: Scheduling jobs
Goto Forum:
  


Current Time: Sat Dec 03 22:03:48 CST 2016

Total time taken to generate the page: 0.12609 seconds