Re: Help needed : Free space checking?

Be careful when using the dbms_space package. It needs to analyse the segments before giving you the actual free space remaining. This will lead to changes to the query execution plan because of the changes in segment statistics.

With only Oracle tools, its hard to determine the free space in a segment without a reload or analyze. BMC has tools to do it and claims no analyse needed. Further evaluation needed here.

If you can use TRUNCATE ... DROP STORAGE, it will give you back the free space but of course, no rollback is possible and you need to discard the whole table's content.

In article <366BB4C4.B84F2292_at_bellsouth.net>,   Bryan Lenihan <lenihan_at_bellsouth.net> wrote:
> This is a multi-part message in MIME format.
> --------------A260819E5EBAF936061F9034
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Johan,
>
> When checking space using the dba_free_space for a tablespace, this is the
> amount of allocated space in a tablespace. When a table extends - it grabs a
> portion of the tablespace free space, and changes the amount of free space.
>
> When you delete records from a table, you are removing them from the extents,
> an object never (except for the truncate and shrink of rollback) gives back
 its
> extents. So when your developers remove rows from a table, the free space in
 a
> tablespace will not come back.
>
> You can use the dbms_space package to determine the amount of free space in a
> table or index. This is a useful tool and I use it a lot on our WMS system.
> This package is not well documented in the manual , but the comments in the
> package source are good enough to understand the output. I am not at work,
> therefore I cannot remember the syntax.
>
> Thanks, and I hope this helps,
> Bryan Lenihan.
>
> Johan Nilsson wrote:
>
> > Hi,
> >
> > platform Oracle 7.3 WG server, WinNT 4.0 SP4.
> >
> > I made a (simplistic?) SQL query to check free space in tablespaces, the
> > output seems to be ok - the same as is seen from the OEM Storage Manager.
> > The administrators of the application need this information as they
> > periodically delete the oldest records as the disc / tablespaces fills up.
> >
> > However, if I delete several thousands of records and commits the changes,
> > no difference in free space is seen? How is this??
> >
> > The SQL query is attached below, together with some sample output.
> >
> > Many thanks,
> >
> > // Johan
> >
> > -------------------------------------
> > SQL
> > ------------------------------------
> > select a.ts_name "Tablespace",
> > ROUND((a.ts_bytes/1024/1024)+(dfs.bytes/1024/1024),2) "Current size (MB)",
> > ROUND(a.ts_bytes/1024/1024,2) "Usage (MB)",
> > ROUND(dfs.bytes/1024/1024,2) "Free (MB)",
> > ROUND(a.ts_extent/1024/1024,2) "Max next extent (MB)"
> > from
> > (select all_ts.tablespace_name ts_name, SUM(all_ts.bytes) ts_bytes,
> > MAX(all_ts.next_extent) ts_extent from
> > (select distinct tablespace_name from dba_segments where (owner = 'USER1')
> > OR (owner = 'USER2')) used_ts,
> > (select tablespace_name, bytes, next_extent from dba_segments) all_ts
> > where used_ts.tablespace_name = all_ts.tablespace_name
> > group by all_ts.tablespace_name
> > ) a,
> > dba_free_space dfs
> > where
> > dfs.tablespace_name = a.ts_name
> > order by a.ts_name;
> > ---------------------------------
> > Sample output
> > --------------------------------
> > Tablespace Current si Usage (MB) Free (MB) Max next e
> > ------------------------------ ---------- ---------- ---------- ----------
> > USER1_DATA 280 271.67 8.32 120.01
> > USER2_DATA 190 181.12 8.88 80.01
> > OTHER_DATA 20 7.1 12.9 3.12
> > .
> > .
> > .
> > -----------------------------
> >
> > ------------------------------------------------------------------------
> > If replying by e-mail, please remove the spam protection '.---'
> > from the end of my return address.
>
> --------------A260819E5EBAF936061F9034
> Content-Type: text/x-vcard; charset=us-ascii;
> name="lenihan.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Bryan Lenihan
> Content-Disposition: attachment;
> filename="lenihan.vcf"
>
> begin:vcard
> n:Lenihan;Bryan
> tel;work:704.8476961 x 4091
> x-mozilla-html:TRUE
> adr:;;;Matthews;North Carolina;28105;USA
> version:2.1
> email;internet:lenihan_at_bellsouth.net
> title:Database Administrator
> fn:Bryan Lenihan
> end:vcard
>
> --------------A260819E5EBAF936061F9034--
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Dec 22 1998 - 00:00:00 CST