Skip to content
≡

Administrator

Developer

Tools

External Tools

Managing Tablespaces

Managing Tablespaces#

This section describes how to manage tablespaces in Altibase.

CREATE#

A tablespace can be created only by the SYS user or by a user to whom the Create Tablespace authority has been granted. To create a tablespace, use the CREATE TABLESPACE … SQL statement. Only user-defined data tablespaces can be created. That is, system tablespaces cannot be created by the user.

Disk tablespaces are classified as either disk data tablespaces or disk temporary tablespaces.

Memory tablespaces are memory data tablespaces. There is no such thing as a "memory temporary tablespace".

Similarly, all volatile tablespaces are volatile data tablespaces. There are no "volatile temporary tablespaces".

The SQL statement that is used to create tablespaces is described below:

CREATE [DISK/MEMORY/VOLATILE] [DATA/TEMPORARY] TABLESPACE   
(1) Tablespace Name
(2) Disk Data File Attributes
(3) Disk Temporary File Attributes
(4) Memory Tablespace Attribute
(5) Volatile Tablespace Attribute;

When determining whether to create a memory, disk, or volatile tablespace, the user should consider the characteristics of the objects to be stored in the tablespace, such as their size and how often it is expected that they will be accessed.

The tablespace attributes that can be specified when a tablespace is created vary depending on whether the tablespace is a disk, memory, or volatile tablespace. Unlike a disk tablespace, in which multiple data files are managed, in a memory tablespace, the objects are stored in single continuous memory space. Therefore, when a disk tablespace is created, some of the attributes that are specified apply to individual data files, whereas when a memory tablespace is created, all of the attributes apply to the entire memory tablespace. In other words, attributes such as the initial size and the size to which the tablespace can expand are set for a memory tablespace, whereas the attributes that are set for a disk tablespace apply to its data files.

Tablespace Name#

The tablespace name must be unique. No more than one object of the same name can be created. While the names of data files can be set in a disk tablespace, for a memory tablespace, only the path where the checkpoint image will be stored can be specified. The name of the checkpoint image is automatically generated based on the name of a tablespace.

Disk Data File Attributes#

Data file attributes can only be set for disk data tablespaces. The DATAFILE clause has the following form: 

DATAFILE    [(1) DATAFILE Clause
AUTOEXTEND  [(2) AUTOEXTEND CLAUSE 
MAXSIZE     [(3) MAXSIZE Clause] ] ] 
EXTENTSIZE  [(4) EXTENTSIZE Clause]

Each data file can have the following attributes:

DATAFILE Clause#
DATAFILE {datafile path and name} SIZE integer [K/M/G] [REUSE]

This is used to specify the data file path and name. The SIZE clause and the REUSE clause can be omitted. The SIZE clause is used to specify the initial size of a data file when it is created. Each data file includes a file header. SIZE is the total size of all pages, excluding the page for the file header (1 page). As a result, the specified initial size of a data file and its actual size are not the same. If the maximum file size supported by the operating system is smaller than the specified initial size, an error will be returned.

AUTOEXTEND Clause#
AUTOEXTEND [{ON NEXT integer [K/M/G]}/{OFF}]

This attribute determines whether a disk data file will increase in size. If it is set to ON, the size of the data file is automatically increased by the system. If it is set to OFF, the user must explicitly increase the file size. The increment by which the temporary file is extended can be specified by the user in the NEXT clause.

When a data file is being extended, that is, when it is being increased in size, all operations that are underway in the tablespace to which the data file belongs must wait until the operation to increase the size of the data file is complete.

MAXSIZE Clause#
MAXSIZE {{integer [K/M/G]}/{UNLIMITED}}

This clause is a subclause of the AUTOEXTEND clause and indicates the maximum size to which the data file can be increased. Like the initial size, if the maximum file size supported by the operating system is smaller than the maximum size specified for a data file, the maximum size is set to the maximum file size of the operating system. If MAXSIZE is set to UNLIMITED, the data file is increased in size until all of the available disk space has been used up.

