Re: Cant drop tablespace

In article <3rmmpa$ral_at_ixnews3.ix.netcom.com>, chuckh_at_ix.netcom.com (Chuck Hamilton) 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!

I had simmiler experience. In my case, Oracle returned ORA-1561. So I did two things.
1. Check the temporary datafile (dba_data_file). If there is some

   temporary segments that refer the table you want to drop, Oracle    won't drop tablespace.
2. Because I could not find any segments on temporary tablespace,

   I dropped table one by one. Because I had so many table in tablespace,     and Oracle's row cache could not deal with those.    (But, you exported every table...)    Received on Mon Jun 19 1995 - 00:00:00 CEST