Home » RDBMS Server » Server Utilities » expdp error
expdp error [message #292680] Wed, 09 January 2008 10:44 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

While doing datapump using with the following query , I'm getting the following error.

$ expdp directory=dp dumpfile=full.dmp full=y job_name=fuls



Export: Release 10.2.0.1.0 - Production on Wednesday, 09 January, 2008 22:08:50

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31637: cannot create job FULS for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20080109220852" and "KUPC$S_1_20080109220852" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
ORA-00832: no streams pool created and cannot automatically create one

I've even added a new tempfile to the existing tablespace.

But nothing is working out.

Please help me out.

Regards,

Raj

Re: expdp error [message #292688 is a reply to message #292680] Wed, 09 January 2008 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

ORA-00832: no streams pool created and cannot automatically create one
Cause: A database feature which needs STREAMS SGA was being used, however, the streams_pool_size parameter was not defined and the value of db_cache_size was too small to permit an automatic transfer of SGA to the streams pool from the buffer cache.
Action: Please set the parameter streams_pool_size or set sga_target.

Regards
Michel
Re: expdp error [message #292689 is a reply to message #292688] Wed, 09 January 2008 11:35 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello Michel,

Please let me know how much we should set the parameter streams_pool_size or set sga_target. Like this is totally a small test database where the size of database is hardly 1 Gb at the OS level.

Regards,
Raj
Re: expdp error [message #292695 is a reply to message #292689] Wed, 09 January 2008 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are your current SGA parameters?

Regards
Michel
Re: expdp error [message #292809 is a reply to message #292680] Wed, 09 January 2008 22:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Just set sga_target to an amount about sga_max_size.

alter system set sga_target=1G;
Re: expdp error [message #292860 is a reply to message #292809] Thu, 10 January 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Just set sga_target to an amount about sga_max_size.

What if sga_max_size is not set?

Regards
Michel
Re: expdp error [message #293005 is a reply to message #292695] Thu, 10 January 2008 09:53 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hi Michel,

Sorry for the delay. anyway here is mt sga parameter


shared_pool_size = 62198988 .

regards,
raj

Re: expdp error [message #293008 is a reply to message #292688] Thu, 10 January 2008 10:09 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello Michel,

As per the error , I've specified I mentioned

streams_pool_size=1000000 , in the pfile, and now the EXPDP is working.

But I'm getting this error only on this TEST DB. But whereas on my other databases the EXPDP is working even without specifying this parameter.

Can you brief me on this please . In what way is the Streams pool related to EXPDP.

Regards,
Raj

[Updated on: Thu, 10 January 2008 10:14]

Report message to a moderator

Re: expdp error [message #293009 is a reply to message #293005] Thu, 10 January 2008 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not only shared pool is interesting but also cache(s) size and other pools.

Regards
Michel

[Updated on: Thu, 10 January 2008 10:10]

Report message to a moderator

Re: expdp error [message #293011 is a reply to message #293009] Thu, 10 January 2008 10:19 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Thats what I mentioned you earlier. Its a small Test DB. But plz throw some light the reason behind this logic
Re: expdp error [message #293013 is a reply to message #293011] Thu, 10 January 2008 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Look at the timestamp, we were writing at the same time.

As per documentation (http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_perf.htm#sthref451):

Quote:

As of Oracle Database 10g release 10.2, if Streams functionality is used, but the STREAMS_POOL_SIZE initialization parameter is not defined, then the size of the streams pool automatically defaults to 10% of size of the shared pool.

When the streams pool is created, the required SGA memory is taken from memory allocated to the buffer cache, reducing the size of the cache to less than what was specified by the DB_CACHE_SIZE initialization parameter. This means that if the buffer cache was configured with only the minimal required SGA, then Data Pump operations may not work properly.


Regards
Michel
Re: expdp error [message #293019 is a reply to message #293013] Thu, 10 January 2008 10:51 Go to previous message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Thanks Michel . Now it makes some sense that due to the low SGA size it could not perform the EXPDP.

Regards,
Raj
Previous Topic: Error encountered while takin export on owner mode.
Next Topic: SQL loader problem
Goto Forum:
  


Current Time: Fri Dec 09 19:39:35 CST 2016

Total time taken to generate the page: 0.10725 seconds