Truncate does not work

[Zenryou]

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

[gandolf989]

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.

[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

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?

[Zenryou]

First of all, thank you very much for your reply.

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.

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.

[PAVB]

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.

[gandolf989]

Somehow I missed the Oracle 8 the first time. dba_recyclebin was first in 10g. You should look at upgrading from Oracle 8.

[Zenryou]

Thanks guys. It worked. First I run TRUNCATE with reuse storage option and deallocated.
I appreciated for your help

[PAVB]

Glad it worked out