Re: How to find objects in a Datafile?

From: Mark Bole <makbo_at_pacbell.net>
Date: Thu, 06 May 2004 02:54:17 GMT
Message-ID: <tNhmc.60727$yB3.19306@newssvr25.news.prodigy.com>

Joel Garry wrote:

> 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

I have also had very good results using OEM Tablespace Map. If nothing else, it tells you how much you can successfully shrink each datafile, using the table view as Joel describes, avoiding the "file contains data beyond request" message. After that, "export/import" or "move" as previously advised is the most effective.

Datafile autoextension is a tool like any other. Use it well or use it poorly. As an example, if I estimate a datafile needs to be created at 1GB in size, I might set it to autoincrement by 50MB at a time to a max of 2 GB -- after I have made sure the server has the disk space available, of course. The more stable the database, the less guess work involved... you need to be aware of growth trends! But I don't like to get paged overnight for lack of a few spare megabytes in the production system.

--Mark Bole Received on Wed May 05 2004 - 21:54:17 CDT