In-Memory Column optional

The IM column store is a new static pool in the SGA. Data in the IM column store does not reside in the traditional row format but instead in a columnar format. Each column is stored as a separate structure. The IM column store does not replace the buffer cache, but acts as a supplement, so that data can be stored in memory in both row and columnar formats. To enable the IM column store, the  INMEMORY_SIZE  initialization parameter must be set to a non-zero value.

    IM列存储是SGA中一个新的静态pool,数据在IM列存储中是以柱状存在而不是传统的row模式，每个列作为单独的存储结构。IM 列存储不是代替buffer cache,而是buffer cache的补充，因此数据可以以row 和columnar 两种模式存在内存中，要开启IM column store, INMEMORY_SIZE需设置为非零值。

支持如下级别开启IM column store:

  

• Column

• Table

• Materialized view

• Tablespace

• Partition

如下类型或者操作将数据库对象以IM column store时可以提高性能：

 

• A query that scans a large number of rows and applies filters that use operators such as the following: =, <, >, and IN

    扫描大量数据并通过： =, <, >, 和IN过滤

• A query that selects a small number of columns from a table or materialized view with a large number of columns, such as a query that selects five columns from a table with 100 columns

  从包含大量列的表或者物化视图查询几列

• A query that joins a small table to a large table

     小表与大表关联

• A query that aggregates data

   聚合数据查询

查询数据库中开启了IM column store的对象：

SQL>  SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION    FROM V$IM_SEGMENTS;    

no rows selected

SQL> 

SQL> startup open;

ORACLE instance started.

Total System Global Area  616562688 bytes

Fixed Size     2927384 bytes

Variable Size   457180392 bytes

Database Buffers    46137344 bytes

Redo Buffers     5459968 bytes

In-Memory Area   104857600 bytes

Database mounted.

Database opened.

SQL> show parameter inmemory_size;

NAME      TYPE  VALUE

------------------------------------ ----------- ------------------------------

inmemory_size      big integer 100M

 通过使用下面带有INMEMORY子句的SQL语句开启IMO特性：

CREATE TABLE

ALTER TABLE

CREATE TABLESPACE

ALTER TABLESPACE

CREATE MATERIALIZED VIEW

ALTER MATERIALIZED VIEW

如下情况IMO不能提高性能

Queries with complex predicates

复杂的谓词查询

Queries that select a large number of columns

大量的列查询

Queries that return a large number of rows

返回大量行的查询

Queries with multiple large table joins

多个大表关联

  

IMO特性压缩方式：

Table 6-5 IM Column Store Compression Methods

CREATE/ALTER Syntax	Description
NO MEMCOMPRESS	The data is not compressed. 不压缩
MEMCOMPRESS FOR DML	This method optimizes the data for DML operations and compresses IM column store data the least (excluding NO MEMCOMPRESS). 这种方式优化数据的DML操作和最小的IM列存储压缩
MEMCOMPRESS FOR QUERY LOW	This method results in the best query performance.  这种方式提供最好的查询性能 This method compresses IM column store data more than MEMCOMPRESS FOR DML but less than MEMCOMPRESS FOR QUERY HIGH. 这种方式压缩比比 MEMCOMPRESS FOR DML大，但是比MEMCOMPRESS FOR QUERY HIGH小 This method is the default when the INMEMORY clause is specified without a compression method in a CREATE or ALTER SQL statement or when MEMCOMPRESS FOR QUERY is specified without including either LOWor HIGH. 在create或者alter命令指定INMEMORY并且未指定压缩方式，这是默认的压缩方式。或者是在MEMCOMPRESS FOR QUERY时并未指定LOW或者是HIGH时。
MEMCOMPRESS FOR QUERY HIGH	This method results in excellent query performance. 这种方式提供优秀的查询性能 This method compresses IM column store data more than MEMCOMPRESS FOR QUERY LOW but less than MEMCOMPRESS FOR CAPACITY LOW. 压缩比比 MEMCOMPRESS FOR QUERY LOW大但是比 MEMCOMPRESS FOR CAPACITY LOW小
MEMCOMPRESS FOR CAPACITY LOW	This method results in good query performance. This method compresses IM column store data more than MEMCOMPRESS FOR QUERY HIGH but less than MEMCOMPRESS FOR CAPACITY HIGH. 压缩比比MEMCOMPRESS FOR QUERY HIGH 大，但是比 MEMCOMPRESS FOR CAPACITY HIGH小 This method is the default when MEMCOMPRESS FOR CAPACITY is specified without including either LOW or HIGH. 当指定 MEMCOMPRESS FOR CAPACITY且并未指定 LOW 或者 HIGH时是默认的压缩方式
MEMCOMPRESS FOR CAPACITY HIGH	This method results in fair query performance. 这种方式提供一般的查询性能 This method compresses IM column store data the most. 提供最大的数据压缩

