Re: In which tablespaces are objects located?

Any tablespace that is online is updated at any checkpoint.

The rollback segments which you mentioned might be used, I would have to go into oracle concepts to explain why (you might want to read that manual).

As far as what objects are in what table, the other scripts given are pretty good. All these views are useful for the things that you might be interested in.

dba_objects
dba_segments
dba_extents

P.S. I would recommend just leaving the tablespace alone, it isn't really hurting you, unless you need the space back.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
<argosy22_at_my-deja.com> wrote in message news:8gh9sb$psr$1_at_nnrp1.deja.com...


> 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.