EXTENTSIZE Clause#
EXTENTSIZE {{integer [K/M/G]}/{UNLIMITED}}

This clause defines the size of an extent, that is, (the size of a page) * (the number of pages that are allocated to a table segment or index segment in disk tablespace at one time). If the extent size is not specified, it defaults to 512kB (64 pages).

Disk Temporary File Attributes#

Temporary file attributes can only be set for disk temporary tablespaces. The TEMPFILE clause has the following form:

TEMPFILE    {(1) TEMPFILE Clause}
AUTOEXTEND  [(2) AUTOEXTEND Clause
MAXSIZE     [(3) MAXSIZE Clause] ]
EXTENTSIZE  [(4) EXTENTSIZE Clause]

Each temporary file can have the following attributes:

TEMPFILE Clause#
TEMPFILE {datafile path and name} SIZE integer [K/M/G] [REUSE]

This specifies the path and name of a temporary file. The SIZE clause and the REUSE clause can be omitted. The SIZE clause is used to specify the initial size of a temporary file when it is created. Each temporary file includes a file header. SIZE is the total size of all pages, excluding the page for the file header (1 page). As a result, the specified initial size of a temporary file and its actual size are not the same. If the maximum file size supported by an operating system is smaller than the specified initial size, an error will be returned.

AUTOEXTEND Clause#
AUTOEXTEND [{ON NEXT integer [K/M/G]}/{OFF}]

This attribute determines whether a disk temporary file will increase in size. If it is set to ON, the size of the data file is automatically increased by the system. If it is set to OFF, the user must explicitly increase the file size. The increment by which the data file is extended can be specified by the user in the NEXT clause.

MAXSIZE Clause#
MAXSIZE {{integer [K/M/G]}/{UNLIMITED}}

This clause is a subclause of the AUTOEXTEND clause and indicates the maximum size to which the temporary file can be increased. Like the initial size, if the maximum file size supported by the operating system is smaller than the maximum size specified for a temporary file, the maximum size is set to the maximum file size of the operating system. If MAXSIZE is set to UNLIMITED, the temporary file is increased in size until all of the available disk space has been used up.

EXTENTSIZE Clause#
EXTENTSIZE integer [K/M/G]

This clause defines the size of an extent, that is, (the size of a page) * (the number of pages that are allocated to a table segment or index segment in temporary tablespace at one time). If the extent size is not specified, it defaults to 256KB (32 pages).

Memory Tablespace Attributes#

The attributes for memory tablespace are similar to those for disk tablespaces, but additionally include a checkpoint image path attribute. Their syntax is as follows:

SIZE            {(1) SIZE Clause} 
AUTOEXTEND      [(2) AUTOEXTEND Clause 
MAXSIZE         [(3) MAXSIZE Clause] ]
CHECKPOINT PATH [(4) CHECKPOINT PATH]

Memory tablespaces can have the following attributes:

SIZE Clause#
SIZE integer [K/M/G]

This is the amount of memory that must be initially allocated when a memory tablespace is created. This value must be a multiple of the default extension increment size for memory tablespaces. (This increment size is equal to the number of page(s) specified in the EXPAND_CHUNK_PAGE_COUNT property multiplied by the size of a memory tablespace page(32KB).1)

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

AUTOEXTEND Clause#
AUTOEXTEND [{ON NEXT integer [K/M/G]}/{OFF}]

This determines whether the size of memory tablespace will be increased automatically. If it is set to ON, the tablespace is automatically increased in size by the system, whereas if it is set to OFF, the user must explicitly increase the size of the tablespace. The extension increment size, that is, the amount by which the size is increased, can be specified by the user. The NEXT clause indicates the extension increment size.

Like the initial size, the extension size must be set to a multiple of the page size specified in the EXPAND_CHUNK_PAGE_COUNT property.

If the automatic extension size is too small, the automatic extension can occur too often. When Altibase performs automatic extension, it adds up the size of all memory tablespaces and compares the total size with the size specified in the MEM_MAX_DB_SIZE property. Frequently performing these operations can degrade system performance.

