一、测试需求背景
1、有没有问我初始化参数修改之后是否可以立即生效,立即做了一个测试,结论是,可以。
2、linux系统下,oracle数据块默认8K,能支持的数据文件最大32G,想使用64G的就需要修改初始化参数 db_16k_cache_size
3、顺便复习总结了一下表空间相关基础概念:
1)同一个数据库内可以设置不同block大小,来设置不同大小的数据文件,有时候业务有这个需求。
2)同一个表空间只能使用同一种block
3)spfile启动的数据库,可以使用scope=both使得参数立即在内存中生效,但是要注意前提,pfile启动的库不行。
二、测试过程
SQL> show parameter block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
db_file_multiblock_read_count integer 128
SQL> show parameter db_16k
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
SQL> alter system set db_16k_cache_size=34603008 scope=spfile;
System altered.
SQL>
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40G;
alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40G
*
ERROR at line 1:
ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocks
SQL> alter system set db_16k_cache_size=34603008 scope=both;
System altered.
SQL> alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40G blocksize 16k;
alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40G blocksize 16k
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> create tablespace aaa datafile '/oracle/oradata/aa.dbf' size 40G blocksize 16k;
Tablespace created.
SQL> SQL> SQL> host du -sh /oracle/oradata/aa.dbf
41G /oracle/oradata/aa.dbf
三、scope参数详解
SCOPE = SPFILE
The change is applied in the
server
parameter file only. The effect is as follows:
■ For dynamic parameters, the change is effective at the next startup and is persistent.
■ For static parameters, the behavior. is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.
(此更改写入初始化参数文件,更改将在下次启动时生效。动态参数与静态参数都一样可以。也是静态参数唯一可以使用的方式。)
必须是spfile启动的
数据库才可以啊。不然报错
SCOPE = MEMORY
The change is applied in memory only. The effect is as follows:
■ For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
■ For static parameters, this specification is not allowed.
(只在内存上修改,立即生效,但重启后将不再生效,因为并没有写入到初始化参数文件。只适用于动态参数,静态参数则不允许。)
用于不是spfile启动的数据库上面。下次启动如果用spfile,就失效了。
SCOPE = BOTH
The change is applied in both the server parameter file and memory. The effect is as follows:
■ For dynamic parameters, the effect is immediate and persistent.
■ For static parameters, this specification is not allowed.
(既写入到初始化参数文件,也在内存上修改,立即生效。同样也只适用于动态参数,静态参数则不允许。)
必须是spfile启动的数据库才可以啊。不然报错
文章内容部分引用自:http://space.itpub.net/24873454/viewspace-709729
___________________________________________________________________________________
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Author: laven54 (lurou)
Email: laven54@163.com
Blog: http://blog.csdn.net/laven54
QQ群: 164734649 可以到群里来提问,Oracle相关的问题我都很感兴趣