the parameteres which most affect the SGA size are :
db_block_size : size of an oracle (or data) block, in bytes
db_block_buffers : number of database buffers, each the size of db_block_size
log_buffer : number of bytes in the redo log buffer
shared_pool_size : self explicit, in bytes
if you do sho sga under SQL+, you'll see the SGA size and what it's made of. you can also look in v$parameter for above parameters and compute your SGA size
Only sga part of the database remains in the RAM.If the RAM of the server is 1gb .Can we have two databases having the same SGA on a single server.
SGA size of the database is as below.
Total System Global Area 965519388 bytes
Fixed Size 75804 bytes
Variable Size 397840384 bytes
Database Buffers 567525376 bytes
Redo Buffers 77824 bytes
wooaaww, you should REALLY decrease db_block_buffers !!!
I dunno how big your instance is and how many connections you have, but having a SGA size which is about the physical RAM size of the server is performance-killing ...
you can perfectly have multiple instances with any SGA size, but the important thing is that the total of the SGA sizes should not be more than about half to 60% maximum of the physical RAM ...
As far as the figures posted by you , your SGA is around 89% of your total RAM. To bring up your 2nd instance, you have to reduce the SGA for your current database to 50% less. Monitor your db's performance before you bring up the 2nd instance. As pipo said - total combined SGA should be 30-40% less then total RAM.
My Database server is having 4GB RAM.I think pipo said that you can have as many instances on the server.
My question now is ,if i am having two databases on one server ,so the combined SGA should be 50 to 60 % or SGA of one database should be 50 to 60 %.
it's the total size.
Oracle says it should be 30 to 40%, but you can go up to 50-60%, nevertheless be aware that NT is totally unable to properly manager memory, so it is advisable not to go over 60% which is already a big percentage.