René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

MIN_EXTLEN of DBA_TABLESPACES

set serveroutput on
set feedback off

-- extents will always be rounded to multiple of (select min_extent from tablespace where tablespace_name='')
--
-- this minimum extent can be set using alter tablespace .... minimum extent 10240;
-- 

create table demo (
  --n number(5),
  v char(60)
)
pctfree 0
storage (
  initial     0K -- Initial segment size
  next        2k -- assuming blocksize 2KB...
  pctincrease 0   
  minextents  1  -- allocate one extent only at the beginning
  maxextents  3);


select blocks from dba_segments where segment_name = 'DEMO';

declare
  bl  number;
  bln number;
  i   number := 0;
begin
  select blocks into bl from dba_segments where segment_name='DEMO';
  bln:=bl;

  dbms_output.put_line('Befor inserting, table occupies ' || bl || ' blocks');
  loop
    insert into demo values ('                                                            '); 
    i:=i+1;

    -- The following line makes it slow
    select blocks into bln from dba_segments where segment_name='DEMO';

    if bln > bl then
      dbms_output.put_line('after ' || i || ' inserts: block count increased to: ' || bln);
      bl := bln;
    end if;
  end loop;
exception
  when others then
    if sqlcode = -1631 then
      dbms_output.put_line('max extends reached when trying insert no ' || i);
    else
      dbms_output.put_line('unexpected exception when trying insert no ' || i);
    end if; 
end;
/


drop table demo;