Overview

This document provides guidance on Tablespace and Datafile management for Tibero Single DB operation.

Method

6. Tablespace / Datafile Management

6.1. Creating Tablespace and Adding Datafile

A Tablespace is a logical storage space for storing data, and a Datafile is the physical storage space where the actual data is stored.

A Tablespace can include multiple Datafiles, and users can create and use Tablespaces and Datafiles as needed.

SQL> create tablespace TEST01 datafile ' /tibero/tbdata/data_1/test01001.dtf ' size 100m;
⇒ Creates a tablespace named ‘TEST’ and a datafile ‘test01001.dtf’ with a size of 100MB.

SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME               STATUS
------------------------------            ---------
TEST01                                    ONLINE


$ ls –l /tibero/tbdata/data_1
-rw------- 1 tibero dba 104857600 Mar 27 15:47 test01001.dtf

⇒ The tablespace named “TEST01” has been created with a status of “Online,” and the 100MB datafile “test01001.dtf” is confirmed to exist on the OS.

6.1.1. Adding a Datafile

If the space available in a Tablespace is insufficient, you can add a Datafile to secure additional space.

SQL> alter tablespace TEST01 add datafile '/tibero/tbdata/data_1/test01002.dtf' size 200m;
SQL> select ts#, file#, name, status, create_bytes/1024/1024 MB from v$datafile where ts#=5;

 TS#   FILE# NAME                              STATUS         MB
------ ------- ------------------------------------------------ ------------- ------------
   5      4 /tibero/tbdata/data_1/test01001.dtf  ONLINE        100
   5      6 /tibero/tbdata/data_1/test01002.dtf  ONLINE        200

$ ls -l /tibero/tbdata/data_1

-rw------- 1 tibero dba 104857600 Mar 27 15:47 test01001.dtf
-rw------- 1 tibero dba 209715200 Mar 27 15:51 test01002.dtf

⇒ Added a 200MB datafile ‘test01002.dtf’ to the “TEST” tablespace, and confirmed its existence on the OS.

6.2. Moving Datafile Location

You may need to change the location of a Datafile due to insufficient capacity of the file system storing the Datafile, or for backup or management reasons.

System Tablespace and Undo Tablespace locations can only be changed when the database is in Mount state,

while normal Data Tablespaces can be changed even when the database is in Normal state (when the Tablespace is Offline).

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME     STATUS
----------------------------   ----------
SYSTEM                         ONLINE
UNDO                            ONLINE
TEMP                             ONLINE
USR                               ONLINE
SYSSUB                         ONLINE
TEST01                          ONLINE
TEST02                          ONLINE

7 rows selected.

⇒ Check the tablespace names and their status (Online/Offline).

SQL> SELECT FILE#,NAME, STATUS FROM V$DATAFILE;



  FILE#  NAME                                                    STATUS
--------- ------------------------------------------------   ---------
         0  /tibero/tbdata/data/system001.dtf     ONLINE
         1  /tibero/tbdata/data/undo001.dtf        ONLINE
         2  /tibero/tbdata/data/usr001.dtf            ONLINE
         3  /tibero/tbdata/data/syssub001.dtf      ONLINE
         4  /tibero/tbdata/data_1/test01001.dtf   ONLINE
         5  /tibero/tbdata/data_1/test02001.dtf   ONLINE
         6  /tibero/tbdata/data_1/test01002.dtf   ONLINE

⇒ Check the Datafile number, name, and status (Online/Offline) existing in the database.

6.2.1. Taking Tablespace Offline and Moving Datafile

SQL> alter tablespace TEST02 offline;
SQL> select tablespace_name, ts_id, status from dba_tablespaces;

TABLESPACE_NAME          TS_ID   STATUS
--------------------------- -------------- -------------
TEST02                                        6 OFFLINE

SQL> select ts#,file#,name,status from v$datafile;

 TS#    FILE#   NAME                                                          STATUS
