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; /