Re: Urgent Help please !!! - In trouble!

Reza,

To time for panic. Most probably SMON is working to coalesce the free extents in this tablespace. You can disable that by setting the following event in your init<sid>.ora file: event = "10269 trace name context forever, level 1"

In your case you have to set it up in the session level: alter session set event '10269 trace name contex forever, level 1';

Not sure if it going to take effect after the cleanup already started. ORA-1575 usually signals that Oracle has timed out waiting to aquire the Space
Transaction enqueue lock (ST enqueue) and transaction that receives an ORA-1575 will be rolled back.

If SMON is busy cleaning up a temporary segment containing a lot of extents it cannot service 'sort segment requests' from other sessions. Pointing the users at a PERMANENT tablespace as their temporary tablespace can help keep the system running until SMON is free again. Create another temporary tablespace and switch the users to use that tablespace as their temporary tablespace. Then leave SMON to finish its job.

Hope that helps,

Trifon Anguelov
Senior Oracle DBA
http://www.dbaclick.com

check the latest Oracle DBA forums:
http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi

"Reza" <reza_at_digital-dispatch.co.uk> wrote in message news:1020804429.23208.0.nnrp-12.d4f07ab4_at_news.demon.co.uk...

> Hi Guys
> Could anyone please help with the urgent matter below please.
>
> Oracle server version 8.0.5.2.1
> I ws trying to drop a big tablespace on a busy PLTP production system.
> 1- The tablespace was off-line
> 2- the tablespace has only one big object (table) with just ordinary data
> type columns (No LOB), and no reference on the table from any other object
> in the database
>
> 3- All the indexes for the table in this table space are in a separate
table
> space which is off-line as well.
>
> I isseud : Drop tablespace <xxxx> including contents;
>
> after a long time oracle came back with ORA- 1575
> 01575, 00000, "timeout waiting for space management resource"
> // *Cause: failed to acquire necessary resource to do space management.
> // *Action: Retry the operation.
>
> Now IN DATA DICTionary the table name in the tablespace and its related
> indexes in the other tablespace have changed to TEMPORARY segments (query
> from dba_segments) with oracle's inernal "xxx.yyy" numbers (x and y are
> just digits).
>
> SMON is now using a lot of processor time (over an hour now) and I don't
> know what to do to stop it.
> The database can not be shutdown (24x7 highly available system for over
700
> users).
> Also I am scared that the database will have problems to open, due to this
> status.
>
> Please give me some advice as soon as possible
>
> Many thanks for saving my a...
> Regards
> Reza
>
>
Received on Tue May 07 2002 - 17:40:42 CDT