Skip to main content

Search

Welcome to Tibero GTS!

suspended _DD_DROPPED_SGMT Action Plan

Document Type | TrobleShooting

Category | Management/Configuration

Applicable Product Version | 7FS02, 7FS02PS

Document Number | TADTS004

 

Issue

If the tablespace (TS) you want to delete is in OFFLINE or READ ONLY status, when deleting an object, meta information remains in _DD_DROPPED_SGMT but the actual segment cannot be removed. 
This causes the error suspended _DD_DROPPED_SGMT : not writable ts (10) to occur.
 

Cause

If the tablespace of the object to be deleted is in READ ONLY or OFFLINE status, the segment cannot be removed, leaving only the meta in _DD_DROPPED_SGMT.

 

Solutions

How to Change

Prevent suspended _DD_DROPPED_SGMT : not writable ts (10) Error Message

To prevent this error message from being displayed, change the parameter value as follows.
SQL> alter system set _LAZY_SGMT_DROP_NOT_WRITABLE_TS_LOG = N;

 

Remove _DD_DROPPED_SGMT Entries

Change the tablespace status to ONLINE or READ WRITE.
SQL> alter tablespace <TABLESPACE_NAME> read write;

 

Example 

Create Tablespace, Table, and Change TS Status

SQL> CREATE TABLESPACE TS1 DATAFILE '/home/viera/tibero7/database/tibero/ts1.dtf' SIZE 100M;
Tablespace 'TS1' created.

SQL> create table t (a number) tablespace ts1;


Table 'T' created.

SQL> alter tablespace ts1 read only;
Tablespace 'TS1' altered.

 

Check Object List

SQL> select * from sys._dd_obj where name='T';
   OBJ_ID   OWNER_ID

---------- ----------
NAME
--------------------------------------------------------------------------------
SUBNAME
--------------------------------------------------------------------------------
    BO_ID    TYPE_NO      FLAGS
---------- ---------- ----------
CTIME
--------------------------------------------------------------------------------
MTIME
--------------------------------------------------------------------------------
STIME
--------------------------------------------------------------------------------
RESERVED1  RESERVED2  RESERVED3
---------- ---------- ----------
RESERVED4
--------------------------------------------------------------------------------
     4064         18
T

4294967295          1          0
2025/02/19
2025/02/19
2025/02/19
        0          0          0

1 row selected.


SQL> select * from sys._dd_dropped_sgmt;
0 row selected.

 

Drop Table and Error Occurrence in sys.log

Drop Table
SQL> drop table t;
Table 'T' dropped.
 
sys.log
[2025-02-19T10:46:57.379133] [DDL-102] [I] sgmt(4064) suspended _DD_DROPPED_SGMT : not writable ts (10)

 

Check Object List

Confirm Object Presence
SQL> select * from sys._dd_obj where name='T';

0 row selected.
 
Confirm _dd_dropped_sgmt Entry
SQL> select * from sys._dd_dropped_sgmt;

   SESSID    SGMT_ID      TS_ID    SHDRDBA      FLAGS
---------- ---------- ---------- ---------- ----------
       89       4064         10   37748745          1

1 row selected.

 

Change Parameter and Check sys.log

No error output in sys.log
SQL> alter system set _LAZY_SGMT_DROP_NOT_WRITABLE_TS_LOG = N;
System altered.

 

Change Tablespace STATUS (READ ONLY โ†’ READ WRITE)

Change ts status
SQL> alter tablespace ts1 read write;
Tablespace 'TS1' altered.

 

Confirm removal from _dd_dropped_sgmt list
SQL> select * from sys._dd_dropped_sgmt;
0 row selected.

 

No Error Output Even After Changing Parameter Back

No error output in sys.log
SQL> alter system set _LAZY_SGMT_DROP_NOT_WRITABLE_TS_LOG =Y;

System altered.

 

 
 

Related to