OraFAQ Forum: SQL & PL/SQL » Table Archive and Purge

Home » SQL & PL/SQL » SQL & PL/SQL » Table Archive and Purge (11.2)

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

Table Archive and Purge [message #668433]

Sun, 25 February 2018 04:41

gopikrish_6
Messages: 1
Registered: February 2018

Junior Member

Hello All,
Need shell script for table archive and purging.

select * from TABLEA where sysdate<180 orderby last_upadate_dt desc;
insert into TABLEA_ARC as select * from TABLEA where sysdate<180;
commit;
delete table TABLEA where sysdate<180 orderby last_upadate_dt desc;
commit;

Kindly provide the shell scripts.

Report message to a moderator

Re: Table Archive and Purge [message #668435 is a reply to message #668433]

Sun, 25 February 2018 07:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

sqlplus scott/tiger <<EOF
select * from TABLEA where sysdate<180 orderby last_upadate_dt desc;
insert into TABLEA_ARC as select * from TABLEA where sysdate<180;
commit;
delete table TABLEA where sysdate<180 orderby last_upadate_dt desc;
commit;
exit
EOF

Report message to a moderator

Re: Table Archive and Purge [message #668436 is a reply to message #668433]

Sun, 25 February 2018 08:13

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

sysdate<180 makes no sense. You probably meant last_update_dt < trunc(sysdate - 180) or last_update_dt < trunc(sysdate - 179), depending on your requirements.

SY.

Report message to a moderator

Re: Table Archive and Purge [message #668437 is a reply to message #668436]

Sun, 25 February 2018 11:01

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"180" smells like "6 months". If that's so, consider using ADD_MONTHS instead of subtracting hardcoded 180, such as

... where last_update_dt < add_months(trunc(sysdate), -6)

Report message to a moderator

Re: Table Archive and Purge [message #668438 is a reply to message #668433]

Sun, 25 February 2018 12:54

John Watson
Messages: 9005
Registered: January 2010
Location: Global Village

Senior Member

I hope you will take this (and previous replies) as constructive criticism.

Are you working with an Oracle database, or some other product? The reason I ask is that this

delete table TABLEA where sysdate<180 orderby last_upadate_dt desc;

is not valid Oracle SQL. First, the word "table" should not be there, it will throw an ORA-00903 error. Second, you cannot apply an ORDER BY clause to a DELETE statement. Perhaps some other SQL implementations permit these?

Report message to a moderator

Re: Table Archive and Purge [message #668448 is a reply to message #668438]

Mon, 26 February 2018 03:00

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Also the initial select is a complete waste of time. What are you going to do with that data? It's being inserted into the archive table anyway.

Report message to a moderator

Re: Table Archive and Purge [message #668469 is a reply to message #668448]

Mon, 26 February 2018 15:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Also, what column are you comparing sysdate - 180 days to?

for arguments sake say the date stamp in your table is last_upadate_dt then your code would be

insert into TABLEA_ARC 
select * from TABLEA where last_upadate_dt < trunc(sysdate-180);

delete table TABLEA 
where last_upadate_dt < trunc(sysdate-180);

commit;

Report message to a moderator

Previous Topic:	Commit after deletion of every 5000 rows
Next Topic:	Count occurrences of String in field

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 04 17:35:11 CDT 2026