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?
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