Subject: Re: Question on tablespaces and table spaces
References: <>

Simon Goland  writes:

>I think I am just being overly confused about something trivial...
>As an example (on a conceptual level and not any specific semantics),
>say I have a tablespace TEST of 50 MB, empty. So I have 50 MB free. Now
>I create one table in this tablespace using something like:

>   ...
>   ...
>   INITIAL 40,000,000
>   ...

>Now, if I use Server Manager or any query to show me the free space in
>tablespace TEST, it will report only 10 MB free for the tablespace
>(approximately, to the nearest block size). Correct?
>Then, if after I insert some rows into my table ABC, I want to know how
>much free space do I have left in the table, I can ran
>  analyze table abc estimate statistics;
>followed by
>  select t.blocks        "Used blocks",
>         t.empty_blocks  "Free blocks",
>         s.blocks        "Total blocks"
>    from dba_tables    t,
>         dba_segments  s
>   where t.owner = ''
>     and s.owner = t.owner
>     and t.table_name = 'ABC'
>     and s.segment_name = 'ABC';

>Which will give me the free/used/total table space, in blocks.
>Am I correct? 
>[ Simon Goland       B-)> ]
>[   Without action there is no change   ]


Your first assumption is correct; a 50M tablespace with a 40M table will 
have 10M free. To see the free space in a tablespace, you can:

SELECT sum(bytes) FROM dba_free_space WHERE tablespace_name = 'TEMP';

The SQL script that you provided will give you the free and used blocks 
WITHIN the pre-allocated space for a TABLE. So in your example, after 
inserting records in the ABC table, you may have 30% used blocks and 
70% free blocks.

To find the total and free space in the TABLESPACE, you can run one of my 
favorite scripts:

SELECT, b.tablespace_name,
       substr('Free: '||sum(b.bytes)/1024/1024,1,30) File_Size
FROM dba_free_space b, v\$database a
GROUP BY b.tablespace_name,
SELECT, b.tablespace_name,
       substr('Total: '||sum(b.bytes)/1024/1024,1,30)
FROM dba_data_files b, v\$database a
GROUP BY b.tablespace_name,
ORDER BY 1,2,3

-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 50+ technical tips, visit my Web Page:                    <->
<->                                                               <->
<->                                    <->
<->                                                               <->
<->             email:                    <->

Back to Ari Kaplan's Home Page dth="16" height="18">Back to Ari Kaplan's Home Page