# turn on the inmemory option (it’s part of the SGA, so get sure that the SGA is big enought)

alter system set inmemory_size=1G scope=spfile;

One gigabyte seems to be less, but inmemory tables are stored column oriented. so, Oracle uses also compression .. for example i loaded a table with 1.2 Mio Rows (but only 5 columns) and i just needed ~30MB (in)memory.

# create a materialized view which is refreshd every 5 minutes

create materialized view sma_ext_5min_snp
refresh complete start with (sysdate) next (sysdate+5/24/60) with rowid
as select * from sma_ext_5min;

# put the materialized view table into memory
alter table sma_ext_5min_snp inmemory;

# check the inmemory state of the materialized view (table)

SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables WHERE table_name = ‘SMA_EXT_5MIN_SNP’

# access the view so that it will be loaded into memory (default is load on use)

select count(*) from sma_ext_5min_snp;

# check if it is loaded into memory:

select * from v$im_segments