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.
|
|
|
|
|
|
|
|
|
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
|
|
|
|
|