-
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
-
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.
-
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.
-
First of all, thank you very much for your reply.
Originally Posted by PAVB
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.
Originally Posted by gandolf989
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.
-
Originally Posted by Zenryou
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.
-
Somehow I missed the Oracle 8 the first time. dba_recyclebin was first in 10g. You should look at upgrading from Oracle 8.
-
Thanks guys. It worked. First I run TRUNCATE with reuse storage option and deallocated.
I appreciated for your help
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|