Temp tablespace resizing [message #301537] |
Thu, 21 February 2008 00:24 |
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 |
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 |
|
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
|
|
|
|