Home » RDBMS Server » Server Administration » drop table = truncate table
drop table = truncate table [message #525397] Sun, 02 October 2011 06:59 Go to next message
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 Go to previous messageGo to next message
Berny
Messages: 3
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.
Re: drop table = truncate table [message #525402 is a reply to message #525400] Sun, 02 October 2011 09:17 Go to previous messageGo to next message
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.
Re: drop table = truncate table [message #525404 is a reply to message #525402] Sun, 02 October 2011 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
>truncate table = removing the data only.
>The table definition/ structure still remmain.
CONSTRAINT & INDEX remain after TRUNCATE, but not after DROP
Re: drop table = truncate table [message #525405 is a reply to message #525404] Sun, 02 October 2011 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
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
Re: drop table = truncate table [message #525406 is a reply to message #525405] Sun, 02 October 2011 09:51 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Oh i see.... ..Thank you very much for the enlightement!
Re: drop table = truncate table [message #525583 is a reply to message #525406] Mon, 03 October 2011 23:27 Go to previous messageGo to next message
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
Re: drop table = truncate table [message #525597 is a reply to message #525583] Tue, 04 October 2011 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
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
Re: drop table = truncate table [message #525601 is a reply to message #525397] Tue, 04 October 2011 01:55 Go to previous messageGo to next message
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

Re: drop table = truncate table [message #525616 is a reply to message #525601] Tue, 04 October 2011 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
there is no Flashback to BEFORE TRUNCATE.

For the moment but it is planned. Wink

Regards
Michel
Re: drop table = truncate table [message #525893 is a reply to message #525616] Thu, 06 October 2011 10:49 Go to previous message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
This is very informative! Thank you!
Previous Topic: Issue with logging in sql with /as sysdba
Next Topic: ASM disk path issue
Goto Forum:
  


Current Time: Sun Dec 21 21:03:06 CST 2014

Total time taken to generate the page: 0.04040 seconds