PART 2: Ways to move XML data; XMLTYPE w/ CLOB storage - RE: What happened!? IMPort tables with "User Defined or XML Datatypes"....so slow!

Env:
AIX 5.3
Oracle 9203  

Considering that I am hitting a nasty bug for AIX and 9203 regarding IMPort and XMLTYPE w/ CLOB storage and there is not obvious patch or fix,
I need to find a way to move tables with XML data; XMLTYPE w/ CLOB storage *AND* some tables with User Defined datatypes.  

Would anyone be able to tell me if "SELECT INSERT..." over db links *OR* SQLPlus COPY command supports XMLTYPE w/ CLOB storage?  

PS Anyone have a query to pull a User Defined datatypes definition?  

Please reply directly as well as to the list.  

Thanks,  

Chris Marquez
Oracle DBA
HEYMONitor(tm) - heymonitor.com
"Oracle Monitoring & Alerting Solution"  

-----Original Message-----
From: Marquez, Chris
Sent: Wednesday, April 20, 2005 10:14 AM To: oracle-l_at_freelists.org
Subject: RE: What happened!? IMPort tables with "User Defined or XML Datatypes"....so slow!

>>RE: What happened!?
 

What happened is that we appear to be hitting a nasty bug for AIX and 9203.
A memory leak related to IMPort and XMLTYPE w/ CLOB storage; Metalink Bug 3330477.
Casues IMP to slow to a crawl.  

I was wrong our env is 9203.  

Chris Marquez
Oracle DBA
HEYMONitor(tm) - heymonitor.com
"Oracle Monitoring & Alerting Solution"  

 ----Original Message-----
From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Friday, April 15, 2005 6:24 PM
To: Marquez, Chris
Cc: oracle-l_at_freelists.org
Subject: Re: What happened!? IMPort tables with "User Defined or XML Datatypes"....so slow!

Try running a 10046 trace on the import session.

If the import is being done across the network you may find that imp is very chatty.

Jared

On 4/15/05, Marquez, Chris <CMarquez_at_aarp.org> wrote:

	--Env
	Oracle 9206
	AIX 5.x
	
	I don't have much experience with "User Defined Datatypes" nor
"XML
	Datatypes".=20
	Trying to move a database for a client that uses these
datatypes.
	(not sure but maybe some of them are Nested Tables or VARRAY's
vs. "User 
	Defined Datatype"...how can I tell the difference?)
	
	Anyway, in the same database I can IMPort
	* 15GB-20GB normal data (no indexes) per hour.
	* 10GB GB BLOB data per hour.
	but only
	* 324MB of "User Defined Datatypes" After 1 Hr. 15 Min. 
	* 53MB of "XML Datatype" After 30 Min.
	
	

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist



--
http://www.freelists.org/webpage/oracle-l