Home » SQL & PL/SQL » SQL & PL/SQL » Table Archive and Purge (11.2)
Table Archive and Purge [message #668433] Sun, 25 February 2018 04:41 Go to next message
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.

Re: Table Archive and Purge [message #668435 is a reply to message #668433] Sun, 25 February 2018 07:40 Go to previous messageGo to next message
BlackSwan
Messages: 26284
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
Re: Table Archive and Purge [message #668436 is a reply to message #668433] Sun, 25 February 2018 08:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
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.
Re: Table Archive and Purge [message #668437 is a reply to message #668436] Sun, 25 February 2018 11:01 Go to previous messageGo to next message
Littlefoot
Messages: 21446
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)
Re: Table Archive and Purge [message #668438 is a reply to message #668433] Sun, 25 February 2018 12:54 Go to previous messageGo to next message
John Watson
Messages: 7671
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?





Re: Table Archive and Purge [message #668448 is a reply to message #668438] Mon, 26 February 2018 03:00 Go to previous messageGo to next message
cookiemonster
Messages: 13340
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.
Re: Table Archive and Purge [message #668469 is a reply to message #668448] Mon, 26 February 2018 15:30 Go to previous message
Bill B
Messages: 1814
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;
Previous Topic: Commit after deletion of every 5000 rows
Next Topic: Count occurrences of String in field
Goto Forum:
  


Current Time: Mon Nov 19 15:38:20 CST 2018