Truncate does not work
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Truncate does not work

  1. #1
    Join Date
    Feb 2009
    Posts
    17

    Truncate does not work

    Our application runs on Solaris 8 and uses DB as oracle 8. I want to truncate one of the table which includes more than 200k row but truncate could not succeed.
    After 6 hours waiting, I saw an error related with Shared memory. But when I exit command with CTRL+C, I saw that most of the rows were deleted. Unless none of the extents were returned.

    Right now, extent count is 99561. How can I return back those extents? Application is running and use that table. I can not stop it. Is there way to succeed?
    I'll appreciate for your help

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    You can try "purge dba_recyclebin;" and then "alter tablespace users coalesce;"

    where users is the name of the tablespace in question. you can also look at dba_waiters and dba_blockers to see if there is a session that is blocking. You can try to kill -9 sessions that are blocking but not otherwise active. Try the first two ideas before thinking about randomly killing sessions.
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Providing your truncate statement is actually getting a lock on the affected object I think the issue you are experiencing is related freeing extents, your table has almost 100K extents and that may take for a while.

    Here is what I would do.

    1- TRUNCATE TABLE your_table REUSE STORAGE

    This way Oracle would not touch extents and truncate statement would work real fast just moving HWM to zero.

    2- ALTER TABLE your_table DEALLOCATE UNUSED KEEP nnM

    Once you have your table truncated you can return unused extents by executing alter table deallocate unused with the keep option.

    Imagine your truncated table is allocating 20 Meg then... issue your statement keeping 18 Meg so Oracle would free just 2 Meg... issue statement again keeping 16 Meg so Oracle would free another 2 Meg... etc.

    Do you get the idea?
    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
    Feb 2009
    Posts
    17
    First of all, thank you very much for your reply.
    Quote Originally Posted by PAVB View Post
    Providing your truncate statement is actually getting a lock on the affected object I think the issue you are experiencing is related freeing extents, your table has almost 100K extents and that may take for a while.

    Here is what I would do.

    1- TRUNCATE TABLE your_table REUSE STORAGE

    This way Oracle would not touch extents and truncate statement would work real fast just moving HWM to zero.

    2- ALTER TABLE your_table DEALLOCATE UNUSED KEEP nnM
    I've tried that on the our machine but it consumed more than %90 CPU. So I am gonna try this night again.

    Quote Originally Posted by gandolf989 View Post
    You can try "purge dba_recyclebin;" and then "alter tablespace users coalesce;"
    .
    Does oracle8 has dba_recyclebin or with other syntax? I've run that command in our test server and I've got syntax error.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Zenryou View Post
    Does oracle8 has dba_recyclebin or with other syntax? I've run that command in our test server and I've got syntax error.
    ... and there is when you find out you do not have two choices.
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Somehow I missed the Oracle 8 the first time. dba_recyclebin was first in 10g. You should look at upgrading from Oracle 8.
    this space intentionally left blank

  7. #7
    Join Date
    Feb 2009
    Posts
    17
    Thanks guys. It worked. First I run TRUNCATE with reuse storage option and deallocated.
    I appreciated for your help

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

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