Database Monitoring

[prakashs43]

Hi Gurus ,

Situation Here is we have increased initial extents of each table to 10 MB in Production server . now we would like to monitor how much space has been occupied in each table .
could u suugest me the sql query .

Thanks in advance .

[pando]

use dbms_space to find real usage, or free blocks in your segments

[prakashs43]

could u suggest me the dictionary table name or the sql query

thanks

[pando]

dbms_space is a package

[stecal]

Oracle DBA?

[pando]

ok ok let's dont be sarcastic

[prakashs43]

Hi stecal ,
(senior member)

I very well know that DBMS_space is a package . i was looking for the dictionary tables from which this package is prepared . Anyway sorry for not projecting the problem the way i wanted.

[dave777]

You can use this SQL to monitor table size and #rows by schema.

SELECT substr(OWNER||'.'||TABLE_NAME,1,35) "Table",
ceil(decode (blocks,0,initial_extent*min_extents/(1024*1024),
null,initial_extent*min_extents/(1024*1024),
(initial_extent*min_extents + ((blocks*4096) - (initial_extent*min_extents)))
/(1024*1024))) "Size (MB)" , NUM_ROWS
FROM DBA_TABLES
WHERE OWNER in ('')
and ceil(decode (blocks,0,initial_extent*min_extents/(1024*1024),
null,initial_extent*min_extents/(1024*1024),
(initial_extent*min_extents + ((blocks*4096) - (initial_extent*min_extents)))
/(1024*1024))) <= 4
ORDER BY 2 desc

Replace with actual schema name and blocks*4096 with your db_block_size.

Regards.

[hany]

hi guys,
when exactly do we need to increase the initial extent?
what difference does it make if the initial extent is increased or no?
another question, if i increase the initial extent and i have set the pctincrease to a number greater than zero, does that mean that the next extents will grow also? ( i mean the extents already created).

Thanks in advance.

[Edited by hany on 07-16-2002 at 01:50 AM]