Re: used space

Try this....

Incidentally....

MB_USED: total amount of space currently in use within a tablespace MB_SIZE: total size of the tablespace, adding up the physical sizes of all datafiles
PCT_FULL: Used divided by Size, expressed as a percentage MB_FREE: Total amount of tablespace still sitting there empty MB_MAXSIZE: This shows how big the tablespace can become. Often this will be the same as

             the MB_SIZE column -but not if you've switched on autoextend, in which case this

             column will show the maxsize parameter PCT_UTIL: Size divided by Maxsize expressed as a percentage

In other words, PCT_FULL shows you what percentage of what you've currently got is being used,
Whereas PCT_UTIL shows what percentage of the maximum you could *possibly* one day have is
being used.

Personally, I wouldn't touch autoextend with a barge pole, and I'd be looking to resize or add
extra datafiles when the PCT_FULL column is reaching around the 75 - 80% mark.

Regards
HJR select tablespace_name,

round(sum(total_mb)-sum(free_mb)) MB_USED,
round(sum(total_mb)) MB_SIZE,
round((sum(total_mb)-sum(free_mb))/sum(total_mb)*100) PCT_FULL,
round(sum(max_mb) - (sum(total_mb)-sum(free_mb))) MB_FREE,
round(sum(max_mb)) MB_MAXSIZE,
round((sum(total_mb)-sum(free_mb))/sum(max_mb)*100) PCT_UTIL

(select
tablespace_name,sum(bytes)/1024/1024 free_mb,0 total_mb,0 max_mb from

dba_free_space
group by
tablespace_name

union

select
tablespace_name,
0 current_mb,
sum(bytes)/1024/1024 total_mb,
sum(decode(maxbytes, 0, bytes, maxbytes))/1024/1024 max_mb from
dba_data_files
group by
tablespace_name)
group by
tablespace_name;

Regards
HJR "Ralf Zwanziger" <goldensurfer_at_gmx.de> wrote in message news:3acabfa5.2338813_at_news.fth.sbs.de...
> How can I find out the used space of a datafile? The v$datafile view
> only shows the total size of the datafile.
> Thanks,
> Ralf
Received on Wed Apr 04 2001 - 06:16:28 CDT