Home » SQL & PL/SQL » SQL & PL/SQL » Advantages & Dis-advantages of Truncate & Delete...
icon5.gif  Advantages & Dis-advantages of Truncate & Delete... [message #245096] Fri, 15 June 2007 02:51 Go to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Embarassed Hello Sir / Madam,

This is Prachi.
I have a query... I know this should not be asked ....
But, something about TRUNCATE Statement.

If I do not have database back up with me, as TRUNCATE is an auto-commit command (DDL) ,
Is it possible to recover that data, in any case??? Embarassed

I know one should take precaution before firing the TRUNCATE command. But still, just for a doubt ...

Mad Do not get irritate, please ....

But if this situation arises then is there any solution?

And can you please clarify the Advantages & Dis-advantages of TRUNCATE & DELETE commands ???
(Not the differences, that I know)
Thanks in advance ...

Waiting for the reply .
Re: Advantages & Dis-advantages of Truncate & Delete... [message #245113 is a reply to message #245096] Fri, 15 June 2007 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible to recover that data, in any case???

Not directly, you have to recover the whole database or use flashback if you are in 10g and flashback in on.

Quote:
But if this situation arises then is there any solution?

See above

Quote:
Advantages & Dis-advantages of TRUNCATE & DELETE commands ???
(Not the differences, that I know)

But WE don't know what you know.
See Database Administrator's Guide, Chapter 13 Managing Schema Objects, section "Truncating Tables and Clusters"

Regards
Michel

Re: Advantages & Dis-advantages of Truncate & Delete... [message #245254 is a reply to message #245113] Fri, 15 June 2007 12:03 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Thanks Michel,

Thanks for reply... Cool

See, DELETE is a DML statement & TRUNCATE is a DDL statement.
I know differences like ...

DELETE TRUNCATE

U have to COMMIT is an AUTO - COMMIT statement.
U can rollback. can not rollback.
Logs are created. Logs are not created.

And one more thing.
I read the water-mark level.

Can you please explain me this level issue in detail.

I want to know that in which case one should use either of the commands??? Embarassed

Please tel me the advantages & disadvantages.

Thanks in advance.

Prachi
Re: Advantages & Dis-advantages of Truncate & Delete... [message #245255 is a reply to message #245254] Fri, 15 June 2007 12:09 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
See, DELETE is a DML statement & TRUNCATE is a DDL statement.
I know differences like ...

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.


Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.
Delete: You can keep object's statistics and all allocated space.
Re: Advantages & Dis-advantages of Truncate & Delete... [message #245262 is a reply to message #245254] Fri, 15 June 2007 12:44 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't what I can tell you more than what is in the link I posted.
DreamzZ already said with his words a part of it.
I can repeat with my words what it is in this link.
But in the end, the best I could do is to copy and paste here the doc.

Regards
Michel
Previous Topic: inline views
Next Topic: no data found
Goto Forum:
  


Current Time: Sat Dec 10 14:42:40 CST 2016

Total time taken to generate the page: 0.19699 seconds