Gene:
You can use the unused_space procedure in the dbms_space pkg. Sample below
For indexes, change reference from table to index.
HTH
Barb
set serverout on size 1000000
DECLARE
Cursor cur_TABLES IS
Select TABLE_name
From USER_TABLEs TBL,
user_segments segs
where segment_name = TABLE_name
and tbl.tablespace_name = '&&TBLSPACE_NAME'
Order By segs.bytes, tbl.TABLE_name;
lv_total_blocks_num INTEGER;
lv_total_bytes_num INTEGER;
lv_unused_blocks_num INTEGER;
lv_unused_bytes_num INTEGER;
lv_last_used_extent_file_num INTEGER;
lv_last_used_extent_block_num INTEGER;
lv_last_used_block_num INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE(
' Total Unused');
DBMS_OUTPUT.PUT_LINE(
'Table_Name Bytes Meg Bytes' ||
' Meg');
DBMS_OUTPUT.PUT_LINE('--------------------' ||
'----------------------------------------------------------');
For cur_tables_rec IN cur_tables LOOP
DBMS_SPACE.UNUSED_SPACE(USER, cur_tables_rec.table_name,
'TABLE', lv_total_blocks_num, lv_total_bytes_num,
lv_unused_blocks_num, lv_unused_bytes_num,
lv_last_used_extent_file_num,
lv_last_used_extent_block_num,
lv_last_used_block_num);
DBMS_OUTPUT.PUT_LINE(
RPAD(CUR_TABLES_REC.table_NAME,30) ||
to_char(LV_TOTAL_BYTES_NUM, '9,999,999,999') ||
to_char(LV_TOTAL_BYTES_NUM/1024/1024, '9,999.00') ||
to_char(LV_UNUSED_BYTES_NUM, '999,999,999') ||
to_char(LV_UNUSED_BYTES_NUM/1024/1024, '9,999.00'));
END LOOP;
END;
/
show error
> ----------
> From: Gene Gurevich[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Monday, December 17, 2001 12:50 PM
> To: Multiple recipients of list ORACLE-L
> Subject: unused blocks BELOW HWM
>
> Hi all:
>
> Is there a way for me to see how many blocks under
> the HWM are unused?
>
> thanks
>
> Gene
>
> =====
>
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gene Gurevich
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
