Truncate table takes time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Truncate table takes time

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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    How many extents does the table have ?

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

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

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

Posting Permissions

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


Click Here to Expand Forum to Full Width