Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: HELP ! ! ! temp tablespace
Howard,
thanks for your help, but it still hangs on me when i try to drop the tablespace. i let it run for almost 7 hrs last night. and yes, when moved the datafile i did the way it should be i.e taking it offline first, alter database rename..., then os level move and then bring it back online. like i mentioned earlier, since it was soo frustating, i have already deleted the temp file from the file system. then i tried to drop the tablespace through 'drop tablespace...' - made no difference. now i have _copied_ my new temp file to the place where my old temp was, and renamed it to as the older temp (tried to make control file fool), it didn't work either. any suggestions?? do i sound too retarded?
-- Thanks, ahmed "Howard J. Rogers" <howardjr_at_www.com> wrote in message news:8_%k6.13$kC2.2766_at_inet16.us.oracle.com... 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...Received on Thu Feb 22 2001 - 16:55:26 CST
> 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
>
>