DBA Question - Finding out ACTUAL space used in n

From: Robert L. Manieri <rmanieri_at_novalink.com>
Date: Thu, 30 Dec 93 13:16:54 EST
Message-ID: <du6e30300009_at_novalink.com>

>
> Are there any ORACLE 6.xx DBAs out there with utilities or
> ways to find out just how much space WITHIN an extent is
> actually being used? We are setting up a new database and
> are trying to evaluate the goodness of the table and index
> space calculation formulas we have used (from Chapter 16 of
> the DBA Manual). We have gone into the 2nd extent in some
> cases (especially for indexes where we didn't multiply the
> formula by 1.1 as suggested) but don't know by how much.
> It would be very helpful if we could somehow 'see inside'
> the extent to evaluate just how much we are off.
>
> Thanks - and yes we know that ORACLE 7.xx will give us more
> DBA tools but won 't be able to convert for a while yet.
> Janet Walker
> Naval Command, Control and Ocean Surveillance Center, RDT&E DIV
> Code 0291, San Diego, California 92152 (619) 553-6838
> MILNET: janet_at_nosc.mil
> UUCP: {ihnp4,akgua,decvax,dcdwest,ucbvax}!sdcsvax!nosc!janet
> --------------------------------------------------------------------

Janet:
Here's a query that will show you block usage. A word of caution though, it is a real CPU ***HOG***, so use it wisely. See ya in the funny papers!
Bob.
.
rem

---

rem = Copyright (c) PirateWare - All Rights Reserved. rem

---

rem FILE: QBLCK.SQL

rem       This file will show the number of total blocks used by a
rem       table, the name of which it asks for.
rem       This DOES NOT provide you with the number of blocks per file.
rem       That can be done by using this same query and adding:
rem                GROUP BY SUBSTR(ROWID,15,4)
REM       TO THE END OF IT.

rem

---

rem
rem Revision History :
rem Date: Name: Comment:
rem 01 Oct 1990 R.Manieri Creation.
rem
rem

---

SELECT '&&username_dot_tablename:' OWNER_TABLE, COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))+1 BLOCKS FROM  &&username_dot_tablename;  

---

  Bob Manieri - Database Administration Consultant

                 Aston Brooke Corp, Plymouth Meeting, PA
                 Pager: 609-342-0314  FAX: 609-853-8050

  email : rmanieri_at_novalink.com

---

          It has been said that a DBA is a professional paranoid.
                    Just because you're paranoid,
             doesn't mean that they're not out to get you.
========================================================================

Received on Thu Dec 30 1993 - 19:16:54 CET