Re: Question on tablespaces and table spaces

From: Ari Kaplan <akaplan_at_psycfrnd.interaccess.com>
Date: 1997/04/17
Message-ID: <5j5djb$f6j@psycfrnd.interaccess.com>#1/1

Simon,

(Your article will be shown after my response...) 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 a.name, 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, a.name
UNION
SELECT a.name, 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, a.name
ORDER BY 1,2,3
/

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

<-> For 50+ technical tips, visit my Web Page:                    <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Simon Goland <sg_at_mda.ca> 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:
 
>CREATE TABLE abc (
>   ...
>   <fields>
>   ...
>)
>TABLESPACE test
>STORAGE (
>   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 = '<my user name>'
>     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-)>     sg_at_mda.ca ]
>[   Without action there is no change   ]

Received on Thu Apr 17 1997 - 00:00:00 CDT