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

[Yash99]

Hi
I am working on index Tablespace which is having around 20GB of Free Space,
But when I tried to release space by resizing the datafiles it is giving me following error.

ALTER DATABASE DATAFILE '/d001/pistss_2006_01.dbf' RESIZE 3000M;
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


This tablespace only contain indexes in it and the tablespace has been created as segment space management Manual.
Please suggest if any one has faces such issue.

[TwoProc]

Yash,

The file doesn't have data in it in one place. It's scattered. So, what this is telling you is that the space that you tried to reduce has data in it. If you go in and rebuild all the indexes, you should be able to get some or maybe even most of the space back.

Did you get 20G free by rebuilding or removing other indexes? If so, you've left holes in the data areas totalling 20G, but you've still got data between 3G and the end of file.

Look in the dba_segments table, and determine what objects have data in the table space, and, if it is indexes as you say - do an "alter index rebuild;" for each of them. HOWEVER, if you've got some indexes that are really declared primary keys or constraints, then they are tougher to move around. However, most of those types of objects live in the tablespace as the data tables that the constraints or keys refer to, that is, more generally the default tablespace for the schema that owns the objects in question.

Start rebuilding indexes and see if you get some space back. Do the rebuilds during offline times, unless you're going to do some online index rebuilding - but by all means research that as a topic in and of itself, and test in a non-production environment before you do so.

[Hein van den Heuvel]

Identifying which index is currently holding you up is probably not very productive nor effective.
How about using the current data to nicely size a whole fresh new index data area for all indexes in the area for the targetted datafile. Possibly base the new area on a single file (allowing extends).
Now in semi-down time, slow time, drop the current indexes and build in the new area/file. Eventually all indexes will have been moved and the old area and its files can be dropped.

fwiw,
Hein.

[TwoProc]

Well, much as I hate to, I disagree with Hein on this one, but only to a point. Whether or not it would be productive really depends on how many indexes you've got in there. If it's just 10 or fewer, it's a very simple process to redo and rebuild everthing in place. If it is hundreds, then Hein's probably correct, as the more complexed and interleaved the data gets, the less you seem to be able to retrieve.

I do this periodically and successfully retrieve space quite easily, especially in dedicated index areas where no "automatic" indexes, etc. get created for you.

Make no mistake however, when Hein gives advice, most everyone would do well to pay attention - and that's why I'm reluctant to say anything. It's just that this situation is pretty common (after purge and archive processes finish) and doesn't usually necessitate a full rebuild of the index space. That, like most things, just depends.

[Hein van den Heuvel]

Two Proc>> Make no mistake however, when Hein gives advice, most everyone would do well to pay attention

You are too kind. I'm just trying to help left and right.

The problem I see with a simple rebuild is that you have no real control over where, specifically in which file, oracle chooses to allocate the pages for the fresh index.
You may have expectations and past experience, but no real control... no SQL syntax.
So you could actually quit possily end up with more pages used in the targetted file than before the rebuild.


Cheers,
Hein

[TwoProc]

Hein -
re:
>>You are too kind. I'm just trying to help left and right.

Nope - not being kind - I have lots of respect for advice given by you. No exaggeration.

Re:
>>So you could actually quit possily end up with more pages used in the targeted file than before the rebuild.

That's true - but usually only when you've got not much slack space in a tablespaces' files. The new consumption would be created if needed to create the new index in place, while leaving the old index intact, until the event is complete - meaning that it takes up roughly twice the space for the index, until the process is complete. In a full tablespace, this is a real issue - however, in this case, he's trying to drop 20G down to 3G, so that's highly unlikely.

[TTr]

You may have 20GB of free space but starting at the end of the file, the space is NOT contiguous. That is what the error is telling you.

Our DBA has in the past done some kind of defragmentation on a database data file to make the free space contiguous so that he could release the space at the end of the file. So look into defragmenting the index table.

[Yogeeraj_1]

hi,

this seems to be related to a known Highwatermark issue.

see metalink note: 130866.1
Subject: How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark

if you need any further assistance, please let us know.

kind regards
yogeeraj