OraFAQ Forum: SQL & PL/SQL » Advantages & Dis-advantages of Truncate & Delete...

Home » SQL & PL/SQL » SQL & PL/SQL » Advantages & Dis-advantages of Truncate & Delete...

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Advantages & Dis-advantages of Truncate & Delete... [message #245096]

Fri, 15 June 2007 02:51

prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune

Member

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 .

Report message to a moderator

Re: Advantages & Dis-advantages of Truncate & Delete... [message #245113 is a reply to message #245096]

Fri, 15 June 2007 03:43

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Advantages & Dis-advantages of Truncate & Delete... [message #245254 is a reply to message #245113]

Fri, 15 June 2007 12:03

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

Report message to a moderator

Re: Advantages & Dis-advantages of Truncate & Delete... [message #245255 is a reply to message #245254]

Fri, 15 June 2007 12:09

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.

Report message to a moderator

Re: Advantages & Dis-advantages of Truncate & Delete... [message #245262 is a reply to message #245254]

Fri, 15 June 2007 12:44

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	inline views
Next Topic:	no data found

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 14:39:31 CST 2024