Document Type | Technical Information
Category | Administration
Applicable Product Versions | 6FS07PS, 7FS02, 7FS02PS
Document Number | TADTI009
Overview
When an object is deleted in Tibero, it is removed from the sys._dd_obj list, and the information can be checked in the _dd_dropped_sgmt table. After a certain period, when the data is also removed from _dd_dropped_sgmt, the object is completely deleted from the data files. This method is used because deleting large amounts of data can cause service delays during the segment allocation recovery process, so the object is not immediately removed but registered in the _dd_dropped_sgmt list.
Check Object Schema
Check the structure of the _DD_OBJ and _DD_DROPPED_SGMT tables.
SQL> desc _DD_OBJ
COLUMN_NAME TYPE CONSTRAINT
---------------------------------------- ------------------ --------------------
OBJ_ID NUMBER
OWNER_ID NUMBER
NAME VARCHAR(128)
SUBNAME VARCHAR(128)
BO_ID NUMBER
TYPE_NO NUMBER
FLAGS NUMBER
CTIME DATE
MTIME DATE
STIME DATE
RESERVED1 NUMBER
RESERVED2 NUMBER
RESERVED3 NUMBER
RESERVED4 VARCHAR(4000)
INDEX_NAME TYPE COLUMN_NAME
-------------------------------- ------------------------ ----------------------
_DD_OBJ_IDX1 NORMAL OBJ_ID
_DD_OBJ_IDX2 NORMAL OWNER_ID
NAME
SUBNAME
OBJ_ID
_DD_OBJ_IDX3 NORMAL BO_ID
TYPE_NO
OBJ_ID
SQL> desc _DD_DROPPED_SGMT
COLUMN_NAME TYPE CONSTRAINT
---------------------------------------- ------------------ --------------------
SESSID NUMBER
SGMT_ID NUMBER
TS_ID NUMBER
SHDRDBA NUMBER
FLAGS NUMBER
INDEX_NAME TYPE COLUMN_NAME
-------------------------------- ------------------------ ----------------------
_DD_DROPPED_SGMT_IDX1 NORMAL SGMT_ID
_DD_DROPPED_SGMT_IDX2 NORMAL TS_ID
_DD_DROPPED_SGMT_IDX3 NORMAL SESSIDProcedure
Object Creation Test Procedure
Create Table
SQL> create table A(A) as select level from dual connect by level<100000; Table 'A' created.
Check sys._dd_obj, sys._dd_dropped_sgmt
SQL> select * from sys._dd_obj where NAME='A';
OBJ_ID OWNER_ID NAME SUBNAME BO_ID TYPE_NO FLAGS CTIME MTIME STIME RESERVED1 RESERVED2 RESERVED3 RESERVED4
---------- ---------- ------------ ------------ ---------- ---------- --------- -------------- ----------- ----------- ---------- ---------- ---------- ----------
4020 18 A 4294967295 1 0 2025/02/18 2025/02/18 2025/02/18 0 0 0
1 row selected.
SQL> select * from sys._dd_dropped_sgmt;
0 row selected.
Delete Table as tibero User and Check Contents
Drop table A
SQL> drop table A; Table 'A' dropped.
Check data in _dd_obj
SQL> select * from sys._dd_obj where NAME='A'; 0 row selected.
Check _dd_dropped_sgmt
SQL> select * from sys._dd_dropped_sgmt; SESSID SGMT_ID TS_ID SHDRDBA FLAGS ---------- ---------- ---------- ---------- ---------- 91 4020 3 8388681 1 1 row selected.
After some time, confirm that data is deleted from _dd_dropped_sgmt table as well
SQL> select * from sys._dd_dropped_sgmt; 0 row selected.