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.
NoteThis 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.
CautionApply the patch through technical support provided by TmaxTibero.