Re: In which tablespaces are objects located?

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. Received on Wed May 24 2000 - 00:00:00 CDT