如何保证truncate table 能正常运行？

apusic 2002-10-11 02:59:25

各位好：

我要定时清空一个表中的内容，就利用truncate table 语句，速度快嘛！但我在执行该语句时出现：
ORA-00054: resource busy and acquire with NOWAIT specified
原因是在执行的同时也有数据插入。

现在请教各位高手，如何保证truncate table 能正常运行？
或者有没有更好的办法来实现？？

...全文

185 8 打赏收藏转发到动态举报

写回复

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

feeling_68 2002-10-11

打赏
举报

回复

“定时清空一个表中的内容”应定一个不可能做表的数据变更的时间，譬如，凌晨几点。。。

developer2002 2002-10-11

打赏
举报

回复

显然表已经被锁住了。

jlandzpa 2002-10-11

打赏
举报

回复

有人还在使用该表.

yuxuan 2002-10-11

打赏
举报

回复

如果表没有被锁定，应该可以truncate table的

hushuangyang 2002-10-11

打赏
举报

回复

设计思路不对！
请把详细情况说清楚！

三杯倒 2002-10-11

打赏
举报

回复

1。表改名
2。建源表
3。drop新表

可能部分数据存不进去

pure_cmh 2002-10-11

打赏
举报

回复

同意楼上，应该在清空时保证该表没有被其他锁锁住

zhaoyongzhu 2002-10-11

打赏
举报

回复

不会吧，你说“定时清空一个表中的内容”，那么在要清空的时候怎么还能在存入数据那？？？

