Database Fragmentation

[Manuel Gómez González]

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.

[Steve Steel]

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

[Yogeeraj_1]

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

[Eric Antunes]

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!

[Eric Antunes]

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