MAXSIZE Clause#
MAXSIZE {{integer [K/M/G]}/{UNLIMITED}}

This is a subclause of the AUTOEXTEND clause and indicates the maximum size to which a memory tablespace can be extended. Like the initial size, it cannot exceed the amount of memory space available in the system. If it is set to UNLIMITED, the tablespace is automatically increased in size until the total size of all memory tablespaces in the system reaches the limit specified in the MEM_MAX_DB_SIZE property.

CHECKPOINT PATH#
CHECKPOINT PATH 'Checkpoint Image Path List'
SPLIT EACH integer [K/M/G]

The checkpoint image path attribute only applies to memory tablespaces. Altibase uses ping-pong checkpointing for high-performance transaction processing in memory tablespaces. For ping-pong checkpointing, at least two sets of checkpoint images are created on disk. Each checkpoint image can be divided into several files and saved in that form. The size of the files into which the checkpoint image is divided can be specified using the SPLIT EACH clause. These files can be stored in different paths in order to distribute the expense of disk I/O. The user can freely specify the size of the files into which the checkpoint image is divided and the path where the checkpoint images are saved. The user can add or change paths for saving checkpoint image files, but cannot change the size of the files into which the checkpoint image is divided once it has been set.

Volatile Tablespace Attributes#

The attributes that are applicable to volatile tablespaces are similar to those for memory tablespaces, with the exception that the checkpoint image path attribute is not supported.

SIZE        {(1) SIZE Clause} 
AUTOEXTEND  [(2) AUTOEXTEND Clause 
MAXSIZE     [(3) MAXSIZE Clause] ]

Volatile tablespaces can have the following attributes:

SIZE Clause#
SIZE  integer [K/M/G]

This specifies the initial memory size that is allocated when a volatile tablespace is created. This value must be a multiple of the default extension increment size for memory tablespaces. (This increment size is equal to the number of page(s) specified in the EXPAND_CHUNK_PAGE_COUNT property multiplied by the size of a memory tablespace page (32KB).2)

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

AUTOEXTEND Clause#
AUTOEXTEND [{ON NEXT integer [K/M/G]}/{OFF}]

This determines whether the size of volatile tablespace will be increased automatically. If it is set to ON, the tablespace is automatically increased in size by the system, whereas if it is set to OFF, the user must explicitly increase the size of the tablespace. The extension increment size, that is, the amount by which the size is increased, can be specified by the user in The NEXT clause.

Like the initial size, the extension size must be set to a multiple of the page size specified in the EXPAND_CHUNK_PAGE_COUNT property.

If the automatic extension size is too small, automatic extension can occur too often. When Altibase performs automatic extension, it adds up the size of all volatile tablespaces and compares the total size with the size specified in the VOLATILE_MAX_DB_SIZE property. Frequently performing these operations can degrade the system performance.

MAXSIZE Clause#
MAXSIZE {{integer [K/M/G]}/{UNLIMITED}}

This is a subclause of the AUTOEXTEND clause and indicates the maximum size to which a volatile tablespace can be extended. Like the initial size, it cannot exceed the memory space available in the system. If it is set to UNLIMITED, the tablespace is automatically increased in size until the total size of all memory tablespaces in the system reaches the limit specified in the VOLATILE_MAX_DB_SIZE property.

Examples#

Example: To create a disk data tablespace comprising 3 data files.

CREATE DISK DATA TABLESPACE user_data DATAFILE
'/tmp/tbs1.user' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 1G,
'/tmp/tbs2.user' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 500M,
'/tmp/tbs3.user' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 1G;

Example: To create a memory data tablespace.

CREATE MEMORY DATA TABLESPACE user_data SIZE 12M
AUTOEXTEND ON  NEXT 4M MAXSIZE 500M
CHECKPOINT PATH '/tmp/checkpoint_image_path1',  
                '/tmp/checkpoint_image_path2' SPLIT EACH 12M;

Example: To create a volatile data tablespace.

