Re: Table space Usage

In article <swhite.703454903_at_fncduc> swhite_at_fncduc (Stephen White) writes:
>In order to allocate space properly I need a way to determine the amount of
>bytes and blocks used by each table in the DB. Can anyone give me a query
>whichreturns this data?
>
>Oh yeah, I'm using version 6.0.33.1.1.

There are two main ways to do what you ask. You can do it as SYS (or be granted read privilege to read the sys.dba_segments view) or you can do it by using a similar view (user_segments) from within each private account.

The sys.dba_segments view maintains data for every table, index, cache, rollback or cluster in the database.

The (current_user).user_segments view can be run from within any account (of course only that data that pertains to that account will be accessible).

Either contains all of the data that you ask.

As SYS try:

select * from sys.dba_segments where owner = 'FOO';

This shows:

 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                                    CHAR(30)
 SEGMENT_NAME                             CHAR(81)
 SEGMENT_TYPE                             CHAR(17)
 TABLESPACE_NAME                          CHAR(30)
 HEADER_FILE                              NUMBER
 HEADER_BLOCK                             NUMBER
 BYTES                                    NUMBER
 BLOCKS                                   NUMBER
 EXTENTS                                  NUMBER
 MAX_EXTENTS                              NUMBER

or from within any account (SCOTT for example):

select * from user_segments [where ...];

This shows:

 Name                            Null?    Type
 ------------------------------- -------- ----
 SEGMENT_NAME                             CHAR(81)
 SEGMENT_TYPE                             CHAR(17)
 TABLESPACE_NAME                          CHAR(30)
 BYTES                                    NUMBER
 BLOCKS                                   NUMBER
 EXTENTS                                  NUMBER
 MAX_EXTENTS                              NUMBER

Ain't it fun...

Dale Cooper, DBA
Center for Seismic Studies
Arlington, VA Received on Mon Apr 20 1992 - 17:31:41 CEST