cancel
Showing results for 
Search instead for 
Did you mean: 

Database Fragmentation

Database Fragmentation

We are planning to rebuild a 8.0.4 Oracle database avoiding possible fragmentation problems.

Before proceeding, we want first to clarify if fragmentation issues are happening or not.

So what we need is any sript that helps on discovering tables and tablespaces fragmentation and then how to recreate objects with the corect clauses to avoid it.

Thanks for your help.
4 REPLIES
Steve Steel
Honored Contributor

Re: Database Fragmentation

Hi

http://www.think-forward.com/sql_scripts.htm

http://www.think-forward.com/sql/ts_free_frag.htm

http://www.orsweb.com/master-list/p-master-list.html


for scripts


For how to avoid
http://www.orsweb.com/downloads/sql/space_management/

Admin processes

Steve Steel





If you want truly to understand something, try to change it. (Kurt Lewin)
Yogeeraj_1
Honored Contributor

Re: Database Fragmentation

hi,

two important things you may wish to consider for the operation you are trying to do:

1. Upgrade to 8.1.7.4.
2. Start using or convert all your Dictionary managed tablespaces (DMT) to Locally managed tablespaces (LMT) (which will allow you to NEVER do such a thing again in the future!!)

if you need any further assistance, please let us know.

best regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Database Fragmentation

Hi,

Tablespaces:

Don't use autoextend because you'll lose the control of extents.

Coalesce the tablespaces:

select 'alter tablespace '||tablespace_name||' coalesce;'
from dba_free_space_coalesced
where percent_extents_coalesced < 50 and
tablespace_name not in ('SYSTEM','SYS')



Segments (tables and indexes):

First you may want to see the major problematic segments:

SELECT COUNT(*), segment_name
FROM DBA_EXTENTS
GROUP BY segment_name
ORDER BY 1 DESC

For the problematic extents you must redefine (increase) the storage values...


Tables:

ALTER TABLE . STORAGE ( NEXT K);

But the above step will just prevent future fragments, not correct the existents. To correct the existent non-sense of extents you must export and import each table that have those problems: you must do the export with the COMPRESS=Y parameter so the import will put the data in only one extent: the first one!


Indexes:

Here you can rebuild.

alter index . rebuild
storage (initial K next K); -- A good rule is to use NEXT_SIZE = INITIAL_SIZE / 2



Hope this and the other answers will help you!

Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Database Fragmentation

One more thing I've forgotted:

Don't change anything on the SYS ans SYSTEM users:

SELECT COUNT(*), segment_name
FROM DBA_EXTENTS
where owner not in ('SYS','SYSTEM')
GROUP BY segment_name
ORDER BY 1 DESC
Each and every day is a good day to learn.