Re-Organization Database Weekly Good or Bad

Doing re-organization of database every week is good or bad? 

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
 

See Also
Describing All Tables In Database

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.