Rebuild Index

[kpate]

Hello All,
I would like to know ,what will be best approach to solve fragmented table/index.Our database size is 20g.DBA before me did unlimited on tablespace and now one of the tablespace has reached to
12 g and i belive that there are lots of delete.Also we are planning
to have new servers in mar.'03.So should i wait until than to reorganize database or is there anyway which take minimal time .
1)Recreate table.
2)Rebuild Index.
3)Rebuild database.
If its rebuilding index,what will be the right steps
doing that online.Is it only one command below....

1)alter index test1 rebuild compute statistics.
i tried that on our development server.After doing for some of the index,it crash database.

[Sameer]

If you say there are lot of deletes took place then I would suggest export/import complete schema, thought it will take bit more time but It will be useful. And if you know specific tables, then just export/import those tables..

Sameer

[pando]

well you say you "believe" there are fragmentation but have you really looked into these tables/indexes and see if they are really fragmentated? If they are not use your time for more productive jobs

[grjohnson]

Originally posted by kpate
DBA before me did unlimited on tablespace and now one of the tablespace has reached to
12 g

Unlimited... on whay. Tablespace default parameter, datafile size...

12 GB.. is this for a particular file or the database on a whole?

I'd repost the results of the following queries to give us a larger understanding in what you are delaing with. A simple Import/Export, probably won't effectiviely deal with future fragmentation.

SELECT
t.tablespace_name "Tablespace",
t.status "Status",
ROUND((MAX(d.bytes)/1024/1024) - (NVL(SUM(f.bytes),0)/1024/1024),2) "Used MB",
ROUND(NVL(SUM(f.bytes),0)/1024/1024,2) "Free MB" ,
t.initial_extent "Initial Extent",
t.next_extent "Next Extent",
t.min_extents "Min Extents",
t.max_extents "Max Extents",
t.pct_increase "Pct Increase" ,
SUBSTR(d.file_name,1,80) "Datafile name"
FROM DBA_FREE_SPACE f , DBA_DATA_FILES d , DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name
AND f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id
GROUP BY t.tablespace_name , d.file_name , t.initial_extent , t.next_extent ,
t.min_extents , t.max_extents , t.pct_increase , t.status
ORDER BY 1,3 DESC;

Select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) "MB", initial_extent, next_extent, extents from dba_segments
where owner not in ('SYS', 'SYSTEM')
and extents > 100;

Cheeers,