In a SQL statement, the MEMCOMPRESS keyword must be preceded by the INMEMORY keyword.

在SQL语句中 ，inmemory需要放在memcompress前面。

IM Column Store Data Population Options

IMO数据量选项

When you enable a database object for the IM column store, you can either let Oracle Database control when the database object's data is populated in the IM column store (default), or you can specify a priority level that determines the priority of the database object in the population queue. Oracle SQL includes an INMEMORY PRIORITY subclause that provides more control over the queue for population. For example, it might be more important or less important to populate a database object's data before populating the data for other database objects.

  当开启IMO，可以让数据库控制当对象是IMO时，也可以指定优先级来决定数据库对象在数据队列中的优先级，ORACLE SQL 包括 INMEMORY PRIORITY提供了更多的控制数量的队列。例如：可以让重要或者次重要的对象在其他对象之前。

able 6-6 Priority Levels for Populating a Database Object in the IM Column Store

CREATE/ALTER Syntax	Description
PRIORITY NONE	Oracle Database controls when the database object's data is populated in the IM column store. A scan of the database object triggers the population of the object into the IM column store. This is the default level when PRIORITY is not included in the INMEMORYclause.
PRIORITY LOW	The database object's data is populated in the IM column store before database objects with the following priority level: NONE. The database object's data is populated in the IM column store after database objects with the following priority levels: MEDIUM, HIGH, orCRITICAL.
PRIORITY MEDIUM	The database object's data is populated in the IM column store before database objects with the following priority levels: NONE or LOW. The database object's data is populated in the IM column store after database objects with the following priority levels: HIGH or CRITICAL.
PRIORITY HIGH	The database object's data is populated in the IM column store before database objects with the following priority levels: NONE, LOW, or MEDIUM. The database object's data is populated in the IM column store after database objects with the following priority level: CRITICAL.
PRIORITY CRITICAL	The database object's data is populated in the IM column store before database objects with the following priority levels: NONE, LOW, MEDIUM, orHIGH.

IMO特性相关参数：

