Hi,

I wish to reorganize my Oracle 8.1.7 tables & indexes (ora_storage).
Can anyone provide sql script to show the number of extents and size?

Thanks.

You can try to use this Oracle-SQL code -

select table_name, initial_extent, next_extent, min_extents,
max_extents, pct_increase, num_rows, avg_space
from dba_tables
where owner = 'BAAN' ;

That'll give you a good idea, but its limited based on your last "analyze table" run..

to get the exact info try:

select sum(bytes) from dba_segments
where segment_name = '<table name>';

and

select count(*) from dba_extents
where segment_name = '<table name>'

Dave

Some ages ago I used the attached script. As the script is nearly updated the last decade you have to decide what use is still in there.

Remember that when reorganising tables asks lots of time but nearly gives performance improvement when having less than 1000 extents on that table.

Kind regards,
Dick

Update: The script uses RDS_FULL and related parameters with a value of 1000. During testing I haven't had any problems, but it seems that these high values can give problems on certain platforms. Therefore I advise to lower the values to 200 or so.

If you´re clever enough, you should use locally tablespaces in oracle 8.1.7 and above.
So you don´t need to edit your ora_storage and insert any storage information except the tablespace, inittrans, pctfree, pctused.
So you´ll save a lot of time and get a fast database.