Re: Bytes Used Inside an Extent

From: <jkstill_at_cybcon.com>
Date: Wed, 24 Jan 2001 09:36:45 -0800 (PST)
Message-Id: <10751.127383@fatcity.com>

On Wed, 24 Jan 2001, Peter Barnett wrote:

> Does anyone have a script to determine the bytes actually used by an
> extent? We are using locally managed tablespaces with uniform extents
> for a warehouse. Once the tables are loaded, they will be static until
> they are reloaded.
>
> Just running the math, I have am pretty sure that the last extent (4M in
> size) is near empty for most of the tables. I would like to be able to look into
> that last extent and confirm my suspicions.
>
> Thanks,
> Pete Barnett

Pete,

The best way I can think of at the moment is to use DBMS_SPACE.

It doesn't break it down by extent, but will tell you total blocks and total unused blocks among other things.

You may already have a script there to do it.

If my home directory is still there, you can find it at:   /home/jkstill/oracle/admin/sql/segfree.sql

or

  /opt/share/oracle/lib/segfree.sql

If not, I've included it below.

HTH Jared

---

set echo off term on feed on pause off verify off

prompt Username to check space for:
col cobjuser noprint new_value uobjuser
set term off feed off
select upper('&1') cobjuser from dual;
set term on feed on

prompt Object to check space on ( for &&uobjuser user ): col cobjname noprint new_value uobjname
set term off feed off
select upper('&2') cobjname from dual;
set term on feed on

prompt Type of object: - (T)able (I)ndex col cobjtype noprint new_value uobjtype
set term off feed off
select ('&3') cobjtype from dual;
set term on feed on

set serverout on size 1000000

prompt
prompt
prompt

set feed off

declare

	op1 number;
	op2 number;
	op3 number;
	op4 number;
	op5 number;
	op6 number;
	op7 number;
	objname varchar2(30);
	objtype varchar2(10);
	objuser varchar2(30);

begin

        select upper('&&uobjuser') into objuser from dual;

	select
		decode(upper('&&uobjtype'),
			'T','TABLE',
			'I','INDEX',
			NULL
		) into objtype from dual;

	select upper('&&uobjname') into objname from dual;

	dbms_space.unused_space(objuser,
		objname, objtype,
		op1,op2,op3,op4,op5,op6,op7);

	dbms_output.put_line('schema                    = ' || objuser);
	dbms_output.put_line('object name               = ' || objname);
	dbms_output.put_line('object type               = ' || objtype);
	dbms_output.put_line('-----------------------------');
	dbms_output.put_line('	total_blocks              = '||op1);
	dbms_output.put_line('	total_bytes               = '||op2);
	dbms_output.put_line('	unused_blocks             = '||op3);
	dbms_output.put_line('	unused_bytes              = '||op4);
	dbms_output.put_line('	last_used_extent_file_id  = '||op5);
	dbms_output.put_line('	last_used_extent_block_id = '||op6);
	dbms_output.put_line('	last_used_block           = '||op7);

Received on Wed Jan 24 2001 - 11:36:45 CST