Re: Script to find segment that have just ONE more extent

From: David Fitzjarrell <oratune_at_aol.com>
Date: Mon, 08 Jan 2001 23:28:23 GMT
Message-ID: <93died$gal$1@nnrp1.deja.com>

In our last gripping episode Luis Santos <lsantos_at_pobox.com> wrote:
> Hi!
>
> Not really.
>
> Letīs assume that you have a segment with a next extent of 500000
> bytes and, on its tablespace, you have the highest chunk of freespace
> of 600000 and the next one of 450000.
>
> The next allocation can be done BUT THE FOLLOWING ONE NOT.
>
> This is true in despite of any value for maxextents.
>
> In article <3A59DD53.62FD9E62_at_edcmail.cr.usgs.gov>,
> Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> > Try:
> >
> > SELECT owner,segment_name,segment_type
> > FROM dba_segments
> > WHERE max_extents - extents = 1;
> >
> > HTH,
> > Brian
> >
> > Luis Santos wrote:
> > >
> > > Hello!
> > >
> > > There are several scripts on the Web (and itīs easy to write
 one!)
> > > that show the segments that canīt be extended anymore, due to
> > > maxextents reached or no more contiguous space on the segmentīs
> > > tablespace.
> > >
> > > But itīs not easy to find or write a script that shows all the
> > > segments that can allocate more one, and only one, extent.
> > >
> > > Does someone has such a script?
> > >
> > > Best regards
> > >
> > > --
> > > Luis Santos
> > > Oracle DBA
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> > --
> > ========================================
> > Brian Peasland
> > Raytheons Systems at
> > USGS EROS Data Center
> > These opinions are my own and do not
> > necessarily reflect the opinions of my
> > company!
> > ========================================
> >
>
> --
> Luis Santos
> Oracle DBA
>
> Sent via Deja.com
> http://www.deja.com/
>

Let's try this, shall we:

set serveroutput on size 1000000
spool one_extent
declare

	cursor get_bytes (tspce in varchar2) is
	select bytes
	from dba_free_space
	where tablespace_name = tspce
	order by 1 desc;

	cursor get_next_ext is
	select owner, tablespace_name, table_name, next_extent
	from dba_tables
	order by 1, 2, 3 desc;

	ctr	number:=0;
	prev_tname	dba_tables.table_name%type;

begin
	for next_ext in get_next_ext loop
		if prev_tname is not null and next_ext.table_name <>
prev_tname then
			ctr := 0;
		end if;
		for remain in get_bytes(next_ext.tablespace_name) loop
			ctr := ctr + 1;
			if next_ext.next_extent > remain.bytes and ctr
= 1 then
				dbms_output.put_line
('Table '||next_ext.owner||'.'||next_ext.table_name||' cannot extend.');
			end if;
			if next_ext.next_extent = remain.bytes and ctr
= 1 then
				dbms_output.put_line

('Table '||next_ext.owner||'.'||next_ext.table_name||' has one extent left.');

			end if;
			if remain.bytes - next_ext.next_extent >= 0 and
			   remain.bytes - next_ext.next_extent <
next_ext.next_extent and ctr = 1 then
				dbms_output.put_line

('Table '||next_ext.owner||'.'||next_ext.table_name||' has one extent left.');

			end if;
		end loop;
		prev_tname := next_ext.table_name;
	end loop;

end;
/

declare

	cursor get_bytes (tspce in varchar2) is
	select bytes
	from dba_free_space
	where tablespace_name = tspce
	order by 1 desc;

	cursor get_next_ext is
	select owner, tablespace_name, index_name, next_extent
	from dba_indexes
	order by 1, 2, 3 desc;

	ctr	number:=0;
	prev_iname	dba_indexes.index_name%type;

begin
	for next_ext in get_next_ext loop
		if prev_iname is not null and next_ext.index_name <>
prev_iname then
			ctr := 0;
		end if;
		for remain in get_bytes(next_ext.tablespace_name) loop
			ctr := ctr + 1;
			if next_ext.next_extent > remain.bytes and ctr
= 1 then
				dbms_output.put_line
('Index '||next_ext.owner||'.'||next_ext.index_name||' cannot extend.');
			end if;
			if next_ext.next_extent = remain.bytes and ctr
= 1 then
				dbms_output.put_line

('Index '||next_ext.owner||'.'||next_ext.index_name||' has one extent left.');

			end if;
			if remain.bytes - next_ext.next_extent >= 0 and
			   remain.bytes - next_ext.next_extent <
next_ext.next_extent and ctr = 1 then
				dbms_output.put_line

('Index '||next_ext.owner||'.'||next_ext.index_name||' has one extent left.');

			end if;
		end loop;
		prev_iname := next_ext.index_name;
	end loop;

end;
/

spool off

I believe this will accomplish your goal, however you should test it on your database. As I have no tables in where only one extent or less is left I cannot vouch for the code, although it looks correct to me.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/

Received on Mon Jan 08 2001 - 17:28:23 CST