Examples of Tablespace Use
Examples of Tablespace Use#
This section describes examples of using memory tablespaces and volatile tablespaces.
Memory Tablespaces#
Creating a Memory Tablespace - Basics#
The simplest and easiest way to create a memory tablespace is to use the CREATE MEMORY TABLESPACE statement, specifying the initial size with the SIZE clause.
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M;
Here, because the automatic extension mode was not set, it will default to OFF. When all 256 MB of the tablespace have been used, any attempt to allocate more space to the tablespace1 will result in an error message saying that there is not enough room in the tablespace.
In addition, one or more checkpoint paths, as specified in the MEM_DB_DIR property, will be used as the checkpoint paths for the new tablespace
Supposing that two checkpoint paths are specified in the altibase.properties. Two paths have been saved for the MEM_DB_DIR property: dbs1 and dbs2, both of which are located in the Altibase home directory.
# altibase.properties
MEM_DB_DIR = ?/dbs1
MEM_DB_DIR = ?/dbs2
The following query can be executed to verify that dbs1 and dbs2, which were specified using the MEM_DB_DIR property, are the checkpoint paths for the USER_MEM_TBS tablespace created above:
iSQL> SELECT CHECKPOINT_PATH
FROM V$MEM_TABLESPACE_CHECKPOINT_PATHS
WHERE SPACE_ID =
(SELECT SPACE_ID
FROM V$MEM_TABLESPACES
WHERE SPACE_NAME='USER_MEM_TBS');
CHECKPOINT_PATH
----------------------------------------------
/altibase_home/dbs1
/altibase_home/dbs2
2 rows selected.
First, let's take a look at the files in the checkpoint folders. The 6 files shown below can be found in the dbs1 directory:
SYS_TBS_MEM_DATA-0-0
SYS_TBS_MEM_DATA-1-0
SYS_TBS_MEM_DIC-0-0
SYS_TBS_MEM_DIC-1-0
USER_MEM_TBS-0-0
USER_MEM_TBS-1-0
All of these files are checkpoint image files for the memory tablespace. Their filename format is 'Tablespace Name-{Ping Pong Number}-{File Number}'. 'Ping Pong No.' is either 0 or 1, each of which indicates one of the two checkpoint images used for ping-pong checkpointing2. In addition, because each of the checkpoint images can be stored as multiple files, 'File Number', at the end of the filename, indicates the number of each checkpoint image file, which begins at 0 and increments by 1. The size of the checkpoint image files is specified by using the SPLIT EACH clause with the CREATE TABLESPACE statement. Since the SPLIT EACH clause was not used in the CREATE MEMORY TABLESPACE statement above, the checkpoint image will be split into files 1 GB in size, which is the default value specified using the DEFAULT_MEM_DB_FILE_SIZE property. Because the space used by the above three tablespaces has not reached 1 GB yet, the only file number that can be seen is 0
In the above example, SYS_TBS_MEM_DIC is a system dictionary tablespace containing metadata. This tablespace is automatically created when a database is created.
SYS_TBS_MEM_DATA is the default system data tablespace. When a user creates a table without specifying a tablespace, the data in the table are stored in this tablespace.
Finally, USER_MEM_TBS is the user-defined data tablespace that was created above.
For reference, the initial size, which is specified in the SIZE clause in the CREATE MEMORY TABLESPACE statement, must be a multiple of the extension increment size. For example, if the EXPAND_CHUNK_PAGE_COUNT property, which indicates the number of pages by which a memory tablespace will be incremented when it is expanded, is set to 128, because the size of one memory page is 32 KB, the default extension increment size of a memory tablespace will be 4 MB and the initial size can be set to a multiple of 4M.
If the size specified in the SIZE clause cannot be divided by the extension increment size, the following error will occur:
iSQL> CREATE MEMORY TABLESPACE user_mem_tbs SIZE 1M;
[ERR-110EE : The initial size of the tablespace should be a multiple of expand chunk size ( EXPAND_CHUNK_PAGE_COUNT * PAGE_SIZE(32K) = 4096K )]
Creating Memory Tablespaces - Details#
This section describes various ways to create a memory tablespace.
In the following example, the initial size of the tablespace is set to 256 MB, the automatic extension mode is set to ON, and the tablespace is configured to extend by 128 MB every time it is extended, to a maximum of 1 GB.
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 1G;
Like its initial size, the automatic extension increment size of a tablespace must be set to a multiple of the EXPAND_CHUNK_PAGE_COUNT property * the size of one page, which is the default number of pages by which a tablespace is extended. For more detailed information, please refer to 'Creating Memory Tablespace – Basics.'
A tablespace can be created with no MAXSIZE, as shown below. If the MAXSIZE clause is not specified, the system operates as if it were set to UNLIMITED.
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;
In this case, USER_MEM_TBS is extended, but not past the point where the total space allocated to all memory tablespaces in the system exceeds MEM_MAX_DB_SIZE.
Checkpoint paths can also be specified when creating a memory tablespace, as follows:
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M
CHECKPOINT PATH 'dbs1', '/new_disk/dbs2';
In the above example, the relative path "dbs1" was specified for the checkpoint path, which has the same effect as if "$ALTIBASE_HOME/dbs1" were specified. Additionally, the DBA must first manually create the checkpoint paths specified in the CREATE TABLESPACE statement in the actual file system and then grant write and file execution privileges for them before creating a tablespace.
The size of the files into which a checkpoint image is divided can also be specified, as seen below:
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M SPLIT EACH 512M;
Like the initial size and the expansion increment size, the size of the files into which a checkpoint image is divided must be set to a multiple of the number of pages specified using the EXPAND_CHUNK_PAGE_COUNT property * the size of one page. For more detailed information, please refer to 'Creating Memory Tablespace – Basics.'
A tablespace can be created offline and then taken online before it is used. Since a memory tablespace takes up the amount of system memory that was specified when it was created, in cases where a tablespace will not be used immediately after it is created, this practice can help optimize the use of system resources.
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M OFFLINE;
ALTER TABLESPACE user_mem_tbs ONLINE;
Here is an example that combines the memory tablespace creation options seen above:
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M
AUTOEXTEND ON NEXT 128M MAXSIZE 1G
CHECKPOINT PATH 'dbs1', '/new_disk/dbs2'
SPLIT EACH 512M OFFLINE;
Adding a Checkpoint Path to a Memory Tablespace#
This section describes how to add a checkpoint path to a memory tablespace.
The checkpoint paths for a memory tablespace can only be set during the control phase. After shutting down the Altibase server, restart it in the control phase.
$ isql -u sys -p manager -sysdba
iSQL(sysdba)> STARTUP PROCESS
iSQL(sysdba)> STARTUP CONTROL
In the control phase, the V$TABLESPACES performance view, which pertains to all tablespaces, can be queried. The V$MEM_TABLESPACES performance view, which displays the attributes that are unique to memory tablespaces, can only be viewed in or after the meta phase. In the control phase, it is thus necessary to view memory tablespaces using V$TABLESPACES.
The V$MEM_TABLESPACE_CHECKPOINT_PATHS performance view can be used to view the checkpoint paths belonging to the USER_MEM_TBS tablespace, which was created earlier.
If the data in the tablespace change frequently, resulting in increased disk I/O during checkpointing, this can be alleviated by adding a new checkpoint path to a disk that is physically different from the disk used by the existing checkpoint path, as follows:
Let's add the "/new_disk/dbs3" path to USER_MEM_TBS.
In order to do this, the checkpoint path and directory to be added must first be created, and the Altibase process must be granted write and execute privileges for that directory. Supposing that Altibase is started using the "altibase" operating system user account, this would be conducted as follows:
$ su - root
$ mkdir /new_disk/dbs3
$ chown altibase /new_disk/dbs3
As shown below, a checkpoint path can now be added using the ADD CHECKPOINT PATH statement:
ALTER TABLESPACE user_mem_tbs
ADD CHECKPOINT PATH '/new_disk/dbs3';```
It is the DBA's responsibility to move or copy the checkpoint image files from the existing checkpoint path to the newly added checkpoint path. After a checkpoint path is added, if a new checkpoint image file is needed, the file is created in the new checkpoint path by Altibase.3
Changing the Checkpoint Path for a Memory Tablespace#
This section describes how to change a checkpoint path for a memory tablespace.
Checkpoint paths for memory tablespaces can only be set during the CONTROL phase. As noted in the Adding a Checkpoint Path to a Memory Tablespace section above, after shutting down the Altibase server, restart it in the control phase.
This example shows the procedure of moving dbs1 under the Altibase home directory, the old checkpoint path, to the newly installed disk "/new_disk".
For more detailed information on viewing a checkpoint path for a tablespace during the control phase, please refer to the Adding a Checkpoint Path to a Memory Tablespace section above.
To change a checkpoint path, the absolute path of the existing checkpoint path must be correctly entered. For information on viewing a checkpoint path for a tablespace during the control phase, please refer to the Adding a Checkpoint Path to a Memory Tablespace section above.
Just as when adding a checkpoint path, when changing a checkpoint path, the DBA must first manually create the directory and grant write and execute privileges for the directory to the OS user account under which Altibase is started. Again, it is assumed that the username under which the Altibase process is started is 'altibase.'
$ su - root
$ mkdir /new_disk/dbs1
$ chown altibase /new_disk/dbs1
Now the checkpoint path can be changed from the "dbs1" checkpoint directory, which is located in the Altibase home directory, to the "/new_disk/dbs1" path on the newly added disk using the RENAME CHECKPOINT PATH statement.
ALTER TABLESPACE user_mem_tbs
RENAME CHECKPOINT PATH '/opt/altibase_home/dbs1' TO '/new_disk/dbs1';
Finally, all checkpoint images for the USER_MEM_TBS tablespace, which are located in the existing $ALTIBASE_HOME/dbs1 directory, are moved to the /new_disk/dbs1 directory.
$ mv $ALTIBASE_HOME/dbs1/USER_MEM_TBS* /new_disk/dbs1
Removing a Checkpoint Path from a Memory Tablespace#
This section describes how to remove a checkpoint path from a memory tablespace.
As noted above, checkpoint paths for memory tablespaces can only be set during the control phase, and thus it is necessary to shut down the Altibase server and restart it in the control phase first.
This example shows how to remove an existing checkpoint path, namely the "dbs2" directory located in the Altibase home directory.
To change a checkpoint path, the absolute existing checkpoint path must be entered correctly. For information on how to view the checkpoint paths for a tablespace during the control phase, please refer to the Adding a Checkpoint Path to a Memory Tablespace section above.
The $ALTIBASE_HOME/dbs2 checkpoint path can now be removed using the DROP CHECKPOINT PATH statement as follows:
ALTER TABLESPACE user_mem_tbs
DROP CHECKPOINT PATH '/opt/altibase_home/dbs2'
Finally, all of the checkpoint images for the USER_MEM_TBS tablespace that are located in the existing $ALTIBASE_HOME/dbs2 directory must be moved to one of the other checkpoint paths defined for the USER_MEM_TBS tablespace.
$ mv $ALTIBASE_HOME/dbs2/USER_MEM_TBS* /new_disk/dbs1
Changing the Auto Extension Setting for a Memory Tablespace#
This section describes how to change the auto extension settings for a memory tablespace.
If the AUTOEXTEND clause is not specified when a memory tablespace is created, the tablespace is set to not auto-extend by default.
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M;
In this case, the the simplest way to change a tablespace so that it extends automatically is as follows:
ALTER TABLESPACE user_mem_tbs ALTER AUTOEXTEND ON;
In the above example, the tablespace will be extended in increments equal to the number of pages specified in the EXPAND_CHUNK_PAGE_COUNT property, which is the default unit of extension for tablespaces
In addition, the tablespace will be able to increase in size the same as if the maximum size were set to UNLIMITED, up to the limit at which the total size of all memory tablespaces in the system would not exceed the MEM_MAX_DB_SIZE property.
Unlike disk tablespaces, checkpoint image files for memory tablespaces do not need to be managed by the DBA. This is because checkpoint image files are automatically created by Altibase when it is necessary to increase the size of a database automatically.
To specify the unit of extension for a memory tablespace, use a statement like the following:
ALTER TABLESPACE user_mem_tbs ALTER AUTOEXTEND ON NEXT 128M;
To specify the maximum size of a memory tablespace, use a statement like the following:
ALTER TABLESPACE user_mem_tbs ALTER AUTOEXTEND ON MAXSIZE 1G;
To specify both the unit of extension and the maximum size of a memory tablespace, use a statement like the following:
```sql ALTER TABLESPACE user_mem_tbs ALTER AUTOEXTEND ON NEXT 128M MAXSIZE 1G;
To turn off the automatic extension setting for a memory tablespace, use a statement like the following:
ALTER TABLESPACE user_mem_tbs ALTER AUTOEXTEND OFF;
Bringing a Memory Tablespace Online or Taking It Offline#
This example describes how to change a memory tablespace from an online state to an offline state and vice-versa.
All of the data in an Altibase memory tablespace are loaded into memory. To accomplish this, an amount of system memory equal to the amount of memory currently being used by a memory tablespace is allocated to the tablespace. Altibase provides functions for allocating memory to memory tablespaces and freeing memory so that DBAs can manage memory usage more easily.
Of course, when the memory of a memory tablespace is returned, all objects created in the tablespace are temporarily inaccessible. To free the memory space being used by a memory tablespace, take the tablespace offline.
ALTER TABLESPACE user_mem_tbs OFFLINE;
When it is subsequently desired to use a table that exists in the memory tablespace that was taken offline, bring the tablespace online as follows:
ALTER TABLESPACE user_mem_tbs ONLINE;
Volatile Tablespaces#
Creating a Volatile Tablespace#
The statements for creating, changing, and deleting volatile tablespaces are essentially identical to those for memory tablespaces. The main difference between them is that the statements related to checkpoint image files are of no use with volatile tablespaces.
A volatile tablespace 256 MB in size can be created using the following statement:
CREATE VOLATILE DATA TABLESPACE user_vol_tbs SIZE 256M;
In the above example, the size of the tablespace is fixed at 256 MB, that is, it is not automatically extended. A tablespace that automatically increases in size can be created using the following statement:
CREATE VOLATILE DATA TABLESPACE user_vol_tbs SIZE 256M AUTOEXTEND ON;
In the above example, the initial size of the tablespace is 256 MB, but it can be automatically extended up to the size specified using the VOLATILE_MAX_DB_SIZE property. The increment by which it automatically increases in size is 4 MB. To create a volatile tablespace for which the unit of automatic extension is 8 MB and the maximum size is 512 MB, execute a statement like the following:
CREATE VOLATILE DATA TABLESPACE user_vol_tbs SIZE 256M AUTOEXTEND ON NEXT 8M MAXSIZE 512M;
Modifying a Volatile Tablespace#
The automatic extension mode, automatic extension increment size, and maximum size settings for volatile tablespaces can be changed.
The following statement enables the automatic extension for a volatile tablespace for which automatic extension was previously disabled:
ALTER TABLESPACE user_vol_tbs ALTER AUTOEXTEND ON;
The following statement enables automatic extension mode, sets the automatic extension increment size to 8 MB, and sets the maximum size of the tablespace at 512 MB.
ALTER TABLESPACE user_vol_tbs ALTER AUTOEXTEND ON NEXT 8M MAXSIZE 512M;
The following statement turns off the automatic extension mode. Before using this statement, the automatic extension mode must previously have been set to ON.
ALTER TABLESPACE user_vol_tbs ALTER AUTOEXTEND OFF;
DROP Tablespace - for Disk, Memory and Volatile Tablespaces#
Discarding Tablespace – Removing Tablespace with Corrupt Data#
This section describes how to discard a tablespace.
If the DBA accidentally deletes a data file of a disk tablespace or a checkpoint file of a memory tablespace, or if the contents of such a file are lost due to amediafault, it will become impossible to start Altibase.
In such cases, the first thing to try is to restore the lost or damaged file throughmediarecovery. However,mediarecovery can only be performed when archive logging has been performed, such that copies of all existing log files remain available in a separate archive.
If this is not the case, andmediarecovery is therefore impossible, the tablespace associated with the lost data file or checkpoint image file can be discarded using the ALTER TABLESPACE DISCARD statement, and Altibase can then be started with only the remaining tablespaces.
Once a tablespace has been discarded using the ALTER TABLESPACE DISCARD statement, the objects in the tablespace become inaccessible, and the only action that can subsequently be performed on the tablespace is to DROP it. Therefore, this statement should be used with caution.
In the following example, the memory tablespace USER_MEM_TBS is created and then, assuming that the checkpoint images for this tablespace have been deleted. Altibase can be started up with the remaining tablespaces after the tablespace is discarded.
First, create a memory tablespace, as shown below:
CREATE MEMORY TABLESPACE user_mem_tbs SIZE 256M;
Then shut down Altibase and delete the checkpoint files for the tablespace. When an attempt is made to start up Altibase, the following error will occur:
[SM-WARNING] CANNOT IDENTIFY DATAFILE
[TBS:user_mem_tbs, PPID-0-FID-0] Datafile Not Found
[SM-WARNING] CANNOT IDENTIFY DATAFILE
[TBS:user_mem_tbs, PPID-1-FID-0] Datafile Not Found
[FAILURE] The data file does not exist.
Startup Failed....
[ERR-91015 : Communication failure.]
Altibase will generate an error if there are no data files and checkpoint images for a tablespace.
Now it is time to discard USER_MEM_TBS.
The Discard statement can be executed only during the control phase. So, start up Altibase in the control phase.
$ isql -u sys -p manager -sysdba
iSQL(sysdba)> STARTUP CONTROL
Now USER_MEM_TBS, which is missing a checkpoint file, can be discarded.
iSQL(sysdba)> ALTER TABLESPACE user_mem_tbs DISCARD;
Then execute the STARTUP SERVICE command to start up Altibase in the service phase.
iSQL(sysdba)> STARTUP SERVICE
Issuing the ALTER TABLESPACE DISCARD command is merely the first step in discarding the tablespace. Therefore, the tablespace and its objects must be manually removed directly by using the DROP TABLESPACE INCLUDING CONTENTS statement.
DROP TABLESPACE user_mem_tbs INCLUDING CONTENTS AND DATAFILES;
If a data file in a disk tablespace is lost, or if some of the contents of a data file become corrupt due to amediafault, Altibase can be started up after discarding the corresponding tablespace in the same way.
Removing Tablespace#
This example shows how to remove a tablespace.
If a tablespace contains no objects, it can be easily removed, as seen below. However, this method does not remove the data files of a disk tablespace or the checkpoint image files for a memory tablespace.
DROP TABLESPACE my_tbs;
If a tablespace contains objects, all objects in the tablespace can be dropped by using the INCLUDING CONTENTS clause together with the DROP statement, as shown below. However, even when using this method, the data files or checkpoint image files are not removed from the file system.
DROP TABLESPACE my_tbs INCLUDING CONTENTS;
If a data file in a disk tablespace is lost, or if some of the contents of a data file become corrupt due to amediafault, Altibase can be started up after discarding the corresponding tablespace in the same way.
DROP TABLESPACE my_tbs INCLUDING CONTENTS CASCADE CONSTRAINTS;
To remove referential constraints from tables in other tablespaces, use the CASCADE CONSTRAINTS clause together with the INCLUDING CONTENTS clause. However, even when using this method, the data files or checkpoint image files are still not removed from the file system.
DROP TABLESPACE my_tbs INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE my_tbs INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
-
If a table is created in a tablespace, if data are entered into an existing table, or if the data in an existing table are changed, additional space is allocated from the tablespace. ↩
-
To ensure the durability of tablespace data in memory, data are saved in disk files. The files in which tablespace data are stored are called images. In ping-pong checkpointing, which is used in Altibase, a pair of checkpoint images is maintained, and tablespace data are stored alternately in each of them. ↩
-
When checkpoint image files are created for a tablespace during checkpointing, they are alternately created in each of the checkpoint paths for that tablespace. ↩