大量使用delete,久而久之会造成表空间越来越大,不管怎么delete删除数据表空间还是没有变化。这里讲表的碎片整理,而不是通过重建表来整理出空闲表空间,毕竟上亿的数据要重建,似乎难度太大。
项目上都会记录日志,对于诊断 or 测试类的项目, 记录的日志比较多, 所需要的表空间也是比较大。对于含有大数据字段(clob)的日志所需要的表空间就更加多了。
我在项目上遇到,有两个日志表,a表保存90天在用日志,b表保存一年日志。每天会产生200W-400W不等的日志。每天会使用定时任务将90天前的日志从a转移到b,所使用的是delete - insert.随着时间的增加,表空间也不堪重负,多次达到99%。多次增加表空间,并不能解决问题。最开始想通过重建a表来解决,发现上亿数据的表想重建?呵呵,就算重建也要花很长时间。所以打消这个念头吧(create truncate),我重建了两天都没完成重建,而且还是加了16个进程来重建的表。
不废话了,直接说怎么解决,然后再说[delete drop truncate]有什么区别。
【表碎片整理回收】10g版本以上
ALTER TABLE tableName ENABLE ROW MOVEMENT; ---启动行移动功能
ALTER TABLE tableName SHRINK SPACE COMPACT; ---只整理碎片,不回收空间
---重置高水位,此时tableName不能有DML操作
ALTER TABLE tableName SHRINK SPACE; ---整理碎片并回收空间,并调整水位线。业务少时执行
ALTER TABLE tableName DISABLE ROW MOVEMENT; ---关闭行移动
---亲测效果极佳。只不过执行了很长时间。建议只用PLSQL工具执行
---表空间回收建议:创建一张表来替换在用表。保证该表没有其他操作之后再执行以上语句。
【truncate drop delete区别】
truncate与不带where的delete :只删除数据,而不删除表的结构。效率很快,基本上不管多少数据都是几秒钟给你删除完。
delete语句为DML(data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。频繁的使用delete删除数据,会产生大量的碎片,这些碎片除了占用原有的空间,还不能重用!也许有一天你会发现,咦! 这个表怎么这么大?delete删除了数据,这个表还是那么大!!! 那是因为delete的数据只是被标记删除。它还在那里占用着你的表空间。
drop语句将删除表的结构被依赖的约束,触发器,索引;依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。
truncate、drop是DLL(data define language),操作立即生效,原数据不放到 rollback segment中,不能回滚。所以没有备份数据的时候,老司机一般都会跟你说谨慎使用drop 和 truncate!!!