CREATE VOLATILE DATA TABLESPACE user_data SIZE 12M
AUTOEXTEND ON NEXT 4M MAXSIZE 500M;

Dropping Tablespaces#

A tablespace can be deleted only by the SYS user or by a user who has been granted the DROP TABLESPACE privilege. To delete a tablespace, use the 'DROP TABLESPACE …' SQL statement. System tablespaces cannot be deleted by general users. Memory, disk, and volatile tablespaces are all deleted the same way, using the following command:

DROP TABLESPACE {Tablespace Name} 
        [{⑴ INCLUDING CONTENTS} [⑵ AND DATAFILES] 
        [⑶ CASCADE CONSTRAINTS]];

The tablespace to be deleted is identified by name. The available options are described below. If the following options are not specified, the only thing that is deleted from the log anchor is the tablespace schema.

INCLUDING CONTENTS Clause#

INCLUDING CONTENTS

This is used to specify that the objects (that is, the tables and indexes) in the tablespace are also to be deleted. If any objects are present in the tablespace, this option must be set, otherwise the DROP TABLESPACE operation will fail.

AND DATAFILES Clause#

INCLUDING CONTENTS AND DATAFILES

Specifying the INCLUDING CONTENTS clause deletes the records and keys of an object, but not the data files themselves. Therefore, in order to delete the data files, the AND DATAFILES clause must also be used. The AND DATAFILES clause is a subclause of the INCLUDING CONTENTS clause. If it is used, when dropping a disk tablespace, all of the data files in the tablespace are physically deleted.

If it is specified when dropping a memory tablespace, all of the checkpoint image files for the memory tablespace are physically deleted.

CASCADE CONSTRAINTS Clause#

INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS

This is also a subclause of the INCLUDING CONTENTS clause. If an attempt is made to drop a tablespace when there are constraints in other tablespaces that refer to objects in the tablespace to be dropped, the drop operation will fail, and an error indicating that objects remain in the tablespace will be raised. In this case, the CASCADE CONSTRAINTS clause should be used to delete all external references to objects in the tablespace additionally.

Modifying Tablespaces (ALTER)#

A tablespace can be modified only by the SYS user or by a user to whom the Alter Tablespace authority has been granted. Tablespaces are modified using the 'ALTER TABLESPACE ...' SQL statement. This command can be used to change the definition of an existing tablespace, the attributes of one or more data files or temporary files, or the attributes of memory or volatile tablespace. The related SQL syntax is as follows: 

ALTER TABLESPACE {Tablespace Name} 
                 {{(1) ALTER Disk Data File Clause}/ 
                 {(2) ALTER Temporary File Clause}/
                 {(3) ALTER Memory Tablespace Clause}/
                 {(4) ALTER Volatile Tablespace Clause}/
                 {(5) ALTER Tablespace State Clause}};

ALTER Disk Data File Clause#

This clause can be used on a disk system tablespace or a disk data tablespace, and has the following options:

ALTER TABLESPACE {Tablespace Name} 
                 {(1) ADD Data File Clause/
                  (2) DROP Data File Clause/
                  (3) ALTER Data File Size Clause/
                  (4) RENAME Data File Clause}
ADD Data File Clause#
ADD {DATAFILE} {Data File Clause} 
    AUTOEXTEND [AUTOEXTEND Clause 
    MAXSIZE [MAXSIZE Clause]]

This clause is used to increase the amount of data storage space in a disk tablespace. The available options are the same as the data file options for the CREATE TABLESPACE statement.

DROP Data File Clause#
DROP {DATAFILE} {Data File Name}

This is used to reduce the amount of data storage space for a disk tablespace. While the data storage space can be freely increased by adding more data files, a data file can be deleted only when it is not in use, that is, when no extents have been allocated to the data file.

ALTER Data File Size Clause#
ALTER {DATAFILE} {Data File Name} 
      {{AUTOEXTEND [AUTOEXTEND Clause]} /
      {SIZE [SIZE Clause]}}

This is used to alter the current size, maximum size, extension increment size, and whether to auto-extend each data file in the disk tablespace.

