Skip to main content

Search

Welcome to Tibero GTS!

TX_NO_TEMPFILE Error Occurring in sys.log

Document Type | Troubleshooting 

Category | Patch/Upgrade

Applicable Product Version | 7FS02

Error Code | 21009

Document Number | TPATS014

 

Issue

After changing the TEMP tablespace in Tibero, the ERROR_TX_NO_TEMPFILE(-21009) error was repeatedly recorded in the sys.log file.
Note
This issue occurred in Tibero7 FS02.
 
Below is a portion of the sys.log.
[2024-07-12T08:36:39.378749] [FRM-421] [I] THROW. ec=ERROR_TX_NO_TEMPFILE(-21009) [ No tempfile was found.] (csr_id:16) [tx_temp_ts.c:1220:temp_ts_alloc_ext]
[2024-07-12T08:36:39.378936] [DDL-421] [I] internal dd update failed : ec=-21009
-- INSERT INTO PLAN_TABLE (STATEMENT_ID, SQL_ID, CHILD_NUMBER, TIMESTAMP, OPERATION, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, SEARCH_COLUMNS, ID, PARENT_ID,DEPTH, POSITION, COST, CARDINALITY, PARTITION_START, PARTITION_END, OTHERS, ACCESS_PREDICATES, FILTER_PREDICATES) SELECT :stmt_id, SQL_ID, CHILD_NUMBER, SYSTIMESTAMP, UPPER(OPERATION), OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH, POSITION, COST, CARDINALITY, PSTART, PEND, OTHERS, ACCESS_PREDICATES, FILTER_PREDICATES FROM VT_USER_SQL_PLAN 
[2024-07-12T08:36:39.381325] [FRM-421] [I] THROW. ec=ERROR_TX_NO_TEMPFILE(-21009) [ No tempfile was found.] (csr_id:26) [tx_temp_ts.c:1220:temp_ts_alloc_ext]
[2024-07-12T08:36:39.381354] [DDL-421] [I] internal dd update failed : ec=-21009
 
The TEMP tablespace is being used normally, and below are the results of queries to check related settings.
SQL> select * from _dd_props where name=โ€˜DFLT_TEMP_TSโ€™

NAME          VALUE         COMMENT_STR
------------  ------------  ------------------------------------
DFLT_TEMP_TS  TEMP3         Name of default temporary tablespace


SQL> select username, DEFAULT_TEMP_TABLESPACE from dba_users;

USERNAME     DEFAULT_TEMP_TABLESPACE
------------ -------------------------- 
SYSMASTER     TEMP3
SYS           TEMP3
SYSCAT        TEMP3
SYSGIS        TEMP3
OUTLN         TEMP3
TIBERO        TEMP3
TIBERO1       TEMP3
YSSSODB       TEMP3
 
Although the TEMP tablespace is being used normally, in cases of excessive TEMP space usage, the following error may occur.
(ec=ERROR_TX_CANT_ALLOC_EXT is an error that occurs when TEMP TABLESPACE space is insufficient.)
TEMP SEGMENT Usage

====================
Temp Segment Usage
====================
      SID USERNAME        SQL_ID        SEGTYPE           TEMP(MB) SQL_TEXT 
