Re: Getting current space of a table ...

Hello, Thomas!

I read all the answers you receive in this newsgroups and I think that is much work to do.

Usually, when a need storage information about Database Objects, I simply execute the command ANALYZE with option COMPUTE STATISTICS for the specific type of object. After this done, I execute queries in the Data Dictionary views, where Oracle stores a lot of useful information.

For example, for table SCOTT.TAB_TEST:

1. Generate the information you need:

analyze table SCOTT.TAB_TEST compute statistics;

2) Get the total of rows, blocks and empty blocks of that table:

select ROWS,BLOCKS,EMPTY_BLOCKS from DBA_TABLES

   where owner = 'SCOTT'

       and table_name = 'TAB_TEST';

You can construct any query you want with this structures, and repeat the analyze only if the size of object changes too much (the old information still in the Data Dictionary)

Hope this can help you, and sorry my poor english.

Regis Vaz
regis.vaz_at_zaz.com.br

P.S.: Don't forget that statistics activate de Cost Based Optimizer, if your applications isn't prepared to this you can experience performance problems. However, you can easy erase the unwanted statistics (ANALYZE <obj_type> <object> DELETE STATISTICS), or set the INIT.ORA parameter OPTIMIZER_MODE=RULE. Thomas Klinger wrote:

> Hi there!
>
> Sounds like an easy question.
> I want to get the used space of a table in percentage digits.
>
> I.e. I have a table which takes at its init size ~174MB. And it is
> still at extent 0. Max_Extents=6.
> But how much is the table really filled up? How to do this?
>
> USED_SPACE_OF_CURRENT_TAKEN_BLOCKS=?
> FREE_SPACE_IN_THIS_EXTENT=100%-USED_SPACE_OF_CURRENT_TAKEN_BLOCKS
>
> Is it also possible to get the value of available rows which can be
> inserted into FREE_SPACE_IN_THIS_EXTENT and how much rows can be
> inserted at the max?
>
> Can anyone help me?
>
> Kind regards
>
> Thomas Klinger
> Systemspecialist
> =======================================
> t.klinger_at_mobilkom.at
> http://www.mobilkom.at
> =======================================
Received on Tue Apr 06 1999 - 18:28:28 CDT