oracle-databasein-memory-tables

Oracle: detect a NOT INMEMORY column


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?


Solution

  • 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