That probably would work well, but does include the overhead of the deletes.
The generalized dinosaur method when one of {Oracle|user} fubared purging until
something got too big for comfort was:
0) Make sure an Oracle script exists to recreate the objects empty with the
proper permissions
1) Copy any contents you think you might need elsewhere (including just
renaming the tables until you can do the research and possibly copy back
selected contents.
2) Drop/purge/recreate the empty tables.
Don’t skip step 0. Nearly everything like this does have a create script (or at
least used to).
3) Eventually decide if you need some of the stuff back from the renamed
tables and possible index same exactly to feed your select from <old_obsolete>
insert append into <new_spanky_clean>.
This is ASKEW from figuring out how the problem erupted, but unless you have
reason to believe this was not an old fubar, my granddaughter can sing you a
FROZEN song about it.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sayan Malakshinov
Sent: Tuesday, May 25, 2021 11:11 AM
To: Beckstrom, Jeffrey
Cc: Karthikeyan Panchanathan; ORACLE-L
Subject: Re: 12.2.0.1 scheduler tables not being purged
The easiest way is to run
select logid from... Minus select logid from... Where {predicates from delete}
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org ;
On Tue, May 25, 2021, 18:04 Sayan Malakshinov <xt.and.r@xxxxxxxxx> wrote:
What do you mean?
You will see delete statements, just compare their predicates with your old
logs and you'll see which predicates exclude your data.
For example, if you have rows with non-null dbid
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org ;
On Tue, May 25, 2021, 17:52 Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx> wrote:
I’m not sure if a trace will tell WHY certain rows ARE being deleted and OTHERS
are NOT.
From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
Sent: Tuesday, May 25, 2021 10:22 AM
To: Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx>
Cc: Karthikeyan Panchanathan <keyantech@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: Re: 12.2.0.1 scheduler tables not being purged
Hi Jeffrey,
That's pretty easy to investigate:
1. execute purge job:
SQL> exec Dbms_scheduler.run_job('PURGE_LOG');
2. find DELETEs from SYS.SCHEDULER$ tables:
https://github.com/xtender/xt_scripts/blob/master/find_sql.sql ;
<https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fxtender%2Fxt_scripts%2Fblob%2Fmaster%2Ffind_sql.sql&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C573d45b138fb41146d2a08d91f8889d7%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637575494595062396%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=hBKdaooXyzlwq4jg22i9SWf2fgKxb5biI8%2BKFwsZOkE%3D&reserved=0>
(or you can trace it with sql_trace level 1 and get them from a trace file)
SQL> @find_sql "DELETE%SCHEDULER$%" all
it will return a few SQL_IDs:
INST_ID SQL_ID EXECS ELAEXE SQL_TEXT_TRUNC
------- ------------- ------ -------- -------------------------------------
1 1xcngwj6ypnkx 1 .010972 DELETE FROM SYS.SCHEDULER$_JOB_RUN_...
1 7kvy2vdfs9ffv 1 .005409 DELETE FROM SYS.SCHEDULER$_EVENT_LO...
1 av3n8cwad6f2n 1 .003241 DELETE FROM SYS.SCHEDULER$_EVENT_LO...
1 bycc25ggkmbmw 1 .003204 DELETE FROM SYS.SCHEDULER$_WINDOW_D...
[I've shortened the output to make it more readable]
3. Get their full text:
1xcngwj6ypnkx:
DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS
WHERE LOG_ID IN (
SELECT E.LOG_ID
FROM SYS.SCHEDULER$_EVENT_LOG E,
SYS.SCHEDULER$_CLASS C
WHERE E.TYPE# = 66
AND E.CLASS_ID = C.OBJ#(+)
AND (E.FLAGS IS NULL OR BITAND(E.FLAGS, 2)=0)
AND OPERATION NOT LIKE 'CHAIN%'
AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(NVL(C.LOG_HISTORY, :B1 ), 'DAY')
)
/
7kvy2vdfs9ffv
DELETE FROM SYS.SCHEDULER$_EVENT_LOG
WHERE DBID IS NULL
AND LOG_ID IN (
SELECT E.LOG_ID
FROM SYS.SCHEDULER$_EVENT_LOG E,
SYS.SCHEDULER$_CLASS C
WHERE E.TYPE# = 66
AND E.CLASS_ID = C.OBJ#(+)
AND (E.FLAGS IS NULL OR BITAND(E.FLAGS, 2)=0)
AND OPERATION NOT LIKE 'CHAIN%'
AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(NVL(C.LOG_HISTORY, :B1 ), 'DAY')
)
/
av3n8cwad6f2n:
DELETE FROM SYS.SCHEDULER$_EVENT_LOG
WHERE DBID IS NULL
AND LOG_ID IN (
SELECT E.LOG_ID
FROM SYS.SCHEDULER$_EVENT_LOG E
WHERE E.TYPE# = 69
AND E.LOG_DATE < :B2 - NUMTODSINTERVAL(:B1 , 'DAY')
)
/
[I have formatted them...]
4. So you can now analyze why these queries do not delete your old data.
For example, sql_id: av3n8cwad6f2n "DELETE FROM SYS.SCHEDULER$_EVENT_LOG WHERE
DBID IS NULL ..." deletes only rows where DBID is null and type#=69, so you can
check your DBID and TYPE#:
select dbid, type#, count(*)
from SYS.SCHEDULER$_EVENT_LOG
group by dbid, type#;
etc...
On Tue, May 25, 2021 at 4:52 PM Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx> wrote:
Looks like very recent information is purging but not all information. Checked
several databases and all doing something similar.
select trunc(log_date),count(*) from sys.dba_scheduler_job_run_details group by
trunc(log_date) order by 1
TRUNC(LOG COUNT(*)
--------- ----------
These go back to 2011
02-JUN-20 16
03-JUN-20 18
04-JUN-20 18
05-JUN-20 16
06-JUN-20 106
07-JUN-20 112
08-JUN-20 16
09-JUN-20 18
10-JUN-20 18
11-JUN-20 17
12-JUN-20 18
13-JUN-20 106
14-JUN-20 111
15-JUN-20 18
16-JUN-20 16
17-JUN-20 18
18-JUN-20 13
19-JUN-20 16
20-JUN-20 11
21-JUN-20 102
22-JUN-20 18
23-JUN-20 16
24-JUN-20 18
25-JUN-20 18
26-JUN-20 16
27-JUN-20 11 - from here on looks like purging
25-APR-21 4
26-APR-21 8
27-APR-21 8
28-APR-21 8
29-APR-21 8
30-APR-21 8
01-MAY-21 10
02-MAY-21 9
03-MAY-21 8
04-MAY-21 8
05-MAY-21 8
06-MAY-21 11
From: Beckstrom, Jeffrey
Sent: Tuesday, May 25, 2021 8:09 AM
To: Karthikeyan Panchanathan <keyantech@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: RE: 12.2.0.1 scheduler tables not being purged
The attached shows no rows from June 28, 2020 to April 24, 2021 so some data is
being purged.Howver there is a lot of data from June 27, 2020 and earlier.
From: Beckstrom, Jeffrey
Sent: Tuesday, May 25, 2021 7:43 AM
To: Karthikeyan Panchanathan <keyantech@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: RE: 12.2.0.1 scheduler tables not being purged
Select * from dba_scheduler_global_attribute ;
ATTRIBUTE_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY
30
DEFAULT_TIMEZONE
US/Eastern
LAST_OBSERVED_EVENT
EVENT_EXPIRY_TIME
CURRENT_OPEN_WINDOW
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION
NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
FILE_WATCHER_COUNT
0
11 rows selected.
From: Beckstrom, Jeffrey
Sent: Tuesday, May 25, 2021 7:33 AM
To: Karthikeyan Panchanathan <keyantech@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: RE: 12.2.0.1 scheduler tables not being purged
There is no object called dba_shcheduler_global_Attirbute.
The scheduler shows “purge_log” as running with no failures.
From: Karthikeyan Panchanathan <keyantech@xxxxxxxxx>
Sent: Monday, May 24, 2021 4:32 PM
To: Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: Re: 12.2.0.1 scheduler tables not being purged
Dbms_scheduler.run_job(‘PURGE_LOG’)
That should purge job log.
Please check dba_shcheduler_global_Attirbute where attribute_name=‘LOG_HISTORY’
to see log history value
Get Outlook for iOS
<https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Faka.ms%2Fo0ukef&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C573d45b138fb41146d2a08d91f8889d7%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637575494595072349%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=BYUP3zGPmIOYpBR56FtHGlotu3uvcJ6le7yVXdsLKGw%3D&reserved=0>
_____
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx>
Sent: Monday, May 24, 2021 2:45:42 PM
To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
Subject: 12.2.0.1 scheduler tables not being purged
What purges the scheduler tables? We are seeing entries going back to 2011. The
sys owned purge_log job does show as being scheduled with no failures.
select min (log_date) from dba_scheduler_job_log;
MIN(LOG_DATE)
---------------------------------------------------------------------------
03-OCT-11 10.00.01.100000 PM -04:00
select min (log_date) from sys.SCHEDULER$_EVENT_LOG;
MIN(LOG_DATE)
---------------------------------------------------------------------------
03-OCT-11 10.00.01.100000 PM -04:00
Jeffrey Beckstrom
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org ;
<https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C573d45b138fb41146d2a08d91f8889d7%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637575494595072349%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=sauIs%2BSItW0mMl4cq0ooe1jiLb8b959hUuZVnKQcQyQ%3D&reserved=0>
