19c Impdp hangs after SCHEMA_EXPORT/VIEW/VIEW with cpu 100%
1
vote
0
answers
6669
views
I've a impdp job on Oracle 19c always hanging with 100% CPU (
ora_dw00_orclcd
process) after the message below:
Elaborazione dell'object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Elaborazione dell'object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Elaborazione dell'object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Elaborazione dell'object type SCHEMA_EXPORT/VIEW/VIEW
Very strange for me is the output of select NAME,SQL_TEXT from dba_resumable;
:
SYSTEM.SYS_IMPORT_SCHEMA_01.1 (null)
SYSTEM.SYS_IMPORT_SCHEMA_01 BEGIN :1 := sys.kupc$que_int.receive(:2); END;
It's a datapump exported from a 11.2.0.1
oracle database with COMPRESSION=ALL
and not Full (SCHEMAS=MYSCHEMA1 MYSCHEMA2 MYSCHEMA3
)
Any hints?
EDIT2:
top
output is:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6313 oracle 20 0 2669888 1,3g 744812 R 91,7 23,9 81:32.14 ora_dw00_ORCLCDB
And this query to find running statement returns null
select
s.username su,
substr(sa.sql_text,1,540) txt,
s.event,
s.SID
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=6313;
"SU" "TXT" "EVENT" "SID"
"SYSTEM" "" "db file sequential read" 276
Impdp status after 18 hours:
Import> status
Job: SYS_IMPORT_SCHEMA_01
Operation: IMPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 8,481,046,744
Percent Done: 14
Current Parallelism: 2
Job Error Count: 0
Job heartbeat: 904
Dump File: /u01/DMP/my.dmp
Worker 1 Status:
Instance ID: 1
Instance name: ORCLCDB
Host name: lnx-db
Object start time: Wednesday, 29 April, 2020 14:48:35
Object status at: Wednesday, 29 April, 2020 14:48:35
Process Name: DW00
State: EXECUTING
Object Schema: MYSCHEMA
Object Name: V_MYVIEW_xxx
Object Type: SCHEMA_EXPORT/VIEW/VIEW
Completed Objects: 80
Worker Parallelism: 1
Worker 2 Status:
Instance ID: 1
Instance name: ORCLCDB
Host name: lnx-db
Object start time: Wednesday, 29 April, 2020 14:48:13
Object status at: Wednesday, 29 April, 2020 14:48:31
Process Name: DW01
State: WORK WAITING
Asked by sgargel
(337 rep)
Apr 29, 2020, 10:08 AM
Last activity: Aug 8, 2024, 03:49 PM
Last activity: Aug 8, 2024, 03:49 PM