Re: Database Fragmentation?
Date: 1995/08/03
Message-ID: <NEWTNews.807462375.28394.steve_at_excal.gate.net>#1/1
In article <DCA1Er.4A5_at_freenet.carleton.ca>, <ab454_at_FreeNet.Carleton.CA> writes:
> Newsgroups: comp.databases.oracle
> Path: news.gate.net!news.sprintlink.net!howland.reston.ans.net!torn!nott!cunews!freenet.carleton.ca!FreeNet.Carleton.CA!ab454
> From: ab454_at_FreeNet.Carleton.CA (Darren Mallette)
> Subject: Database Fragmentation?
> Message-ID: <DCA1Er.4A5_at_freenet.carleton.ca>
> Sender: ab454_at_freenet2.carleton.ca (Darren Mallette)
> Reply-To: ab454_at_FreeNet.Carleton.CA (Darren Mallette)
> Organization: The National Capital FreeNet
> Date: Tue, 25 Jul 1995 14:54:27 GMT
> Lines: 14
>
>
> Hello,
>
> I'm looking for a sql script that shows the amount of fragmentation in an
> Oracle 6 database. I had a file (posted here about 1.5 years ago) which
> showed all the database objects and the database files they are stored in,
> but I think it's long gone from my hard drive. Any scripts/advice is
> greatly appreciated.
>
> Darren
>
> --
> -Darren Mallette, computer guy
> Home page: http://www.magi.com/~dooley/index.html
>
For free space fragmentation:
select tablespace_name, count(0), max(bytes) / 1024 KB from sys.dba_free_space group by tablespace_name;
returns the number of free extents and the size of the largest extent per tablespace. Ideally, there should be only one free extent per data file in the tablespace.
For table/index fragmentation (excessive growth)
select owner, segment_name, count(0), sum(bytes) / 1024 KB from sys.dba_extents having count(0) > 10 group by owner, segment_name;
Returns a list of tables/indexes with more than 10 extents. Time to rethink the next extent/pct increase plan.
For Oracle V6 you can re-combine adjacent free extents by creating/dropping temporary tables of the appropriate size. I have seen PL/SQL scripts to do this, and I have a Pro*C program for the same purpose. If you would like a copy, contact me via E-mail -- excal_at_gate.net
Steve Preisach
Excalibur Software Consultants
Received on Thu Aug 03 1995 - 00:00:00 CEST