不小心Truncate表的事情也是有的, 其中大部份时因为工具连错了库, 从儿跑错了角本. 遇到这种事情而没有备份时怎么办呢? 首先要停止数据库, 将这个表所在的表空间的文件拷贝出来, 因为Oracle在Truncate只时将相应Segment的第一个块格式化掉了, 而后面的都还存在, 到下次用时到才真正地重新格式化. 下面来讲一个Truncate表后进行恢复的例子: SQL> CREATE TABLE T_TRUNCATE AS SELECT * FROM TAB; Table created. SQL> SELECT COUNT(*) FROM T_TRUNCATE; COUNT(*) ---------- 14 SQL> ALTER SYSTEM CHECKPOINT; System altered. SQL> TRUNCATE TABLE T_TRUNCATE; Table truncated. SQL> ALTER SYSTEM CHECKPOINT; System altered. 在Truncate时只是Segment Header格式化了, 并将Data Object ID换成一个新的值, 我们可以在AUL中用DESC命令来查看: AUL> desc anysql.t_truncate Storage(OBJ#=9976 OBJD=9977 TS=4 FILE=4 BLOCK=5235 CLUSTER=0) No. SEQ INT Column Name Type --- --- --- ----------------------------- ---------------- 1 1 1 TNAME VARCHAR2(30) NOT NULL 2 2 2 TABTYPE VARCHAR2(7) 3 3 3 CLUSTERID NUMBER 要恢复这个表的数据, 首先要在AUL中运行SCAN EXTENT命令, 因为Segment Header被格式化了, 所以Extent Map也可能丢失, 而Scan Extent则将扫描整个数据文件并将Extent分配信息写入AULEXT.TXT文件: AUL> SCAN EXTENT FILE 4 2006-12-18 21:32:10 2006-12-18 21:32:24 恢复的关键是要获得这个表原来的Data Object ID, 在这个例子中我在Truncate表后什么也没有做就关闭数据库进行恢复了. 从上面的DESC命令可以看出表的Segment Header是(4,5235), 而新的Data Object ID是9977, 老的Data Object ID我们可以从Segment Header的后面一个数据块中得到, 如果这个表有几个Free List Group, 则可能还要再后面几个块. 用AUL的ORADUMP命令来看一下后面一个块: AUL> ORADUMP FILE 4 BLOCK 5236 RDBA=0x01001474(4/5236)=16782452,type=0x06,fmt=0xa2,seq=0x02,flag=0x04 seg/obj=0x000026f8=9976,csc=0x0000.0006caf5,itc=3,typ=1 - DATA FLG=0x32, fls=0, nxt=0x01001471(4/5233)=16782449 ...... 可以看到原来的Data Object ID是9976, 现在可以恢复了, 先不指定原来的Data Object ID试试? AUL> unload table anysql.t_truncate; 2006-12-18 21:33:37 Unload OBJD=9977 FILE=4 BLOCK=5235 CLUSTER=0 ... 2006-12-18 21:33:37 接下来指定原来的Data Object ID, 再试试? AUL> unload table anysql.t_truncate object 9976; 2006-12-18 21:33:45 Unload OBJD=9976 FILE=4 BLOCK=5235 CLUSTER=0 ... P_MV_FACT_SALES|TABLE TIME_DIM|TABLE FACT_SALES|TABLE MV_FACT_SALES|TABLE SEG$|TABLE NUMTEST|TABLE T_OBJECTS|TABLE T_LOBTEST|TABLE T_INCLOB|TABLE CF_XXK|TABLE T_TESTDMP|TABLE T_CLOBDEMO|TABLE T_BLOBDEMO|TABLE T_TRUNCATE|TABLE 2006-12-18 21:33:45 可以看到14条数据全回来了, 当然数据库是复杂的, 如果是一个很大的表, 还是不能保证可以100%恢复的. 最近至少看到二次错误地截断(Truncate)表的例子, 并在网上询问如何恢复, 在这儿我给出AUL/MyDUL的解决方案, 下面是我用的一个测试表: ASQL> DESC TRUNCDEMO NO# NAME NULLABLE TYPE --- ----------------- -------- ------------ 1 COL1 VARCHAR2(20) ASQL> SELECT * FROM TRUNCDEMO; COL1 ----- ROW 1 ROW 2 2 rows returned. 接下来我们来截断表, 其实这个操作只是重新格式化了段头块(Segment Header), 并分配一个新的数据对象号(Data Object ID), 当然空间分配信息也改了, 除非加了重用空间选项(Reuse Storage). 来看一下这个操作的前后变化: ASQL> SELECT DATA_OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS; DATA_OBJECT_ID OBJECT_NAME -------------- ----------- 13676 TRUNCDEMO 1 rows returned. ASQL> truncate table truncdemo; Truncate Table Succeed. ASQL> SELECT DATA_OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS; DATA_OBJECT_ID OBJECT_NAME -------------- ----------- 13677 TRUNCDEMO 1 rows returned. 由于在System表空间中已经记录了新的信息, 因此用当前的System信息是不能恢复过来的,在AUL/MyDUL中可以当作没有System时的情况来处理,在下面的命令中, 我们用Truncate后的数据对象号就不能进行恢复, 而使用Truncate以前的就可以, 当然空间不能被重新利用了是恢复的前提. AUL> unload object 13676 column varchar file 4; 2006-09-18 22:38:58 ROW 1 ROW 2 2006-09-18 22:39:04 AUL> unload object 13677 column varchar file 4; 2006-09-18 22:39:10 2006-09-18 22:39:10 AUL> 因此在意外发生Truncate后, 如果没有备份可以恢复, 首先要做的事是备份一下当前的文件, 免得空间被重用. 而Truncate之前的数据对象号在AUL/MyDUL中是很容易找出来的. 到此已经说明了如何恢复Truncate表了. 跟据原理可以创建一个恢复包Recover_Truncate_Data，然后我们可以做个实验进行验证恢复效果如何：第一步：创建表 create table truntab1 as select * from dba_objects; 第二步：查询表中记录数 select count(*) from truntab1; --72622 第三步：truncate表中业务数据 truncate table truntab1; 第四步：确认表中记录数为零 select count(*) from truntab1; -- 0 第五步：设置恢复前环境变量 set serveroutput on size 10000000 --//设置大点,默认为2000 bytes exec dbms_output.enable(999999999999999999999); --//默认为2000 bytes 注意：如果不不进行设置，为报PLSQL ORA-20000: ORU-10027: buffer overflow, limit of 10000 第六步：实施truncate表中数据恢复 declare tgtowner varchar2(30); tgttable varchar2(30); datapath varchar2(4000); datadir varchar2(30); rects varchar2(30); recfile varchar2(30); rstts varchar2(30); rstfile varchar2(30); blksz number; rectab varchar2(30); rsttab varchar2(30); copyfile varchar2(30); begin tgtowner := 'SYS'; --指定表名的属用户 tgttable := 'TRUNTAB1'; --指定需要恢复的表名 datapath := 'D:\app\Administrator\oradata\lmis\'; --数据文件所在位置 datadir := 'FY_DATA_DIR'; Recover_Truncate_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz); Recover_Truncate_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile); Recover_Truncate_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz); end; 第七步：查看输出内容和构造表名： 15:32:44: Directory Name: FY_DATA_DIR4 15:32:45: Recover Tablespace: FY_REC_DATA4; Data File: FY_REC_DATA4.DAT 15:32:46: Restore Tablespace: FY_RST_DATA4; Data File: FY_RST_DATA4.DAT 15:32:48: Recover Table: SYS.TRUNTAB1$2 15:32:48: Restore Table: SYS.TRUNTAB1$$2 15:33:04: [fill_blocks] Data Blocks formatted. 15:33:05: [copy_file] begin copy file: FY_DATA_DIR4\FY_REC_DATA4.DAT => FY_DATA_DIR4\FY_REC_DATA_COPY.DAT 15:33:05: [copy_file] completed. 15:33:05: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT 15:33:05: begin to recover table SYS.TRUNTAB1 15:33:19: [restore_table] Trying to restore data to SYS.TRUNTAB1$$2 15:33:20: [restore_table] Expected Records in this round: 411 15:33:20: [restore_table] 411 records recovered 此处省略N行输出............................................ 15:33:44: [restore_table] Expected Records in this round: 0 15:33:44: [restore_table] 0 records recovered 15:33:44: 1033 truncated data blocks found. 15:33:44: 72622 records recovered in backup table SYS.TRUNTAB1$$2 15:33:44: Recovery completed. PL/SQL procedure successfully completed 从红色字体可以看出，恢复72622条，刚好是truncate前业务表中记录数，恢复临时表为：SYS.TRUNTAB1$$2 第七步：查看输出内容和构造表名： insert into truntab1 select * from SYS.TRUNTAB1$$2 第八步：验证数据是否完全恢复 select count(*) from truntab1; --72622 至此，truncate掉的数据成功恢复，并且此方法也可以恢复drop table tablename purge删除的数据，第九步：清理恢复产生的表空间和数据文件特别提醒：恢复完成后，该方法会在数据库中产生一个表空间：FY_RST_DATA*，恢复一次产生一个，记得及时清理！否则会导致服务器RMAN备份失败ORA-19566 超出损坏块限制（切记） truncate原理： ? ? ? ?TRUNCATE不会逐个清除用户数据块上的数据，而仅仅重置数据字典和元数据块上的元数据（如存储段头和扩展段图）。也就是说，此时，其基本数据并未被破坏，而是被系统回收、等待被重新分配————因此，要恢复被TRUNCATE的数据，需要及时备份其所在的数据文件。 ? ? 方法：用存储过程包Fy_Recover_Data ? ? 它是利用Oracle表扫描机制、数据嫁接机制恢复TRUNCATE或者损坏数据的工具包，这个包是由行内有影响力的DBA大师黄炜先生通过PLSQL编写的，再这里再次感谢他的无私技术分享。Fy_Recover_Data去本文附近中下载好了，闲话少说，下面通过oracle数据库中scott用户自带的emp表做测试：步骤1：先把Fy_Recover_Data包拷贝到oracle相关目录下步骤2：在scott用户下创建test_emp表： SQL> conn scott/tiger; Connected. SQL> select * from tab; TNAME ? ? ? TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS ? ? ? TABLE DEPT ? ? ? TABLE EMP ? ? ? ? ? ? ? ?TABLE SALGRADE ? ? ? TABLE SQL> select count(*) from emp; ? COUNT(*) ---------- 14 SQL> create table test_emp ?as select * from emp; Table created. SQL> select count(*) from test_emp; ? COUNT(*) ---------- 14 步骤3：用truncate删除test_emp表： SQL> truncate table test_emp; Table truncated. SQL> select count(*) from test_emp; ? COUNT(*) ---------- 0 步骤4：在linux中的oracle用户下解压FY_Recover_Data.zip包 $ unzip FY_Recover_Data.zip Archive: ?FY_Recover_Data.zip ? inflating: FY_Recover_Data.SQL? 步骤5：恢复 1）在sys用户下执行存储过程 SQL> @/home/oracle/FY_Recover_Data.SQL Package created. Package body created. 2）查看test_emp表在数据文件中的目录 SQL> select file_name from dba_data_files f, dba_tables t where t.owner='SCOTT' and t.table_name='TEST_EMP' and t.tablespace_name = f.tablespace_name; FILE_NAME -------------------------------------------------------------------------------- /u03/oracle/oradata/WUTONG/datafile/o1_mf_users_cx3xt940_.dbf 3）通过脚本恢复，可以用sqlplus命令行或者plsql developer执行 declare ? ? ? tgtowner varchar2(30); ? ? ? tgttable varchar2(30); ? ? ? datapath varchar2(4000); ? ? ? datadir varchar2(30); ? ? ? rects varchar2(30); ? ? ? recfile varchar2(30); ? ? ? rstts varchar2(30); ? ? ? rstfile varchar2(30); ? ? ?blksz number; ? ? ?rectab varchar2(30); ? ? ?rsttab varchar2(30); ? ? ?copyfile varchar2(30); ? ?begin ? ? ?tgtowner := 'SCOTT'; --table owner ? ? ?tgttable := 'TEST_EMP'; ?--table name ? ? ?datapath := '/u03/oracle/oradata/WUTONG/datafile/'; ? ?--必须和test.t1表所在的数据文件的目录相同 ? ? ?datadir := 'FY_DATA_DIR'; ? ? ? ?--oracle中目录的名字，可以修改 ? ? ?Fy_Recover_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz); ? ? ?Fy_Recover_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile); ? ? ?Fy_Recover_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz); ? ?end; ? ?以上SQL脚本产生2个表空间(2个数据文件)，还有1个copy文件。 4）切换到scott用户下查看会发现多了些不一样以test_emp的表，这时找到相关有数据的表，把数据插入原表test_emp SQL> conn scott/tiger Connected. SQL> select * from tab; TNAME ? ? ? TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS ? ? ? TABLE DEPT ? ? ? TABLE EMP ? ? ? TABLE SALGRADE ? ? ? TABLE TEST_EMP ? ? ? TABLE TEST_EMP$ ? ? ? TABLE TEST_EMP$$ ? ? ? TABLE 7 rows selected. SQL> insert into test_emp select * from TEST_EMP$$; 14 rows created. SQL> commit; Commit complete. SQL> select count(*) from test_emp; ? COUNT(*) ---------- 14 当你看到这一步的时候，说明truncate的表已经完全恢复了，恭喜你数据恢复成功！紧张的压力随之而释放，脸上露出灿烂的笑容和自豪感（做DBA很辛苦，数据库能保持正常运行，DBA在幕后做了大量的工作，有时是不会不被公司其他人理解的。。。。。）步骤6：恢复数据后，把恢复时产生的2个表空间删除，再删除对应数据文件 SQL> conn / as sysdba Connected. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u03/oracle/oradata/WUTONG/datafile/o1_mf_system_cx3xt90z_.dbf /u03/oracle/oradata/WUTONG/datafile/o1_mf_sysaux_cx3xt930_.dbf /u03/oracle/oradata/WUTONG/datafile/o1_mf_undotbs1_cx3xt93b_.dbf /u03/oracle/oradata/WUTONG/datafile/o1_mf_users_cx3xt940_.dbf /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf /u03/oracle/oradata/WUTONG/datafile/FY_REC_DATA.DAT /u03/oracle/oradata/WUTONG/datafile/FY_RST_DATA.DAT 7 rows selected. SQL>?drop tablespace FY_REC_DATA INCLUDING CONTENTS; Tablespace dropped. SQL>?drop tablespace FY_RST_DATA INCLUDING CONTENTS; Tablespace dropped. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u03/oracle/oradata/WUTONG/datafile/o1_mf_system_cx3xt90z_.dbf /u03/oracle/oradata/WUTONG/datafile/o1_mf_sysaux_cx3xt930_.dbf /u03/oracle/oradata/WUTONG/datafile/o1_mf_undotbs1_cx3xt93b_.dbf /u03/oracle/oradata/WUTONG/datafile/o1_mf_users_cx3xt940_.dbf /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf 然后去操作系统下把对应的数据文件删除即可 ---------------------

该文章来自http://www.oracleblog.cn/working-case/troubleshooting-unable-to-truncate-table/ 个人猜测问题出自四川移动 ●问题处理总结故障编号...

truncate/drop表非常慢，怎么办？用硬链接，极速体验这个这个，我必须花巨大篇幅，记录下今天清空表记录的英雄壮举，可知道一个drop操作，执行了一下午啊一下午，这是要急出翔的节奏。。呵呵，下面开始我的需求：某表因历史原因，积压了1亿条记录，约占360G空间。我要清掉它，就是这么简单。尝试1：作为DB小菜，首先想到的，当然是delete命令。于是欢快的执...

可以配合WHERE条件删除部分数据。支持事务回滚（Rollback）。可以被触发器捕获。性能相对较低，尤其在删除大量数据时。快速删除所有数据，性能远高于DELETE。不支持事务回滚（取决于数据库系统，如 Oracle 支持回滚，MySQL 不支持）。不触发触发器。不记录详细日志，恢复难度大。✅对比小结DELETE是逐行删除，支持条件和事务控制；TRUNCATE是整体删除，效率高但不可回滚，且不记录详细日志。类型。

语句，而不是普通的。

基础和管理

17,378

社区成员

95,113

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章