问题实验环境
操作系统：red hat enterprise linux server release 5.7 (tikanga)
数据库 ：oracle database 10g release 10.2.0.4.0 - production
错误再现分析
在使用数据泵导数据时，遇到下面错误：
[oracle@gsp db_expd_bak]$ expdp system/xxxx directory=dump_dir dumpfile=dm.dmp tablespaces=dm content=all;
export: release 10.2.0.4.0 - production on thursday, 29 august, 2013 21:38:44
copyright (c) 2003, 2007, oracle. all rights reserved.
connected to: oracle database 10g release 10.2.0.4.0 - production
ora-31626: job does not exist
ora-31637: cannot create job sys_export_tablespace_01 for user system
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_20130829213845" and "kupc$s_1_20130829213845" for data pump job
ora-06512: at "sys.kupc$que_int", line 1606
ora-00832: no streams pool created and cannot automatically create one
造成ora-00832：no streams pool created and cannot automatically create one错误的原因一般是streams_pool_size太小或没有定义streams_pool_size
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
sql> show parameter streams_pool_size
name type value
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
sql>
由于streams_pool_size一般在assm中是动态分配的，所以参数streams_pool_size一直为0，要查看streams_pool_size的真实大小就必须通过下面脚本来查询：
code snippet
epps> col name for a36;
epps> col value for a10;
epps> col idfefault for a10;
epps> col ismod for a10;
epps> col isadj for a10;
epps>select x.ksppinm name ,
2 y.ksppstvl value ,
3 y.ksppstdf idfefault ,
4 decode(bitand(y.ksppstvf,7), 1, 'modified', 4, 'system_mod', 'false') ismod,
5 decode(bitand(y.ksppstvf,2), 2, 'true', 'false') isadj
6 from sys.x$ksppi x,
7 sys.x$ksppcv y
8 where x.inst_id = userenv('instance') and
9 y.inst_id = userenv('instance') and
10 x.indx = y.indx and
11 x.ksppinm like '%_streams%'
12 order by translate(x.ksppinm, '_', '');
name value idfefault ismodisadj
------------------------------------ ---------- ---------- ---------- ----------
__streams_pool_size 0 true falsefalse
_memory_broker_shrink_streams_pool 900 true falsefalse
_disable_streams_pool_auto_tuning false true falsefalse
_streams_pool_max_size 0 true falsefalse
epps> show parameter sga
lock_sga boolean true
pre_page_sga boolean false
sga_max_size big integer 3424m
sga_target big integer 0
epps>
查看streams_pool_size的实际大小，发现其大小为0，更让我吃惊
的却在后面：sga_target 为0，也就是说数据库没有启动自动共享内存管理（automatic shared memory
management asmm）。真是绕了一大圈。所以必须手动调整streams_pool_size的大小：
epps> alter system set streams_pool_size=100m scope=memory;
alter system set streams_pool_size=100m scope=memory
*
error at line 1:
ora-02097: parameter cannot be modified because specified value is invalid
ora-04033: insufficient memory to grow pool
epps> alter system set streams_pool_size=1m scope=memory;
alter system set streams_pool_size=1m scope=memory
因为sga采用老的分配方式，没有采用assm管理sga，需要从其它内存中释放一些内存出来分配给streams_pool_size，结合分析，最后从
shared_pool_size中分配32m出来给streams_pool_size。问题解决，另外一个解决方法就是讲sga_target设为非零。让sga动态给streams_pool_size分配内存。