During AIA message
re-submission, the tables AIA_ERROR_MONITOR & AIA_ERROR_FLEX_FIELD stores
the faulted instances referenced date. After the re-submission, these data can
be safely purged.
First the records in
child table 'AIA_ERROR_FLEX_FIELD' need to be deleted, followed by records in
'AIA_ERROR_MONITOR' table.
CREATE OR REPLACE PACKAGE
XX_AIA_PURGE_ERRORS AS
/* TODO enter package declarations (types, exceptions, methods etc) here
*/
procedure delete_instances ( min_creation_date in timestamp,
max_creation_date in
timestamp,
batch_size in integer
default 20000,
retention_period in
integer default 30
);
END XX_AIA_PURGE_ERRORS;
/
CREATE OR REPLACE PACKAGE BODY
XX_AIA_PURGE_ERRORS
AS
PROCEDURE delete_instances(
min_creation_date IN TIMESTAMP,
max_creation_date IN TIMESTAMP,
batch_size IN INTEGER
DEFAULT 20000,
retention_period IN INTEGER
DEFAULT 30 )
IS
v_total_rows INTEGER;
v_min_creation_date TIMESTAMP :=
NVL(min_creation_date,TO_TIMESTAMP('2000/01/01 01:00:00', 'YYYY/MM/DD
HH:MI:SS'));
v_max_creation_date TIMESTAMP := NVL(max_creation_date,SYSTIMESTAMP);
v_retention_period INTEGER := NVL(retention_period,30);
v_batch_size INTEGER := NVL(batch_size,20000);
/*
Parameters:
min_creation_date = hardcoded
to an old value in the past if no value is passed
max_creation_date = set to
current datetime if no value is passed
batch_size = determines
the max no. of records to be deleted in each iteration. Set to 20000 if no
value is passed.
retention_period = determines
the no.of days for which the data must be retained without deleting. Set to 30
days if no value is passed.
*/
BEGIN
/*
Capture the total records that needs to be purged in total_rows
variable.
*/
SELECT COUNT(*)
INTO v_total_rows
FROM AIA_ERROR_MONITOR
WHERE rownum <= v_batch_size
AND REPORTED_DATE_TIME BETWEEN v_min_creation_date AND
(v_max_creation_date - (interval '1' DAY * v_retention_period));
/*
Loop through multiple batches if the total records to delete exceeds
batch_size
*/
WHILE v_total_rows>0
LOOP
/*
Delete records from AIA_ERROR_FLEX_FIELD table
*/
DELETE
FROM aia_error_flex_field
WHERE parent_oid IN
(SELECT oid
FROM AIA_ERROR_MONITOR
WHERE rownum <= v_batch_size
AND REPORTED_DATE_TIME BETWEEN v_min_creation_date AND
(v_max_creation_date - (interval '1' DAY * v_retention_period))
);
/*
Delete records from AIA_ERROR_MONITOR table
*/
DELETE
FROM AIA_ERROR_MONITOR
WHERE rownum <= v_batch_size
AND REPORTED_DATE_TIME BETWEEN v_min_creation_date AND
(v_max_creation_date - (interval '1' DAY * v_retention_period));
/*
Commit transactions
*/
COMMIT;
/*
Capture the total records that needs to be purged in total_rows variable
after the delete.
*/
SELECT COUNT(*)
INTO v_total_rows
FROM AIA_ERROR_MONITOR
WHERE rownum <= v_batch_size
AND REPORTED_DATE_TIME BETWEEN v_min_creation_date AND
(v_max_creation_date - (interval '1' DAY * v_retention_period));
END LOOP;
END delete_instances;
END XX_AIA_PURGE_ERRORS;
/
13 comments:
I know this website gives quality based articles and additional stuff, is
there any other web page which gives such stuff in quality?
Hello There. I found your blog using msn. This is a really well written article. I will make sure to bookmark it and return to read more of your useful information. Thanks for the post. I'll definitely comeback. itunes login account
Keep posting blogs like this and I am waiting for the next part of your blog.
iOS Training in Chennai
Big Data Training in Chennai
Hadoop Training in Chennai
Android Training in Chennai
Selenium Training in Chennai
Digital Marketing Training in Chennai
JAVA Training in Chennai
Hadoop Course in Chennai
Hi,Very nice post.Thanks for the information provided.It is very interesting and very informative.I am always impressed with your post and helpful tips.Keep posting tips and relevant content as usual.
big data training in btm
Amazing Post. keep update more information.
German Classes in Chennai
German Classes in Bangalore
German Classes in Coimbatore
German Classes in Madurai
German Language Course in Hyderabad
German language classes in bangalore
German language course in bangalore
German courses in bangalore
Selenium Training in Bangalore
Software Testing Course in Bangalore
7 tips to start a career in digital marketing
“Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.
we have offered to the advanced syllabus course digital marketing for available join now
more details click the link now
[url]https://www.webdschool.com/digital-marketing-course-in-chennai.html[/url]
Web designing trends in 2020
When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, WhatsApp, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.
we have offered to the advanced syllabus course web design and development for available join no
more details click the link now
[url]https://www.webdschool.com/web-development-course-in-chennai.html[/url]
"Good job and thanks for sharing such a good blog You’re doing a great job. Keep it up !!
Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery
"
Aivivu - đại lý chuyên vé máy bay trong nước và quốc tế
vé máy bay đi Mỹ bao nhiêu tiền
mua vé máy bay hà nội đi sài gòn
vé cần thơ đi hà nội
giá vé máy bay hà nội đà lạt khứ hồi
vé máy bay đi Huế
cho thuê xe đi sân bay nội bài
wonderful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
Hello, this weekend is good for me, since this time i am reading this enormous informative article here at my home. Miraz Tony Denver Jacob dyna Vini Ramos Rahim Mina Taylor
Don’t follow your role model. Be the Role model person for others. But it's so simple by getting Hadoop training in Chennai. Because it is an assurance course to bounce back from a double salary. For joining call 7502633633.
Thank you for sharing such kind of precious information with us.It really useful for many of them like me. Join Ziyyara Edutech’s highly interactive and dynamic online English class in Doha, designed to cater to individuals seeking to improve their spoken English language proficiency.
For more info visit English language Class in Qatar
Post a Comment