Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: unused blocks BELOW HWM

RE: unused blocks BELOW HWM

From: Baker, Barbara <bbaker_at_denvernewspaperagency.com>
Date: Mon, 17 Dec 2001 13:09:06 -0800
Message-ID: <F001.003DE699.20011217130024@fatcity.com>

oops
this doesn't give you blocks.
sorry....
barb

> ----------
> From: Baker, Barbara[SMTP:bbaker_at_denvernewspaperagency.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Monday, December 17, 2001 1:25 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: unused blocks BELOW HWM
>
> 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:g_u_r_e_v_i_c_h_at_yahoo.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > 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: g_u_r_e_v_i_c_h_at_yahoo.com
> >
> > 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: ListGuru_at_fatcity.com (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: bbaker_at_denvernewspaperagency.com
>
> 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: ListGuru_at_fatcity.com (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: bbaker_at_denvernewspaperagency.com

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: ListGuru_at_fatcity.com (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).
Received on Mon Dec 17 2001 - 15:09:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US