OraFAQ Forum: Server Administration » drop table = truncate table

Home » RDBMS Server » Server Administration » drop table = truncate table

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

drop table = truncate table [message #525397]

Sun, 02 October 2011 06:59

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

hi guys,

just need some clearification.

Is the below 2 statement give the same effect?

DROP TABLE list_customers PURGE;

VS

TRUNCATE TABLE list_customers;

Meaning space are release on the tablespace they are create in. And there is no way to recover them via FLASH TECHNOLOGY or from the recycle bin.

[EDITED by LF: fixed topic title typo; was "teuncate"]

[Updated on: Sun, 02 October 2011 12:33] by Moderator

Report message to a moderator

Re: drop table = truncate table [message #525400 is a reply to message #525397]

Sun, 02 October 2011 08:23

Berny
Messages: 4
Registered: June 2009
Location: Munich

Junior Member

Of course drop table <> truncate table.
Drop table removes the table while truncate table removes the table data only. You are right though as far as recovery is concerned they both don't leave you an option to get back your data, unless you have a backup of the table data from before their execution.

Report message to a moderator

Re: drop table = truncate table [message #525402 is a reply to message #525400]

Sun, 02 October 2011 09:17

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

hi Berny,

tks for the prompt reply. I see the difference now.

So Drop table = removing table + data

&

truncate table = removing the data only. The table definition/ structure still remmain.

Report message to a moderator

Re: drop table = truncate table [message #525404 is a reply to message #525402]

Sun, 02 October 2011 09:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>truncate table = removing the data only.
>The table definition/ structure still remmain.
CONSTRAINT & INDEX remain after TRUNCATE, but not after DROP

Report message to a moderator

Re: drop table = truncate table [message #525405 is a reply to message #525404]

Sun, 02 October 2011 09:38

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

...And also privileges and also procedures/packages/functions that use it still remains valid and usable...

Regards
Michel

Report message to a moderator

Re: drop table = truncate table [message #525406 is a reply to message #525405]

Sun, 02 October 2011 09:51

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

Oh i see.... ..Thank you very much for the enlightement!

Report message to a moderator

Re: drop table = truncate table [message #525583 is a reply to message #525406]

Mon, 03 October 2011 23:27

bharatsabnis
Messages: 1
Registered: October 2011
Location: Bangalore

Junior Member

Hi,
TRUNCATE = DDL statement, commits immediately,
there is no undo information generated,
cannot be rolled back,
it does not affect any structures

DROP = Removes data
Removes table structure
invalidates all the dependent objects
If we use PURGE, then it releases space to the tablespace

Report message to a moderator

Re: drop table = truncate table [message #525597 is a reply to message #525583]

Tue, 04 October 2011 01:03

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

DROP is also a DDL.
With or without PURGE the space is released.

Regards
Michel

Report message to a moderator

Re: drop table = truncate table [message #525601 is a reply to message #525397]

Tue, 04 October 2011 01:55

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

>And there is no way to recover them via FLASH TECHNOLOGY or from the recycle bin.

I don't think that this part of your question was answered.
There is a difference if you do not use PURGE.

DROP TABLE ... PURGE is not really the same thing as
TRUNCATE TABLE because
a. without PURGE you don't really "guarantee" that the space for the dropped table is available, it might still get overwritten
but
b. with REUSE STORAGE you DO "guarantee" that that space is still available for the truncated table.

If you have a RECYCLEBIN you may be able to Flashback the table to BEFORE DROP. But there is no Flashback to BEFORE TRUNCATE.

SQL> create table TAB_2_DROP as select * from user_objects;

Table created.

SQL> create table TAB_2_TRUNCATE as select * from user_objects;

Table created.

SQL> select count(*) from TAB_2_DROP;

  COUNT(*)
----------
        64

SQL> select count(*) from TAB_2_TRUNCATE;

  COUNT(*)
----------
        65

SQL> -- TAB_2_TRUNCATE found 1 more entry -- that for TAB_2_DROP -- in user_objects
SQL> drop table TAB_2_DROP ;

Table dropped.

SQL> truncate table TAB_2_TRUNCATE ;

Table truncated.

SQL> flashback table TAB_2_DROP to before drop;

Flashback complete.

SQL> select count(*) from TAB_2_DROP;

  COUNT(*)
----------
        64

SQL> flashback table TAB_2_TRUNCATE to before truncate;
flashback table TAB_2_TRUNCATE to before truncate
                                  *
ERROR at line 1:
ORA-00905: missing keyword


SQL>

Hemant K Chitale

[Updated on: Tue, 04 October 2011 01:58]

Report message to a moderator