OraFAQ Forum: Linux » Shell script for table purging

Home » Infrastructure » Linux » Shell script for table purging (Oracle database 11.2.0.2 running on Oracle enterprise linux 5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Shell script for table purging [message #560885]

Tue, 17 July 2012 11:48

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 -

Report message to a moderator

Re: Shell script for table purging [message #560886 is a reply to message #560885]

Tue, 17 July 2012 11:55

Michel Cadot
Messages: 68634
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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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/

Report message to a moderator

Re: Shell script for table purging [message #560915 is a reply to message #560887]

Tue, 17 July 2012 14:39

divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA

Senior Member

Thanks Michel, i'll look into that.

- divroro12 -

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 07:52:02 CDT 2024