Example 11-1 Designating an IM FastStart Area

This example creates a tablespace and designates it as the FastStart area.

1. In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.

2. Create a tablespace named fs_tbs:

   CREATE TABLESPACE fs_tbs
     DATAFILE 'fs_tbs.dbf' SIZE 500M REUSE
     AUTOEXTEND ON NEXT 500K MAXSIZE 1G;
   

3. Enable IM FastStart, and designate the fs_tbs tablespace as the FastStart area, using the default NOLOGGING option for the FastStart LOB:

   EXEC DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE('fs_tbs');

4. Query the status and size of the FastStart area:

   COL TABLESPACE_NAME FORMAT a15
   
   SELECT TABLESPACE_NAME, STATUS,
          ( (ALLOCATED_SIZE/1024) / 1024 ) AS ALLOC_MB,
          ( (USED_SIZE/1024) / 1024 ) AS USED_MB
   FROM   V$INMEMORY_FASTSTART_AREA;
   
   TABLESPACE_NAME STATUS                 ALLOC_MB    USED_MB
   --------------- -------------------- ---------- ----------
   FS_TBS          ENABLE                      500      .0625

   At this stage, no user data is in the FastStart area.

5. Query the logging mode of the FastStart LOB:

   COL SEGMENT_NAME FORMAT a20
   SELECT SEGMENT_NAME, LOGGING 
   FROM   DBA_LOBS 
   WHERE  TABLESPACE_NAME = 'FS_TBS';
   
   SEGMENT_NAME         LOGGING
   -------------------- -------
   SYSDBIMFS_LOBSEG$    NO
   

6. Force the IM column store to repopulate any currently populated objects.

   The following queries force the repopulation of the sales, products, and customers tables:

   SELECT /*+ FULL(s) NO_PARALLEL(s) */ COUNT(*) FROM sh.sales s;
   SELECT /*+ FULL(p) NO_PARALLEL(p) */ COUNT(*) FROM sh.products p;
   SELECT /*+ FULL(c) NO_PARALLEL(c) */ COUNT(*) FROM sh.customers c;

7. Query the size of the FastStart area:

   COL TABLESPACE_NAME FORMAT a15
   
   SELECT TABLESPACE_NAME, STATUS,
          ( (ALLOCATED_SIZE/1024) / 1024 ) AS ALLOC_MB,
          ( (USED_SIZE/1024) / 1024 ) AS USED_MB
   FROM   V$INMEMORY_FASTSTART_AREA;
   
   TABLESPACE_NAME STATUS                 ALLOC_MB    USED_MB
   --------------- -------------------- ---------- ----------
   FS_TBS          ENABLE                      500       2.25
   

   Now the same query shows that 2.25 MB of the FastStart area has been filled.

Example 11-2 Getting the Name of the Current IM FastStart Tablespace

This example queries the name and status of the FastStart tablespace (sample output included):

COL TABLESPACE_NAME FORMAT a20

SELECT TABLESPACE_NAME, STATUS 
FROM   V$INMEMORY_FASTSTART_AREA;

TABLESPACE_NAME      STATUS
-------------------- ----------
FS_TBS               ENABLE

Example 11-3 Migrating the FastStart Area to a Different Tablespace

This example migrates the IM FastStart area to the new_fs_tbs tablespace.

1. In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.

2. Query the name of the current FastStart tablespace:

   COL TABLESPACE_NAME FORMAT a15
   
   SELECT TABLESPACE_NAME, STATUS
   FROM   V$INMEMORY_FASTSTART_AREA;
   
   TABLESPACE_NAME STATUS
   --------------- -----------
   FS_TBS          ENABLE
   

3. Create a tablespace named new_fs_tbs:

   CREATE TABLESPACE new_fs_tbs
     DATAFILE 'new_fs_tbs.dbf' SIZE 500M REUSE
     AUTOEXTEND ON NEXT 500K MAXSIZE 1G;
   

4. Migrate the FastStart area to the new tablespace:

   EXEC DBMS_INMEMORY_ADMIN.FASTSTART_MIGRATE_STORAGE('new_fs_tbs');

5. Query the name of the current FastStart tablespace:

   TABLESPACE_NAME      STATUS
   -------------------- --------------------
   NEW_FS_TBS           ENABLE

Example 11-4 Disabling IM FastStart

This example disables the IM FastStart area, and then drops the fs_tbs tablespace.

1. In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.

2. Query the status of the FastStart area:

   COL TABLESPACE_NAME FORMAT a15
   
   SELECT TABLESPACE_NAME, STATUS
   FROM   V$INMEMORY_FASTSTART_AREA;
   
   TABLESPACE_NAME STATUS
   --------------- -----------
   FS_TBS          ENABLE
   

3. Disable the FastStart area:

   EXEC DBMS_INMEMORY_ADMIN.FASTSTART_DISABLE;

4. Query the status of the FastStart area:

   SELECT TABLESPACE_NAME, STATUS
   FROM   V$INMEMORY_FASTSTART_AREA;
   
   TABLESPACE_NAME      STATUS
   -------------------- --------------------
   INVALID_TABLESPACE   DISABLE

   When IM FastStart is not enabled, the value of TABLESPACE_NAME is INVALID_TABLESPACE and the value of STATUS is DISABLE.

5. Drop the former FastStart tablespace:

   DROP TABLESPACE fs_tbs INCLUDING CONTENTS AND DATAFILES;