My Oracle Support Banner

Database Export slow while exporting triggers - XDB installed (Doc ID 727411.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.6 - Release: 9.2 to 11.1
Information in this document applies to any platform.

Symptoms

EXPORT LOG 
----------
...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers <== Export seems to hang.
SQL> SELECT SUBSTR(comp_id,1,15) comp_id, status,  
            SUBSTR(version,1,10) version,      
            SUBSTR(comp_name,1,30) comp_name      
       FROM dba_registry ORDER BY 1;
       
COMP_ID              STATUS VERSION    COMP_NAME
--------------- ----------- ---------- ------------------------------
... 
XDB                   VALID 10.1.0.5.0 Oracle XML Database 
XML                   VALID 10.1.0.5.0 Oracle XDK 

ANALYSIS

To diagnose this in more detail, repeat the export, open a separate sqlplus session to create several errorstack dumps.

-- Determine SPID of export process 
select distinct(p.spid),s.sid,s.serial#
from v$process p, v$session s, v$mystat m
where s.PADDR = p.ADDR
and lower(s.program) like '%exp%';

 

-- connect to the process as sysdba 
connect / as sysdba
oradebug setospid xxxx <-- use SPID here
oradebug unlimit
-- Create process state dumps 

oradebug dump errorstack 3
... wait 30 seconds
oradebug dump errorstack 3
... wait 30 seconds
oradebug dump errorstack 3


TRACE FILE

Look at the "Current SQL statement for this session". This will confirm package body XDB.DBMS_XDBUTIL_INT is executed:

ksedmp: internal or fatal error 
Current SQL statement for this session:
SELECT COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :B2 AND V.OBJECT_NAME = :B1 AND (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%')
----- PL/SQL Call Stack -----
object line object
handle number name
c000000257c950c8 465 package body XDB.DBMS_XDBZ0
c0000002479c2048 65 package body XDB.DBMS_XDBUTIL_INT <==
c0000002479c3080 1 anonymous block

 

Now, look at 'psql' field in the session state dump information. This will point to a library cache handle.




(session) trans: 0000000000000000, creator: c00000025d2fd8f0, flag: (10000041) USR/- BSY/-/-/-/-/-
...
oct: 3, prv: 0, sql: c000000257c7d0b8, psql: c00000024a58e988, user: 5/SYSTEM
O/S info: user: oracle, term: pts/ta, ospid: 9260, machine: vir-hp7
program: exp@vir-hp7 (TNS V1-V3)
application name: exp@vir-hp7 (TNS V1-V3), hash value=0
last wait for 'SQL*Net message from client' blocking sess=0x0000000000000000 seq=10615 wait_time=58
driver id=62657100, #bytes=1, =0

Check the previous sql statement:

 

Here, 'psql' points to handle 0xc00000024a58e988 which shows a query against ALL_TRIGGERS.





SO: c00000025a3d07d8, type: 54, owner: c00000025e3ad198, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c00000025a3d07d8 handle=c00000024a58e988 mode=N
call pin=c0000002511a07b0 session pin=0000000000000000 hpc=0000 hlc=0000
htl=c00000025a3d0848[c00000025a316140,c00000025a312a20] htb=c00000025bd6df68
user=c00000025e3ad198 session=c00000025e3ad198 count=1 flags=[0000] savepoint=7419397
LIBRARY OBJECT HANDLE: handle=c00000024a58e988
name=SELECT TABLE_NAME FROM ALL_TRIGGERS WHERE TRIGGER_NAME = :B2 AND TABLE_OWNER = :B1

Have a look at the explain plan

 


Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
  Symptoms
     ANALYSIS
  Cause
  Solution
     FIX
     WORKAROUND
     PATCH
  References

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.