Re: [Q] How to check what kind of objects on tablespace and datafile???

On 5/17/05, dba1 mcc <mccdba1_at_yahoo.com> wrote:
> I check ORACLE view "dba_tablespace", "dba_data_files"
> and "dba_objects". I can not find any object
> information. Does there has way to check what kind of
> Oracle objects (like tables, index, ..) in tablespace
> and datafiles?

select tablespace_name,
segment_type,
count(segment_name)
from dba_segments
group by tablespace_name, segment_type;

That will give you a list of each tablespace with segments in and how many of each. If you're interested in just one particular tablespace then use a where clause.

As one segment can have extents in multiple datafiles within the same tablespace it's possibly not that useful to look for what segments are in what datafile. If you really wanted/needed to know then I figure you'd have to use dba_extents instead of dba_segments, lose the count() and the group by but join to dba_data_files on file_id to get the filename.

Stephen

--=20
It's better to ask a silly question than to make a silly assumption.

--
http://www.freelists.org/webpage/oracle-l