Home » Infrastructure » Linux » Shell script for table purging (Oracle database 11.2.0.2 running on Oracle enterprise linux 5)
Shell script for table purging [message #560885] Tue, 17 July 2012 11:48 Go to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Hi Guys,

I have a table within my DB that's growing at a very fast rate & i need to do some maintenance to prevent constant filling of the underlying tablespace. Just one month worth of records is necessary.
This is what I have in mind.

1. Create an identical temporary table as original table
2. Insert last one month records into temp table
3. Truncate the original table
4. Insert temporary table records into original table
5. truncate temporsry table.

All of above should be in a single shell script or a PL/SQL procedure.
Does anyone have anything similar already working?

Thanks in advance...

- divroro12 -
Re: Shell script for table purging [message #560886 is a reply to message #560885] Tue, 17 July 2012 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Partition the table by month and use partitions maintenance.
And as you are in 11gR2, you can use interval partitioning so you even don't have to create the partitions just to drop (or archive) the obsolete ones.

Regards
Michel

[Updated on: Tue, 17 July 2012 12:21]

Report message to a moderator

Re: Shell script for table purging [message #560887 is a reply to message #560886] Tue, 17 July 2012 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
daily delete records that are older than 31 days.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Shell script for table purging [message #560915 is a reply to message #560887] Tue, 17 July 2012 14:39 Go to previous message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Thanks Michel, i'll look into that.

- divroro12 -
Previous Topic: installation on linux 5 on laptop core i 3 hp
Next Topic: missing INITSID.ORA file
Goto Forum:
  


Current Time: Sun Dec 21 03:42:52 CST 2014

Total time taken to generate the page: 0.07603 seconds