# 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