Initialization Parameter	Description
INMEMORY_SIZE	This initialization parameter sets the size of the IM column store in a database instance. 设置INMEMORY大小 The default value is 0, which means that the IM column store is not used. This initialization parameter must be set to a non-zero value to enable the IM column store. If the parameter is set to a non-zero value, then the minimum setting is 100M. 默认是0，表示不使用IMO特性，开启IMO特性需要设置一个非零值，最小是100M。 In a multitenant environment, the setting for this parameter in the root is the setting for the entire multitenant container database (CDB). This parameter can also be set in each pluggable database (PDB) to limit the maximum size of the IM column store for each PDB. The sum of the PDB values can be less than, equal to, or greater than the CDB value. However, the CDB value is the maximum amount of memory available in the IM column store for the entire CDB, including the root and all of the PDBs. Unless this parameter is specifically set for a PDB, the PDB inherits the CDB value, which means that the PDB can use all of the available IM column store for the CDB. 在多租户环境中，此参数是root在CDB中设置，也可在每个PDB中设置此参数来限制每个PDB中IMO的最大值，PDB中设置大小的总合可以小于，等于，大于CDB的值，但是CDB的值是CDB中IMO的最大值（包括root和所有的PDB），除非PDB指定了继承CDB的参数，指定了继承参数，意味着这个PDB可以使用所有CDB中可用的IMO。
INMEMORY_FORCE	This initialization parameter can enable tables and materialized views for the IM column store or disable all tables and materialized views for the IM column store. Set this parameter to DEFAULT, the default value, to allow the INMEMORY or NO INMEMORY attributes on the individual database objects determine if they will be populated in the IM column store. 设置为DEFAULT Set this parameter to OFF to specify that all tables and materialized views are disabled for the IM column store.  设置为OFF,禁用IMO特性
INMEMORY_CLAUSE_DEFAULT	This initialization parameter enables you to specify a default IM column store clause for new tables and materialized views. 在建表或者视图时开启IMO特性 Leave this parameter unset or set it to an empty string to specify that there is no default IM column store clause for new tables and materialized views. Setting the value of this parameter to NO INMEMORYhas the same effect as setting it to the default value (the empty string). 不设置或者是设置为空字符串表示在建表和视图时不开启IMO。 Set this parameter to a valid INMEMORY clause to specify that the clause is the default for all new tables and materialized views. The clause can include valid clauses for IM column store compression methods and data population options. If the clause starts with INMEMORY, then all new tables and materialized views, including those without an INMEMORY clause, are populated in the IM column store. If the clause omits INMEMORY, then it only applies to new tables and materialized views that are enabled for the IM column store with an INMEMORY clause during creation.
INMEMORY_QUERY	This initialization parameter specifies whether in-memory queries are allowed. Set this parameter to ENABLE, the default value, to allow queries to access database objects populated in the IM column store, or set this parameter to DISABLE to disable access to the database objects populated in the IM column store. 设置是否允许IM查询 默认是：ENABLE ENABLE |  DISABLE
INMEMORY_MAX_POPULATE_SERVERS	This initialization parameter specifies the maximum number of background populate servers to use for IM column store population, so that these servers do not overload the rest of the system. Set this parameter to an appropriate value based on the number of cores in the system. 此参数设置将数据加载到内存的后台进程数
INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT	This initialization parameter limits the maximum number of background populate servers used for IM column store repopulation, as trickle repopulation is designed to use only a small percentage of the populate servers. The value for this parameter is a percentage of theINMEMORY_MAX_POPULATE_SERVERS initialization parameter value. For example, if this parameter is set to 10 andINMEMORY_MAX_POPULATE_SERVERS is set to 10, then on average one core is used for trickle repopulation.
OPTIMIZER_INMEMORY_AWARE	This initialization parameter enables or disables all of the optimizer cost model enhancements for in-memory. Setting the parameter to FALSEcauses the optimizer to ignore the in-memory property of tables during the optimization of SQL statements. 是否开启optimizer 对IM的支持 默认：true TRUE  | FALSE

 

1、创建并开启IMO特性表：

SQL> create table dj.inmem (

  2  id number,

  3  name varchar2(15))

  4  inmemory;

Table created.

SQL> insert into dj.inmem values (1,'test inmemory');

1 row created.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly;

SQL> select * from dj.inmem;

Execution Plan

----------------------------------------------------------

Plan hash value: 3520722930

--------------------------------------------------------------------------------

----

| Id  | Operation    | Name  | Rows  | Bytes | Cost (%CPU)| Time

   |

--------------------------------------------------------------------------------

----

|   0 | SELECT STATEMENT    |    |  1 | 22 |  1   (0)| 00:00:

01 |

|   1 |  TABLE ACCESS INMEMORY FULL| INMEM |  1 | 22 |  1   (0)| 00:00:

01 |

--------------------------------------------------------------------------------

