DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Truncate table takes time

  1. 10-14-2009, 08:39 PM #1
    darshilm
    darshilm is offline Junior Member
    Join Date
    Oct 2009
    Posts
    2

    Truncate table takes time

    Hi friends

    I am using Oracle 10g. I have a table with 7 columns. No index on anyone of them. It contains approx 25,000,000 rows. I am using the following command to truncate the table.

    TRUNCATE TBALE table name.

    It take ages to truncate this table. What could be the reason?

    Please help.

    Thanks...
    Reply With Quote Reply With Quote
  2. 10-15-2009, 03:51 AM #2
    hrishy
    hrishy is offline Senior Advisor
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    How many extents does the table have ?
    Reply With Quote Reply With Quote
  3. 10-15-2009, 10:00 AM #3
    PAVB's Avatar
    PAVB
    PAVB is offline Senior Advisor
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    please show the actual command...

    Does your "actual" truncate table command included "drop storage" option?

    If that's the case I'm with hrishy; most probably affected table has a zillion extents.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
    Reply With Quote Reply With Quote
  4. 10-15-2009, 07:39 PM #4
    darshilm
    darshilm is offline Junior Member
    Join Date
    Oct 2009
    Posts
    2
    PAVB, I am not using drop storage option.

    Unfortunately, I do not have access to DBA_EXTENTS view. But I could get the following stats from ALL_TABLES

    Initial Extent = 65536
    Next Extent = NULL
    Min Extent = 1
    Max Extent = 2147483645
    Blocks Used = 314176
    Empty Blocks = 8
    Reply With Quote Reply With Quote
  5. 10-16-2009, 03:20 AM #5
    malay_biswal
    malay_biswal is offline Senior Member
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Quote Originally Posted by darshilm View Post
    PAVB, I am not using drop storage option.

    Unfortunately, I do not have access to DBA_EXTENTS view. But I could get the following stats from ALL_TABLES

    Initial Extent = 65536
    Next Extent = NULL
    Min Extent = 1
    Max Extent = 2147483645
    Blocks Used = 314176
    Empty Blocks = 8
    get the values from all_extents then.
    http://www.perf-engg.com
    A performance engineering forum
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Click Here to Expand Forum to Full Width