The specified current size and maximum size must be greater than the amount that is currently being used.

RENAME Data File Clause#
RENAME {DATAFILE} {The path and name of the existing data file} 
    TO {The path and name of a new data file}

This is used to change the location of a data file. This clause can be used in any startup phase, regardless of whether the applicable tablespace is online or offline. However, it can only be used with offline tablespaces in the service phase.

ALTER Temporary File Clause#

This can be used only with disk temporary tablespaces. It has the following options:

ALTER TABLESPACE {Tablespace Name} 
     {(1) ADD Temporary File Clause/
      (2) DROP Temporary File Clause/
      (3) ALTER Temporary File Size Clause/
      (4) RENAME Temporary File Clause}
ADD Temporary File Clause#
ADD {TEMPFILE} {Temporary File Clause} 
AUTOEXTEND  [AUTOEXTEND Clause
MAXSIZE     [MAXSIZE Clause]]

This is used to extend the data storage space in a disk temporary tablespace. The available options are the same as the temporary file options that are available when a disk temporary tablespace is created.

DROP Temporary File Clause#
DROP {TEMPFILE} {Temporary File Name}

This is used to reduce the amount of data storage space in a disk temporary tablespace. While the data storage space can be freely extended by adding more data files, a data file can be deleted only when it is not in use, that is, when no extents have been allocated to the data file.

ALTER Temporary File Size Clause#
ALTER {TEMPFILE} {Temporary File Name} 
      {{AUTOEXTEND [AUTOEXTEND Clause]} /
      {SIZE [SIZE Clause]}}

This is used to change the current size, maximum size, extension increment size, and whether to expand each temporary file in the disk temporary tablespace automatically.

RENAME Temporary File Clause#
RENAME {TEMPFILE} {The path and name of the existing temporary file} 
    TO {The path and name of a new temporary file}

This command can be used to change the location of a data file. This clause can be used in any startup phase, regardless of whether the applicable tablespace is online or offline. However, it can only be used with offline tablespaces in the service phase.

ALTER Memory Tablespace Clause#

This can be used with a system or user-defined tablespaces in memory and has the following options. Checkpoint paths can be added, deleted or changed during any startup phase. However, during the service phase, only tablespaces that are offline can be modified.

ALTER TABLESPACE {Tablespace Name} 
     {(1) ADD Checkpoint Path Clause/
      (2) DROP Checkpoint Path Clause/
      (3) RENAME Checkpoint Path Clause/
      (4) ALTER Tablespace Size Clause}
ADD Checkpoint Path Clause#
ADD CHECKPOINT PATH {Directory Path}

This is used to set an additional checkpoint image path.

DROP Checkpoint Path Clause#
DROP CHECKPOINT PATH {Directory Path}

This is used to delete an existing checkpoint image path.

RENAME Checkpoint Path Clause#
RENAME CHECKPOINT PATH {The existing directory path} 
    TO {A new directory path}

This is used to change an existing checkpoint image path to a new path.

ALTER Tablespace Size Clause#
ALTER 
      {{AUTOEXTEND [AUTOEXTEND Clause]} /
      {SIZE [SIZE Clause]}}

This is used to change the attributes of a memory tablespace, such as its maximum size, extension increment size, and whether it is automatically increased in size.

ALTER Volatile Tablespace Clause#

This is used with volatile user-defined tablespaces, and has the following option: 

ALTER TABLESPACE {Tablespace Name} 
         {(1)  ALTER Tablespace Size Clause}
ALTER Tablespace Size Clause#
ALTER 
      {{AUTOEXTEND [AUTOEXTEND Clause]} /
      {SIZE [SIZE Clause]}}

This is used to change the attributes of a volatile tablespace, such as its maximum size, extension increment size, and whether it is increased in size automatically.

ALTER Tablespace State Clause#

The state of a tablespace can be either online or offline, which can be set using the following clause:

ALTER TABLESPACE {Tablespace Name} {ONLINE/OFFLINE/DISCARD}

