oracle的truncate table的操作

本文介绍了Oracle数据库中TRUNCATE TABLE操作的独特实现方式。通过实际案例演示了该操作仅更新数据字典而不直接操作数据的特点,展示了TRUNCATE TABLE执行速度快的原因。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

 

oracle对于truncate table的操作有着自己的独特做法,truncate一个表几十条数据和千万条数据所用的时间都差不多,

说穿了oracle对这类操作只做数据字典的更新,并不对实际数据操作,只需要拉回HWM位置。

 

sys@ORCL>  create table big_table as select * from all_objects;

 

Table created.

 

sys@ORCL> insert into big_table select * from big_table;

 

68480 rows created.

 

sys@ORCL> commit;

 

Commit complete.

 

sys@ORCL> select count(*) from big_table;

 

  COUNT(*)

----------

    136960

 

 

sys@ORCL> select OBJECT_ID  ,DATA_OBJECT_ID  from dba_objects where owner='SYS' and object_name='BIG_TABLE';

 

 OBJECT_ID DATA_OBJECT_ID

---------- --------------

     71485          71485

 

sys@ORCL> select dbms_rowid.ROWID_OBJECT(rowid) from big_table where rownum<10;

 

DBMS_ROWID.ROWID_OBJECT(ROWID)

------------------------------

                         71485

                         71485

                         71485

                         71485

                         71485

                         71485

                         71485

                         71485

                         71485

 

9 rows selected.

 

 

sys@ORCL>  alter session set events '10046 trace name context forever, level 12';

 

Session altered.

 

sys@ORCL> truncate table big_table;

 

Table truncated.

 

sys@ORCL> alter session set events '10046 trace name context off';

 

Session altered.

 

再查一下数据字典,发现data_object_id已经变化

 

sys@ORCL> select OBJECT_ID  ,DATA_OBJECT_ID  from dba_objects where owner='SYS' and object_name='BIG_TABLE';

 

 OBJECT_ID DATA_OBJECT_ID

---------- --------------

     71485          71486

 

 

找到对应的trace文件:

 

sys@ORCL> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum<=1));

 

sys@ORCL> show parameter user_dump

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/orc

                                                 l/orcl/trace

 

truncate table big_table

....

update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1

....

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3

...

update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)

....

 

 

 