----

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Statistics

----------------------------------------------------------

 13  recursive calls

  0  db block gets

 19  consistent gets

  1  physical reads

  0  redo size

616  bytes sent via SQL*Net to client

551  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL> 

2、开启IMO特性：

SQL> create table dj.ch_inmem(  

  2  id number,

  3  lastname varchar2(15))

  4  ;

Table created.

SQL> alter table dj.ch_inmem inmemory;

Table altered.

3、开启IMO特性并带 for capacity low compress

SQL> alter table dj.ch_inmem inmemory memcompress for capacity low;

Table altered.

4、开启IMO特性并带有 HIGH Data Population Priority：

SQL> alter table dj.ch_inmem inmemory priority high;

Table altered.

5、开启IMO 并且是 FOR CAPACITY HIGH Compression and LOW Data Population Priority

SQL> alter table dj.ch_inmem inmemory memcompress for capacity high priority low;

Table altered.

6、针对表中的部分列开启IMO特性：

SQL> alter table dj.ch_inmem inmemory memcompress for query (name,lastname)

  2  inmemory memcompress for capacity high (address)

  3  no inmemory (tel);

Table altered.

注：针对name和lastname开启了 memcompress for query， address 开启了 memcompress for capacity high  ，tel不开启IMO特性；

  SQL> insert into dj.ch_inmem values (1,'deng','jiang','xss0001',188888888,'12345@qq.com');  

1 row created.

SQL> commit;

Commit complete.

SQL> select tel from dj.ch_inmem;

Execution Plan

----------------------------------------------------------

Plan hash value: 2444740115

------------------------------------------------------------------------------

| Id  | Operation   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    1 |   13 |    2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| CH_INMEM |    1 |   13 |    2   (0)| 00:00:01 |

------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

 38  recursive calls

  0  db block gets

 49  consistent gets

  0  physical reads

  0  redo size

541  bytes sent via SQL*Net to client

551  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  5  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL> select name from dj.ch_inmem;

Execution Plan

----------------------------------------------------------

Plan hash value: 2444740115

----------------------------------------------------------------------------

| Id  | Operation    | Name     | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |       |     1 |     9 |     0 (0)|

|   1 |  TABLE ACCESS INMEMORY FULL| CH_INMEM |     1 |     9 |     |

----------------------------------------------------------------------------

Statistics

----------------------------------------------------------

233  recursive calls

  0  db block gets

159  consistent gets

  0  physical reads

  0  redo size

541  bytes sent via SQL*Net to client

551  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

 16  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL> 

7、禁用表IMO特性：

SQL> alter table dj.inmem no inmemory;

Table altered.

SQL> select * from dj.inmem;

Execution Plan

----------------------------------------------------------

Plan hash value: 3520722930

---------------------------------------------------------------------------

| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time   |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |   | 1 |    17 | 3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| INMEM | 1 |    17 | 3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Statistics

----------------------------------------------------------

  5  recursive calls

  0  db block gets

 15  consistent gets

  0  physical reads

  0  redo size

620  bytes sent via SQL*Net to client

551  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  2  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL> 

8、禁用列上的IMO特性：

SQL> alter table dj.ch_inmem no inmemory (name);

Table altered.

SQL> select name from dj.ch_inmem;

Execution Plan

----------------------------------------------------------

Plan hash value: 2444740115

------------------------------------------------------------------------------

| Id  | Operation   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    1 |    9 |    2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| CH_INMEM |    1 |    9 |    2   (0)| 00:00:01 |

------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

 14  recursive calls

  0  db block gets

 22  consistent gets

  0  physical reads

  0  redo size

541  bytes sent via SQL*Net to client

551  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  2  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL>

9、表空间开启IMO：

SQL> create tablespace test_inmem datafile '/u01/app/oracle/oradata/ora12c/pdb5/test_inmem.dbf' size 10M

  2  default inmemory;

Tablespace created.

