In which tablespaces are objects located?

From: <argosy22_at_my-deja.com>
Date: 2000/05/24
Message-ID: <8gh9sb$psr$1@nnrp1.deja.com>#1/1

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