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