Re: SQL : informations about occupied data/index
From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 22 Sep 2002 13:08:29 +0200
Message-ID: <uordpp42okvv1b_at_corp.supernews.com>
Date: Sun, 22 Sep 2002 13:08:29 +0200
Message-ID: <uordpp42okvv1b_at_corp.supernews.com>
Your analysis is incorrect.
The table DEPT has by default 10240 *bytes* allocated, not *blocks*, so 10k
is allocated. You can't use it for any purpose, so that space is *USED*
(Know what that is?)
The formula avg_row_len * num_rows also provides *meaningless* results, as
the *average* row length is the *average* row length (You know what the word
'average' means), and each block has a row directory, so there are more
bytes in every block, than your query shows.
If you think you know better, why don't you resolve your own problems?
-- Sybrand Bakker Senior Oracle DBA to reply remove '-verwijderdit' from my e-mail address "cava123" <cava123_at_noos.fr> wrote in message news:3D8D0C68.B7072AEA_at_noos.fr...Received on Sun Sep 22 2002 - 13:08:29 CEST
> request USER_SEGMENTS dont much informations about data USED
> sql> select * from user_segments where segment_name = 'DEPT'; ==> 10 240
> Blocks
> sql> select count(*) from dept ;
> ==> 4
> and the average size of data in DEPT is :
> sql> analyze table DEPT compute statistics ;
> sql> select avg_row_len * num_rows ||' bytes ' from user_tables where
> table_name='DEPT' ;
> ==> 92 bytes
>
> SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS
> EXTENTS FREELISTS
> -------------------- ----------------- -------------------- ---------
> --------- ---------
>
> DEPT TABLE USER_DATA 10240
1 1
>
> that is mean
> Daniel Morgan a écrit :
>
> > cava123 wrote:
> >
> > > Hi all,
> > > I create a table T_STATS (with User SCOTT) and want to store
> > > informations about occupied data/index in TS like this :
> > >
> > > USER TS NAME SIZE (MB) USED FREE
> > > STATS_DATE
> > > ------- -------------- --------- ----------
> > > ------- --------------
> > > SCOTT DATA_1 200 MB 50 MB 150 MB
> > > 24/04/2000
> > > SCOTT INDEX_1 100 MB 40 MB 60 MB
> > > 24/04/2000
> > > SCOTT DATA_1 200 MB 100 MB 50 MB
> > > 24/04/2001
> > > SCOTT INDEX_1 100 MB 60 MB 40 MB
> > > 24/04/2001
> > > SCOTT DATA_1 200 MB 170 MB 30 MB
> > > 24/04/2002
> > > SCOTT INDEX_1 100 MB 60 MB 40 MB
> > > 24/04/2002
> > >
> > > Someting look like :
> > > INSERT INTO SCOTT.T_STATS VALUES (SELECT .............., sysdate from
> > > xxx where ....) ;
> > >
> > > I dont know how to write the request.
> > > Thanks for help.
> > > I'm simple user in the database and have no grants for dba_% or V$%,
> > >
> > > Yhab
> > > yhab.abiad-sica_at_renault.com
> >
> > We don't do other people's homework assignments for them. Do your
> > research work and then we will give you pointers.
> >
> > Your pointer d'jour is:
> >
> > SQL> desc user_segments
> >
> > Daniel Morgan
>