Sorry for long story since I wanted to give clear understandings.
Friends yesterday I requested plsql to complete tricky task but it turns out I
can't use simple delete statement to delete data due to size.
Application generate 1m audit logs everday and stores log into audit_logs1 &
Since app generates 1m rows everday business needs to use perl script to delete
logs older than 2 months leaving Sunday & 1st of the month log but delete 1 day at
a time in loop and not in single delete statement.
1. Procedure: call_delete()
1.1 Check if it's First Saturday of the month if yes THEN
1.2 query to get "created" date older than 2 months and date !='SUN' & !
1.3 and for every date found in 1.2 delete 1 date at a time by calling
2. Procedure: delete_logs(v_created IN DATE)
Procedure delete_logs(v_created IN DATE)
DELETE FROM AUDIT_LOGS1
WHERE TRUNC(CREATED) = v_created;
DELETE FROM AUDIT_LOGS2
WHERE TRUNC(CREATED) = v_created;
-- Yesterday I endup creating below delete which I think I can still use for
select query for 1.2 to get required dates to delete
DELETE FROM AUDIT_LOGS
WHERE TRUNC (CREATED) < ADD_MONTHS(TRUNC(SYSDATE), -2)
AND TO_CHAR (CREATED, 'DY') != 'SUN'
AND TO_CHAR (CREATED, 'DD') != '01'
so basically idea is to use procedure call_delete() to find dates to delete and
call delete_logs() for every date found.
Both procedure are under same pkg and package has public constants as
dateFormat Constant VARCHAR2(22) := 'mm/dd/yyyy hh24:mi:ss';
can some guru help me out?
You might want a procedure to delete a single day's data for some other reason, but it won't help this job. Stick with the solution you got yesterday.
If you need a statement that will only DELETE rows if it is run on the 1st Saturday of a month, then you can add conditions to your DELETE statement, like this:
DELETE FROM audit_logs1
WHERE created < ADD_MONTHS (SYSDATE, -2)
AND TO_CHAR (created, 'DY') != 'SUN'
AND TO_CHAR (created, 'DD') != '01';
AND TO_CHAR (SYSDATE, 'DY') = 'SAT' -- *** NEW ***
AND TO_CHAR (SYSDATE, 'DD') <= '07' -- *** NEW ***
No PL/SQL is required, but if you have some reason for using PL/SQL, then the DELETE statement above will work just as well in PL/SQL.
As mentioned above, if you're using Oracle Enterprise Edition, you might consider partitioning the table by day, and dropping the unwanted partitions.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), a couple of run dates, and the results you want from that data (that is, what's left in the table) for each of the run dates.
Point out where the statement above is getting the wrong results, and explain, using specific examples, how you get the right results from the given data in those places.
Always say which version of Oracle you're using (e.g., 220.127.116.11.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
Oracle version is 18.104.22.168
thanks frank... I will try to provide more details as mentioned in this and more comments.
mentioned delete statement will works but since pkg is already using delete_logs() procedure my senior developer don't want to have another delete statement, so suggested to get dates to delete and feed into delete_logs() procedure.
so I'm trying to use call_delete() procedure to query and loop everydate into delete_logs().
Sorry for pitching in, (More specifically as this call is for Gurus).. yeah this query will delete the records, however with this kind of table & data, why don't you consider partitioning and then partition purging, which can be much more efficient in every sense.
Precisely what I was going to suggest.
create table audit_logs_partitioned partition by range ( created ) interval ( numtoyminterval(1,'MONTH') ) (partition p0 values less than (to_date('20130701','yyyymmdd'))) as select * from audit_logs; -- setup PKs, indexes, grants, etc. rename audit_logs to audit_logs old; rename audit_logs_partitioned to audit_logs; -- change synonyms make sure point to audit_logs
Then all you need to do is cleanup old partitions when you want to purge old data:
alter table audit_logs drop partition '<partition name>';
That'll go very quickly.
oh, by day? yeah, same difference, just alter the interval to numtodsinterval(1,'day') if you get that many .. wow lot of audit lol
My vote is definitely still partitions - saves alot of headache in this kind of scenario