Is there a way to do a checking on truncate table?

Question

Sometimes I try test scenarios between several schemas , deleting/modifying tables , inserting/updating/deleting queries , some schemas are testing and the others are Important for production. so sometimes by accident I run queries in wrong schemas. so the commit functionality does really help in this scenario.

however Truncate table tab1 doesnt need commit, and if I execute it in a wrong schema .. well you know the scneario.

My question: Is there a workarround like the commit for truncate table like the DML Statment ? If you delete a statment you have to include a commit, or in plsql you have to click the green button to commit.

I use such check , its really annoying every time I want to truncate I have to modify the condition.

select count(1) into cnt from tab1 if cnt =0 then execute'Truncate table tab1'; end if;

I am not searching for flashback. I need a checking on truncate table

Why not using DELETE FROM table? This is a DML-statement and can be commited or rollbacked. — sfrutig, May 29 '15 at 08:18
@sfrutig `TRUNCATE` will reset the high watermark to zero, `DELETE` won't. — Lalit Kumar B, May 29 '15 at 08:18
possible duplicate of [Use of FLASHBACK in Oracle](http://stackoverflow.com/questions/25950145/use-of-flashback-in-oracle) — Lalit Kumar B, May 29 '15 at 08:22
@Baalback You requirement is not clear. What is that you want to check? Your posted query makes no sense to me as you are checking the count, and if there are no rows you are truncating the table. Why? To reset the high water mark? That aside, if there are no rows, then what is that you are truncating? — Lalit Kumar B, May 29 '15 at 08:28
@LalitKumarB I want something similar to commit for truncate, which seems there isn't . the checking above I modify it when I want to truncate a table.. if I want to truncate a table I modify the checking to >0. in such way I make sure that I am not truncating by mistake — Baalback, May 29 '15 at 08:33

score 2 · Answer 1 · answered May 29 '15 at 08:20

TRUNCATE is a DDL statement, not DML, and DDL statements automatically include commits. See https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:7072180788422 for more info.

I'm not entirely sure I understand what it is you're trying to do - you could, as Tom suggests, perhaps use an autonomous transaction to keep the truncate separate? If you're after the ability to separate the commit part from the truncate part (ie. to rollback the truncate if you decide you called it in error), then I'm afraid you're out of luck.

I am trying to avoid truncating tables by mistakes, I am working on different schemas. I was trying to see If there is a way to have something similar to auto commit. — Baalback, May 29 '15 at 08:28

score 2 · Accepted Answer · edited May 23 '17 at 12:06

As @Boneist said, truncate is DDL statement which implicitly commits. If you are not sure of the action you do in a schema, and want to commit only after a manual verification, then do not TRUNCATE, use DELETE instead.

With DELETE statement, you could control the commit. Having said that, TRUNCATE resets the high watermark back to zero, however, DELETE doesn't. Even if you delete all the rows from the table, Oracle would scan all the blocks under the HWM. Have a look at this AskTom link.

If you are looking to bring back the truncated data, and if you are on 11gR2 and up, you could use the Flashback support for DDL statements.

Is there a way to do a checking on truncate table?

2 Answers2