热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
DM8三种删除数据方式释放空间的时机
培训园地/
文章详情
/
DM8三种删除数据方式释放空间的时机
codePanda
2025/12/11
242
0
0
一、总结
删除数据有三种方式：delete、drop、truncate ，delete属于DML，后两种属于DDL，按照Oracle的经验，drop删除的数据会进回收站，delete清理的页空间可以复用，truncate几乎不会产生undo，空间立即释放
下面是 DM8 的行为总结表：
| 方式 | UNDO 量 | 是否进入回收站 | 空间是否立即释放 | 特性 |
|---|---|---|---|---|
| DELETE | 大量 | 否 | 否 | 行级删除；产生大量 UNDO；清理依赖 UNDO_RETENTION；慢、有碎片 |
| DROP | 少 | 是 | 否 | 表段仅被重命名并挂入回收站；不释放空间；几乎不消耗 undo |
| TRUNCATE (回收站开) | 少 | 是 | 否 | 与 DROP 一样进入回收站，因此不释放空间 |
| TRUNCATE (回收站关) | 少 | 否 | 是 / 几乎立即 | 立即释放 |
DELETE 会在后台逐步回收空间（与 UNDO_RETENTION 相关）；DROP/TRUNCATE 在回收站开启时都不会释放空间；关闭回收站truncate会立即释放空间；
二、创建环境
创建测试表空间 TEST 测试用户 TEST 并授予 DBA 权限，建三张表TEST.tab1，TEST.tab2，TEST.tab3分别放置三张TEST01、TEST02、TEST03表空间用于测试delete、drop、truncate
create tablespace TEST01 datafile '/u01/dmdata/DAMENG/TEST01.DBF' size 150 AUTOEXTEND off;
create tablespace TEST02 datafile '/u01/dmdata/DAMENG/TEST02.DBF' size 150 AUTOEXTEND off;
create tablespace TEST03 datafile '/u01/dmdata/DAMENG/TEST03.DBF' size 150 AUTOEXTEND off;
create user test identified by dm_OPS_123 default tablespace test01;
grant dba to test;
dmdba@DAMENG:5236 SQL> @ls test
TABLESPACE_NAME FILE_ID LS_FILE_NAME EXT LS_MB LS_MAXSIZE
--------------- ----------- ----------------------------- --- ----- ----------
TEST01 0 /u01/dmdata/DAMENG/TEST01.DBF NO 150 NULL
TEST02 0 /u01/dmdata/DAMENG/TEST02.DBF NO 150 NULL
TEST03 0 /u01/dmdata/DAMENG/TEST03.DBF NO 150 NULL
已用时间: 6.216(毫秒). 执行号:266686.
dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 56 4104 2% YES |# |
SYSAUX 1472 1081 391 74% YES |############### |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 1 149 1% NO |# |
TEST02 150 1 149 1% NO |# |
TEST03 150 1 149 1% NO |# |
10 rows got
已用时间: 6.900(毫秒). 执行号:266687.
dmdba@DAMENG:5236 SQL> @us test
Show database usernames from dba_users matching %test%
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE USER_ID CREATED PROFILE
-------- -------------- ------------------ -------------------- ----------- -------------------------- -------
TEST OPEN TEST01 TEMP 50331767 2025-11-24 16:03:17.559063 NULL
已用时间: 1.973(毫秒). 执行号:266688.
create table TEST.tab1(id number,name varchar2(100))STORAGE(on TEST01);
create table TEST.tab2(id number,name varchar2(100))STORAGE(on TEST02);
create table TEST.tab3(id number,name varchar2(100))STORAGE(on TEST03);
insert into TEST.tab1 select rownum,'test'||rownum from dual connect by rownum<=2000000;
insert into TEST.tab2 select rownum,'test'||rownum from dual connect by rownum<=2000000;
insert into TEST.tab3 select rownum,'test'||rownum from dual connect by rownum<=2000000;
COMMIT;
dmdba@DAMENG:5236 SQL> select count(*) from TEST.tab3;
COUNT(*)
--------------------
2000000
已用时间: 5.803(毫秒). 执行号:330212.
dmdba@DAMENG:5236 SQL> select count(*) from TEST.tab2;
COUNT(*)
--------------------
2000000
已用时间: 1.681(毫秒). 执行号:330213.
dmdba@DAMENG:5236 SQL> select count(*) from TEST.tab1;
COUNT(*)
--------------------
2000000
已用时间: 0.254(毫秒). 执行号:330214.
dmdba@DAMENG:5236 SQL>
dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 56 4104 2% YES |# |
SYSAUX 1472 1081 391 74% YES |############### |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 81 69 54% NO |########### |
TEST02 150 81 69 54% NO |########### |
TEST03 150 81 69 54% NO |########### |
10 rows got
已用时间: 60.026(毫秒). 执行号:330215.
dmdba@DAMENG:5236 SQL> @seg TEST.%
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------- ------------- ------------ --------------- -------------------- ----------- -----------
80 TEST INDEX33561639 NULL INDEX TEST01 2560 0 32
80 TEST INDEX33561640 NULL INDEX TEST02 2560 0 32
80 TEST INDEX33561641 NULL INDEX TEST03 2560 0 32
80 TEST TAB1 NULL TABLE TEST01 2560 0 32
80 TEST TAB2 NULL TABLE TEST02 2560 0 32
80 TEST TAB3 NULL TABLE TEST03 2560 0 32
6 rows got
设置回滚参数 UNDO_RETENTION，设置小一点60s，方便观察
dmdba@DAMENG:5236 SQL> @p undo_re
PARA_NAME PARA_VALUE DEFAULT_VALUE SESS_VALUE FILE_VALUE PARA_TYPE PARA_MODULE DESCRIPTION
-------------- ----------- ------------- ----------- ----------- --------- ----------- ----------------------------------------------------------------------------------------
UNDO_RETENTION 3600.000000 90.000000 3600.000000 3600.000000 SYS transaction Maximum retention time in seconds for undo pages since relative transaction is committed
已用时间: 8.972(毫秒). 执行号:266633.
dmdba@DAMENG:5236 SQL> alter system set 'UNDO_RETENTION'=60 both;
DMSQL 过程已成功完成
已用时间: 11.864(毫秒). 执行号:266637.
dmdba@DAMENG:5236 SQL> @p undo_re
PARA_NAME PARA_VALUE DEFAULT_VALUE SESS_VALUE FILE_VALUE PARA_TYPE PARA_MODULE DESCRIPTION
-------------- ---------- ------------- ---------- ---------- --------- ----------- ----------------------------------------------------------------------------------------
UNDO_RETENTION 60.000000 90.000000 60.000000 60.000000 SYS transaction Maximum retention time in seconds for undo pages since relative transaction is committed
dmdba@DAMENG:5236 SQL> select * from dba_recyclebin;
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME PARTITION_NAME BASE_OBJECT
------ ----------- ------------- --------- ----- ------- ------------------- ------------------- -------------- -----------
SYSDBA BIN$1137$0 T2 DROP TABLE MAIN 2025-10-23:11:30:27 2025-11-24:09:46:03 NULL 1137
SYSDBA BIN$1574$0 TABLE_COUNT DROP TABLE MAIN 2025-11-10:17:06:33 2025-11-10:17:10:23 NULL 1574
PANDA BIN$1376$0 T2 DROP TABLE DATA 2025-10-30:11:48:25 2025-11-24:11:14:02 NULL 1376
PANDA BIN$1552$0 FLASH_TEST DROP TABLE DATA 2025-10-31:16:25:12 2025-10-31:16:25:25 NULL 1552
已用时间: 6.019(毫秒). 执行号:266634.
dmdba@DAMENG:5236 SQL> purge dba_recyclebin;
操作已执行
已用时间: 82.002(毫秒). 执行号:266635.
dmdba@DAMENG:5236 SQL>
dmdba@DAMENG:5236 SQL>
dmdba@DAMENG:5236 SQL> select * from dba_recyclebin;
未选定行
三、测试
3.1 delete
dmdba@DAMENG:5236 SQL> delete from test.tab1;
影响行数 2000000
1 #DELETE : [0, 0->0, 0]; table(TAB1), type(select), mpp_opt(0)
2 #PRJT2: [214, 2000000->2000000, 12]; exp_num(1), is_atom(FALSE)
3 #CSCN2: [214, 2000000->2000000, 12]; INDEX33561639(TAB1); btr_scan(1)
Statistics
-----------------------------------------------------------------
2000000 data pages changed
2005790 undo pages changed
6034044 logical reads <<<<< 6百万逻辑读
0 physical reads
376104461 redo size
64 bytes sent to client
87 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000000 rows processed
273 io wait time(ms)
10186 exec time(ms)
已用时间: 00:00:10.180. 执行号:330218.
dmdba@DAMENG:5236 SQL> commit;
操作已执行
3.2drop
drop table test.tab2;
commit;
-- 更新了一堆数据字典信息
dmdba@DAMENG:5236 SQL> drop table test.tab2;
操作已执行
1 #SELECT INTO2: [0, 0->0, 0]
2 #PRJT2: [1, 1->1, 196]; exp_num(1), is_atom(FALSE)
3 #SLCT2: [1, 1->1, 196]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
4 #CSEK2: [1, 1->1, 196]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'TAB2'),('SCHOBJ',150995960,'TAB2')]
1 #UPDATE : [0, 0->0, 0]; table(SYSOBJECTS), type(select)
2 #PRJT2: [1, 1->1, 160]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 1->1, 160]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
4 #CSEK2: [1, 1->1, 160]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'TAB2'),('SCHOBJ',150995960,'TAB2')]
1 #UPDATE : [0, 0->0, 0]; table(SYSOBJECTS), type(select)
2 #NTTS2: [1, 1->0, 160]; for_mdis(FALSE)
3 #PRJT2: [1, 1->1, 160]; exp_num(5), is_atom(FALSE)
4 #SLCT2: [1, 1->1, 160]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
5 #CSEK2: [1, 1->1, 160]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'TAB2'),('SCHOBJ',150995960,'TAB2')]
1 #UPDATE : [0, 0->0, 0]; table(SYSOBJECTS), type(select)
2 #PRJT2: [1, 1->1, 160]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 1->1, 160]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
4 #CSEK2: [1, 1->1, 160]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'BIN$4563$0'),('SCHOBJ',150995960,'BIN$4563$0')]
1 #UPDATE : [0, 0->0, 0]; table(SYSTABLECOMMENTS), type(select)
2 #NTTS2: [1, 1->0, 108]; for_mdis(FALSE)
3 #PRJT2: [1, 1->0, 108]; exp_num(4), is_atom(FALSE)
4 #SSEK2: [1, 1->0, 108]; scan_type(ASC), SYSINDEXTABLECOMMENTSKEYS(SYSTABLECOMMENTS), is_global(0), scan_range[('TEST','TAB2'),('TEST','TAB2')]
1 #UPDATE : [0, 0->1, 0]; table(SYSCOLUMNCOMMENTS), type(select)
2 #NTTS2: [1, 1->0, 156]; for_mdis(FALSE)
3 #PRJT2: [1, 1->0, 156]; exp_num(5), is_atom(FALSE)
4 #SSEK2: [1, 1->0, 156]; scan_type(ASC), SYSINDEXCOLUMNCOMMENTSKEYS(SYSCOLUMNCOMMENTS), is_global(0), scan_range[('TEST','TAB2',min),('TEST','TAB2',max))
1 #DELETE : [0, 0->1, 0]; table(SYSTEXTS), type(select), mpp_opt(0)
2 #PRJT2: [1, 3->0, 80]; exp_num(3), is_atom(FALSE)
3 #NEST LOOP INDEX JOIN2: [1, 3->0, 80]
4 #PRJT2: [1, 1->0, 60]; exp_num(1), is_atom(FALSE)
5 #SLCT2: [1, 1->0, 60]; (SYSOBJECTS.SUBTYPE$ = 'TRIG' AND SYSOBJECTS.INFO1 >> 12&var7 = 1)
6 #BLKUP2: [1, 79->1, 60]; SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS)
7 #SSEK2: [1, 79->1, 60]; scan_type(ASC), SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS), is_global(0), scan_range[(4563,min),(4563,max))
8 #CSEK2: [1, 3, 8]; scan_type(ASC), SYSINDEXSYSTEXTS(SYSTEXTS), scan_range[(DMTEMPVIEW_889433475.colname,min),(DMTEMPVIEW_889433475.colname,max))
1 #DELETE : [0, 0->0, 0]; table(SYSOBJINFOS), type(select), mpp_opt(0)
2 #PRJT2: [1, 3->0, 128]; exp_num(4), is_atom(FALSE)
3 #NEST LOOP INDEX JOIN2: [1, 3->0, 128]
4 #PRJT2: [1, 1->0, 60]; exp_num(1), is_atom(FALSE)
5 #SLCT2: [1, 1->0, 60]; (SYSOBJECTS.SUBTYPE$ = 'TRIG' AND SYSOBJECTS.INFO1 >> 12&var8 = 1)
6 #BLKUP2: [1, 79->1, 60]; SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS)
7 #SSEK2: [1, 79->1, 60]; scan_type(ASC), SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS), is_global(0), scan_range[(4563,min),(4563,max))
8 #CSEK2: [1, 3, 56]; scan_type(ASC), SYSINDEXSYSOBJINFOS(SYSOBJINFOS), scan_range[(DMTEMPVIEW_889433478.colname,min,min),(DMTEMPVIEW_889433478.colname,max,max))
1 #DELETE : [0, 0->0, 0]; table(SYSOBJECTS), type(select), mpp_opt(0)
2 #PRJT2: [1, 1->0, 172]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 1->0, 172]; (SYSOBJECTS.SUBTYPE$ = 'TRIG' AND SYSOBJECTS.INFO1 >> 12&var9 = 1)
4 #BLKUP2: [1, 79->1, 172]; SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS)
5 #SSEK2: [1, 79->1, 172]; scan_type(ASC), SYSINDEXPIDIDSYSOBJECTS(SYSOBJECTS), is_global(0), scan_range[(4563,min),(4563,max))
1 #INSERT : [0, 0->0, 0]; table(RECYCLEBIN$), type(values) mpp_opt(0)
1 #UPDATE : [0, 0->0, 0]; table(SYSOBJECTS), type(select)
2 #PRJT2: [1, 1->1, 164]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 1->1, 164]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
4 #CSEK2: [1, 1->1, 164]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'BIN$4563$0'),('SCHOBJ',150995960,'BIN$4563$0')]
1 #DELETE : [0, 0->0, 0]; table(SYSOBJINFOS), type(select), mpp_opt(0)
2 #PRJT2: [1, 1->1, 68]; exp_num(4), is_atom(FALSE)
3 #CSEK2: [1, 1->1, 68]; scan_type(UNIQUE), SYSINDEXSYSOBJINFOS(SYSOBJINFOS), scan_range[(4563,'ALTTIME',0),(4563,'ALTTIME',0)]
1 #INSERT : [0, 0->0, 0]; table(SYSOBJINFOS), type(values) mpp_opt(0)
Statistics
-----------------------------------------------------------------
20 data pages changed
16 undo pages changed
451 logical reads
0 physical reads
4326 redo size
64 bytes sent to client
86 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
0 io wait time(ms)
117 exec time(ms)
已用时间: 117.232(毫秒). 执行号:330220.
dmdba@DAMENG:5236 SQL> commit;
操作已执行
3.3truncate
经验是 truncate不会产生undo空间立即释放，
truncate table test.tab3;
dmdba@DAMENG:5236 SQL> truncate table test.tab3;
操作已执行
1 #DELETE : [0, 0->0, 0]; table(SYSOBJINFOS), type(select), mpp_opt(0)
2 #PRJT2: [1, 1->0, 68]; exp_num(4), is_atom(FALSE)
3 #CSEK2: [1, 1->0, 68]; scan_type(UNIQUE), SYSINDEXSYSOBJINFOS(SYSOBJINFOS), scan_range[(4564,'TRUNCTIME',0),(4564,'TRUNCTIME',0)]
1 #INSERT : [0, 0->0, 0]; table(SYSOBJINFOS), type(values) mpp_opt(0)
1 #SELECT INTO2: [0, 0->11, 0]
2 #PRJT2: [1, 1->1, 196]; exp_num(1), is_atom(FALSE)
3 #SLCT2: [1, 1->1, 196]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
4 #CSEK2: [1, 1->1, 196]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'TAB3'),('SCHOBJ',150995960,'TAB3')]
1 #UPDATE : [0, 0->0, 0]; table(SYSOBJECTS), type(select)
2 #PRJT2: [1, 1->1, 160]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 1->1, 160]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
4 #CSEK2: [1, 1->1, 160]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'TAB3'),('SCHOBJ',150995960,'TAB3')]
1 #UPDATE : [0, 0->0, 0]; table(SYSOBJECTS), type(select)
2 #NTTS2: [1, 1->0, 160]; for_mdis(FALSE)
3 #PRJT2: [1, 1->1, 160]; exp_num(5), is_atom(FALSE)
4 #SLCT2: [1, 1->1, 160]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
5 #CSEK2: [1, 1->1, 160]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'TAB3'),('SCHOBJ',150995960,'TAB3')]
1 #UPDATE : [0, 0->1, 0]; table(SYSOBJECTS), type(select)
2 #PRJT2: [1, 1->1, 160]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 1->1, 160]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
4 #CSEK2: [1, 1->1, 160]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'BIN$4564$0'),('SCHOBJ',150995960,'BIN$4564$0')]
1 #UPDATE : [0, 0->0, 0]; table(SYSTABLECOMMENTS), type(select)
2 #NTTS2: [1, 1->0, 108]; for_mdis(FALSE)
3 #PRJT2: [1, 1->0, 108]; exp_num(4), is_atom(FALSE)
4 #SSEK2: [1, 1->0, 108]; scan_type(ASC), SYSINDEXTABLECOMMENTSKEYS(SYSTABLECOMMENTS), is_global(0), scan_range[('TEST','TAB3'),('TEST','TAB3')]
1 #UPDATE : [0, 0->0, 0]; table(SYSCOLUMNCOMMENTS), type(select)
2 #NTTS2: [1, 1->0, 156]; for_mdis(FALSE)
3 #PRJT2: [1, 1->0, 156]; exp_num(5), is_atom(FALSE)
4 #SSEK2: [1, 1->0, 156]; scan_type(ASC), SYSINDEXCOLUMNCOMMENTSKEYS(SYSCOLUMNCOMMENTS), is_global(0), scan_range[('TEST','TAB3',min),('TEST','TAB3',max))
1 #INSERT : [0, 0->26, 0]; table(RECYCLEBIN$), type(values) mpp_opt(0)
1 #INSERT : [0, 0->0, 0]; table(SYSTABLECOMMENTS), type(select) mpp_opt(0)
2 #NTTS2: [1, 1->0, 192]; for_mdis(FALSE)
3 #PRJT2: [1, 1->0, 192]; exp_num(4), is_atom(FALSE)
4 #SLCT2: [1, 1->0, 192]; SYSTABLECOMMENTS.TABLE_TYPE = 'TABLE'
5 #BLKUP2: [1, 1->0, 192]; SYSINDEXTABLECOMMENTSKEYS(SYSTABLECOMMENTS)
6 #SSEK2: [1, 1->0, 192]; scan_type(ASC), SYSINDEXTABLECOMMENTSKEYS(SYSTABLECOMMENTS), is_global(0), scan_range[('TEST','BIN$4564$0'),('TEST','BIN$4564$0')]
1 #INSERT : [0, 0->1, 0]; table(SYSCOLUMNCOMMENTS), type(select) mpp_opt(0)
2 #NTTS2: [1, 1->0, 240]; for_mdis(FALSE)
3 #PRJT2: [1, 1->0, 240]; exp_num(5), is_atom(FALSE)
4 #SLCT2: [1, 1->0, 240]; (SYSCOLUMNCOMMENTS.SCHNAME = 'TEST' AND SYSCOLUMNCOMMENTS.TVNAME = 'BIN$4564$0' AND SYSCOLUMNCOMMENTS.TABLE_TYPE = 'TABLE')
5 #CSCN2: [1, 33->33, 240]; SYSINDEXCOLUMNCOMMENTS(SYSCOLUMNCOMMENTS); btr_scan(1)
1 #UPDATE : [0, 0->0, 0]; table(SYSOBJECTS), type(select)
2 #PRJT2: [1, 1->1, 168]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 1->1, 168]; SYSOBJECTS.SUBTYPE$ = 'UTAB'
4 #CSEK2: [1, 1->1, 168]; scan_type(UNIQUE), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[('SCHOBJ',150995960,'TAB3'),('SCHOBJ',150995960,'TAB3')]
Statistics
-----------------------------------------------------------------
142 data pages changed
40 undo pages changed
1283 logical reads
0 physical reads
16727 redo size
64 bytes sent to client
90 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
0 io wait time(ms)
48 exec time(ms)
已用时间: 48.295(毫秒). 执行号:330225.
16:31:15 dmdba@DAMENG:5236 SQL> @p undo_ret
PARA_NAME PARA_VALUE DEFAULT_VALUE SESS_VALUE FILE_VALUE PARA_TYPE PARA_MODULE DESCRIPTION
-------------- ---------- ------------- ---------- ---------- --------- ----------- ----------------------------------------------------------------------------------------
UNDO_RETENTION 60.000000 90.000000 60.000000 60.000000 SYS transaction Maximum retention time in seconds for undo pages since relative transaction is committed
UNDO_RETENTION 空间都没有立即释放，truncate也是为什么呢？
开启 RECYCLEBIN，truncate 进入回收站，因此不可能立即释放空间。
执行 truncate table test.tab3; 后，RECYCLEBIN 中出现 TAB3 的 TRUNCATE 记录，表段被重命名为 BIN$xxxx$0，原 TAB3 段大小（80MB）并未释放。
而段信息中有TAB3也就是说truncate 做了两个动作一是将老表 TAB3 → 回收站变成 BIN$xxxx$0 （段不释放）二是新建一个同名 TAB3（空表）
16:32:38 dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 143 4017 4% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 81 69 54% NO |########### |
TEST02 150 81 69 54% NO |########### |
TEST03 150 82 68 55% NO |########### |
10 rows got
已用时间: 0.810(毫秒). 执行号:335044.
16:32:11 dmdba@DAMENG:5236 SQL> @seg TEST.%
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------- ------------- ------------ --------------- -------------------- ----------- -----------
80 TEST BIN$4646$0 NULL TABLE TEST02 2560 0 32
80 TEST BIN$4648$0 NULL TABLE TEST03 2560 0 32
80 TEST INDEX33561789 NULL INDEX TEST01 2560 0 32
80 TEST INDEX33561790 NULL INDEX TEST02 2560 0 32
80 TEST INDEX33561791 NULL INDEX TEST03 2560 0 32
1 TEST INDEX33561793 NULL INDEX TEST03 32 0 2592
80 TEST TAB1 NULL TABLE TEST01 2560 0 32
1 TEST TAB3 NULL TABLE TEST03 32 0 2592
8 rows got
已用时间: 140.737(毫秒). 执行号:335046.
16:32:22 dmdba@DAMENG:5236 SQL>
16:32:23 dmdba@DAMENG:5236 SQL> select * from dba_recyclebin;
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME PARTITION_NAME BASE_OBJECT
----- ----------- ------------- --------- ----- ------- ------------------- ------------------- -------------- -----------
TEST BIN$4646$0 TAB2 DROP TABLE TEST02 2025-11-24:16:30:44 2025-11-24:16:31:58 NULL 4646
TEST BIN$4648$0 TAB3 TRUNCATE TABLE TEST03 2025-11-24:16:32:06 2025-11-24:16:32:06 NULL 4648
已用时间: 2.530(毫秒). 执行号:335047.
16:32:36 dmdba@DAMENG:5236 SQL>
drop 、truncate全部进了回收站没有释放，delete的过一段时间（>>UNDO_RETENTION开始清理空间）观察TEST01开始逐步释放空间。
16:32:41 dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 143 4017 4% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 81 69 54% NO |########### |
TEST02 150 81 69 54% NO |########### |
TEST03 150 82 68 55% NO |########### |
10 rows got
已用时间: 17.080(毫秒). 执行号:335049.
16:32:58 dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 143 4017 4% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 69 81 46% NO |########## |
TEST02 150 81 69 54% NO |########### |
TEST03 150 82 68 55% NO |########### |
10 rows got
已用时间: 53.631(毫秒). 执行号:335050.
16:33:01 dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 143 4017 4% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 45 105 30% NO |###### |
TEST02 150 81 69 54% NO |########### |
TEST03 150 82 68 55% NO |########### |
10 rows got
已用时间: 11.095(毫秒). 执行号:335051.
16:33:05 dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 143 4017 4% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 30 120 20% NO |#### |
TEST02 150 81 69 54% NO |########### |
TEST03 150 82 68 55% NO |########### |
10 rows got
已用时间: 13.806(毫秒). 执行号:335052.
16:33:07 dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 143 4017 4% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 19 131 13% NO |### |
TEST02 150 81 69 54% NO |########### |
TEST03 150 82 68 55% NO |########### |
10 rows got
已用时间: 8.414(毫秒). 执行号:335053.
16:33:08 dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 143 4017 4% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 18 132 12% NO |### |
TEST02 150 81 69 54% NO |########### |
TEST03 150 82 68 55% NO |########### |
10 rows got
已用时间: 12.951(毫秒). 执行号:335054.
16:33:09 dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 143 4017 4% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 18 132 12% NO |### |
TEST02 150 81 69 54% NO |########### |
TEST03 150 82 68 55% NO |########### |
10 rows got
清空回收站后，空间不到60s就释放了，所以说 UNDO_RETENTION 并不准确前后都有可能
16:42:09 dmdba@DAMENG:5236 SQL> purge dba_recyclebin;
操作已执行
已用时间: 74.346(毫秒). 执行号:335086.
16:42:10 dmdba@DAMENG:5236 SQL> select * from dba_recyclebin;
未选定行
已用时间: 0.786(毫秒). 执行号:335087.
16:42:16:43:15 dmdba@DAMENG:5236 SQL> @seg TEST.%
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------- ------------- ------------ --------------- -------------------- ----------- -----------
17 TEST INDEX33561789 NULL INDEX TEST01 544 0 32
1 TEST INDEX33561793 NULL INDEX TEST03 32 0 2592
17 TEST TAB1 NULL TABLE TEST01 544 0 32
1 TEST TAB3 NULL TABLE TEST03 32 0 2592
已用时间: 106.125(毫秒). 执行号:335098. dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 69 4091 2% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 4 1020 1% YES |# |
TEST01 150 18 132 12% NO |### |
TEST02 150 1 149 1% NO |# |
TEST03 150 2 148 2% NO |# |
10 rows got
16:43:15 dmdba@DAMENG:5236 SQL> @seg TEST.%
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------- ------------- ------------ --------------- -------------------- ----------- -----------
17 TEST INDEX33561789 NULL INDEX TEST01 544 0 32
1 TEST INDEX33561793 NULL INDEX TEST03 32 0 2592
17 TEST TAB1 NULL TABLE TEST01 544 0 32
1 TEST TAB3 NULL TABLE TEST03 32 0 2592
已用时间: 106.125(毫秒). 执行号:335098.
3.4 关闭回收站测试，truncate是否立即释放
RECYCLEBIN：0-关闭 1-开启回收站支持drop，2-支持truncate SP_SET_PARA_VALUE(2,'RECYCLEBIN',2)需要重启
RECYCLEBIN_RETENTION：回收站保留时间
UNDO_RETENTION：commit的事务在undo段的保留时间
reate tablespace TEST04 datafile '/u01/dmdata/DAMENG/TEST04.DBF' size 150 AUTOEXTEND off;
create table TEST.tab4(id number,name varchar2(100))STORAGE(on TEST04);
insert into TEST.tab4 select rownum,'test'||rownum from dual connect by rownum<=2000000;
16:46:58 dmdba@DAMENG:5236 SQL> alter system set 'RECYCLEBIN'=0 spfile;
DMSQL 过程已成功完成
已用时间: 8.917(毫秒). 执行号:353005.
16:47:04 dmdba@DAMENG:5236 SQL> @p RECYCLEBIN
PARA_NAME PARA_VALUE DEFAULT_VALUE SESS_VALUE FILE_VALUE PARA_TYPE PARA_MODULE DESCRIPTION
-------------------- ---------- ------------- ---------- ---------- --------- ----------- ----------------------------------------------------
RECYCLEBIN 2 0 2 0 IN FILE transaction Whether to enable recycle bin
RECYCLEBIN_RETENTION 3600 0 3600 3600 SYS transaction Maximum retention time of objects in the recycle bin
[root@panda101:/root]# systemctl restart DmServiceDAMENG.service
关闭回收站truncate立即释放空间
-- truncate table test.tab4;
dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 17 4143 1% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 3 1021 1% YES |# |
TEST01 150 18 132 12% NO |### |
TEST02 150 1 149 1% NO |# |
TEST03 150 2 148 2% NO |# |
TEST04 150 81 69 54% NO |########### |
11 rows got
已用时间: 22.253(毫秒). 执行号:607.
dmdba@DAMENG:5236 SQL> @seg TEST.TAB4
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------ ------------- ------------ --------------- -------------------- ----------- -----------
80 TEST TAB4 NULL TABLE TEST04 2560 0 32
已用时间: 978.068(毫秒). 执行号:608.
dmdba@DAMENG:5236 SQL> truncate table test.tab4;
操作已执行
已用时间: 22.540(毫秒). 执行号:609.
dmdba@DAMENG:5236 SQL> dmdba@DAMENG:5236 SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
--------------- ------- ------ ------ ------ --- ----------------------
BENCHMARK 1000 487 513 49% NO |########## |
DATA 128 60 68 47% NO |########## |
MAIN 256 99 157 39% YES |######## |
ROLL 4160 22 4138 1% YES |# |
SYSAUX 1472 1164 308 80% YES |################ |
SYSTEM 276 179 97 65% YES |############# |
TEMP 1024 3 1021 1% YES |# |
TEST01 150 18 132 12% NO |### |
TEST02 150 1 149 1% NO |# |
TEST03 150 2 148 2% NO |# |
TEST04 150 2 148 2% NO |# |<<<< 关闭回收站立即释放
11 rows got
四、社区地址
更多达梦技术分享：
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1