Skip to main content

Search

Welcome to Tibero GTS!

ERROR_TCCF_NO_SUCH_TS(-24012) Error Occurs When Performing add partition

Document Type | Troubleshooting

Category | Patch/Upgrade

Applicable Product Versions | 6FS07, 7FS02

Error Code | 24012

Document Number | TPATS025

 

Issue

When performing add partition, the ERROR_TCCF_NO_SUCH_TS(-24012) error occurs.
 
Example 1) drop tablespace - table
SQL> create tablespace ts1;

SQL> create table t1(c1 number) tablespace ts1
partition by range(c1)
(partition p1 values less than (10) tablespace usr,
partition p2 values less than (20) tablespace usr);


SQL> alter table t1 modify default attributes tablespace ts1;

SQL> select tablespace_name from dba_tables where table_name = 'T1';
-- Before/After patch: TS1
SQL> select def_tablespace_name from dba_part_tables where table_name = 'T1';
-- Before/After patch: TS1

SQL> drop tablespace ts1;

SQL> alter table t1 add partition p3 values less than (30);
-- Before patch: TBR-24012: No such tablespace 5. (5 is ts_id and may vary)
-- After patch: Table 'T1' altered.


SQL> select tablespace_name from dba_tables where table_name = 'T1';
-- Before patch: TS1
-- After patch: USR

SQL> select def_tablespace_name from dba_part_tables where table_name = 'T1';
-- Before patch: TS1
-- After patch: USR

 

Example 2) drop tablespace - index
SQL> create tablespace ts1;

SQL> create table t1(c1 number)
partition by range(c1)
(partition p1 values less than (10),
partition p2 values less than (20));


SQL> create index i1 on t1(c1) local;
SQL> alter index i1 modify default attributes tablespace ts1;

SQL> select tablespace_name from dba_indexes where index_name = 'I1';
-- Before/After patch: TS1

SQL> select def_tablespace_name from dba_part_indexes where index_name = 'I1';
-- Before/After patch: TS1

SQL> drop tablespace ts1;

SQL> alter table t1 add partition p3 values less than (30);

-- Before patch: TBR-24012: No such tablespace 5. (5 is ts_id and may vary)
-- After patch: Table 'T1' altered.

 

SQL> select tablespace_name from dba_indexes where index_name = 'I1';

-- Before patch: TS1
-- After patch: USR

 

SQL> select def_tablespace_name from dba_part_indexes where index_name = 'I1';

-- Before patch: TS1
-- After patch: USR

 

Cause

When drop tablespace is executed, even if the deft_ts_id column value of the partition object (_dd_partobj) is the same as the ts_id column value in the dba_tablespace view, the value was not changed.
 
As a result, when add partition is performed later, it tries to create the partition in deft_ts_id, but since the tablespace does not exist, an error occurs.
 
Related Dictionary
SQL> desc _dd_partobj
COLUMN_NAME                              TYPE               CONSTRAINT          
---------------------------------------- ------------------ --------------------
OBJ_ID                                   NUMBER             
PARTTYPE                                 NUMBER             
PARTCNT                                  NUMBER             
PARTKEYCOLS                              NUMBER             
FLAGS                                    NUMBER             
DEFTS_ID                                 NUMBER             
DEFPCTFREE                               NUMBER             
DEFPCTFREE_BYTES                         NUMBER             
DEFINITRANS                              NUMBER             
DEFEXTSIZE                               NUMBER             
DEFMAXEXTS                               NUMBER             
DEFBUFPOOL                               NUMBER             
SUBPARTTYPE                              NUMBER             
SUBPARTCNT                               NUMBER             
SUBPARTCOLCNT                            NUMBER             
BOUND_TOTAL_SIZE                         NUMBER             
INTERVAL                                 VARCHAR(128)      

SQL> desc dba_tablespaces;
COLUMN_NAME                              TYPE               CONSTRAINT          
---------------------------------------- ------------------ --------------------
TABLESPACE_NAME                          VARCHAR(128)       
TS_ID                                    NUMBER             
DATAFILE_COUNT                           NUMBER             
BLOCK_SIZE                               NUMBER             
NEXT_EXTENT                              NUMBER             
STATUS                                   VARCHAR(9)         
CONTENTS                                 VARCHAR(9)         
LOGGING                                  VARCHAR(9)         
FORCE_LOGGING                            VARCHAR(3)         
ALLOCATION_TYPE                          VARCHAR(7)         
ENCRYPTED                                VARCHAR(3)         
INITIAL_EXTENT                           NUMBER             
MIN_EXTENTS                              NUMBER             
MAX_EXTENTS                              NUMBER             
EXTENT_MANAGEMENT                        VARCHAR(5)         
SEGMENT_SPACE_MANAGEMENT                 VARCHAR(4)

 

Solutions

Patch Application

FS07PS_337253a: When drop tablespace is performed, the default tablespace of table, index, and lob objects that have the dropped tablespace as their default tablespace has been changed to the default tablespace of the user.

Caution

Apply the patch through technical support provided by TmaxTibero.

 

Workaround

You can specify the tablespace when adding a partition or recreate the dropped tablespace to resolve the issue.
 
Example 1) Specify tablespace when adding partition
SQL > ALTER TABLE (table_name) ADD PARTITION (partition_name) VALUES LESS THAN (value) TABLESPACE (tablespace_name);

 

Example 2) Create TABLESPACE
SQL > ALTER TABLESPACE [tablespace_name] ADD DATAFILE ('datafile_path') SIZE 150M AUTOEXTEND ON;

 

 

 

Related to