Greatest Common Divisor

Here is a little routine that implements Euclid's method for calculating the greatest common divisor.

  function gcd(a number,b number) return number is
   r1  pls_integer;
   a1 pls_integer := a;
   b1 pls_integer := b;
  begin
   if a1 = 0 then return b1; end if;
   while b1 != 0 loop
     r1 := mod(a1,b1);
     a1 := b1;
     b1 := r1;
    end loop;
    return a1;
  end;

Now I'm sure you're all thrilled but what has that got to do with Oracle databases?

Well, it is very useful if you have existing databases that you want to convert a dictionary managed tablespace to a locally managed tablespace. In the DBMS_SPACE_ADMIN procedure you can specify an allocation unit (or Oracle will determine one for you). But of course, its handy to know what this allocation will be before you do the conversion - because if its tiny, then you want to reconsider the migration

Here is a little routine to do this for a nominated tablespace

Note: You will want to have a careful read of the updates here before doing a conversion from dictionary to locally managed.

declare
  x number;
  f number;
  c pls_integer := 1;
  type numlist is table of pls_integer index by binary_integer;
  b numlist;
begin
  select bytes 
  bulk collect into b 
  from ( select bytes from dba_extents 
         where tablespace_name = '&tspace' 
         union all
         select bytes from dba_free_space
         where tablespace_name = '&tspace');
  for i in b.first .. b.last loop
    if c = 1 then f := b(i);
    elsif c = 2 then
       x := gcd(b(i),f);
    else
       x := gcd(b(i),x);
    end if;
    c := c + 1;
  end loop;
  dbms_output.put_line(x);
end;
/