Oracle 10G R2 Make Table resident memory

First check the Oracle parameters:

Sql> Show Parameters SGA;

NAME TYPE VALUE
------------------------------------ ----------- -------
LOCK_SGA Boolean TRUE
PRE_PAGE_SGA Boolean TRUE

If the value of the above two options is false, it needs to be modified to true;

Modified: LOCK_SGA:

Alter system set LOCK_SGA=TRUE Scope=spfile;

Modify PRE_PAGE_SGA:

Alter system set PRE_PAGE_SGA=TRUE Scope=spfile;

Modify Sga_max_size to a suitable memory value based on the actual memory of the server and the size of the memory consumed:

alter system Set sga_max_size = 4096m scope=spfile;

alter system Set Sga_target = 4000m scope = SPFile;

Modify Db_cache_size (modify two parameters to require the actual SGA size, which is larger than the specified memory):

alter system Set db_cache_size = 2048m scope=spfile;

Alter system set DB_KEEP_CACHE_SIZE=1024M Scope=spfile;

Close the database and restart;

Shutdown immediate;

Startup

The above steps allow the table to have the possibility of resident memory:

Executing the statement allows the specified table to enter the resident memory:

ALTER TABLE table_name storage (Buffer_pool keep);
ALTER TABLE TABLE_NAME cache;

The above steps are done to make a table directly resident memory.

Oracle 10G R2 Make Table resident memory