---- -----------  ---------------------------------------------------- -----------
   6             5  /tibero/tbdata/data_1/test02001.dtf       OFFLINE

⇒ When a tablespace is taken offline, its Datafiles also go offline.

$ cp /tibero/tbdata/data_1/test02001.dtf  /tibero/tbdata/data_2/test02001.dtf

⇒ Copy the Datafile to the new location on the OS.
   (Verify on OS and copy to a file system with sufficient free space)

SQL> alter database rename file '/tibero/tbdata/data_1/test02001.dtf' to
 '/tibero/tbdata/data_2/test02001.dtf';
Database altered.

SQL> select ts#,file#,name,status from v$datafile;

     TS#      FILE#   NAME                                                         STATUS
--------   ---------    --------------------------------------------------  -----------
         6            5     /tibero/tbdata/data_2/test02001.dtf     OFFLINE

⇒ After modifying the Datafile location to the copied one on the OS, verify the result.
Status remains “OFFLINE.”

SQL> alter tablespace TEST02 online;
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME                  STATUS
------------------------------             -----------
TEST02                                      ONLINE

SQL> select ts#,file#,name,status from v$datafile;

     TS#   FILE#    NAME                                                           STATUS
--------    ------      --------------------------------------------------- -----------
         6         5      /tibero/tbdata/data_2/test02001.dtf      ONLINE

⇒ When the TEST tablespace is brought online, the moved Datafile also changes to Online status and becomes available.

6.3. Tablespace / Datafile Management

6.3.1. Datafile Resize

If the Datafile size is small and space is insufficient (or the Tablespace space is insufficient), you can adjust the size of the Datafile without adding a new one.

Make sure to check the free space of the file system where the Datafile is stored before performing this operation.

SQL> alter database datafile '/tibero/tbdata/data_2/test02001.dtf' resize 200m;
SQL> select TABLESPACE_NAME, FILE_NAME, STATUS, BYTES/1024/1024 MB from dba_data_files;

TABLESPACE_NAME  FILE_NAME                                             STATUS          MB
-------------------------- ----------------------------------------------     ---------------  -----
TEST02                       /tibero/tbdata/data_2/test02001.dtf  AVAILABLE    200

⇒ Resized the Datafile ‘test02001.dtf’ to 200MB. Resizing can be done during database operation.

6.3.2. Managing Datafile Autoextensible (Autoextend)

When the Datafile size is small, instead of adding or resizing Datafiles, you can secure additional space by using the autoextensible feature.

If the autoextend option of a Datafile is set to ‘On,’ the Datafile will automatically extend up to the configured maximum size.

Before using this method, check the free space in the file system, and avoid setting the maximum size too large to prevent management issues. Set it considering usage.

SQL> select file_name, autoextensible from dba_data_files;

FILE_NAME                             AUTOEXTENSIBLE
----------------------------------------------     ----------------------
/tibero/tbdata/data_2/test02001.dtf     NO

⇒ The autoextensible feature of the Datafile 'test02001.dtf' is currently “OFF.”

SQL> alter database datafile '/tibero/tbdata/data_2/test02001.dtf' autoextend on next 1m maxsize 1000m;
⇒ Turn on the autoextend feature of the Datafile.
It will extend by 1MB increments up to a maximum of 1000MB.

SQL> select file_name, autoextensible from dba_data_files;

FILE_NAME                             AUTOEXTENSIBLE
-----------------------------------------------    ------------------------
/tibero/tbdata/data_2/test02001.dtf     YES

⇒ The autoextensible feature of the Datafile 'test02001.dtf' has been changed to “YES.”

6.3.3. Datafile Error Check

If there is an issue with a Datafile, you can check for errors while the database is in Mount state using the following command.

SQL> select * from v$recover_file;
0 row selected

⇒ This command checks for Datafile corruption. If ‘0 row selected’ is returned, the Datafile is normal.