| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help needed : Free space checking?
On Mon, 7 Dec 1998 15:12:25 +0100, "Johan Nilsson" <jni_at_esrange.ssc.se.---> wrote:
>Thanks very much for your answer,
>
>although using dbms_space looks like the best solution, the quickest and
I don't think you will find DBMS_SPACE very helpfull in your situation. It will show you the number of blocks above/below the HWM (HighWatterMark), not the number of empty blocks. It will only tell you the highest number of blocks being populated at any time in the lifetime of the table. So even if you delete *all* rows from the table it can still show you a very large number of "used blocks".
>dirtiest seems to be using ALTER TABLE XXX DEALLOCATE UNUSED KEEP X; after
>each batch of deletes, which will allow me to see the usage. I'd rather not
>begin to write any stored procedures for this.
Again this technique will not help you as the deallocation works from the end of the table towards the HWM. No space below the HWM can be dealocated. Remember that when you delete records, the HWM is not changed, it stays in the same position - that is in the highest block that *ever* held the data. So no matter how many records you will delete in each bach, you will not dealocate any space once you allready dealocated space above HWM.
>These big deletes will only occur every two weeks.
>
>Any opinions on this?
If you actualy only want to determine the space in a table that is occupied by the data, you can run this simple query:
SELECT COUNT(DISTINCT SUBSTR(rowid,1,8) || SUBSTR(rowid,15,4)) FROM table_name;
This will show you the number of blocks containing at least one data record. The rest of blocks up to the number of USER_SEGMENTS.BLOCKS for that particular table are unused and are available for new data.
>// Johan
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |