Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HELP ! ! ! temp tablespace

Re: HELP ! ! ! temp tablespace

From: ahmed kashif <Ahmed_at_U.Arizona.Edu>
Date: Thu, 22 Feb 2001 15:55:26 -0700
Message-ID: <9745b8$oc1$1@news.ccit.arizona.edu>

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...

> 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 Thu Feb 22 2001 - 16:55:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US