Home » SQL & PL/SQL » SQL & PL/SQL » TRUNCATE TABLE
TRUNCATE TABLE [message #193177] Fri, 15 September 2006 03:10 Go to next message
prashanth_gs
Messages: 67
Registered: November 2005
Location: chennai
Member
Hi all,

I am trying to truncate a table which has only 5 to 6 rows.

TRUNCATE TABLE PCOP.ORDERDETAILS;

But its taking very long time to get executed.

What would be the reason to take this much time?

Oracle database version : 9i

Please advice?

Thanks and Regards,
Prashanth

Re: TRUNCATE TABLE [message #193182 is a reply to message #193177] Fri, 15 September 2006 03:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Q1) Does the truncate actually finish, or is the session hung?

Q2) Did the table only ever have a small number of rows, or has it been very big at one point?
Re: TRUNCATE TABLE [message #193185 is a reply to message #193177] Fri, 15 September 2006 03:27 Go to previous messageGo to next message
prashanth_gs
Messages: 67
Registered: November 2005
Location: chennai
Member
A1) Still hanging.

A2) Only small number of rows
Re: TRUNCATE TABLE [message #193194 is a reply to message #193185] Fri, 15 September 2006 04:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you try
truncate table table_name reuse storage;

Is this LMT or DMT tablespace?
Re: TRUNCATE TABLE [message #193207 is a reply to message #193185] Fri, 15 September 2006 05:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that someone else has a lock on the table, and your session is waiting for that to clear.

Have a look in the view DBA_BLOCKERS.
Re: TRUNCATE TABLE [message #193226 is a reply to message #193194] Fri, 15 September 2006 06:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
seems the suggestion was accidentally 'reported'.
Quote:


Reported By: ANJEET06 On: Fri, 15 September 2006 06:34 In: SQL & PL/SQL SQL Experts TRUNCATE TABLE
Reason dear prashant i think because you are using the another segma's table and oracle will check your privillage. i am not sure but i think this should be.
Re: TRUNCATE TABLE [message #193229 is a reply to message #193226] Fri, 15 September 2006 06:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
again:

Quote:

Reported By: ANJEET06 On: Fri, 15 September 2006 06:43 In: SQL & PL/SQL SQL Experts TRUNCATE TABLE
Reason dear mahesh TRUNCATE TABLE is a data definition language and it's purpose is to remove all rows from a table and release the storage space used by that table and also that space can be user for another objects.
Re: TRUNCATE TABLE [message #193231 is a reply to message #193194] Fri, 15 September 2006 06:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
[edit]Damn, your fast Mahesh!

MHE

[Updated on: Fri, 15 September 2006 06:46]

Report message to a moderator

Re: TRUNCATE TABLE [message #193372 is a reply to message #193231] Fri, 15 September 2006 22:19 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Partitioned tables can sometimes take a while to truncate. Also, what happens when there are ON-DELETE-CASCADE constraints? (Truly, I don't know, and am too lazy to look it up).

Doesn't TRUNCATE try for a NOWAIT lock? I wouldn't have expected that to cause hanging.

Ross Leishman
Previous Topic: How to remove fixed number of characters from a text data type
Next Topic: Update statement
Goto Forum:
  


Current Time: Sun Dec 04 02:35:58 CST 2016

Total time taken to generate the page: 0.07459 seconds