Re: HELP ! ! ! temp tablespace

Try this:

Select * from v$datafile -note the full path and filename that Oracle thinks the big temporary datafile is located at.

After doing a shutdown abort, do a startup mount.

Then do an 'alter database datafile '/fullpath/andfilename.dbf' offline drop' (ie, specify the full path and filename to the datafile for your enormous temporary tablespace as obtained earlier).

Then 'alter database open', followed by a 'drop tablespace temp (or whatever it's actually called)'.

Then you can remove the file from the disk using the usual operating system commands (remember that doing 'drops' in Oracle never actually gets rid of things off the hard disk). Then try a shutdown immediate and see what happens.

It's difficult to be certain, but I wonder what you actually did when you 'moved it to another disk' -did you issue the appropriate 'alter database rename file '/bing/blah' to '/bong/blah' command, or did you just move the file at the operating system? I wonder whether the hang on a shutdown (remember that immediates and normals *can* take hours to complete if there are active users on the system or lots of active transactions to be rolled back) is because any form of clean shutdown requires a final checkpoint to be issued -which means CKPT has to be able to write to the headers of all datafiles, including temp ones. If you just moved the file at the o/s level, CKPT is going to throw a wobbly trying to a checkpoint a file that's not where it expects to find it.

In any case, it sounds like you could do with a fresh start as far as your temp tablespace is concerned, so doing the things I mentioned above wouldn't be a bad place to start.

Incidentally, unlimited growth is the default for all datafiles -you must explicitly set a MAXSIZE when creating the datafiles/tablespaces if you want to limit it.

Regards
HJR "ahmed kashif" <Ahmed_at_U.Arizona.Edu> wrote in message news:971ll1$jvv$1_at_news.ccit.arizona.edu...
> my temp (temporary) tablespace grew to ~14g (dont know why it was set to
> unlimited?) - ended up taking all the disk space. I moved it to another
 disk
> and brought it online, but then it wouldn't let me shutdown the database
> with the exception of shutdown abort. i tried to drop it with drop
> tablespace command, but it always seemed like it hung the database. i even
> let it go for hours - no luck. then i created a new tablespace 'temp2',
 and
> made that tablespace the temporary tablespace for all the users. then i
> shutdown (abort) the database and removed that sucker from the filesystem.
> brought the database up with no problem. then i tried again to drop it
> throught drop tablespace, but it still does the same - hang the database
 on
> me.
>
> i still can see that tablespace in dba_tablespaces, and i still can't
> shutdown normal or immediate. please HELP!!!
>
> --
>
>
> Thanks,
>
> ahmed
>
>
Received on Wed Feb 21 2001 - 21:41:23 CST