Re: Freeing up temporary segments

In article <3522B3B7.10A927E1_at_isc.upenn.edu>, Tad Davis wrote:

Before the Coalesce command was introduced, I uses the following PL/SQL to coalesce the space whenever required (e.g. just after dropping extents and prior to rebuilding)

| spool coalesce.out
| 
| set serveroutput on
| 
| DECLARE
| tsid number;
| cnt number;
| lev number;
| dummy integer;
| cur integer;
| nothing boolean := TRUE;
| 
| cursor contig is
|        select a.ts#, count(*)
|        from sys.fet$ a, sys.fet$ b
|        where a.ts#=b.ts#
|        and a.file#=b.file#
|        and (a.block#+a.length)=b.block#
|        group by a.ts#;
| begin
|  open contig;
|  while TRUE LOOP
|        fetch contig into tsid, cnt;
|        exit when contig%NOTFOUND;
|        nothing:= FALSE;
|        lev := power(2,16)*cnt + tsid;
|        if (lev < 0) or (lev > 4294967296) then
|                raise INVALID_NUMBER;
|        end if;
|        cur:= dbms_sql.open_cursor;
|        dbms_sql.parse(cur, 'alter session set events
|           ''immediate trace name coalesce level '||lev|| ' ''', dbms_sql.v7);
|        dummy:= dbms_sql.execute(cur);
|        dbms_sql.close_cursor(cur);
|        dbms_output.put_line('Tablespace '||tsid||' -- coalesced '||cnt||
|                ' extents.');
|  end loop;
|  close contig;
|  if (nothing) then
|        dbms_output.put_line('Nothing to coalesce!');
|  end if;
| end;
| /
| 
| spool off
| exit

regs

Neil Chandler

>Alan V wrote:
>
>> ALTER TABLESPACE xxxxx COALESCE;
>
>We had the same problem in Oracle 7.1, which does not have a COALESCE
>option. The symptom was that when we recreated indexes after an update,
>we kept running into "initial extent" problems, even though (in theory)
>there was plenty of contiguous free space available. Our DBA group
>applied a patch from Oracle that fixed the problem -- at least it hasn't
>happened again since then, although it was happening 2-3 times a week
>before that. I don't know the patch ID but will try to get more info.
>
>--
>Tad Davis Lead Programmer Analyst
>davist_at_isc.upenn.edu Information Systems and Computing
>voice 215-898-7864 Administrative Information Technologies
>fax 215-898-0386 University of Pennsylvania
Received on Thu Apr 02 1998 - 00:00:00 CST