从Oracle 18c开始，支持对外部表的IM列存储。

从DBA_TABLES生成一个CSV文件

先创建/tmp/data、/tmp/log、/tmp/bad三个目录，CSV文件存在/tmp/data目录下

SET HEAD OFF
SET PAGES 0
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL /tmp/data/sh_sales.csv
SELECT OWNER       || ',' || TABLE_NAME  || ',' || TABLESPACE_NAME || ',' ||
       CLUSTER_NAME    || ',' || STATUS || ',' ||
       NUM_ROWS || ',' || INMEMORY
FROM   dba_tables;
SPOOL OFF

使用sh_sales.csv文件，并通过以下脚本来创建外部表

SYS用户创建DIRECTORY，并赋权限给用户LEI

CREATE OR REPLACE DIRECTORY admin_dat_dir AS '/tmp/data';
CREATE OR REPLACE DIRECTORY admin_log_dir AS '/tmp/log';
CREATE OR REPLACE DIRECTORY admin_bad_dir AS '/tmp/bad';

GRANT READ ON DIRECTORY admin_dat_dir TO lei;
GRANT WRITE ON DIRECTORY admin_log_dir TO lei;
GRANT WRITE ON DIRECTORY admin_bad_dir TO lei;
  • 以用户lei连接，并创建外部表
SQL> CONNECT lei/oracle@cndbapdb
Connected.

CREATE TABLE admin_ext_tables
                   (owner           VARCHAR2(128),
                    table_name       VARCHAR2(128),
                    tablespace_name   VARCHAR2(30),
                    cluster_name     VARCHAR2(128),
                    status          NUMBER,
                    num_rows     NUMBER,
                    inmemory      VARCHAR2(8)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY admin_dat_dir
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile admin_bad_dir:'empxt%a_%p.bad'
         logfile admin_log_dir:'empxt%a_%p.log'
         fields terminated by ','
         missing field values are null
         ( owner, table_name,
           tablespace_name ,
           cluster_name, status, num_rows, inmemory
         )
       )
       LOCATION ('sh_sales.csv')
     )
     REJECT LIMIT UNLIMITED
     INMEMORY;

2.2. 查询ALL_EXTERNAL_TABLES视图验证是否启用了INMEMORY

COL OWNER FORMAT A10
COL TABLE_NAME FORMAT A15
SELECT OWNER, TABLE_NAME,
       INMEMORY, INMEMORY_COMPRESSION
FROM   ALL_EXTERNAL_TABLES
WHERE  TABLE_NAME = 'ADMIN_EXT_TABLES';

OWNER      TABLE_NAME      INMEMORY INMEMORY_COMPRESS
---------- --------------- -------- -----------------
LEI     ADMIN_EXT_TABLES   ENABLED  FOR QUERY LOW