Re: How to find objects in a Datafile?

learning_still_at_hotmail.com (Newbie) wrote in message news:<6093c29e.0405030424.707c0c4_at_posting.google.com>...
> 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.
See tablespace mapping on OEM. Database --> Storage --> Tablespace --> right click on tablespace --> show tablespace map. You can zoom in on whatever it is at the end of the datafile. Hover over it and it shows useful information (that the other posters hassle with scripts to get) then click on it to see where the object winds about within the tablespace. You should be able to see where one datafile ends and the next begins by watching file name during the hovers. Also click on tabular under the view dropdown, then click on Block ID and scroll to the bottom, then back up to find the end of each filename.

It may be as simple as moving whatever the object is to another tablespace, then back after shrinking.

Be aware of temporary objects. OEM on some versions may give misleading results.

If you are using LMT's, don't be concerned about the map appearing to show fragmentation. If you are not using LMT's, do some research.

jg

--
@home.com is bogus.
http://www.jazybones.com/archives/cat_wierd_stuff.html