-
PL/SQL script to delete millions of rows from a table based on date
HI All
I am very new to the database stuff. Correct me if I am wrong.
Is there a possiblity to write a script in oracle which deletes the rows from a table based on the age. i.e., I want to delete the rows which are >60 days. I have a table with millions of rows in it and I want to keep only the latest two months rows. I have the following table with column names as
ERROR_IND
FEED_ID
QUEUE_ID
FEED_DT_TM -date datatype .
I have written few of the scripts to implement it.
1.DELETE FROM table_name WHERE sysdate - feed_dt_tm > 60;
2. declare
begin
create table hif_psfeed1
as
select *
from hif_psfeed
where feed_dt_tm > (sysdate-60);
drop table hif_psfeed;
alter table hif_psfeed1 rename to hif_psfeed;
end;
/
3. declare
job_delete in number;
begin
sys.dbms_job.submit
(job=>job_delete,
what=>'begin delete from hif_psfeed WHERE sysdate - feed_dt_tm >''60''; end;',
next_date=>trunc(sysdate)+1,
interval=>'trunc(sysdate)+1'
);
commit;
end;
/
The first one and second seems to take long time and I am getting errors while running the 3rd one. Please advise me on this. I need to keep running this script everyday to keep only the latest 2 months of data in the table
Thanks In advance
-
How many rows in the table?
How many rows are expected to be deleted?
Is this a one off process or are you planning to run it in a montly basis or so?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
PL/SQL script to delete millions of rows from a table based on date
@PAVB: There 19 million rows in the table. If I consider only the latest two months , it will be around 100,000 rows. The remaining rows are expected to be deleted. Yes, I am plannning it to perform weekly or monthly basis.
-
Look at this site It gives lot of information about this problem
http://asktom.oracle.com/pls/apex/f?...:2345591157689
Naeem
-
I would suggest to do reverse purging at least for the first run where about 18.9 million rows have to be "deleted" out of a total of 19 million.
In this context a version of Option #2 is the way to go.
I would...
1- Pre Create hif_psfeed1 as nologging, with no indexes
2- Insert into it the 100,000 targetted-to-stay-alive rows probably using /*+ append */ hint.
3- Rename table hif_psfeed as hif_psfeed_old and keep it alive for a couple of days (you never know).
4- Rename table hif_psfeed1 as hif_psfeed.
5- Create all needed indexes and fix referential integrity if needed.
6- Alter table to logging.
7- Gather fresh stats on table and related indexes.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|