Re: Database Fragmentation?

From: Steve Preisach <steve_at_mailhost.gate.net>
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

Original text of this message