Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL procedure to split Partition table (10.2.0.1 oracle Enterprise edition)
PLSQL procedure to split Partition table [message #333908] Mon, 14 July 2008 18:04 Go to next message
Nick_01
Messages: 36
Registered: March 2008
Member
Hi,

I appreciate if any one can help me in writing a procedure that will split the partition table for the next day and drop the partition that are older than 15 days. I have a range partition table created to contain data for every month. Since we are running out of space.. we planned to split the monthly partition to daily partition and drop the partitions older than 15 days.

I appreciate and thank in advance.

Re: PLSQL procedure to split Partition table [message #333909 is a reply to message #333908] Mon, 14 July 2008 18:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: PLSQL procedure to split Partition table [message #333918 is a reply to message #333908] Mon, 14 July 2008 21:12 Go to previous messageGo to next message
Nick_01
Messages: 36
Registered: March 2008
Member
Hi,

I apologise for the mistakes and request you to guide me.

Re: PLSQL procedure to split Partition table [message #333920 is a reply to message #333918] Mon, 14 July 2008 21:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How many rows are there per day? Would it be easier to just delete the rows on a dialy basis.

The information you need is stored in the data dictionary views
- USER_PART_TABLES
- USER_TAB_PARTITIONS

The high-value for range partitions is stored in a LONG column that is difficult to deal with. It is typically more convenient to use a naming strategy on the partition and drop partitions based on the name.

In PL/SQL, you will need to EXECUTE IMMEDIATE your ALTER TABLE statements that DROP PARTITION and SPLIT PARTITION.

Also worth noting that splitting partitions is a bad approach that increases your IO. Much better to create the partitions you need and not split at all.

Ross Leishman
Re: PLSQL procedure to split Partition table [message #333949 is a reply to message #333908] Tue, 15 July 2008 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please search before posting, we answered this kind of question several times and not so far than last week.

Regards
Michel

[Updated on: Tue, 15 July 2008 01:55]

Report message to a moderator

Re: PLSQL procedure to split Partition table [message #333974 is a reply to message #333949] Tue, 15 July 2008 01:41 Go to previous messageGo to next message
Nick_01
Messages: 36
Registered: March 2008
Member
The high_value of the range partition is using Timestamp.Already the partitions are created based on Timestamp < '2009-01-01 00.00.00'..There are already 12 partitions for the year 2008.

If I need to split the partition for next day I was able to split manually only from 2nd of every month to the last day of month, since the partition for 1st already created. I believe we may have to use loop.

Micheal,
I have worked on managing parition tables. I need a procedure that will split the existing parition table for the next day and drop the partitions that are older 15 days.

Since I am not good at development and programming I seek the help to write a procedure so that I can schedule in cron to split the partition for next day.

Re: PLSQL procedure to split Partition table [message #333979 is a reply to message #333974] Tue, 15 July 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Since I am not good at development and programming I seek the help to write a procedure

Post what you tried with what Ross posted.

Regards
Michel
Re: PLSQL procedure to split Partition table [message #334095 is a reply to message #333920] Tue, 15 July 2008 07:38 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
rleishman wrote on Tue, 15 July 2008 10:42

Also worth noting that splitting partitions is a bad approach that increases your IO. Much better to create the partitions you need and not split at all.

Ross Leishman



hi,
just a question on this "bad approach", not sure if this case is the same. but we also have partitioned tables, then there's a job that creates ahead of a partition of the whole next week, although it doesnt have any data yet, it rans every saturday. i think they said it was done that way because its much costly to split partition with already data than those that dont have yet. is this bad? thanks.

[Updated on: Tue, 15 July 2008 07:39]

Report message to a moderator

Re: PLSQL procedure to split Partition table [message #334105 is a reply to message #334095] Tue, 15 July 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is better to create the partitions before you need it.
So either create them at table creation time, split the last one before it is used or add new partitions. In this latter case this means you are sure that no one will try to insert a row in a non-existent partition or your application knows how to handle this.

Regards
Michel
Re: PLSQL procedure to split Partition table [message #334107 is a reply to message #334105] Tue, 15 July 2008 08:05 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
thanks michel, yes we are loading on that table on a daily basis. is there any bad effect if a partition is created then no data had been inserted for that partition, but on the next range of partition there is data?
Re: PLSQL procedure to split Partition table [message #334119 is a reply to message #334107] Tue, 15 July 2008 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there any bad effect if a partition is created then no data had been inserted for that partition

Only waste of space.

Regards
Michel
Re: PLSQL procedure to split Partition table [message #334145 is a reply to message #334119] Tue, 15 July 2008 09:32 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
when you say "waste of space", does that mean once you created that partition, even though there are no data yet, there are already allocated space? or is this just on the data dictionary? thanks.
Re: PLSQL procedure to split Partition table [message #334151 is a reply to message #334145] Tue, 15 July 2008 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you allocate a new segment.
Each partition is a segment. Each segment has (at least) an initial extent.

Regards
Michel
Re: PLSQL procedure to split Partition table [message #334685 is a reply to message #333908] Thu, 17 July 2008 12:28 Go to previous messageGo to next message
Nick_01
Messages: 36
Registered: March 2008
Member
Hi,

Here is the code I am using.
Since there are many partition tables in the schema i declared the part_table it throws errors..

how do I use cursor and loop to check for the number of days to drop the partitions.

create or replace procedure split_prc
part_table in varchar2 default MKT_PART1;
part_drop in number default 15;
IS
V_PARTNAME VARCHAR2(15);
V_PARTDAY VARCHAR2(2);
V_SQL VARCHAR2(255);
SELECT partition_name into V_PARTNAME from user_tab_partitions;
SELECT TO_CHAR(SYSDATE+1,'DD') INTO V_PARTDAY FROM DUAL;
begin
V_SQL:='ALTER TABLE '||part_table||' split partition '||V_PARTNAME|| 'AT (to_date(sysdate+1,'YYYY-MM-DD HH24:MI:SS')) INTO ('||V_PARTNAME||'_'||V_PARTDAY||', PARTITION '||V_PARTNAME||';';
execute immediate v_sql;
end;

Re: PLSQL procedure to split Partition table [message #334686 is a reply to message #334685] Thu, 17 July 2008 12:32 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: How to create rows for each character string in a field?
Next Topic: ORA-00054: resource busy and acquire with NOWAIT specified indexe creation
Goto Forum:
  


Current Time: Sun Dec 04 20:42:54 CST 2016

Total time taken to generate the page: 0.09563 seconds