Truncate and Commit [message #19335] |
Tue, 12 March 2002 05:41 |
uma
Messages: 67 Registered: May 2001
|
Member |
|
|
Do I need to commit after the truncate? I have a procedure like this
truncate table A;
truncate table B;
insert into the table A;
COMMIT;
insert into the table B;
commit;
alter table space;
But I got a problem inserting into the table B with different valid reason. So it rolled back my table A with previous data + the new data I inserted.
My question is 'Is it mandatory to commit after each truncate statement?' Any commit in the SQL is not means commit everything until you reached that point. Here in the above case why it is not commited the truncation of the table A after it successfully inserted into table A.
Thanks in advance,
Uma
|
|
|
Re: Truncate and Commit [message #19336 is a reply to message #19335] |
Tue, 12 March 2002 07:26 |
Grant
Messages: 578 Registered: January 2002
|
Senior Member |
|
|
Truncate is a DDL command so you do not need a commit. Once you type "truncate table A;" it is gone. I beleive it isn't even logged so I am not sure how you would have data prior to truncate left in your table. Maybe someone else has an idea.
|
|
|
Re: Truncate and Commit [message #19346 is a reply to message #19335] |
Tue, 12 March 2002 17:57 |
seng
Messages: 191 Registered: February 2002
|
Senior Member |
|
|
TRUNCATE table won't have logged(Redo log) and it can drop or reuse space of data. This is difference then DELETE from table, which has logged and space is still occupied(maintenance high water mark).and also TRUNCATE is more faster then DELETE because of logged, it is prefered for large table.
I don't think that have data in table after TRUNCATE
|
|
|