Re: bytes per extent
There are lots of versions of this around, but this reports the sum of the allocated extents. My problem is that the creater of this db made a huge extent and I don't seem to have a way to track growth within the extent. On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote: Here is a script that I got from another lister. Hope it is what you're looking for. column tsname format a25 heading 'Tablespace Name' column tot format 99,999,999 heading 'Size (K)' column fsp Format 99,999,999 heading 'Free (K)' column csp Format 999,999 heading 'Free|Extents' column msp Format 9,999,999 heading 'Max Free|Ext (K)' column pctused Format 999.99 heading '% Used' column tsno noprint compute sum of tot fsp on report break on report select fi.tablespace_name tsname, sum(fi.bytes)/1024 tot, iv.free/1024 fsp, ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 pctused, iv.no_of_exts csp, iv.max/1024 msp from dba_data_files fi, ( select t.tablespace_name, NVL(MAX(f.bytes),0) max, NVL(sum(f.bytes),0) free, count(f.bytes) no_of_exts from sys.dba_free_space f, sys.dba_tablespaces t where t.tablespace_name=f.tablespace_name(+) and t.status != 'INVALID' group by t.tablespace_name ) iv where fi.tablespace_name = iv.tablespace_name GROUP BY fi.tablespace_name, iv.free, iv.no_of_exts, iv.max ORDER BY fi.tablespace_name / clear breaks clear columns clear computes Regards, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 11, 2002 10:39 AM Is there a query to get the number of bytes used and free in an each extent? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^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: Ruth Gramolini 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^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).
RE: bytes per extent
I am assumming the table is contained within one extent. If you did an analyze table, wouldn't that show the amount of data (num_rows * avg_row_len) within the table (extent) which could be used to monitor growth. -Original Message- Sent: April 12, 2002 10:04 AM To: Multiple recipients of list ORACLE-L There are lots of versions of this around, but this reports the sum of the allocated extents. My problem is that the creater of this db made a huge extent and I don't seem to have a way to track growth within the extent. On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote: Here is a script that I got from another lister. Hope it is what you're looking for. column tsname format a25 heading 'Tablespace Name' column tot format 99,999,999 heading 'Size (K)' column fsp Format 99,999,999 heading 'Free (K)' column csp Format 999,999 heading 'Free|Extents' column msp Format 9,999,999 heading 'Max Free|Ext (K)' column pctused Format 999.99 heading '% Used' column tsno noprint compute sum of tot fsp on report break on report select fi.tablespace_name tsname, sum(fi.bytes)/1024 tot, iv.free/1024 fsp, ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 pctused, iv.no_of_exts csp, iv.max/1024 msp from dba_data_files fi, ( select t.tablespace_name, NVL(MAX(f.bytes),0) max, NVL(sum(f.bytes),0) free, count(f.bytes) no_of_exts from sys.dba_free_space f, sys.dba_tablespaces t where t.tablespace_name=f.tablespace_name(+) and t.status != 'INVALID' group by t.tablespace_name ) iv where fi.tablespace_name = iv.tablespace_name GROUP BY fi.tablespace_name, iv.free, iv.no_of_exts, iv.max ORDER BY fi.tablespace_name / clear breaks clear columns clear computes Regards, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 11, 2002 10:39 AM Is there a query to get the number of bytes used and free in an each extent? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^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: Ruth Gramolini 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^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: Browett, Darren 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
Re: bytes per extent
On Fri, Apr 12, 2002 at 12:13:25PM -0800, Browett, Darren wrote: I am assumming the table is contained within one extent. If you did an analyze table, wouldn't that show the amount of data (num_rows * avg_row_len) within the table (extent) which could be used to monitor growth. Yep, but I don't want an estimate, I want the byte count. great metalink notes I found: Note:116565.1 Note:116923.1 -Original Message- Sent: April 12, 2002 10:04 AM To: Multiple recipients of list ORACLE-L There are lots of versions of this around, but this reports the sum of the allocated extents. My problem is that the creater of this db made a huge extent and I don't seem to have a way to track growth within the extent. On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote: Here is a script that I got from another lister. Hope it is what you're looking for. column tsname format a25 heading 'Tablespace Name' column tot format 99,999,999 heading 'Size (K)' column fsp Format 99,999,999 heading 'Free (K)' column csp Format 999,999 heading 'Free|Extents' column msp Format 9,999,999 heading 'Max Free|Ext (K)' column pctused Format 999.99 heading '% Used' column tsno noprint compute sum of tot fsp on report break on report select fi.tablespace_name tsname, sum(fi.bytes)/1024 tot, iv.free/1024 fsp, ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 pctused, iv.no_of_exts csp, iv.max/1024 msp from dba_data_files fi, ( select t.tablespace_name, NVL(MAX(f.bytes),0) max, NVL(sum(f.bytes),0) free, count(f.bytes) no_of_exts from sys.dba_free_space f, sys.dba_tablespaces t where t.tablespace_name=f.tablespace_name(+) and t.status != 'INVALID' group by t.tablespace_name ) iv where fi.tablespace_name = iv.tablespace_name GROUP BY fi.tablespace_name, iv.free, iv.no_of_exts, iv.max ORDER BY fi.tablespace_name / clear breaks clear columns clear computes Regards, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 11, 2002 10:39 AM Is there a query to get the number of bytes used and free in an each extent? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^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: Ruth Gramolini 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^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: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: bytes per extent
Here is a script that I got from another lister. Hope it is what you're looking for. column tsname format a25 heading 'Tablespace Name' column tot format 99,999,999 heading 'Size (K)' column fsp Format 99,999,999 heading 'Free (K)' column csp Format 999,999 heading 'Free|Extents' column msp Format 9,999,999 heading 'Max Free|Ext (K)' column pctused Format 999.99 heading '% Used' column tsno noprint compute sum of tot fsp on report break on report select fi.tablespace_name tsname, sum(fi.bytes)/1024 tot, iv.free/1024 fsp, ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 pctused, iv.no_of_exts csp, iv.max/1024 msp from dba_data_files fi, ( select t.tablespace_name, NVL(MAX(f.bytes),0) max, NVL(sum(f.bytes),0) free, count(f.bytes) no_of_exts from sys.dba_free_space f, sys.dba_tablespaces t where t.tablespace_name=f.tablespace_name(+) and t.status != 'INVALID' group by t.tablespace_name ) iv where fi.tablespace_name = iv.tablespace_name GROUP BY fi.tablespace_name, iv.free, iv.no_of_exts, iv.max ORDER BY fi.tablespace_name / clear breaks clear columns clear computes Regards, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 11, 2002 10:39 AM Is there a query to get the number of bytes used and free in an each extent? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^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: Ruth Gramolini 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).
RE: bytes per extent
Here are a few scripts:
PROMPT
PROMPTShow 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:
PROMPT1 = owner of tables to check (wild cards allowable)
PROMPT2 = name of table to check (wild cards allowable)
SET SERVEROUT ON SIZE 20
SET VERIFY OFF ECHO OFF
DECLARE
--
CURSOR CU_tabl IS
SELECT *
FROMall_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_BYTESNUMBER ;
UNUSED_BLOCKS NUMBER ;
UNUSED_BYTES NUMBER ;
LAST_USED_EXTENT_FILE_ID NUMBER ;
LAST_USED_EXTENT_BLOCK_ID NUMBER ;
LAST_USED_BLOCKNUMBER ;
--
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 :=99;
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
fromV$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 KE4TJC28^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
