Sample Header Ad - 728x90

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