OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » resize datafile (Oracle9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

resize datafile [message #500451]

Mon, 21 March 2011 07:25

famegaurav
Messages: 23
Registered: November 2010
Location: Delhi India

Junior Member

Hi,

Today i am facing an error when going to rezise the datafile its fixed size is 19000M abut after truncating all tables it is 112M, but when i am going to resize its datafile to 500M its get me an error ora-03297 file contain used data beyond requested size values.

I have done the same before a week without any error.But this time i got the error
Thanks,
Gaurav

Report message to a moderator

Re: resize datafile [message #500456 is a reply to message #500451]

Mon, 21 March 2011 08:03

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

famegaurav wrote on Mon, 21 March 2011 08:25

Hi,

Today i am facing an error when going to rezise the datafile its fixed size is 19000M abut after truncating all tables it is 112M, but when i am going to resize its datafile to 500M its get me an error ora-03297 file contain used data beyond requested size values.

I have done the same before a week without any error.But this time i got the error
Thanks,
Gaurav

Message is pretty self explanatory. There is data in the datafile past the 500Mb marker. You may have a single segment in an entire 8 Gb datafile, and if that segment is at the end of the datafile, you will not be able to shrink it down at all.

Here is a very crude query to show you a map of your datafiles

select owner,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,FILE_ID,
        BLOCK_ID,BYTES,BLOCKS
from dba_extents
where tablespace_name in ('DATA')
UNION
select '-','free',TABLESPACE_NAME,0,FILE_ID,BLOCK_ID,BYTES,BLOCKS
from dba_free_space
where tablespace_name in ('DATA')
order by FILE_ID,BLOCK_ID
/

Report message to a moderator

Re: resize datafile [message #500458 is a reply to message #500451]

Mon, 21 March 2011 08:05

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Even if you have only 100M inside your file, if these ones are at the end of the file cannot shrink it.
You have to first move the objects at the begin of the file.
You can try with ALTER TABLE MOVE but the only sure way is to export, drop the objects and reimport.

Regards
Michek

Report message to a moderator

Re: resize datafile [message #500560 is a reply to message #500458]

Tue, 22 March 2011 04:21

famegaurav
Messages: 23
Registered: November 2010
Location: Delhi India

Junior Member

Hi All,

How can i identified that the perticuler table uses the space at the end of the file.

Thanks,
Gaurav

Report message to a moderator

Re: resize datafile [message #500562 is a reply to message #500560]

Tue, 22 March 2011 04:24

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Query dba_extents for the max(block_id) by file_id and join it with dba_data_files on file_id.

Regards
Michel

Report message to a moderator

Re: resize datafile [message #500568 is a reply to message #500562]

Tue, 22 March 2011 04:31

famegaurav
Messages: 23
Registered: November 2010
Location: Delhi India

Junior Member

Thanks Michel..

Regards,
Gaurav

Report message to a moderator

Previous Topic:	Oracle version
Next Topic:	frm-92101

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 06:31:45 CST 2024