Re: Script to find free space in an index

From: Jared Still <jkstill_at_cybcon.com>
Date: Mon, 24 Jun 2002 11:43:24 -0800
Message-ID: <F001.00485A5F.20020624114324@fatcity.com>

If that's one of my scripts, RCS says it's from 1997.

Here's a newer one. It gets *everything* in the database, so a little tweaking may be in order.

Jared

---

• showspace_all.sql
• use dbms_space to show exact amount
• of space consumed by tables/indexes/clusters
• jared still

set verify off
set echo off feed off

prompt
prompt Working...
prompt

declare

	object_blocks	number;
	unused_blocks	number;

	object_bytes	number;
	unused_bytes	number;

	total_blocks	number default 0;
	total_bytes		number default 0;

	last_used_extent_file_id 	number;
	last_used_extent_block_id	number;
	last_used_block				number;

	cursor c_objects
	is
	select owner, object_name, object_type
	from dba_objects
	where object_type in ('CLUSTER','INDEX','TABLE');

begin

	for objrec in c_objects
	loop

		dbms_space.unused_space(
			objrec.owner,objrec.object_name,objrec.object_type,
			object_blocks, object_bytes,
			unused_blocks, unused_bytes,
			last_used_extent_file_id ,
			last_used_extent_block_id,
			last_used_block
		);
		dbms_output.put_line(
			'Total space used by ' || objrec.owner || '.' || objrec.object_name
		);

		-- comment out these 2 lines if you get buffer overflow on dbms_output
		dbms_output.put_line('	BLOCKS: ' || to_char( object_blocks - unused_blocks ));
		dbms_output.put_line('	BYTES : ' || to_char( object_bytes - unused_bytes ));

		total_blocks := total_blocks + ( object_blocks - unused_blocks );
		total_bytes := total_bytes + ( object_bytes - unused_bytes );

	end loop;

	dbms_output.put_line('	'  );
	dbms_output.put_line('TOTAL BLOCKS: ' || to_char( total_blocks ));
	dbms_output.put_line('TOTAL BYTES : ' || to_char( total_bytes ));

end;
/

set feed on

==========================-

ù
On Sunday 23 June 2002 19:28, Ferenc Mantfeld wrote:
> Hi All
> Jared notified me that the attachment did not come through, so I am
> attaching again. I am also enclosing it in the body of the email.

>

> Credits go to Jared who wrote the shell of this some time back (98 was it
> Jared ?). I just modified an excellent foundation.

>

> <<tblindspace.sql>>

>

> set linesize 80
> set verify off
> set echo off
> set feed off

>

> undef tab_name;
> undef object_type;
> undef schema_name;

>

> prompt Enter the schema and table name, and you are shown the space that
> the table
> prompt and each associated index uses in the database
> prompt

>

> accept schema_name prompt 'Enter Schema: '
> accept tab_name prompt 'Enter TABLE: '
> prompt

>

> set serverout on size 1000000

>


> declare

> 	ind_name	varchar2(30);

> 	total_blocks	number;

> 	unused_blocks	number;

>


> 	total_bytes		number;

> 	unused_bytes	number;

>


> 	last_used_extent_file_id 	number;

> 	last_used_extent_block_id	number;

> 	last_used_block			number;

> 	cursor find_ind is

> 	select index_name from all_indexes where

> owner=upper('&&schema_name') and

> 	table_name=upper('&&tab_name') ;

>

> begin

>


> 	dbms_space.unused_space(upper('&&schema_name'),upper('&&tab_name'),

> 		'TABLE',total_blocks, total_bytes,unused_blocks,

> unused_bytes,

>


> last_used_extent_file_id,last_used_extent_block_id,last_used_block);

> 	dbms_output.put_line('Total space used by TABLE  '||

> 		upper('&&schema_name')||'.' ||upper('&&tab_name'));

>


> 	dbms_output.put_line('TOTAL BLOCKS  USED_BLOCKS   FREE BLOCKS');

> 	dbms_output.put_line('============  ===========   ===========');

> 	dbms_output.put_line(to_char(total_blocks)   ||'      '||

> 		to_char(total_blocks - unused_blocks)||'      '||

> 		to_char(unused_blocks));

> 	dbms_output.put_line('  ');

> 	open find_ind ;

> 	 loop

> 	 	fetch find_ind into ind_name ;

> 	 	exit when find_ind%NOTFOUND or find_ind%NOTFOUND is null ;

>
>


> dbms_space.unused_space(upper('&&schema_name'),upper(ind_name),'INDEX',

>  		total_blocks, total_bytes,

>  		unused_blocks, unused_bytes,

>  		last_used_extent_file_id ,

>  		last_used_extent_block_id,

>  		last_used_block	);

>


>  		dbms_output.put_line('Total space used by INDEX  '||

>  			upper('&&schema_name')||'.' ||upper(ind_name)||'

> '||

> 			to_char(total_blocks)   ||'

> '||to_char(total_blocks - unused_blocks)||

> 			'      '|| to_char(unused_blocks));

>  	end loop ;

>  	if find_ind%ISOPEN then close find_ind;

> 	end if;

>

> end;
> /

>

> set feed on

>
>

> Regards:
> Ferenc Mantfeld
> Senior Performance Engineer
> Siebel Performance Engineering
> Melbourne, 3000, VIC, Australia
> Only Robinson Crusoe had all his work done by Friday

---

Content-Type: application/octet-stream; charset="iso-8859-1"; name="tblindspace.sql"
Content-Transfer-Encoding: 7bit
Content-Description: 
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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 Jun 24 2002 - 14:43:24 CDT