Re: ora-03232

On Nov 6, 10:30 am, markg22..._at_yahoo.com wrote:
> On Nov 6, 11:10 am, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On 6 nov, 17:02, markg22..._at_yahoo.com wrote:
>
> > > ORACLE 8i     (we are in process of upgrading to 10g).
>
> > > UNIX solaris 5.8  (we are moving to new box).
>
> > > We recovered our production database in our current environment with
> > > RMAN.
>
> > > We started getting ORA-25153.
>
> > > We added the old tempfile to the temporary tablespace and resized it
> > > at the same time. (don't know if this makes a difference).
>
> > > We are now getting ORA-03232 on random queries. These are
> > > queries that have always run even when the tempfile was only 2GB.
> > > It is now 10GB.
>
> > > We have not bounced the database since the recover, as this is a
> > > production database.
>
> > > Could anyone please help  ?
>
> > > Thank you.
>
> > Please make sure for every error you post, you include the error text.
> > You can get the error text from the online documentation.
> > Please do not expect everyone knows all errors from memory and/or is
> > going to look the error up on your behalf.
> > This is a forum of volunteers.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA- Hide quoted text -
>
> > - Show quoted text -
>
> I am sorry.
>
> We first got ORA-25153  -  Temporary Tablespace is Empty
> Cause: An attempt was made to use space in a temporary tablespace with
> no files.
> Action: Add files to the tablespace using ADD TEMPFILE command.
>
> This was not unusual, because RMAN doesn't restore temp tablespaces.
>
> Now we are getting
> ORA-03232: unable to allocate an extent of 119 blocks from tablespace
> 2
>
> tablespace 2  is our temporary tablespace.
>
> There is over 9GB of free space.- Hide quoted text -
>
> - Show quoted text -

Is it a true temporary tablespace, or is it using datafiles?

If it's not a true temporary tablespace (using tempfiles) then it's entirely possible for you to have 9 Gig 'free' but for Oracle to see it only as small 'bits' less than 119 blocks in size. Have you tried coalescing the tablespace?

David Fitzjarrell Received on Thu Nov 06 2008 - 12:02:28 CST