Re: Efficient way to monitor table growth...

From: <tday6_at_csc.com>
Date: Tue, 28 Aug 2001 07:06:13 -0700
Message-ID: <F001.00379811.20010828061034@fatcity.com>

Here's a script (for Oracle 8) that will show you exactly how much space a given table uses, as opposed to how much space its extents have reserved. It takes one parameter, the name of the table being looked at. It can take a long time to run so I'm not sure if that counts as efficient. If you want to see tablespace fragmentation, then run the mapper script down below.

rem ****************************************************

rem this code was inspired by Kevin Loney

rem ****************************************************

set verify off;
set pages 24;
select COUNT(DISTINCT(SUBSTR(a.ROWID,1,16)))* b.value Bytes_Used from &&1 a,
v$parameter b where b.name = 'db_block_size' group by b.value /

MAPPER rem
rem file: mapper.sql
rem location: $HOME/sql
rem parameters: the tablespace name being mapped rem
rem Sample invocation:
rem @mapper SYSTEM
rem
rem This script generates a mapping of the space usage rem (free space vs used) in a tablespace. It graphically rem shows segment and free space fragmentation. rem
set pagesize 66 linesize 132 verify off
ttitle 'Map of Tablespace ' &&1 right datevar skip 1 column substr(file_id,1,4) heading "File|Id" column bytes format 999,999,999,999
column today noprint new_value datevar

SELECT
to_char(sysdate, 'MM/DD/YY') today,
'free space' owner, /*"owner" of free space*/ ' ' object, /*blank object name*/
substr(file_id,1,4), /*file ID for the extent header*/ block_id, /*block ID for the extent header*/ blocks, /*length of the extent in blocks*/ bytes /*length of the extent in bytes*/
FROM sys.dba_free_space
WHERE tablespace_name = upper('&&1')
UNION
SELECT
to_char(sysdate, 'MM/DD/YY') today,

substr(owner,1,10), /*owner name (first 20 chars)*/
substr(segment_name,1,27), /*segment name (first 26 chars)*/
substr(file_id,1,4), /*file ID for extent header*/

block_id, /*block ID for block header*/
blocks, /*length of the extent in blocks*/ bytes /*length of the extent in bytes*/
FROM sys.dba_extents
WHERE tablespace_name = upper('&&1')
ORDER BY 4,5 /
undefine 1

                    "Michael                                                           
    
                    Netrusov"            To:     Multiple recipients of list ORACLE-L  
    
                    <mn_at_g-fax.com        <ORACLE-L_at_fatcity.com>                        
    
                    >                    cc:                                           
    
                    Sent by:             Subject:     Re: Efficient way to monitor 
table   
                    root_at_fatcity.        growth...                                     
    
                    com                                                                
    
                                                                                       
    
                                                                                       
    
                    08/27/2001                                                         
    
                    07:40 PM                                                           
    
                    Please                                                             
    
                    respond to                                                         
    
                    ORACLE-L

Analyze tables then look at dba_tables.num_rows. Doing it on a daily will give you what you want.

HTH,
Michael

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, August 27, 2001 15:58

> Hi DBA's

>
> Does an Oracle Stored Procedure or function exist that returns the row
count
> by table in each tablespace?
> I've observed that one of our production tablespaces is has only 8% free
> space. Perhaps it could be fragmented. I'll have to find a script that
can
> provide these statistics. I know I'll need to add another datafile to the
> tablespace soon.
> However, I'd like to find out which are the active tables in each
tablespace
> and track the row insert growth on a daily basis.

> Any ideas?

> Thanks for your help.

> Denmark Weatherburne
> Belize
> "Knowledge is power, but it is only useful if it is shared!"

>


> _________________________________________________________________

> Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp

> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Denmark Weatherburne
> INET: denmark_weatherburne_at_hotmail.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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Michael Netrusov
  INET: mn_at_g-fax.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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: tday6_at_csc.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 Tue Aug 28 2001 - 09:06:13 CDT