Oracle 18c新特性--IM支援外部表

從Oracle 18c開始，支援對外部表的IM列存儲。

從DBA_TABLES生成一個CSV檔案

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;      

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