Home » SQL & PL/SQL » SQL & PL/SQL » Truncate Command
Truncate Command [message #222998] Wed, 07 March 2007 02:41 Go to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
Does Truncate command drops and recreate a object?
Re: Truncate Command [message #223003 is a reply to message #222998] Wed, 07 March 2007 02:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes and no.

No objects that depend on the table that is being truncated will be invalidated by the Truncate, and the table exists continually throughout the process, so in that sense, no, it does not drop the object.

However, if you look behind the scenes, you will see that the Data_object_id changes as a result of the truncate, so yes -something gets dropped and recreated, but it isn't really the table.
SQL> create table trunc_test (col_1  number);

Table created.

SQL> 
SQL> select cast(object_name as varchar2(20)) object_name 
  2        ,object_id
  3        ,data_object_id
  4        ,to_char(created,'dd-mon-yyyy hh24:mi:ss') 
  5  from   dba_objects 
  6  where  object_name = 'TRUNC_TEST';

OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID TO_CHAR(CREATED,'DD-
-------------------- ---------- -------------- --------------------
TRUNC_TEST               200593         200593 07-mar-2007 08:49:36

SQL> 
SQL> truncate table trunc_test;

Table truncated.

SQL> 
SQL> select cast(object_name as varchar2(20)) object_name 
  2        ,object_id
  3        ,data_object_id
  4        ,to_char(created,'dd-mon-yyyy hh24:mi:ss') 
  5  from   dba_objects 
  6  where  object_name = 'TRUNC_TEST';

OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID TO_CHAR(CREATED,'DD-
-------------------- ---------- -------------- --------------------
TRUNC_TEST               200593         200594 07-mar-2007 08:49:36

If anyone can provide a more detailed explanation, please do.
Re: Truncate Command [message #223006 is a reply to message #222998] Wed, 07 March 2007 03:09 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
thanx for it.

but i have seen when a table is truncated it's relating triggers get invalid.

which is more faster truncate or delete?
what is the relation with high water mark with truncate commad?

[Updated on: Wed, 07 March 2007 03:30]

Report message to a moderator

Re: Truncate Command [message #223021 is a reply to message #223006] Wed, 07 March 2007 04:04 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
truncate is faster.
Truncate will reset HWM. Delete will not.
Just search the forum before posting. Many examples on this topic.
Previous Topic: need a dummy column
Next Topic: Error while using UTL_FILE in Oracle10g
Goto Forum:
  


Current Time: Sat Dec 10 15:08:03 CST 2016

Total time taken to generate the page: 0.10945 seconds