CNDBA社区
从Oracle 18c开始，支持对外部表的IM列存储。
1. 从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
2 使用sh_sales.csv文件，并通过以下脚本来创建外部表
2.1 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;
n 以用户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
版权声明：本文为博主原创文章，未经博主允许不得转载。
IM 外部表
