Re: Space Utilization Problems

From: John P. Higgins <jh33378_at_deere.com>
Date: Tue, 02 Jun 1998 11:53:06 -0500
Message-ID: <35742DF1.4BCFEE38@deere.com>

Here is a SQL script to check a table:

variable total_blocks number
variable total_bytes number
variable unused_blocks number
variable unused_bytes number

variable last_file_id number
variable last_extent number
variable last_used_block number
variable free_blocks number

set autoprint on
accept owner prompt 'Enter owner name ===> ' accept tabname prompt 'Enter table name ==> ' column tab new_value table
select upper('&tabname') tab from dual; execute sys.dbms_space.unused_space ('&&owner','&&table','TABLE', -

:total_blocks, -
:total_bytes, -
:unused_blocks, -
:unused_bytes, -
:last_file_id, -
:last_extent, -
:last_used_block );

execute sys.dbms_space.free_blocks ('&&owner','&&table','TABLE', 0, - :free_blocks );
set autoprint off
undefine table
undefine owner

stuco_at_mailcity.com wrote:

> In article <35739C89.C852F4F3_at_dplus.net>,
> Em Pradhan <empradhan_at_dplus.net> wrote:
> >
> >
> > stuco_at_mailcity.com wrote:
> >
> > > When I query my DBA_FREE_SPACE and DBA_DATA_FILES, I notice that my free
> > > space and space used has not changed in byte count in a week in all
> > > tablespaces, save for SYSTEM and an index tablespace, where I explicitly
> > > created a new index late last week.
> > >
> > > These loads occur every morning through a Pro*C application via an
> > > application server and we INSERT approx 20,000 new rows daily into a
> > > particular tablespace.
> > >
> > > I am running Oracle 7.3.3 on NT 4.0. My machine is an HP Netserver LX Pro
> > > with two 200Mhz Pentium Processors, 128MB memory and their corresponding
> > > Model 1 Raid Array.
> > >
> > > Do you think I have to totally rebuild my database???
> > >
> > > Thanking you in advance for your help.
> > >
> > > Stuart Cowen
> > > DBA Consultant
> > > Paladin Consulting, Inc. - Dallas
> > >
> > > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > > http://www.dejanews.com/ Now offering spam-free web-based newsreading
> >
> > Hi
> > Analyzing table where U insert 20K rows everyday will give U more
> > info that why your dba_free_space view is not changed. Well my guess is
> > you have created table with enough initial ... this is just a guess.. .
> > 'cause
> > in one instance some one did mentioned same problem and it happened
> > to be a table with initial of 64M and total row length was 40 bytes only.
> > Hope this will help
> > Good luck
> >
> > Pradhan
> >
> >

> Well, that was certainly a good guess! As it turns out, I have several
> tables that have a very large initial extent size for the data they are
> holding! Example: a 40MB initial extent for a table that has ZERO rows!
> That's right, NO DATA.

> Now, I have another question. How do I find out the exact amount of raw data
> (not allocated) that is in each table?

> Again, thanks for your help out there!

> Stuart

> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Tue Jun 02 1998 - 11:53:06 CDT