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.
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
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).
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.
September 14, 2006 - 9:15 am UTC
did my script emit that alter statement?
query dba_extents and see whats in there.