You've got to have a dream. When you lose your dreams, you die.
expdp slow after upgrade from 22.214.171.124 to 126.96.36.199
DataPump Export (EXPDP) Runs Very Slow After Upgrade From 188.8.131.52 to 184.108.40.206 (Doc ID 1075468.1)
Oracle Server - Enterprise Edition - Version: 220.127.116.11 to 18.104.22.168 - Release: 11.1 to 11.1 Information in this document applies to any platform.
DataPump Export (EXPDP)/Import (IMPDP) runs very slow after upgrade from 22.214.171.124 to 126.96.36.199.
Upgrade to 188.8.131.52 was successful. Database registry shows all components are valid and no invalid objects. No changes in the database.
The import log shows waits for very long time on SYS.SCHEDULER$_EVENT_LOG.
Objekttyp DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ wird verarbeitet
Current Wait Event db file scattered read
Current Wait Class User I/O
Wait Duration 0 (s)
P1 file# E:\ORACLE\ORADATA\DWHPROD...SYSAUX01.DBF
P2 block# 1212177
P3 blocks 128
Further investigation narrowed down to table SCHEDULER$_EVENT_LOG that allocated huge space in tablespace SYSAUX..
SYS.SCHEDULER$_EVENT_LOG is a system table contains logging information. Here are the options to clear all entries from both the job and window logs:
1) Run in SQL*Plus:
2) Run in SQL*Plus:
alter table SCHEDULER$_EVENT_LOG shrink space cascade;
then monitor with:
from (select substr (owner, 1, 10) owner,
substr (segment_name, 1, 30) seg_name,
substr (segment_type, 1, 10) type,
rank() over (order by bytes desc) position
where tablespace_name = 'SYSAUX'
order by bytes, extents desc)
where position < 10
order by position;
After cleared and purged all scheduler event log entries, DataPump export/import completes in short time.