SQL> alter user dj  quota unlimited on test_inmem;

User altered.

SQL> create  table dj.test_inmtab (col1 number,col2 varchar2(5)) tablespace test_inmem;

Table created.

SQL> insert into dj.test_inmtab values(1,'123');

1 row created.

SQL> commit;

Commit complete.

SQL> set autot trace

SQL> select * from dj.test_inmtab;

Execution Plan

----------------------------------------------------------

Plan hash value: 230704405

--------------------------------------------------------------------------------

----------

| Id  | Operation    | Name  | Rows  | Bytes | Cost (%CPU)|

Time  |

--------------------------------------------------------------------------------

----------

|   0 | SELECT STATEMENT    |  |     1 |    17 |     1   (0)|

00:00:01 |

|   1 |  TABLE ACCESS INMEMORY FULL| TEST_INMTAB |     1 |    17 |     1   (0)|

00:00:01 |

--------------------------------------------------------------------------------

----------

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Statistics

----------------------------------------------------------

 14  recursive calls

  0  db block gets

 24  consistent gets

  0  physical reads

  0  redo size

608  bytes sent via SQL*Net to client

551  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL> 

10、关闭表空间IMO特性：

SQL> alter tablespace test_inmem default no inmemory;

Tablespace altered.

SQL> create table dj.t_nointab (col1 number);

Table created.

SQL> insert into dj.t_nointab values (1);

1 row created.

SQL> select * from dj.t_nointab;

Execution Plan

----------------------------------------------------------

Plan hash value: 3946618774

-------------------------------------------------------------------------------

| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       |     1 |    13 |     3 (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_NOINTAB |     1 |    13 |     3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

11、alter开启表空间IMO：

SQL> alter tablespace test_inmem  default inmemory

  2  memcompress for capacity high

  3  priority low;

Tablespace altered.

12、物化视图开启和关闭IMO特性：

SQL>  GRANT CREATE MATERIALIZED VIEW TO dj;

SQL> grant create table to dj;

SQL> create materialized view dj.m_t_inmem inmemory as select * from dj.inmem;

Materialized view created.

SQL> alter table dj.inmem no inmemory;

Table altered.

SQL> set autot traceonly

SQL> select * from dj.inmem;

Execution Plan

----------------------------------------------------------

Plan hash value: 3520722930

---------------------------------------------------------------------------

| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time   |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |   | 1 |    17 | 3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| INMEM | 1 |    17 | 3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Statistics

----------------------------------------------------------

  6  recursive calls

  0  db block gets

 16  consistent gets

  0  physical reads

  0  redo size

620  bytes sent via SQL*Net to client

551  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  2  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL> select * from  dj.m_t_inmem;

Execution Plan

----------------------------------------------------------

Plan hash value: 2070012857

--------------------------------------------------------------------------------

-----------

| Id  | Operation       | Name   | Rows  | Bytes | Cost (%CPU)|

 Time   |

--------------------------------------------------------------------------------

-----------

|   0 | SELECT STATEMENT       |    | 1 |    17 | 1   (0)|

 00:00:01 |

|   1 |  MAT_VIEW ACCESS INMEMORY FULL| M_T_INMEM | 1 |    17 | 1   (0)|

 00:00:01 |

--------------------------------------------------------------------------------

SQL> alter materialized view dj.m_t_inmem  inmemory priority high;

Materialized view altered.

SQL> alter materialized view dj.m_t_inmem no inmemory;

Materialized view altered.

SQL> 

13、使用数据汞时开启和关闭IMO特性：

     在使用impdp导入数据时使用 TRANSFORM=INMEMORY:y开启IMO特性，关闭时使用： TRANSFORM=INMEMORY:n,也可以使用 TRANSFORM=INMEMORY_CLAUSE: string  来改写导入对象的IMO子句。

SQL> select table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression from dba_tables where  table_name= 'INMEM'  and owner= 'DJ' ;

TABLE_NAME INMEMORY INMEMORY_PRIORITY    INMEMORY_DISTRI INMEMORY_COMPRESS

--------------- -------- -------------------- --------------- -----------------

INMEM ENABLED  NONE       AUTO       FOR QUERY LOW

[oracle@ora12c ~]$ expdp dj/dj@pdb5  dumpfile=inmem.dmp DIRECTORY=dump  TABLES=dj.inmem

Export: Release 12.1.0.2.0 - Production on Fri Sep 2 10:29:31 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "DJ"."SYS_EXPORT_TABLE_01":  dj/********@pdb5 dumpfile=inmem.dmp DIRECTORY=dump TABLES=dj.inmem 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "DJ"."INMEM"                                5.484 KB       1 rows

Master table "DJ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DJ.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/dump/inmem.dmp

Job "DJ"."SYS_EXPORT_TABLE_01" successfully completed at Fri Sep 2 10:30:53 2016 elapsed 0 00:01:10

SQL> drop table dj.inmem purge;

Table dropped.

[oracle@ora12c ~]$  impdp dj/dj@pdb5 dumpfile=inmem.dmp directory=dump TRANSFORM=inmemory:n

Import: Release 12.1.0.2.0 - Production on Fri Sep 2 10:34:55 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "DJ"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DJ"."SYS_IMPORT_FULL_01":  dj/********@pdb5 dumpfile=inmem.dmp directory=dump TRANSFORM=inmemory:n 

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DJ"."INMEM"                                5.484 KB       1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DJ"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 2 10:35:37 2016 elapsed 0 00:00:36

SQL> select table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression from dba_tables where  table_name='INMEM' and owner='DJ';

TABLE_NAME INMEMORY INMEMORY_PRIORITY    INMEMORY_DISTRI INMEMORY_COMPRESS

--------------- -------- -------------------- --------------- -----------------

INMEM DISABLED

SQL> drop table dj.inmem purge;

Table dropped.

[oracle@ora12c ~]$  impdp dj/dj@pdb5 dumpfile=inmem.dmp directory=dump TRANSFORM=inmemory:y

Import: Release 12.1.0.2.0 - Production on Fri Sep 2 10:37:57 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "DJ"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DJ"."SYS_IMPORT_FULL_01":  dj/********@pdb5 dumpfile=inmem.dmp directory=dump TRANSFORM=inmemory:y 

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DJ"."INMEM"                                5.484 KB       1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DJ"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 2 10:38:37 2016 elapsed 0 00:00:32

SQL> select table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression from dba_tables where  table_name='INMEM' and owner='DJ';

TABLE_NAME INMEMORY INMEMORY_PRIORITY    INMEMORY_DISTRI INMEMORY_COMPRESS

--------------- -------- -------------------- --------------- -----------------

INMEM ENABLED  NONE       AUTO       FOR QUERY LOW

SQL> drop table dj.inmem purge;

Table dropped.

[oracle@ora12c ~]$  impdp dj/dj@pdb5 dumpfile=inmem.dmp directory=dump TRANSFORM=inmemory_clause:\"inmemory memcompress for capacity high priority low\"

Import: Release 12.1.0.2.0 - Production on Fri Sep 2 10:46:40 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "DJ"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DJ"."SYS_IMPORT_FULL_01":  dj/********@pdb5 dumpfile=inmem.dmp directory=dump TRANSFORM=inmemory_clause:"inmemory memcompress for capacity high priority low" 

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DJ"."INMEM"                                5.484 KB       1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DJ"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 2 10:47:21 2016 elapsed 0 00:00:38

 

SQL> select table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression from dba_tables where  table_name='INMEM' and owner='DJ';

TABLE_NAME INMEMORY INMEMORY_PRIORITY    INMEMORY_DISTRI INMEMORY_COMPRESS

--------------- -------- -------------------- --------------- -----------------

INMEM ENABLED  LOW       AUTO       FOR CAPACITY HIGH