Enabling and disabling table for the IM column store in Oracle 12c

In order to Enable or Disable the IM Column Store for Tables, your database must be with the Oracle Database In-Memory (Database In-Memory) option enabled.
If not, see this post : https://www.techdatabasket.com/enabling-oracle-database-in-memory-database-in-memory-option-in-oracle-database-12c-release-1/

According to Oracle, “You enable a table for the IM column store by including an INMEMORY clause in a CREATE TABLE or ALTER TABLE statement. You disable a table for the IM column store by including a NO INMEMORY clause in a CREATE TABLE or ALTER TABLE statement.”.

Then, in this post I am going to show you how to setup the table INMEMORY and the option levels:

— Example about how to create a Table and enable it for the IM column store:

CREATE TABLE techdatabasket (
id        NUMBER(5) PRIMARY KEY,
blog_type  VARCHAR2(15))
INMEMORY;

— There are several priorities and each one belongs to a determined SQL-Key as below:

ALTER TABLE techdatabasket INMEMORY PRIORITY NONE;
ALTER TABLE techdatabasket INMEMORY PRIORITY HIGH;
ALTER TABLE techdatabasket INMEMORY PRIORITY LOW;
ALTER TABLE techdatabasket INMEMORY PRIORITY MEDIUM;
ALTER TABLE techdatabasket INMEMORY PRIORITY CRITICAL;

Each priority determines how Oracle will manage and populate the datas of the object in the IM Column Store.

You can easily check whether your table are IN-MEMORY using the following select:

SQL> SELECT table_name, inmemory_compression, inmemory_priority , inmemory_distribute FROM user_tables;

TECHDATABASKET     FOR QUERY LOW     NONE     AUTO

or

SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION
FROM V$IM_SEGMENTS;

— Disable the TECHDATABASKET for the IM Column Store:

SQL> ALTER TABLE TECHDATABASKET  NO INMEMORY;

Table altered.

brunors

*The views expressed here are my own and do not represent those of my employer.*

Hello, I’m Bruno — a dual citizen of Brazil and Sweden. I bring a global perspective shaped by experiences in both South America and Europe, with a strong focus on collaboration and innovation across cultures. I am a Computer Scientist, PhD Candidate in Information and Communication Technologies, focusing on Data Science and Artificial Intelligence, and hold dual Master’s degrees in Data Science and Cybersecurity. With over fifteen years of international experience spanning Brazil, Hungary, and Sweden, I have collaborated with global organizations such as IBM, Playtech, and Oracle, as well as contributed remotely to projects across multiple regions. My professional interests include Databases, Cybersecurity, Cloud Computing, Data Science, Data Engineering, Big Data, Artificial Intelligence, Programming, and Software Engineering,  all driven by a deep passion for transforming data into strategic business value.