[20191211]11g streams_pool_size参数.txt

来源：这里教程网时间：2026-03-03 14:40:59 作者：

[20191211]11g streams_pool_size参数.txt --//以前我看别人的文章,发现streams_pool_size设置为0,会导致expdp,impdp无法运行. --//我自己测试过,发现没有这种现象,最近才明白问题在哪里. 1.环境: SYS@book> @ ver1 PORT_STRING VERSION BANNER -------------------- ----------- ---------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@book> @ hide streams_pool_size NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ------------------- ------------------------------------ ------------- ------------- ------------ ----- --------- __streams_pool_size Actual size in bytes of streams pool TRUE 0 0 FALSE IMMEDIATE streams_pool_size size in bytes of the streams pool FALSE 0 0 FALSE IMMEDIATE SYS@book> show spparameter streams_pool_size SID NAME TYPE VALUE -------- ------------------ ------------ ------ * streams_pool_size big integer 0 2.测试: --//测试前: SYS@book> select * from v$sgainfo; NAME BYTES RES ---------------------------------------- ---------- --- Fixed SGA Size 2255872 No Redo Buffers 7487488 No Buffer Cache Size 427819008 Yes Shared Pool Size 180355072 Yes Large Pool Size 12582912 Yes Java Pool Size 12582912 Yes Streams Pool Size 0 Yes ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Shared IO Pool Size 0 Yes Granule Size 4194304 No Maximum SGA Size 643084288 No Startup overhead in Shared Pool 133446832 No Free SGA Memory Available 0 12 rows selected. --//可以发现streams_pool_size的可以RESIZEABLE的.RESIZEABLE='YES' --//expdp导出看看. $ expdp scott/book Export: Release 11.2.0.4.0 - Production on Wed Dec 11 10:56:25 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/a***** Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 11.31 MB Processing object type SCHEMA_EXPORT/USER 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/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."TX" 8.219 MB 84768 rows . . exported "SCOTT"."SESSION_WAIT_RECORD" 386.7 KB 8122 rows . . exported "SCOTT"."LOCK_OBJECT_RECORD" 308.4 KB 8122 rows . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows . . exported "SCOTT"."EMPY" 8.562 KB 14 rows . . exported "SCOTT"."EMPYY" 8.570 KB 14 rows . . exported "SCOTT"."EMPZ" 8.562 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows . . exported "SCOTT"."T" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/book/dpdump/expdat.dmp Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Dec 11 10:56:44 2019 elapsed 0 00:00:18 --//ok,没有任何问题. --//测试后: SYS@book> select * from v$sgainfo; NAME BYTES RES ---------------------------------------- ---------- --- Fixed SGA Size 2255872 No Redo Buffers 7487488 No Buffer Cache Size 406847488 Yes Shared Pool Size 180355072 Yes Large Pool Size 12582912 Yes Java Pool Size 12582912 Yes Streams Pool Size 20971520 Yes ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Shared IO Pool Size 0 Yes Granule Size 4194304 No Maximum SGA Size 643084288 No Startup overhead in Shared Pool 133446832 No Free SGA Memory Available 0 12 rows selected. --//Streams Pool Size=20971520 SYS@book> @ hide streams_pool_size NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ------------------- ------------------------------------ ------------- ------------- ------------ ----- --------- __streams_pool_size Actual size in bytes of streams pool TRUE 0 0 FALSE IMMEDIATE streams_pool_size size in bytes of the streams pool FALSE 20971520 20971520 FALSE IMMEDIATE --//oracle会从别的地方借用内存,我这里从Buffer Cache Size . --//406847488/1024/1024 = 388M SYS@book> show spparameter db_cache_size SID NAME TYPE VALUE -------- ------------- ----------- ----- * db_cache_size big integer 408M --//我以前总以为如果我定义db_cache_size是使用数据缓存的最小值.实际上还是可以缩小的. 3.继续分析: SYS@book> show spparameter sga_ SID NAME TYPE VALUE -------- ------------- ------------ ----------- * sga_max_size big integer * sga_target big integer --//实际上我的测试环境并没有配置sga_target,sga_max_size.而实际上启动后设置sga_max_size到内存. SYS@book> show parameter sga_ NAME TYPE VALUE ------------ ----------- ----- sga_max_size big integer 616M sga_target big integer 0 --//假设我写入参数文件. *.sga_max_size=600M *.sga_target=600M SYS@book> startup pfile='/tmp/@.ora'; ORA-00821: Specified value of sga_target 600M is too small, needs to be at least 616M ORA-01078: failure in processing system parameters --//而如果修改: *.sga_max_size=600M *.sga_target=0M SYS@book> startup pfile='/tmp/@.ora'; ORACLE instance started. Total System Global Area 643084288 bytes Fixed Size 2255872 bytes Variable Size 205521920 bytes Database Buffers 427819008 bytes Redo Buffers 7487488 bytes Database mounted. Database opened. SYS@book> show parameter sga_ NAME TYPE VALUE ------------ ----------- ------ sga_max_size big integer 616M sga_target big integer 0 --//sga_max_size自动在内存中修改参数为616M. SYS@book> create spfile from pfile='/tmp/@.ora'; File created. SYS@book> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. SYS@book> startup ORACLE instance started. Total System Global Area 643084288 bytes Fixed Size 2255872 bytes Variable Size 205521920 bytes Database Buffers 427819008 bytes Redo Buffers 7487488 bytes Database mounted. Database opened. SYS@book> show spparameter sga_ SID NAME TYPE VALUE -------- ------------ ------------ ------- * sga_max_size big integer 600M * sga_target big integer 0 SYS@book> show parameter sga_ NAME TYPE VALUE ------------ ----------- ------ sga_max_size big integer 616M sga_target big integer 0

编辑推荐：

返回列表

[20191211]11g streams_pool_size参数.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推