[20190917]oracle参数deferred属性.txt
--//以前测试要修改一些参数，如果发现不能修改，我一般选择写入spfile文件，重启测试文件。
--//或者写入initXXXX.ora文件，使用该参数文件启动数据库实例。
--//实际上有小量参数是具有deferred属性。这类参数对当前回话不生效，对以后登录的回话生效。
--//这类类型的参数不是很多，简单看看顺便做一个记录：
1.环境：
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.测试:
SYS@test> select name,DISPLAY_VALUE,ISSES_MODIFIABLE, ISSYS_MODIFIABLE from v$parameter where ISSYS_MODIFIABLE='DEFERRED';
NAME DISPLAY_VALUE ISSES ISSYS_MOD
------------------------------ ------------------------------ ----- ---------
backup_tape_io_slaves FALSE FALSE DEFERRED
recyclebin on TRUE DEFERRED
session_cached_cursors 50 TRUE DEFERRED
audit_file_dest D:\APP\ORACLE\ADMIN\TEST\ADUMP FALSE DEFERRED
object_cache_optimal_size 10240000 TRUE DEFERRED
object_cache_max_size_percent 10 TRUE DEFERRED
sort_area_size 65536 TRUE DEFERRED
sort_area_retained_size 0 TRUE DEFERRED
olap_page_pool_size 0 TRUE DEFERRED
9 rows selected.
--//12cR2仅仅9个这样类型的参数(不包括隐含参数).可以发现一个参数session_cached_cursors参数设置其实不用重启数据库.
--//其它应该很少修改.
SYS@test> alter system set session_cached_cursors=100;
alter system set session_cached_cursors=100
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
SYS@test> alter system set session_cached_cursors=100 scope=memory;
alter system set session_cached_cursors=100 scope=memory
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
--//按照以前我如果要修改就修改spfile文件,然后等待时机重启数据库.
SYS@test> alter system set session_cached_cursors=100 DEFERRED scope=memory;
System altered.
--//再重新登录
SYS@test> @ hide session_cached_cursors
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------- ---------------------------------------- ------------- ------------- ------------
session_cached_cursors Number of cursors to cache in a session. TRUE 100 100
3.修改我自己的脚本:
$ cat hide.sql
col name format a40
col description format a66
col session_value format a22
col default_value format a22
col system_value format a22
select
a.ksppinm name,
a.ksppdesc DESCRIPTION,
b.ksppstdf DEFAULT_VALUE,
b.ksppstvl SESSION_VALUE,
c.ksppstvl SYSTEM_VALUE,
DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') ISSES_MODIFIABLE,
DECODE
(
BITAND (a.ksppiflg / 65536, 3)
,1, 'IMMEDIATE'
,2, 'DEFERRED'
,3, 'IMMEDIATE'
,'FALSE'
) ISSYS_MODIFIABLE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and lower(a.ksppinm) like lower('%&1%')
order by 1;
SYS@test> @ hide.txt session_cached_cursors
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------- ---------------------------------------- ------------- ------------- ------------ ----- ---------
session_cached_cursors Number of cursors to cache in a session. TRUE 100 100 TRUE DEFERRED
--//实际上对于参数session_cached_cursors,对于会话是可以修改的,.执行如下:
SYS@test> alter session set session_cached_cursors=200 ;
Session altered.
4.继续测试:
--//再来看看open_cursor参数:
SYS@test> @ hide open_cursor
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------------- --------------------------------------------- ------------- ------------- ------------ ----- ---------
_close_cached_open_cursors close cursors cached by PL/SQL at each commit TRUE FALSE FALSE TRUE FALSE
open_cursors max # cursors per session FALSE 300 300 FALSE IMMEDIATE
--//可以发现ISSYS_MODIFIABLE=IMMEDIATE.按照道理立即生效而实际情况呢?
--//下面一段测试ora-04031例子,先打开2个会话分别以scott,sys用户登录:
--//session 1,scott用户登录:
create table a1 ( id1 number,id2 number);
--//session 2,sys用户登录:
SYS@test01p> @ hide open_cursor
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------------- --------------------------------------------- ------------- ------------- ------------ ----- ---------
_close_cached_open_cursors close cursors cached by PL/SQL at each commit TRUE FALSE FALSE TRUE FALSE
open_cursors max # cursors per session FALSE 300 300 FALSE IMMEDIATE
--//open_cursors=300.
--//session 1:
SCOTT@test01p> alter system set open_cursors=50000 scope=memory ;
System altered.
--//session 2:
SYS@test01p> @ hide open_cursor
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------------- --------------------------------------------- ------------- ------------- ------------ ----- ---------
_close_cached_open_cursors close cursors cached by PL/SQL at each commit TRUE FALSE FALSE TRUE FALSE
open_cursors max # cursors per session FALSE 50000 50000 FALSE IMMEDIATE
--//你可以发现open_cursors无论是session还是system,都是50000.
--//session 1:
--//不要退出执行如下脚本
$ cat sharepool/shp7
declare
msql varchar2(500);
mcur number;
mstat number;
begin
for i in 1 .. 49000 loop
mcur := dbms_sql.open_cursor;
msql := 'select id1 from a1 where id2='||to_char(i);
dbms_sql.parse(mcur,msql,dbms_sql.native);
-- mstat := dbms_sql.execute(mcur);
end loop;
end;
/
--//注解那行不用执行。
--//测试例子是打开光标并分析,由于没有使用绑定变量,并且光标没有正常关闭,大量消耗共享池.
SCOTT@test01p> @ sharepool/shp7
declare
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at line 9
--//如果不退出执行，出现ORA-01000: maximum open cursors exceeded.很明显光标数量不足也就是当前open_cursors=300.
--//如果退出再次执行，就会消耗大量共享池，出现ora-04031错误。
SCOTT@test01p> @ sharepool/shp7
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","select name,online$,contents...","SQLA^56d22d85","qeeRwo: qeeCreateRwo")
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at line 9
总结:
--//实际上想通过一些简单的例子说明就是简简单单的参数设置,oracle有时候都能人搞晕.在看看那些隐含参数是具有DEFERRED特性的.
/* Formatted on 2019/9/17 21:48:38 (QP5 v5.227.12220.39754) */
SELECT *
FROM (SELECT a.ksppinm name
,a.ksppdesc DESCRIPTION
,b.ksppstdf DEFAULT_VALUE
,b.ksppstvl SESSION_VALUE
,c.ksppstvl SYSTEM_VALUE
,DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')
ISSES_MODIFIABLE
,DECODE
(
BITAND (a.ksppiflg / 65536, 3)
,1, 'IMMEDIATE'
,2, 'DEFERRED'
,3, 'IMMEDIATE'
,'FALSE'
)
ISSYS_MODIFIABLE
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx)
WHERE ISSYS_MODIFIABLE = 'DEFERRED';
--//输出太长,不在贴出.
SELECT count(*)
FROM (SELECT a.ksppinm name
,a.ksppdesc DESCRIPTION
,b.ksppstdf DEFAULT_VALUE
,b.ksppstvl SESSION_VALUE
,c.ksppstvl SYSTEM_VALUE
,DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')
ISSES_MODIFIABLE
,DECODE
(
BITAND (a.ksppiflg / 65536, 3)
,1, 'IMMEDIATE'
,2, 'DEFERRED'
,3, 'IMMEDIATE'
,'FALSE'
)
ISSYS_MODIFIABLE
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx)
WHERE ISSYS_MODIFIABLE = 'DEFERRED';
COUNT(*)
----------
124
--//可以发现许多隐含参数具有这个特性的.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2657326/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
