Bigfile Tablespaces do not use the uniform size definition when autoextend is enabled. (Doc ID 2729184.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

Bigfile Tablespaces do not use the uniform size definition when autoextend is enabled.

Changes

 If a bigfile tablespace uses uniform extent sizes and the resize of the tablespace does not use the uniform size, you may be hitting this defect.

For example:
1) Create a bgfile tablespace with extent management local uniform size clause
CREATE BIGFILE TABLESPACE "BIG_T" DATAFILE
  '<datrafile clause> ' SIZE 20971520
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 33554431M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10485760 DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
   
2) Create a table in the bigfile tablespace

  CREATE TABLE "U1"."T2_B2"
   (    "C1" DATE,
        "C2" VARCHAR2(20),
        "C3" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 
3) Load the data into the table to force data file growth

DECLARE
    l_string    varchar2(14);
       n_num       number;
    begin
     for i in 1..500000 LOOP
     n_num := i +1;
        l_string := dbms_random.string('U',14);
      insert into t2_b2 values(SYSDATE, l_string,n_num);
--      dbms_output.put_line(l_string);
--      dbms_output.put_line(n_num);
      end loop;
      commit;
    for j in 1..100 LOOP
     insert into t1_b1  (select * from dba_objects);
    end loop;
    commit;
    end;
/

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.