|
|
| Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136776 is a reply to message #136748] |
Sat, 10 September 2005 01:41   |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
U can not change the default block size of the database but Oracle9i allows u to create separate tablespaces with non-default block sizes.
So if it is for some specific purpose then u can create a new tablespace with a non default block size in the same database.
here is the process if u need, suppose i want to create a new tablespace with 16K block size & default is 8K.
| Quote: |
SQL> select name,block_size,resize_state, current_size, buffers from v$buffer_pool;
NAME BLOCK_SIZE RESIZE_STA CURRENT_SIZE BUFFERS
-------------------- ---------- ---------- ------------ ----------
DEFAULT 8192 STATIC 24 6000
SQL> alter system set db_16K_cache_size=24M;
System altered.
SQL> create tablespace tb1_bigblock datafile
2 'c:\tbsp_bigblock.dbf' size 2M blocksize 16K;
Tablespace created.
|
regards,
tarun
|
|
|
|
 Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136969 is a reply to message #136776] |
Mon, 12 September 2005 08:51 |
merovingio
Messages: 4
Registered: September 2005
Location: Santa Cruz - Bolivia
|
Junior Member |
|
|
ok I did this and i got this errors, what am i doing wrong?
| Quote: |
SQL> select name,block_size,resize_state, current_size, buffers from v$buffer_pool;
NAME BLOCK_SIZE RESIZE_STA CURRENT_SIZE BUFFERS
------- ---------- ---------- ------------ --------
DEFAULT 8192 STATIC 88 11011
SQL> alter system set db_16K_cache_size=24M;
alter system set db_16K_cache_size=24M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
|
[Updated on: Mon, 12 September 2005 08:53] Report message to a moderator |
|
|
|
| Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136986 is a reply to message #136748] |
Mon, 12 September 2005 10:27 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
For testing purposes, why don't you try 2 m instead of 24 m and see what happens?
That error message is fairly explicit...You might want to read the concepts guide chapter on oracle memory before implementing anything in production.
|
|
|
|
| Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136989 is a reply to message #136986] |
Mon, 12 September 2005 11:12 |
merovingio
Messages: 4
Registered: September 2005
Location: Santa Cruz - Bolivia
|
Junior Member |
|
|
ok igot the same problem and i did what you tell me:
| Quote: |
SQL> alter system set db_16k_cache_size=2m;
alter system set db_16k_cache_size=2m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
|
ahh and look this :
SQL> show parameter cache;
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_cache_advice string ON
db_cache_size big integer 92274688
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
NAME TYPE VALUE
------------------------------------- ----------- -------------
session_cached_cursors integer 0 |
|
|
|
| Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136991 is a reply to message #136748] |
Mon, 12 September 2005 11:53 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
What version? What is your sga_max_size and sga_target?
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
MYDBA@ORCL >
MYDBA@ORCL >
MYDBA@ORCL > alter system set db_16k_cache_size = 24m;
System altered.
MYDBA@ORCL > show parameter cache;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size big integer 40M
db_16k_cache_size big integer 24M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 4M
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
session_cached_cursors integer 10
MYDBA@ORCL > alter system set db_16k_cache_size=0;
System altered.
MYDBA@ORCL > show parameter cache;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size big integer 64M
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 4M
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
session_cached_cursors integer 10
MYDBA@ORCL >
MYDBA@ORCL > alter system set sga_target=0 scope=memory;
System altered.
MYDBA@ORCL > alter system set db_cache_size=32m scope=memory;
System altered.
MYDBA@ORCL > alter system set db_16k_cache_size=16m scope=memory;
System altered.
MYDBA@ORCL > show parameter cache;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
__db_cache_size big integer 32M
db_16k_cache_size big integer 16M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 32M
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
session_cached_cursors integer 10
MYDBA@ORCL > show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
lock_sga boolean TRUE
pre_page_sga boolean TRUE
sga_max_size big integer 152M
sga_target big integer 0
|
|
|
|
|
|
| Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #137008 is a reply to message #136993] |
Mon, 12 September 2005 14:07 |
merovingio
Messages: 4
Registered: September 2005
Location: Santa Cruz - Bolivia
|
Junior Member |
|
|
ok i did it. the problem was that my SGA_MAX_SIZE was small so i had to encrease it.
i did this:
SQL>ALTER SYSTEM SET SGA_MAX_SIZE=450M;
System altered.
SQL>ALTER SYSTEM SET DB_16K_CACHE_SIZE=[NUMBER][M|K];
System altered.
SQL>create tablespace PRUEBA1 DATAFILE
2 'F:\ORACLE\ORADATA\CENTRAL\PRUEBA1.DBF'SIZE 10M blocksize 16K;
Tablespace created.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