---------- --------------- ------------- --------------- ---------- ----------------------------------------------------------------
     1554 SYS             b0kv7pqkxys8f SORT               1590.19 select * from ( select A.*,

ERROR Occurrence (can be confirmed in sys.log)
[2024-11-28T13:58:14.258037] [TXT-1554] [W] can't extend tablespace TS #10(TEMP3)
[2024-11-28T13:58:14.258074] [FRM-1554] [I] THROW. ec=ERROR_TX_CANT_ALLOC_EXT(-21004) [ No more extent available in tablespace 'TEMP3'.] (csr_id:6) [tx_temp_ts.c:1464:temp_ts_extend]

 

Cause

Although the existing TEMP TABLESPACE was dropped during the TEMP TABLESPACE change operation, it was not executed properly, causing queries to reference the previous TEMP TABLESPACE and resulting in errors.

Below is a part of the CALLSTACK.
During the process of creating a TEMP segment for segment ID 1241, an attempt was made to use the space of TEMP tablespace ID 2.

#4  0x0000000001f17a06 in temp_ts_alloc_ext (temp_ts_id=temp_ts_id@entry=2, unitcnt=<optimized out>, type=type@entry=TEMP_TYPE_GTEMP_TBL, my_total_alloc_for_stat=my_total_alloc_for_stat@entry=0) at /home/chef/workspace/Build_centos_7-6/tibero7/src/tbsvr/tx/tx_temp_ts.c:1220
#5  0x0000000001f0e08d in ts_alloc_ext_internal (tsid=tsid@entry=2, extsize=<optimized out>, extsize@entry=8, rec_tx=rec_tx@entry=1 '\001', j_stat=j_stat@entry=1 '\001', extend_need_=extend_need_@entry=1 '\001', deferred_undo=deferred_undo@entry=0 '\000', use_df_hint=use_df_hint@entry=0 '\000', min_extsize=min_extsize@entry=0, ret_extsize=ret_extsize@entry=0x0, schema_id=schema_id@entry=0) at /home/chef/workspace/Build_centos_7-6/tibero7/src/tbsvr/tx/tx_ts.c:1679
#6  0x0000000001ea8e5e in tx_sgmt_add_1st_ext (schema_id=0, blksize=8192, dd_extsize=1, sgmt_type=1 '\001', sgmt_id=1241, ts_id=<optimized out>) at /home/chef/workspace/Build_centos_7-6/tibero7/src/tbsvr/tx/tx_sgmt.c:1934
#7  tx_sgmt_create (ts_id=<optimized out>, sgmt_id=sgmt_id@entry=1241, sgmt_type=sgmt_type@entry=1 '\001', extsize=extsize@entry=8, blksize=8192, schema_id=schema_id@entry=0) at /home/chef/workspace/Build_centos_7-6/tibero7/src/tbsvr/tx/tx_sgmt.c:2486

 

Below are the results of querying TEMP TABLESPACE (TS_ID=2) and DBA_TEMP_FILES.

SQL> select TABLESPACE_NAME, TS_ID from dba_tablespaces where TS_ID=2;

TABLESPACE_NAME    TS_ID
-----------------  -----------------
TEMP               2

SQL> SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME FROM DBA_TEMP_FILES;

FILE_NAME        FILE_ID      TABLESPACE_NAME
---------------- ------------ ----------------
/dev/rlv_data021  2           TEMP3
/dev/rlv_data022  3           TEMP3

SQL>  SELECT SGMT_ID, TS_ID  FROM SYS._DD_SGMT WHERE TS_ID = 2;

 SGMT_ID      TS_ID  
---------- ---------- 
     1241          2
The tablespace with TS_ID 2 is shown as TEMP, and TS_ID 2 is the ID of the TEMP tablespace created during initial DB installation. Although the existing TEMP tablespace was dropped while changing the TEMP tablespace, the operation was not properly executed. As a result, segment ID 1241 references the previous TEMP tablespace ID (2), causing the error.

 

Solutions

1. Recreate the Table or Modify DD_SGMT Information

Recreate the Table Having the Problematic SGMT ID

The table referencing the SGMT ID was SYS.PLAN_TABLE. After dropping and recreating this table, the error no longer occurred. If the table is in use, backup followed by DROP and CREATE operations are necessary.
 

Modify DD_SGMT Information to the Default TEMP Tablespace

It is also possible to change the TS_ID referenced by the segment to the current DEFAULT TEMP tablespace ID.

 

2. Apply Patch Related to DEFAULT TEMPORARY TABLESPACE (256390)

This patch improves the issue by storing the TS_ID value as NULL in the data dictionary (DD) when creating TEMP tables in the DEFAULT TEMP tablespace, preventing reference errors.
Caution
Apply the patch through technical support provided by TmaxTibero.
 
 
 

Related to