Home » RDBMS Server » Server Utilities » export/import xml records in oracle 11gR2
export/import xml records in oracle 11gR2 [message #541156] Sat, 28 January 2012 13:30 Go to next message
shipon_97
Messages: 8
Registered: March 2008
Junior Member
Dear Friends ,

In oracle 11g R2 , I face a problem when I export/import xml records in the tables . Everytime it takes huge time (like 2 or 3 days) to import or export the data . But the dump size is very small (4gb) and this dump comes from a vendor so that I dont understand that is it a data structure problem or it the normal behaviour to import xml records.

I am not used to with xml record with oracle database before. Here I am using datapump feature . I also mention that when I delete the schema (where I imported the xml data) , it also takes 2/3 days to delete .

The import script will hang in the following stage :

=========================
bash-3.2$ impdp system/sys123 DIRECTORY=isb_dir DUMPFILE=jblbld_20110301_01.dmp,jblbld_20110301_02.dmp,jblbld_20110301_03.dmp logfile=JBLBLD_28Jan2012.log schemas=TBLD;date

Import: Release 11.2.0.1.0 - Production on Sun Jan 29 11:10:38 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02": system/******** DIRECTORY=isb_dir DUMPFILE=jblbld_20110301_01.dmp,jblbld_20110301_02.dmp,jblbld_20110301_03.dmp logfile=JBLBLD_28Jan2012.log schemas=TBLD
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TBLD" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
=======================================

The machine has 16 cpu , 32 gb RAM and during export/import most of the time maximum memory will free . Also I use parallel feature but problem is not solved .

Need expert opinion please ... ....
Re: export/import xml records in oracle 11gR2 [message #541160 is a reply to message #541156] Sat, 28 January 2012 20:57 Go to previous message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id   IN  BINARY_INTEGER DEFAULT NULL,
    serial_num   IN  BINARY_INTEGER DEFAULT NULL,
    waits        IN  BOOLEAN DEFAULT TRUE,
    binds        IN  BOOLEAN DEFAULT FALSE,
    plan_stat    IN  VARCHAR2 DEFAULT NULL);
Previous Topic: Tracking a Data Pump job
Next Topic: Can not load LOB/XML in a nested table along with the parent table
Goto Forum:
  


Current Time: Sun Dec 21 06:34:31 CST 2014

Total time taken to generate the page: 0.08370 seconds