Re: Cant drop tablespace

In article <3rmmpa$ral_at_ixnews3.ix.netcom.com>, chuckh_at_ix.netcom.com says...
>
>I'm having trouble dropping a tablespace and was hoping someone could
>give me a hand and tell me what I'm doing wrong.
>
>I need to both shrink and defragment an existing tablespace called
>USER_DATA. First I exported the users that had objects in that
>tablespace. Second I wanted to drop the tablespace. Third I wanted to
>recreate it and then import the objects. Is this correct?
>
>When I try to drop the tablespace with the command shown below, I get
>the following errors...
>
>SQL> drop tablespace user_data including contents;
>drop tablespace user_data including contents
>*
>ERROR at line 1:
>ORA-00604: error occurred at recursive SQL level 1
>ORA-00376: file 2 cannot be read at this time
>ORA-01110: data file 2: 'SYS:ORANW\DATABASE\wguser.ora'
>
>What am I doing wrong? I checked the manual for ORA-00376 and it just
>says to make sure that the datafile is ONLINE. It is.
>
>There is nobody else connected to the database.
>
>This is Oracle 7.1 for Netware. The Netware version is 3.12.
>--
>Chuck Hamilton
>chuckh_at_ix.netcom.com
>
>Never share a foxhole with anyone braver than yourself!

Hi!

I am not comig from the same environment that you are using, however, in my VAX environment I can sometimes have trouble dropping a tablespace if a process is still accessing data in that tablespace. If you have active rollback segments in that tablespace, you will not be able to drop the tablespace...check the sys.dba_rollback_segment dictionary table for their status and location. You can drop a tablespace if it either OFFLINE or ONLINE...I would recommend ALTERing it OFFLINE..just to make

sure that nothing is attached to it...then DROP it.  If you have 
rollback segments in that tablespace...take them offline..and relocate 
them before dropping the tablespace...

Hope this helps.... Sandra Fergusson Received on Sun Jun 18 1995 - 00:00:00 CEST