Re: How to find objects in a Datafile?

Newbie wrote:

> Hi,
>
> Can we find what all objects lie in a datafile?
>
> I have 3 datafiles mapped to a tbsp, out of which 1 has become really
> HUUGE =~ 24GB. Datafile usage in OEM shows a measly 0.5% full.
> Resizing the datafile says "file contains used data beyond requested
> RESIZE value".
>
> Please suggest what to do in this situation.
>
> TIA.
You will need to look in DBA_SEGMENTS to find out which segments are stored in which tablespace, which is where you need to start.

Then you'll need DBA_EXTENTS to find out which extents are housed in which particular data file (DBA_SEGMENTS only shows you the file where the first extent is housed).

Preventing the problem is easy: don't use autoextend in the first place.

Fixing the problem up once it's happened is rather harder, and depends on your version, which you don't mention. If it's 8i or above, then you could 'alter table X move tablespace Y' -move all the segments out to another tablespace, just temporarily. You could then resize your datafiles and get the original tablespace looking good, and then simply move all the tablespaces back into the original. Not easy with so much data to move, but about the only thing that can do the job.

If you're on something earlier than 8i, the same principle applies: move the data somewhere else, fix up the tablespace, move the data back. But the only tool you've got to move data around in 8.0 or earlier, really, is export/import. You could also do CTAS (create table Xcopy tablespace Y as select * from X), but that's a lot of subsequent hassle with constraints and permissions and indexes.

Whatever method you use, when the tables come back into the re-sized tablespace, you should see their extents allocated around all available data files in the tablespace on a round-robin basis, so all three data files should be more or less evenly consumed.

But to stop the problem ever happening again in the future, make sure you get rid of the autoextension attribute on the data files.

Regards
HJR Received on Mon May 03 2004 - 18:02:54 CDT