Newsgroups: comp.databases.oracle.server
Subject: Re: Question on tablespaces and table spaces
References: <335540BA.77AE@mda.ca>
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:
>CREATE TABLE abc (
> ...
>
> ...
>)
>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 = ''
> 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@mda.ca ]
>[ Without action there is no change ]
Simon,
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: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page