Oracle 12c Database IN-Memory For Testing

To store tables in In-Memory :- 

SQL> conn hari/hari;
Connected.
SQL> CREATE TABLE inmemory_tab (id NUMBER) INMEMORY;

Table created.

SQL> CREATE TABLE noinmemory_tab (id NUMBER) NO INMEMORY;

Table created.

SQL> CREATE TABLE default_tab (id NUMBER);

Table created.
Table INMEMORY status 

SQL> COLUMN table_name FORMAT A20
select table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate FROM user_tables ORDER BY table_name;

To change the status of table,

SQL> ALTER TABLE INMEMORY_TAB NO INMEMORY;

Table altered.

To store column in In-Memory :- 

SQL> CREATE TABLE inmemory_col_tab (id NUMBER,col1 NUMBER,col2 NUMBER,col3 NUMBER,col4 NUMBER) INMEMORY INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1, col2)INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)NO INMEMORY (id, col4);

Table created.

Table INMEMORY status 

SELECT table_name,segment_column_id,column_name,inmemory_compression FROM   v$im_column_level WHERE  owner = 'HARI' and    table_name = 'inmemory_col_tab' ORDER BY segment_column_id;

To store MV views in In-Memory :- 

SQL> show user
USER is "HARI"
SQL> CREATE TABLE objects AS SELECT * FROM all_objects;

Table created.

SQL> CREATE MATERIALIZED VIEW objects_mv INMEMORY AS SELECT * FROM HARI.objects;

Materialized view created.

SQL> SELECT table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate FROM user_tables WHERE table_name = 'OBJECTS_MV';

TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS  INMEMORY_DUPL
---------- -------- -------- --------------- -----------------             -------------
OBJECTS_MV ENABLED NONE      AUTO            FOR QUERY LOW             NO DUPLICATE

To store Tablespace in In-Memory :- 

The following example shows how to set the IM column store parameters during a tablespace creation.

SQL> create tablespace NEW datafile '/oradb/app/oracle/oradata/ORCLDEMO/datafile/new.dbf' size 300m default inmemory;

Tablespace created.

Check the status of tablespace name,

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                          FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba