Skip to Main Content
  • Questions
  • ora-03297 when resizing a datafile on a temporary tablespace

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Vittorio.

Asked: November 14, 2000 - 4:37 am UTC

Last updated: September 14, 2006 - 9:15 am UTC

Version: 806

Viewed 10K+ times! This question is

You Asked

I try to resize a datafile abnormally grown, on which there is only a temporary tablespace.

I have a ora-03297 error. Have i to reaasign another temporary tablespace to all users that use this tablespace ? Note that sys and system use this temporary tablespace.

Thanks

and Tom said...

Well -- you might be best off creating a NEW temporary tablespace and selecting:

select 'alter user ' || username || ' temporary tablespace
NEW_TABLESPACE;'
from dba_users
where temporary_tablespace = 'OLD_TABLESPACE'
/

and running the resulting commands and then dropping the old tablespace and rm'ing the associated files.


The reason this is is because temporary segments in a true TEMPORARY tablespace are never deallocated until you bounce the database. You must stop and restart to get the system to give up those extents. This is by design -- we allocate the segment once and then manage it internally as needed. In order to shrink those files, you need to get us to release the segments.


Another way to get us to release the segments is to

alter tablespace temporary_tablespace PERMANENT;
alter tablespace temporary_tablespace TEMPORARY;

that'll get us to give them back as well but it'll take a long time if there are lots of allocated segments (a really long time).

I think option number 1 is easiest, create new, alter everyone, and drop the old one.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What do we do in 8.1.6 ?

Johan, April 17, 2002 - 8:54 am UTC

Tom,

I have a situation in Oracle 8.1.6.2 where the temporary tablespace grew larger than is normally required, due to the creation of a large index.

When I tried the methods used in this post, neither succeeded, since:

SQLWKS> alter tablespace temp permanent
2>
alter tablespace temp permanent
*
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

and if I try to create another temporary tablespace, change all the users to use that tablespace, and try to drop the original temp tablespace, the session that attempts the drop is blocked by the session used by SMON.

What can I do to resize the tablespace without downtime ?

PS: Would it be possible to display the DB version for which the post is valid more prominently ? I know some people don't even post the version, but maybe it could be a mandatory field ?
It would also be useful if the date of the original post is displayed - at the moment the original date is only apparent when one get to the followup and see the date of the followup.

Tom Kyte
April 17, 2002 - 12:50 pm UTC

The DB version is always displayed right in the subject:

Vittorio -- Thanks for the question regarding "ora-03297 when resizing a datafile on a temporary tablespace", version 806
^^^^^^^^^^^

You cannot alter a true temporary tablespace to permanent -- that'll never work (that only works for permanent tablespaces that are being used as "temporary".

It should just be a matter of time for SMON to give up the tablespace -- how long have you waited?


cant we resize temporary tablespace ?

Reader, May 03, 2003 - 8:57 am UTC

- oracle 9iR2 -
<quote>
You must stop and restart to get the system to give up those extents. 
<quote>

i tried restarting the server and issued ..
SQL> ALTER DATABASE TEMPFILE 
  2      'C:\ORACLE\ORA81B\ORADATA\ORACLEB\TEMP01.DBF' RESIZE  1600M;
ALTER DATABASE TEMPFILE
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL>  select TABLESPACE_NAME,ALLOCATION_TYPE,CONTENTS  from  dba_tablespaces;

TABLESPACE_NAME                ALLOCATIO CONTENTS
------------------------------ --------- ---------
SYSTEM                         SYSTEM    PERMANENT
UNDOTBS1                       SYSTEM    UNDO
TEMP                           UNIFORM   TEMPORARY
.
.
XDB                            SYSTEM    PERMANENT

its true temporary tablespace only.  cant we resize temporary tablespace?

Thanks
 

Tom Kyte
May 03, 2003 - 12:03 pm UTC

just drop and recreate it -- tempfiles are instantaneous to do that to.

It is pointless to shrink these files, they will after all just grow again (and that is expensive to do)

Thankyou !

A reader, May 03, 2003 - 12:39 pm UTC


Any Reason to Include Locally Mgd Temporary Tablespaces?

mary, June 03, 2003 - 4:12 pm UTC

Tom, sorry if this is an obvious question, but is there any reason for backing up datafiles associated with locally managed temporary tablespaces (tempfiles)? (i.e. alter tablespace ... begin backup no longer works).

Tom Kyte
June 04, 2003 - 7:39 am UTC

nope. TEMPFILES should not be backed up.

disregard my last question

mary, June 03, 2003 - 4:21 pm UTC

Sorry to waste your time. I found the answer on metalink--


fact: Oracle Server - Enterprise Edition 8.1
fact: Oracle Server - Enterprise Edition 9
fact: Locally Managed Temporary Tablespace
symptom: ALTER TABLESPACE <tablespacename> BEGIN BACKUP fails
symptom: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
cause: Alter database and alter tablespace will not work on locally managed
tempfiles as they have no reference in the data dictionary. There is no need
to back up the tempfiles as they are used and destroyed every time you start up
and shut down the database.



fix:

There is no need to backup the temporary locally manged tablespaces because:

1. Locally managed tempfiles are always set to NOLOGGING mode. So thus will
have no undo.
2. Extents are managed by bitmap in each datafile to keep track of free or
used status of blocks in that datafile.
3. The data dictionary does not manage the tablespace.
4. Rollback information is not generated because there is no update on the
data dictionary.
5. Media recovery does not recognize tempfiles.


Solved my problem

Gursoy YERLI, November 08, 2004 - 4:38 pm UTC

Thanks a lot, Tom.

ORA-03297

Serge Shmygelsky, September 14, 2006 - 7:31 am UTC

Hello Tom,

I have a strange problem. I'm trying to shrink 'USERS' tablespace which was created during DB creation and is default tablespace for all users. All the scripts (either from Metalink and your site) show that I can squeeze it from 3G to 1.5G.
I cannot find any objects in the tablespace. But when I try to resize it, ora-03297 pops up (see below)

FILE_NAME SMALLEST CURRSIZE SAVINGS
-------------------------------------------------- ---------- ---------- ----------
/u01/app/oracle/oradata/rex/system01.dbf 487 490 3
/u03/undotbs.dbf 3072 3072 0
/u03/users1.dbf 1047 1047 0
/u01/app/oracle/oradata/rex/sysaux01.dbf 388 390 2
/u01/app/oracle/oradata/rex/users01.dbf 1 3635 3634
SYS@REX> SYS@REX> alter database datafile '/u01/app/oracle/oradata/rex/users01.dbf' resize 2G;
alter database datafile '/u01/app/oracle/oradata/rex/users01.dbf' resize 2G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

What can be the reason for that?

Thanks in advance.

Tom Kyte
September 14, 2006 - 9:15 am UTC

did my script emit that alter statement?

query dba_extents and see whats in there.