Re: In which tablespaces are objects located?

Thanks,

I found that this is a good one too.

  1 select segment_type,
  2 tablespace_name,
  3 count(1)
  4 from dba_segments
  5 group by segment_type,
  6* tablespace_name

But, it doesn't tell us where the stored functions, procedures, views, etc. are. Is there a way to find out?

Ciao,

Argosy

In article <8ghcop$s4j$1_at_nnrp1.deja.com>,   tandym_at_hotmail.com wrote:
> you could try:
>
> select segment_name, segment_type, tablespace_name from dba_segments
> where tablespace_name = 'RBS' order by segment_name;
>
> This will tell you indexes, tables, etc.
>
> sonya
>
> In article <8gh9sb$psr$1_at_nnrp1.deja.com>,
> argosy22_at_my-deja.com wrote:
> > Hi all,
> >
> > I have inherited a database with a tablespace: RBS
> > that has 3 rollback segments: R01, R02, R03
> > and is owned by SYS.
> >
> > The rollback segments are offline, but the tablespace
> > is online. But when I look at this file on the Unix side,
> > it is always current to within 30 minutes.
> >
> > I was thinking of taking the tablespace offline,
> > but first I wondered what objects are contained in
> > it. We know about the rollback segments. But what else?
> > I'm thinking of stored functions or procedures.
> >
> > This query does not return the tablespace.
> >
> > Select DEFAULT_TABLESPACE,
> > TEMPORARY_TABLESPACE
> > from dba_users
> > group by DEFAULT_TABLESPACE,
> > TEMPORARY_TABLESPACE
> >
> > These the views that have a column about tablespaces.
> >
> > SYS ALL_CLUSTERS TABLESPACE_NAME
> > SYS ALL_INDEXES TABLESPACE_NAME
> > SYS ALL_TABLES TABLESPACE_NAME
> > SYS DBA_CLUSTERS TABLESPACE_NAME
> > SYS DBA_DATA_FILES TABLESPACE_NAME
> > SYS DBA_EXTENTS TABLESPACE_NAME
> > SYS DBA_FREE_SPACE TABLESPACE_NAME
> > SYS DBA_FREE_SPACE_COALESCED TABLESPACE_NAME
> > SYS DBA_INDEXES TABLESPACE_NAME
> > SYS DBA_ROLLBACK_SEGS TABLESPACE_NAME
> > SYS DBA_SEGMENTS TABLESPACE_NAME
> > SYS DBA_TABLES TABLESPACE_NAME
> > SYS DBA_TABLESPACES TABLESPACE_NAME
> > SYS DBA_TS_QUOTAS TABLESPACE_NAME
> > SYS SM$TS_AVAIL TABLESPACE_NAME
> > SYS SM$TS_FREE TABLESPACE_NAME
> > SYS SM$TS_USED TABLESPACE_NAME
> > SYS USER_CLUSTERS TABLESPACE_NAME
> > SYS USER_EXTENTS TABLESPACE_NAME
> > SYS USER_FREE_SPACE TABLESPACE_NAME
> > SYS USER_INDEXES TABLESPACE_NAME
> > SYS USER_SEGMENTS TABLESPACE_NAME
> > SYS USER_TABLES TABLESPACE_NAME
> > SYS USER_TABLESPACES TABLESPACE_NAME
> > SYS USER_TS_QUOTAS TABLESPACE_NAME
> > SYS V_$SORT_SEGMENT TABLESPACE_NAME
> >
> > Other than the views that are specific to an object type,
> > (ie. dba_tables, dba_indexes, dba_rollback_segs)
> > I can't see any view that links objects and tablespaces
> > together. Is there one?
> >
> > How can I tell which tablespace an object is stored in?
> > Or, what objects are in which tablespace?
> >
> > Ciao,
> >
> > Argosy
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 24 2000 - 00:00:00 CDT