ONLINE is the normal state of a tablespace. In this state, its objects can be accessed by users. In contrast, when a tablespace is offline, only tablespace-related DLL statements can be executed on it; the objects it contains cannot be accessed by users in other ways. This offline state can be used to overcome limitations, to perform a RENAME operation during the service phase, etc. However, system tablespaces must always remain online; that is, they cannot be taken offline. This clause cannot be used with volatile tablespaces.

The DISCARD option is used when Altibase can't be started due to a data error in one of the tablespaces currently in use.3 DISCARDing the tablespace allows the user to start up Altibase with the remaining tablespaces. Because the only operation that can be performed on a discarded tablespace is the DROP operation, care should be taken when using this option. Additionally, tablespaces can be discarded only during the control phase. This option can be performed on both disk and memory data tablespaces.

Tablespace Backup and Recovery#

This section provides a simple overview of the concept of online and offline tablespace backup. For more detailed information on backup and recovery in Altibase, please refer to the Backup and Recovery chapter of this manual and the Getting Started Guide.

Tablespace Online Backup (HOT Backup)#

The term "tablespace online backup" refers to backup that is conducted while the tablespace is actively providing service. Because online backup does not influence the execution of transactions, it can be performed during the service phase. Online backup has the following characteristics:

  • Online backup is only possible when the database is operating in archive log mode.
  • In the archive log mode, because all log files are backed up in a separate storage space, a sufficiently large storage space must be set aside, even if checkpointing and log flushing have just been conducted.
  • Use the ALTER DATABASE BACKUP statement to perform an online backup while the database is running.
  • Even if a fault causes data files to be damaged or lost,mediarecovery can be performed to restore data files to the current point in time.

[Figure 6-14] The Concepts of Media Recovery
  • If data file xyz, which exists in a disk tablespace, is damaged, it can be restored using a data file that was previously created during a hot backup. A memory tablespace can be recovered using a checkpoint image file that was previously created during a hot backup.
  • On the basis of the final checkpoint SCN (140) and recovery LSN (32:010), which are written in the header of the data file that was created during the backup, the file can be restored to the current final checkpoint SCN (200).
  • When the system is restarted, the most recent image of a data file or memory tablespace can be recovered by repeating all recent transactions using online logs and rolling back all uncommitted transactions using UNDO logs and REDO logs.

Offline Backup of Tablespaces (Cold Backup)#

When a tablespace is backed up offline, the tablespace service is suspended while the backup is performed. Offline backup is faster than online backup, and thus enables recovery to be performed more quickly. Offline backup has the following characteristics:

  • Offline backup is possible when the database is operating in noarchivelog mode.
  • Offline backup is performed by copying data files, log files, and log anchor files after the database is shut down normally.
  • When a data file is damaged or lost due to a fault, it can be restored only up to the time point at which offline backup was most recently performed.

Offline Recovery#

Recovery is a process in which the consistency of a database is restored using a backup image. Recovery cannot be performed while the database is online; it must be performed offline.

Recovery is performed by replacing the existing database with offline backup files while database service is stopped and then restarting the database.


  1. For example, if EXPAND_CHUCK_PAGE_COUNT is set to 128, the default expansion size of the memory tablespace is calculated to be 128 * 32K, which is 4MB. Therefore, the specified size as SIZE is a multiple of 4MB. ↩

  2. For example, if EXPAND_CHUCK_PAGE_COUNT is set to 128, the default expansion size of the memory tablespace is calculated to be 128 * 32K, which is 4MB. Therefore, the specified size as SIZE is a multiple of 4MB. ↩

  3. For example, assume that the DBA has mistakenly deleted a checkpoint image file for a particular memory tablespace. In this case, since the memory tablespace cannot be loaded when the server is started, the DBA might first consider re-creating the deleted checkpoint image by performingmediarecovery. However, if archive logging has not been conducted,mediarecovery will be impossible, and thus this method will be unusable. In such cases, as long as the tablespace can be deleted without causing a problem, the DBA can discard the tablespace, restart the database without the tablespace, and then remove the tablespace. ↩