Re: HELP: Getting DB/Device size INFO

In <31DA23B3.14C4_at_tpg.tpg.oz.au>, Jo Manna <jgm_at_tpg.tpg.oz.au> writes:
>Hello there Oracle Guru's:
>
>I am trying to find the best way to determine:
>
>o how much space an account is taking.
>o how much room I have free on a tablespace.
>o and how much room ALL user accounts are taking up
> for a tablespace.
>o how much free room I have on a tablespace.
>
>I attempted to construct some queries to calculate this
>(even an approximate figure will do).
>
>Can anyone tell me what I am doing wrong here or suggest
>a better way to do this?
>
>
>Please cc mail direct if possible.
>Thanks in advance .
>
>Jo
>
>
>
>/* ************************************************
> * size of my tablespace device.
> */
>SQLDBA> select * from dba_data_files;
>/dev/rrz3d 4 GLOB 498073600 243200 AVAILABLE
>
>In meg:
> 498073600 / (1024 * 1024) = 475
>
>/*
> * how much space does the account JOHN use up.
> */
>SQLDBA> select sum(bytes) from dba_extents where owner = 'JOHN';
>16640000
>
>/*
> * The same as above except in Meg?
> */
>SQLDBA> select sum(bytes / (1024 * 1024))
> from dba_extents where owner = 'JOHN';
>15.8691406
>
>/*
> * how much free space do we have in the GLOB tablespace? In meg.
> */
>SQLDBA> select sum(bytes / (1024 * 1024)) from dba_free_space
> where tablespace_name = 'GLOB' ;
>284.251953
>
>/*
> * How much space taken by all user accounts?
> */
>SQLDBA> select sum(bytes / (1024 * 1024))
> from dba_extents
> where owner in (select distinct owner from dba_extents);
>235.478516
>
>
>/*
> * Space taken + space available.
> */
>235.478516 + 284.251953 =
>519.730469
>
>This does seem to large. device is only 475
>?? Where Have I gone wrong?
>
Jo,

whats happened to your SYSTEM tablespace - I thought you cannot have a database without it - this could be the missing 45M you are looking for.

If it is there but you didn't supply the info then your statement

>SQLDBA> select sum(bytes / (1024 * 1024))
> from dba_extents
> where owner in (select distinct owner from dba_extents);

is wrong - as it includes system tablespace as well as rollback seg info. try
SQLDBA> select sum(bytes / (1024 * 1024))

           from dba_extents
           where tablespace_name = 'GLOB'

Bye,
Steve

| any similarity 'tween my opinions and that |
|  of my employers are purely hypothetical    |
|     and should give no cause for alarm      |
 ------------------------------------