Home » RDBMS Server » Performance Tuning » Temp tablespace resizing
Temp tablespace resizing [message #301537] Thu, 21 February 2008 00:24 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,

Have a look at the following result about the TEMP tablespace.
When I try to resize the datafiles under the TEMP table then it is showing me error like

ORA-03297: file contains used data beyond requested RESIZE value


SQL> select sum(bytes)/1024/1024 from dba_data_files
  2  where tablespace_name='TEMP'
  3  /

SUM(BYTES)/1024/1024
--------------------
               25148

SQL> select sum(bytes)/1024/1024 from dba_segments
  2  where tablespace_name='TEMP'
  3  /

SUM(BYTES)/1024/1024
--------------------
                6360

SQL> select round(sum(user_bytes)/1024/1024) from dba_data_files
  2  where tablespace_name='TEMP'
  3  /

ROUND(SUM(USER_BYTES)/1024/1024)
--------------------------------
                           25148



Please let me know how to resize this datafile?

Thanks
Re: Temp tablespace resizing [message #301540 is a reply to message #301537] Thu, 21 February 2008 00:34 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
And also please have a look the following for the free space in this tablespace

SQL> select round(sum(bytes)/1024/1024) from dba_free_space
  2  where tablespace_name='TEMP'
  3  /

ROUND(SUM(BYTES)/1024/1024)
---------------------------
                      18788


Thanks.
Re: Temp tablespace resizing [message #301578 is a reply to message #301537] Thu, 21 February 2008 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If TEMP tablespace is a temporary tablespace, you must create it with "CREATE TEMPORARY TABLESPACE" command and not "CREATE TABLESPACE" one.

You must create a new real temporary tablespace and drop this one (after switching all users temporary tablespace with ALTER USER).

To answer your question, you can't resize a datafile below the last allocated extent that you can see in dba_extents.
But the correct way, in your case, is to create a new and real temporary tablespace.

Regards
Michel

[Updated on: Fri, 22 February 2008 04:28]

Report message to a moderator

Re: Temp tablespace resizing [message #301902 is a reply to message #301578] Fri, 22 February 2008 03:55 Go to previous message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks for that Michel.
Previous Topic: sql tuning
Next Topic: statspack
Goto Forum:
  


Current Time: Sat Nov 23 04:59:41 CST 2024