Skip to content
≡

Administrator

Developer

Tools

External Tools

CREATE DISK TABLESPACE

CREATE DISK TABLESPACE#

Syntax#

create_disk_tablespace ::=#

create_disk_tablespace_image137

datafile_spec ::=#

datafile_spec

autoextend_clause ::=#

autoextend_clause

maxsize_clause ::=#

maxsize_clause

Prerequisites#

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

Description#

The CREATE DISK TABLESPACE statement is used to create a disk tablespace, in which database objects can be permanently stored within the database. Tablespaces created using this command can be used to hold tables and indexes.

DISK#

This keyword is used to specify that the tablespace to be created will be a disk tablespace. A disk tablespace is created even when the CREATE TABLESPACE statement is executed without the DISK keyword.

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 be created. Refer to "Rules for Object Names" in Chapter 2 for more information on specifying names.

datafile_spec#

This is used to specify the attributes of the data file constituting the tablespace.

EXTENTSIZE Clause#

This is used to specify the size of an extent, which is a collection of pages. This cannot be changed after the tablespace is created. The default unit used to specify the size of an extent is kB (kilobytes, expressed as "K"), but it is also permissible to use MB (megabytes, expressed as "M") or GB (gigabytes, expressed as "G").

If size of the extents in the tablespace is not specified, the extents will be the default size, which is 64 times the size of a single page. When specifying the size of the extents in the tablespace, the extent size must be set to a multiple of the size of a single page. If the extent size is set to a value other than a multiple of the page size, the extent size will be rounded to the closest value internally so that it is a multiple of the page size.

Additionally, the size of an extent must be at least five times the size of a single page. In other words, because the size of a single page is 8 kB, the extent size must be set to at least 40 kB.

SEGMENT MANAGEMENT Clause#

This is used to specify how segments are to be managed in the disk tablespace to be created. This clause is optional. If this option is not specified, segments in the newly created disk space will be managed according to the setting of the DEFAULT_SEGMENT_MANAGEMENT_TYPE property in the altibase.properties file. (The default value for this property is AUTO.)

MANUAL#

This specifies that segments are created on the basis of a so-called "free list" method of managing available space in the user tablespace.

AUTO#

This specifies that segments are created on the basis of a so-called "bitmap index" method of managing available space in the user tablespace.

datafile_spec#

file_name#

This is used to specify the absolute path and name of the data file to be created.

SIZE Clause#

This is used to specify the size of the data file. If this clause is omitted, the data file will be the default size, which is 100 megabytes. This default file size can be changed by setting the USER_DATA_FILE_INIT_SIZE property as desired.

The size is specified by providing an integer followed by one of the following units: 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.

REUSE#

This is used to specify whether or not to reuse an existing data file. If a file with the name specified in file_name exists, the REUSE option must be specified. Note however that if an existing file is reused, the original contents of the file will be lost, so care must be taken in order to prevent data loss.
If the REUSE option is specified but no file with the name specified in file_name exists, this option will be ignored, and a new file will be created.

autoextend_clause#

This is used to specify whether to automatically increase the size of the data file 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 for the file.

OFF#

This disables the AUTOEXTEND option for the file.

NEXT#

This is used to specify the amount by which the size of the file will increase when it is automatically increased in size.

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

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 the data file can increase. If AUTOEXTEND is enabled but this value is not set, the default is the value set using the USER_DATA_FILE_MAX_SIZE property in the altibase.properties file.

It can be expressed 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 file can increase. If this option is used, the actual maximum size of the file will be determined by the operating system or by the amount of available space in the file system.

Examples#

<Query> Create the user_data tablespace, comprising three data files. Specify that segments are to be managed using the "free list" method.

iSQL> CREATE TABLESPACE user_data
    DATAFILE '/tmp/tbs1.user' SIZE 10M,
    '/tmp/tbs2.user' SIZE 10M,
    '/tmp/tbs3.user' SIZE 10M
    SEGMENT MANAGEMENT MANUAL;
Create success.

<Query> Create the user_data tablespace, which has an initial size of 10 MB, consists of the tbs.user data file (in which the tables and indexes in this tablespace will be stored), and extends automatically.

iSQL> CREATE TABLESPACE user_data DATAFILE '/tmp/tbs.user' SIZE 10M AUTOEXTEND
ON;
Create success.

<Query> Create the user_data tablespace, which can increase in size up to 100 MB in 500 kB increments.

iSQL> CREATE TABLESPACE user_data
    DATAFILE '/tmp/tbs.user' SIZE 500K REUSE
    AUTOEXTEND ON NEXT 500K MAXSIZE 100M;
Create success.

<Query> Create the user_data tablespace comprising the tbs.user data file, which does not automatically increase in size.

iSQL> CREATE TABLESPACE user_data
    DATAFILE '/tmp/tbs.user' AUTOEXTEND OFF;
Create success.