In-Memory Database Columnar Technology in a VirtualBox

Refers to train.oracle12.migration

The Problem
You have imported the user OE into the pluggable database PDB1 inside your guest VM. Now you want to test the In-Memory features of Oracle12c.
                                        

click on the pictures to enlarge

Context of this demo

The CDB1 has a pluggable database PDB1, in which we have imported the user OE from the EXAMPLE tablespace of a host database; EXAMPLE was remapped to the USERS tablespace of the PDB1. This was demonstrated in the lesson Datapump between host and guestVM.

The user OE in PDB1

The imported user OE only exists in PDB1, hence we connect

[CDB1] oracle@oracle12c:~

$ sqlplus system/vagrant@pdb1

and execute this query stored in a script pdb_users.sql

SYSTEM@pdb1>@pdb_users

SYSTEM@pdb1>select username, created, account_status from dba_users where common = 'NO';

USERNAME CREATED ACCOUNT_STATUS

OE 15-MAY-15 OPEN

VAGRANT 09-MAY-15 OPEN

Set INMEMORY_SIZE

Default inmemory_size is 0, i.e. columnar mode is disabled. Enabling it by increasing it's value affects the whole container, hence we must do it as SYS in the CDB1:

SYS@CDB1>alter system set inmemory_size = 200M scope = spfile;

System altered.

and bounce the database to take effect:

SYS@CDB1>show sga

Total System Global Area 1593835520 bytes

Fixed Size 2924880 bytes

Variable Size 939527856 bytes

Database Buffers 637534208 bytes

Redo Buffers 13848576 bytes

SYS@CDB1>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@CDB1>startup

ORACLE instance started.

Total System Global Area 1593835520 bytes

Fixed Size 2924880 bytes

Variable Size 1006636720 bytes

Database Buffers 352321536 bytes

Redo Buffers 13848576 bytes

In-Memory Area 218103808 bytes

Database mounted.

Database opened.

Columnar access for table OE.PRODUCT_INFORMATION

SYS@CDB1>alter session set container = pdb1;

SYS@CDB1>alter database open;

INFO: Compress options for columnar mode 

Check for the table - status

SYS@CDB1>select table_name, inmemory_compression,

2 inmemory_priority, inmemory_distribute

3 from dba_tables where owner = 'OE' and

4 table_name = 'PRODUCT_INFORMATION';

TABLE_NAME INMEMORY_COMPRESS INMEMORY INMEMORY_DISTRI

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

PRODUCT_INFORMATION FOR CAPACITY HIGH LOW AUTO

Check for the segment status

SYS@CDB1>select segment_name, bytes/1024 as MB, populate_status

2 from v$im_segments where owner = 'OE'

3 and segment_name = 'PRODUCT_INFORMATION';

SEGMENT_NAME MB POPULATE_

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

PRODUCT_INFORMATION 128 COMPLETED

Check for the colum - status

SYS@CDB1>select column_number, column_name

2 from v$im_col_cu i, dba_objects o, dba_tab_cols c

3 where i.objd = o.data_object_id

4 and o.object_name = c.table_name

5 and i.column_number = c.column_id

6 and o.object_name = 'PRODUCT_INFORMATION'

7 order by i.column_number;

COLUMN_NUMBER COLUMN_NAME

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

1 PRODUCT_ID

2 PRODUCT_NAME

3 PRODUCT_DESCRIPTION

4 CATEGORY_ID

6 WARRANTY_PERIOD

7 SUPPLIER_ID

8 PRODUCT_STATUS

9 LIST_PRICE

10 MIN_PRICE

And that's it