<think>嗯,用户的问题是关于Oracle数据库中执行truncate table后如何真正释放空间。从用户补充的"我想在使用Oracle数据库时,执行truncate操作时如何进行清理"这个表述来看,用户应该是个DBA或者开发人员,遇到了实际的空间回收问题。 用户可能已经知道truncate会标记空间但不会立即释放给操作系统,现在需要具体的操作方案。从ta特意强调"清理释放空间"来看,可能遇到了存储空间紧张的实际问题,情绪上应该比较急切。 参考站内资料很有意思,第一个引用解释了truncate不释放空间的设计原理,第二个引用展示了数据字典查询方法。不过用户需要的是解决方案而非原理说明。需要重点整理空间释放的具体操作路径。 关于技术细节:在Oracle中,truncate的高效性正是源于不释放空间的设计,但用户显然需要突破这个默认行为。核心方案其实就三种:移动表、收缩段、重建表。其中移动表方案最彻底但需要停业务;收缩段最实用但需要启用行移动;重建表适合小表但会失效索引。 特别要注意的是LOB字段这个坑,用户没提但必须预警,因为LOB段独立存储,很多DBA会忽略这点。另外ASSM管理下收缩段才有效,这点也要说明。 用户可能还想知道验证方法,所以补充了dba_segments视图的查询语句。最后用"立即释放"和"渐进释放"来区分方案特性,这样用户可以根据业务场景选择。 相关问题设计成阶梯状:先问原理(为什么没释放),再问操作(怎么释放),最后问特殊情况(LOB段),这样覆盖了用户可能的所有后续疑问。</think>在 Oracle 数据库中,执行 `TRUNCATE TABLE` 命令会高效删除表中所有数据,但它**默认不会立即将占用的空间释放回操作系统**,而是将空间标记为可重用,供该表后续的插入操作使用。这是为了提升性能的设计。若需立即释放空间给操作系统或表空间,需额外操作: ### 📌 释放空间的核心方法 1. **移动表 (`MOVE TABLESPACE`)** ```sql ALTER TABLE 表名 MOVE TABLESPACE 原表空间名; ``` * **原理**:在同一个表空间内移动表,相当于重建表结构并迁移数据(此时无数据),释放原有段空间。 * **效果**:立即释放空间给表空间(若表空间是自动扩展管理的,可能进一步释放给OS)。 * **注意**: * 会**使依赖该表的索引失效**,需重建索引 (`ALTER INDEX ... REBUILD`)。 * 表在操作期间会被锁定。 * 需要足够的空闲空间完成移动操作。 2. **收缩段 (`SHRINK SPACE`) - 推荐 (需满足条件)** ```sql -- 启用行移动 (必需) ALTER TABLE 表名 ENABLE ROW MOVEMENT; -- 收缩表段 (释放空间给表空间) ALTER TABLE 表名 SHRINK SPACE; -- 或 收缩表段并级联压缩相关对象 (如LOB段、索引) ALTER TABLE 表名 SHRINK SPACE CASCADE; -- 或 压缩空间但不释放HWM (准备阶段) ALTER TABLE 表名 SHRINK SPACE COMPACT; -- 或 压缩后释放HWM (释放阶段) ALTER TABLE 表名 SHRINK SPACE; ``` * **原理**:压缩数据块,移动行以降低高水位线 (High Water Mark, HWM),释放未使用的块。 * **效果**:释放空间给表空间。是更精细、影响更小的操作。 * **条件**: * 表必须位于使用**自动段空间管理 (ASSM)** 的表空间中(最常见)。 * 必须先执行 `ENABLE ROW MOVEMENT`。 * 操作期间会加锁,但允许并发DML(在压缩阶段可能需要短暂排他锁)。 * 对LOB列效果有限,需用 `CASCADE` 或单独处理LOB段。 3. **重建表 (`DROP` + `CREATE`)** ```sql -- 1. 备份表结构 (可选) CREATE TABLE 表名_backup AS SELECT * FROM 表名 WHERE 1=0; -- 2. 删除原表 DROP TABLE 表名; -- 3. 重建表 CREATE TABLE 表名 ... ; -- 使用原定义 ``` * **原理**:彻底删除表及其段,然后重建空表。 * **效果**:立即释放所有空间给表空间/操作系统。 * **注意**: * 操作期间表完全不可用。 * 所有权限、触发器、约束、索引等**丢失**,需手动重建。 * **风险最高**,仅在其他方法无效或表结构简单时考虑。 ### 📊 验证空间释放效果 执行释放操作后,查询数据字典确认: ```sql SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB, BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = '表名' AND OWNER = '表所属用户'; ``` 对比操作前后的 `SIZE_MB` 和 `BLOCKS` 值即可[^2]。 ### ⚠ 关键注意事项 * **LOB 字段**:若表包含 `CLOB`, `BLOB` 等大对象字段,其数据存储在独立的 **LOB段** 中。`TRUNCATE TABLE` 或上述方法通常**不会自动释放LOB段空间**。需单独处理: ```sql ALTER TABLE 表名 MODIFY LOB (lob_column_name) (SHRINK SPACE); -- 或使用 MOVE 时指定 LOB 存储 ALTER TABLE 表名 MOVE LOB (lob_column_name) STORE AS (TABLESPACE ...); ``` * **高水位线 (HWM)**:`TRUNCATE` 会重置 HWM,但空间仍在段内。`MOVE` 和 `SHRINK` 都能降低 HWM 释放空间。 * **表空间管理**:释放的空间首先归还给表空间。若表空间由数据文件组成且启用了 `AUTOEXTEND`,操作系统空间是否释放取决于数据库和文件系统的管理策略。使用 `MOVE` 或 `DROP/CREATE` 更可能触发文件收缩(若支持且配置允许)。 * **索引**:`MOVE` 操作会使索引失效,必须重建。`SHRINK SPACE CASCADE` 可自动重建索引。 ### 📝 总结建议 1. **优先尝试 `SHRINK SPACE`**: * 启用行移动 (`ENABLE ROW MOVEMENT`)。 * 执行 `ALTER TABLE ... SHRINK SPACE [CASCADE];`。影响最小,适合在线操作。 2. **空间需求大且可接受短时锁定** → 使用 `ALTER TABLE ... MOVE`。完成后重建索引。 3. **LOB字段单独处理** → 使用 `ALTER TABLE ... MODIFY LOB ... (SHRINK SPACE)` 或 `MOVE` 时指定 LOB。 4. **极端情况或小表** → 考虑 `DROP` + `CREATE`(需重建所有依赖对象)。 5. **定期维护**:对大表频繁删除/插入操作,定期使用 `SHRINK` 或 `MOVE` 管理空间碎片。 > **立即释放**:`MOVE` 和 `DROP/CREATE` 效果最直接。 > **渐进释放**:`SHRINK SPACE` 更灵活,适合生产环境[^1][^2]。 --- ### ❓相关问题 1. `TRUNCATE TABLE` 与 `DELETE` 在空间释放上有何本质区别? 2. Oracle 中的高水位线 (HWM) 是什么?它对表空间管理有什么影响? 3. 如何处理包含 LOB 字段的大表空间回收问题? 4. 自动段空间管理 (ASSM) 如何优化 Oracle 的存储性能? 5. 除了 `TRUNCATE`,哪些操作会导致 Oracle 表产生大量空间碎片?如何修复?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值