Oracle-L: RE: bytes per extent

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Thu, 11 Apr 2002 07:43:41 -0800
Message-ID: <F001.00441DA9.20020411074341@fatcity.com>

Here are a few scripts:

PROMPT
PROMPT Show free and used blocks allocated to table or index and indicate HWM
PROMPT PROMPT This script will request 2 parameters if not specified on the command line:
PROMPT 1 = owner of tables to check (wild cards allowable) PROMPT 2 = name of table to check (wild cards allowable)

SET SERVEROUT ON SIZE 200000
SET VERIFY OFF ECHO OFF DECLARE
--

	CURSOR CU_tabl IS
	SELECT *
	FROM 	all_objects
	WHERE	owner LIKE UPPER('&&1')
	and	object_name LIKE UPPER('&&2')
	and	object_type IN ('TABLE','INDEX')
	ORDER BY owner,object_type,object_name;


--

	R_tabl CU_tabl%ROWTYPE;


--

SEGMENT_OWNER                  VARCHAR2(30);
SEGMENT_NAME                   VARCHAR2(30);
SEGMENT_TYPE                   VARCHAR2(20);
TOTAL_BLOCKS                   NUMBER      ;
TOTAL_BYTES                    NUMBER      ;
UNUSED_BLOCKS                  NUMBER      ;
UNUSED_BYTES                   NUMBER      ;
LAST_USED_EXTENT_FILE_ID       NUMBER      ;
LAST_USED_EXTENT_BLOCK_ID      NUMBER      ;
LAST_USED_BLOCK                NUMBER      ;


--

FREELIST_GROUP_ID              NUMBER   ;
FREE_BLKS                      NUMBER   ;
SCAN_LIMIT                     NUMBER   ;

L_temp NUMBER;
L_min_blocks NUMBER(40) DEFAULT 10;
L_act_perf BOOLEAN DEFAULT FALSE;

--

BEGIN FOR R_tabl IN CU_tabl LOOP
--

DBMS_SPACE.UNUSED_SPACE(R_tabl.owner,R_tabl.object_name,R_tabl.object_type,

		TOTAL_BLOCKS,
		TOTAL_BYTES,
		UNUSED_BLOCKS,
		UNUSED_BYTES,
		LAST_USED_EXTENT_FILE_ID,
		LAST_USED_EXTENT_BLOCK_ID,
		LAST_USED_BLOCK);


--

		freelist_group_id := 0;
		scan_limit :=999999;

sys.DBMS_SPACE.FREE_BLOCKS(R_tabl.owner,R_tabl.object_name,R_tabl.object_typ e,

		FREELIST_GROUP_ID,
		FREE_BLKS,
		SCAN_LIMIT);


--

			dbms_output.put_line('.');
			dbms_output.put_line('Table
'||R_tabl.owner||'.'||R_tabl.object_name||'('||R_tabl.object_type||')');
			dbms_output.put_line('Total blocks
='||TO_CHAR(TOTAL_BLOCKS,'999,999'));
			dbms_output.put_line('Total bytes(k)
='||TO_CHAR(TOTAL_BYTES/1024,'999,999'));
			dbms_output.put_line('Unused bytes(k)
='||TO_CHAR(UNUSED_BYTES/1024,'999,999'));
			dbms_output.put_line('Blocks above HWM
='||TO_CHAR(UNUSED_BLOCKS,'999,999'));
			dbms_output.put_line('Blocks below HWM
='||TO_CHAR(TOTAL_BLOCKS-UNUSED_BLOCKS-1,'999,999'));
			dbms_output.put_line('Free blocks
='||TO_CHAR(FREE_BLKS,'999,999'));
			IF (TOTAL_BLOCKS-1) >0 THEN
				dbms_output.put_line('%free wrt used blocks
='||TO_CHAR(free_blks/(TOTAL_BLOCKS-1) * 100,'999,999')||'%');
			ELSE
				dbms_output.put_line('%free wrt used blocks    =n/a');
			END IF;
			L_act_perf := TRUE;


--

	END LOOP;


--

	IF NOT L_act_perf THEN
		dbms_output.put_line('.');
		dbms_output.put_line('No tables found');
	END IF;

END;
/

--SET VERIFY ON
@@GetBlockSize

column blocks_used format 9,999,999 heading "Blocks used"
column bk          format 9,999,999 heading "Space used(k)"
column rc          format 9,999,999 heading "Row Count"

PROMPT Count up how many actual blocks are used for a table and translate this into kilobytes
PROMPT select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4))) Blocks_Used ,

COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))*&&_db_block_size/1024 bk,

COUNT(*) rc
from &1;

CLEAR COLUMNS And GetBlockSize is:

REM Get block size from V$PARAMETER and store for use in other utilities

undef _db_block_size
column xpvx new_value _db_block_size heading "Block size|(bytes)" Format A10

select	p.Value xpvx
from 	V$PARAMETER p
WHERE	name = 'db_block_size'

clear columns

All courtesy of Tim Onions I believe (but can't remember for sure)..

HTH Mark

-----Original Message-----

Sent: 11 April 2002 15:39
To: Multiple recipients of list ORACLE-L

Is there a query to get the number of bytes used and free in an each extent?

Ray Stell stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Ray Stell
INET: stellr_at_cns.vt.edu

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: Mark Leith
INET: mark_at_cool-tools.co.uk

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