Oracle: detect a NOT INMEMORY column

Question

I have a table that has been created with a NOT INMEMORY column:

CREATE TABLE myTable (
  foo VARCHAR2(20 BYTE) NOT NULL,
  bar VARCHAR2(20 BYTE) NOT NULL,
  baz VARCHAR2(2000 BYTE) NOT NULL,
);
ALTER TABLE myTable INMEMORY;
ALTER TABLE myTable NO INMEMORY ("baz") ;

What I need to do is identify the NO INMEMORY column by querying the static data dictionary views, but it appears all_tables, all_tab_columns and similar don't carry this information, and I can't find mention of one in the documentation. Is there a view which does?

Answer 1

You can use the ALL_TABLES and V$IM_COLUMN_LEVEL views to get the information. The information was found on Oracle-Base.

SQL> select inmemory from all_tables
  2* where table_name = 'MYTABLE';

   INMEMORY
___________
ENABLED

SQL> SELECT table_name,
  2         segment_column_id,
  3         column_name,
  4         inmemory_compression
  5  FROM   v$im_column_level
  6  WHERE  table_name = 'MYTABLE'
  7* ORDER BY segment_column_id;

   TABLE_NAME    SEGMENT_COLUMN_ID    COLUMN_NAME    INMEMORY_COMPRESSION
_____________ ____________________ ______________ _______________________
MYTABLE                          1 FOO            DEFAULT
MYTABLE                          2 BAR            DEFAULT
MYTABLE                          3 BAZ            NO INMEMORY