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 [EMAIL PROTECTED] (540) 231-4109 KE4TJC 28^D
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ray Stell
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: Mark Leith
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).
