Method

Issue

• When a GLOBAL TEMPORARY TABLE is created with ON COMMIT PRESERVE ROWS, the allocated space and data are preserved.

• In this state, even if TRUNCATE GLOBAL TEMPORARY TABLE is executed, the allocated space is not released.

• As a result, temp tablespace full errors may occur during repeated creation and subsequent operations on GLOBAL TEMPORARY TABLES.

Countermeasures

Tibero’s GLOBAL TEMPORARY TABLE feature is designed considering OLTP environments where tables are used repeatedly.
To reduce the cost of allocating new extents when inserting again after truncate, it operates by reusing the existing space.
Therefore, this phenomenon is not a bug but a specification of Tibero.

The timing of space release differs depending on whether the ON COMMIT PRESERVE ROWS option is used.
It operates as follows.

• If the ON COMMIT PRESERVE ROWS option is not used
  space is released at the end of the transaction, so no issues occur.

• If the ON COMMIT PRESERVE ROWS option is used
  space is not released until the session ends, so the session must be terminated if necessary.

Additionally, in environments where the following patches are applied, space can be released immediately when truncate is performed using the FOR STATISTICS option.

• Patch: 6 FS07PS_339187a, 7 FS02PS_339187a

• Patch Details: Functionality to release space with TRUNCATE GLOBAL TEMPORARY TABLE ~ FOR STATISTICS option.

• Note: This feature was implemented to resolve issues occurring when using GLOBAL TEMPORARY TABLE during statistics collection and is not an official feature provided to customers.

Issue Verification

The test environment and verification results are as follows.

Test Environment

• Tibero 7.2.4 distribution version

• FS02PS_339187a patch applied

Caution

• Releasing space does not mean deleting the table.

• A separate DROP operation is required to delete the table.

(1) General GLOBAL TEMPORARY TABLE

Space is released immediately at the transaction level.

SQL> CREATE GLOBAL TEMPORARY TABLE TIBERO.TEST_TMP
   2 AS SELECT level as idnum from dual connect by level < 1000001;  

Table 'TIBERO.TEST_TMP' created.

SQL> SELECT USERNAME,SEGTYPE,BLOCKS FROM V$TEMPSEG_USAGE;

0 row selected.

(2) When Using ON COMMIT PRESERVE ROWS

Space is retained even after TRUNCATE.

Space is released after session termination and reconnection.

SQL> CREATE GLOBAL TEMPORARY TABLE TIBERO.TEST_TMP ON COMMIT PRESERVE ROWS
   2 AS SELECT level as idnum from dual connect by level < 1000001;

Table 'TIBERO.TEST_TMP' created.

SQL> SELECT USERNAME,SEGTYPE,BLOCKS FROM V$TEMPSEG_USAGE;

USERNAME                         SEGTYPE                              BLOCKS
-------------------------------- -------------------------------- ----------
TIBERO                           DATA                                   1672

1 row selected.

SQL> TRUNCATE TABLE TIBERO.TEST_TMP;

Table 'TIBERO.TEST_TMP' truncated.

SQL> SELECT USERNAME,SEGTYPE,BLOCKS FROM V$TEMPSEG_USAGE;

USERNAME                         SEGTYPE                              BLOCKS
-------------------------------- -------------------------------- ----------
TIBERO                           DATA                                   1672

1 row selected.

SQL> exit
Disconnected.
[python@t1 ~]$ tbsql tibero/tmax

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL>  SELECT USERNAME,SEGTYPE,BLOCKS FROM V$TEMPSEG_USAGE;

0 row selected.

(3) When Using FOR STATISTICS Option

Space is released immediately when TRUNCATE is performed.

SQL> CREATE GLOBAL TEMPORARY TABLE TIBERO.TEST_TMP ON COMMIT PRESERVE ROWS
   2 AS SELECT level as idnum from dual connect by level < 1000001;

Table 'TIBERO.TEST_TMP' created.

SQL> SELECT USERNAME,SEGTYPE,BLOCKS FROM V$TEMPSEG_USAGE;

USERNAME                         SEGTYPE                              BLOCKS
-------------------------------- -------------------------------- ----------
TIBERO                           DATA                                   1672

1 row selected.

SQL> TRUNCATE TABLE TIBERO.TEST_TMP for statistics;

Table 'TIBERO.TEST_TMP' truncated.

SQL> SELECT USERNAME,SEGTYPE,BLOCKS FROM V$TEMPSEG_USAGE;

0 row selected.