Help needed : Free space checking?

From: Johan Nilsson <jni_at_esrange.ssc.se.--->
Date: 1998/12/07
Message-ID: <366baa0e.0@d2o19.telia.com>#1/1

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. Received on Mon Dec 07 1998 - 00:00:00 CST