Re: In which tablespaces are objects located?

<argosy22_at_my-deja.com> wrote in message news:8ghgl7$uvo$1_at_nnrp1.deja.com...
> 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?

Stored functions, procedures and views are stored in the data dictionary which is in the system tablespace.

> 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 Thu May 25 2000 - 00:00:00 CDT