Skip to content
≡

Administrator

Developer

Tools

External Tools

CREATE MEMORY TABLESPACE

CREATE MEMORY TABLESPACE#

Syntax#

create_memory_tablespace ::=#

create_memory_tablespace_image140

initsize_clause ::=, autoextend_clause ::=, checkpoint_path_clause ::=, splitsize_clause ::=

initsize_clause ::=#

initsize_clause_image141

autoextend_clause ::=#

autoextend_clause

maxsize_clause ::=#

maxsize_clause

checkpoint_path_clause ::=#

checkpoint_path_clause_image143

splitsize_clause ::=#

splitsize_clause_image144

Prerequisites#

Only the SYS user and users having the CREATE TABLESPACE system privilege can create tablespaces.

Description#

The CREATE MEMORY TABLESPACE statement is used to create a memory data tablespace, in which database objects can be stored within the database. Tablespaces created using this command can be used to hold memory tables.

MEMORY#

This keyword is used to specify that the tablespace to be created will be a memory tablespace.

DATA#

This keyword is used to specify that the tablespace to be created will be used to store user data. A data tablespace is created even when the CREATE TABLESPACE statement is executed without the DATA keyword.

tablespace_name#

This is used to specify the name of the tablespace to create. Refer to "Rules for Object Names" in Chapter 2 for more information on specifying names.

initsize_clause#

This is used to specify the initial size of the tablespace to create.

SIZE#

This is used to specify the initial size of the tablespace. The initial size of a memory tablespace must be a multiple of the default allocation size. (i.e. the number of pages specified in the EXPAND_CHUNK_PAGE_COUNT property * the size of one page in memory tablespace (32 KB )).

For example, if the EXPAND_CHUNK_PAGE_COUNT property is set to 128, the default allocation size would be 128 * 32 = 4 MB. Therefore, the initial size must be set to a multiple of 4 MB.

The size can be specified in kB (kilobytes, expressed as "K"), MB (megabytes, expressed as "M") or GB (gigabytes, expressed as "G"). If no units are specified, the default unit is kilobytes.

autoextend_clause#

This is used to specify whether the tablespace automatically increases in size when it fills up, and the maximum size to which it can increase. If this clause is omitted, AUTOEXTEND is disabled by default.

ON#

This enables the AUTOEXTEND option.

OFF#

This disables the AUTOEXTEND option. This is the default.

NEXT#

This is used to specify the increment by which the tablespace increases in size when it is automatically increased in size.

Note that this size must be a multiple of the default allocation size (the number of pages specified in the EXPAND_CHUNK_PAGE_COUNT property * the size of one page in memory tablespace (32 KB)).

If AUTOEXTEND is enabled but this value is not set, the default is the value set using the EXPAND_CHUNK_PAGE_COUNT property in the altibase.properties file

If AUTOEXTEND is OFF, this value is irrelevant.

The size can be specified in KB(kilobytes, expressed as "K"), MB (megabytes, expressed as "M") or GB (gigabytes, expressed as "G"). If no units are specified, the default unit is kilobytes.

maxsize_clause#

This is used to specify the maximum size to which a tablespace can increase when it automatically increases in size. If AUTOEXTEND is enabled but this value is not set, the default value is UNLIMITED.

If AUTOEXTEND is OFF, this value is meaningless.

The size can be specified in KB (kilobytes, expressed as "K"), MB (megabytes, expressed as "M") or GB (gigabytes, expressed as "G"). If no units are specified, the default unit is kilobytes.

UNLIMITED#

This is used to indicate that there is no upper limit to the size to which the tablespace can increase.

If this option is used, the tablespace will automatically increase in size up to the point at which the total size of all memory tablespaces and all volatile tablespaces in the system reaches the size specified in the MEM_MAX_DB_SIZE property in the altibase.properties file.

checkpoint_path_clause#

To ensure the durability of the data in memory tablespaces, the data must be saved in files. These memory tablespace data storage files are known as "checkpoint images".

The checkpoint_path clause is used to specify the checkpoint path, that is, the path and directory where these image files are stored.

If no checkpoint path is provided, the path specified in the MEM_DB_DIR property is used as the default path.

checkpoint_path#

This is used to specify the location at which a checkpoint image is stored when checkpointing is performed for the memory tablespace. It is permissible to specify more than one path, which is helpful in distributing the disk I/O costs incurred when checkpointing is performed and when the contents of tablespaces are read from disk at startup.

split_each_clause#

This clause is used to split checkpoint files into smaller files. This is useful when the size of the memory tablespace exceeds the maximum file size supported by the operating system, or in order to distribute I/O costs. The size of the resulting files can be specified by the user. If this size is not specified, the default split size specified in the DEFAULT_MEM_DB_FILE_SIZE property is used.

The size can be specified in KB(kilobytes, expressed as "K"), MB (megabytes, expressed as "M") or GB (gigabytes, expressed as "G"). If no units are specified, the default unit is kilobytes.

Examples#

<Query 1> Create a user-defined memory data tablespace that is initially 512 MB in size and does not automatically increase in size. (The checkpoint image is stored in the path specified in the MEM_DB_DIR property. If the checkpoint image file is split into multiple files, the size of those files will be the same as the value set in the DEFAULT_MEM_DB_FILE_SIZE property.)

iSQL> CREATE MEMORY DATA TABLESPACE user_data SIZE 512M;
Create success.

<Query 2> Create a user-defined memory data tablespace that is initially 512 MB in size and increases in size in 128 MB increments1 (The checkpoint image is stored in the path specified in the MEM_DB_DIR property. If the checkpoint image file is split into multiple files, the size of those files will be the same as the value set in the DEFAULT_MEM_DB_FILE_SIZE property.)

iSQL> CREATE MEMORY DATA TABLESPACE user_data 
SIZE 512M 
AUTOEXTEND ON NEXT 128M;
Create success.

<Query 3> Create a user-defined memory data tablespace that is initially 512 MB in size and increases in size in 128 MB increments up to a maximum size of 1 GB. (The checkpoint image is stored in 3 directories, and the size of each of the checkpoint image files is 256 MB).

iSQL> CREATE MEMORY DATA TABLESPACE user_data 
SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE 1G 
CHECKPOINT PATH '/dbs/path1', '/dbs/path2', '/dbs/path3' 
SPLIT EACH 256M;
Create success.

  1. If the maximum size of the tablespace is not specified using the MAXSIZE clause, it defaults to UNLIMITED. In this case, the tablespace can increase in size as long as the combined size of all of the memory tablespaces and volatile tablespaces that exist in the system does not exceed the amount of memory specified in the MEM_MAX_DB_SIZE property. ↩