CODEWITHSUNDEEP
sqloracle-databasecommittruncate
Baalback
Baalback

Reputation: 461

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

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

Upvotes: 3

Views: 5110

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

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.

Upvotes: 2

Boneist
Boneist

Reputation: 23588

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.

Upvotes: 2

Related Questions