[20191211]11g streams_pool_size引數.txt
[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2667945/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191211]hugepages pagetables.txt
- [20191211]Free SGA Memory Available.txtAI
- OGG-整合模式抽取與資料庫引數streams_pool_size關係模式資料庫
- [20180413]bash 位置引數.txt
- [20200620]expdp impdp exclude引數.txt
- Oracle 11G 隱含引數“_controlfile_autobackup_delay”Oracle
- [20210826]核心引數kernel.sem.txt
- [20190917]oracle引數deferred屬性.txtOracle
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- [20200220]windows設定keepalive引數.txtWindows
- [20210209]修改CPU_COUNT引數.txt
- [20201130]11g or_expand提示.txt
- [20190306]11g health monitor.txt
- [20190219]那個更快(11g).txt
- [20220913]hugepage相關引數含義.txt
- [20191204]hugepage相關引數含義.txt
- [20190417]隱含引數_SPIN_COUNT.txt
- [20210310]db_lost_write_protect引數.txt
- [20190409]latch get 引數where and why.txt
- [20180308]測試ARG_MAX引數.txt
- [20211025]11g sequemce nocahe測試.txt
- [20201126]11g VPD的問題.txt
- [20231109]bbed p命令dba引數問題.txt
- [20211027]引數plscope_settings分析PLSQL.txtSQL
- [20210209]修改CPU_COUNT引數2.txt
- [20190409]latch get 引數where and why測試.txt
- [20190401]隱含引數_mutex_spin_count.txtMutex
- [20180529]模擬會話引數變化.txt會話
- [20201210]11G ACS相關問題.txt
- [20220228]測試Max_idle_blocker_time 引數.txtBloC
- [20181109]12c sqlplus rowprefetch引數5.txtSQL
- [20181108]12c sqlplus rowprefetch引數4.txtSQL
- [20181106]12c sqlplus rowprefetch引數3.txtSQL
- [20190416]11g下那些latch是Exclusive的.txt
- [20190415]11g下那些latch是共享的.txt
- [20190214]11g Query Result Cache RC Latches.txt
- [20180928]如何能在11g下執行.txt
- [20191206]隱含引數_db_always_check_system_ts.txt