OraFAQ Forum: Server Administration » diff b/w truncate and drop

Home » RDBMS Server » Server Administration » diff b/w truncate and drop

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

diff b/w truncate and drop [message #61861]

Mon, 07 June 2004 02:15

ashokmote
Messages: 56
Registered: December 2003

Member

i want to know the difference of truncate and drop.

�

SQL> select * from tab;

TNAME�� TABTYPE� CLUSTERID
------------------------------ ------- ----------
DEMO�� TABLE
DEMO1�� TABLE
DEPT�� TABLE

SQL> drop table demo1;

Table dropped.

SQL> select * from tab;

TNAME�� TABTYPE� CLUSTERID
------------------------------ ------- ----------
DEMO�� TABLE
DEPT�� TABLE

SQL> truncate table demo;

Table truncated.

SQL> select * from tab;

TNAME�� TABTYPE� CLUSTERID
------------------------------ ------- ----------
DEMO�� TABLE
DEPT�� TABLE

SQL> drop table demo;

Table dropped.

SQL> select * from tab;

TNAME�� TABTYPE� CLUSTERID
------------------------------ ------- ----------
DEPT�� TABLE

SQL>

�

�

�

Report message to a moderator

Re: diff b/w truncate and drop [message #61865 is a reply to message #61861]

Mon, 07 June 2004 03:57

Maaher
Messages: 7065
Registered: December 2001

Senior Member

TRUNCATE will reset your table's high water mark (HWM).

It will cause all your data to be "forgotten" by the database. It is like a bulk delete, but there's no safety net. No rollback, since it is a DDL (Data Definition Language) command just like 'ALTER TABLE'.

From Oracle 9i Concepts:
----------------------------------------------------------------------
The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.
----------------------------------------------------------------------
See also Tom Kyte's website. If you search it, you get lots of info.

DROP is the removal of the object in question. Again, this is a DDL command so no ROLLBACK possible.

MHE

Report message to a moderator

Re: diff b/w truncate and drop [message #61868 is a reply to message #61865]

Mon, 07 June 2004 05:22

ashokmote
Messages: 56
Registered: December 2003

Member

when u truncate also it is showing in the table name in the display.

Report message to a moderator

Re: diff b/w truncate and drop [message #61875 is a reply to message #61868]

Mon, 07 June 2004 19:58

Srinivas
Messages: 138
Registered: June 2000

Senior Member

Hi Kiran...

Truncate will delete the records and set the High water mark, nothing to do with table structure, will remains same.
But, drop will delete entire table structure along with records.
U can't compare drop and truncate commands.
U can compare delete and truncate commands.
Drop is a DDL statement.
Truncate is also a DDL statement, but acts as a DML statement.
We can't rollback data from the above commands..

HTH,
Srinivas

Report message to a moderator

Previous Topic:	about bulk binding
Next Topic:	dynamic search for the missing values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 08 20:00:59 CST 2025