topic Re: Database Fragmentation in Operating System - HP-UX

Database Fragmentation

Manuel Gómez González — Tue, 13 Jul 2004 06:23:01 GMT

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.

Re: Database Fragmentation

Steve Steel — Tue, 13 Jul 2004 06:42:48 GMT

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

Re: Database Fragmentation

Yogeeraj_1 — Tue, 13 Jul 2004 07:56:00 GMT

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

Re: Database Fragmentation

Eric Antunes — Tue, 13 Jul 2004 08:48:48 GMT

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!

Re: Database Fragmentation

Eric Antunes — Tue, 13 Jul 2004 08:53:59 GMT

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