We are using windows 2003 server and oracle 10.2.0.4, having two databases with 38 gb and another is 72 gb. And as per application team recommendation they suggest to do the re-organization of database for every week. So, I want to know whether doing re-organization of full database for every week is good or bad.
Suggestion:
You should not be re-organizing the database every week because someone said so. There are many approaches and reasons for re-organizing mainly fragmentation (also could be to change storage parameters/location).
If you have performance issues, you should check if you
are running statistics, how much changes (DML) are occurring in the database,
missing indexes, improperly sized memory pools, bad storage parameters
on tables etc.
We re-organize once a year max.
Here is how you would check for fragmentation (very basic check):
set verify off
ttitle -
center 'Index Fragmentation Statistic' skip 2
col owner format a25
col segment_name format a30
col segment_type format a20
--col max_extents format 9999999999
-- Table Fragmentation
select a.owner, segment_name, segment_type, sum(bytes)/1024/1024 "MB", max_extents, count(*)
from dba_extents a, dba_tables b
where a.segment_name = b.table_name
and a.owner = 'SOME_SCHEMA'
and a.tablespace_name='SOME_TBLSP' having count(*) > 500
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents
/
-- Index Fragmentation
select a.owner, segment_name, segment_type, sum(bytes)/1024/1024 "MB", a.tablespace_name, count(*)
from dba_extents a, dba_indexes b
where a.segment_name = b.index_name
and a.owner = 'SOME_SCHEMA'
having count(*) > 500
group by a.owner, segment_name, segment_type, a.tablespace_name
order by a.owner, segment_name, segment_type, a.tablespace_name
/
