DB version: 10.2.0.5
OS : RHEL 5.4
We were doing a 'huge' batch activity which involved 18GB worth of INSERT, UPDATE on three tables which stored data on the below mentioned tablespace. It was going very slow.
When we ran the below query the NEXT_EXTENT was showing null.
SQL> select tablespace_name, next_extent, initial_extent from dba_tablespaces where tablespace_name like 'SHPMSPBL_SP_D%';
TABLESPACE_NAME NEXT_EXTENT INITIAL_EXTENT
---------------------------------------- ----------- --------------
SHPMSPBL_SP_DATA01 65536
Since the NEXT_EXTENT was showing null I set the value for NEXT_EXTENT to 2m.
SQL> alter database datafile '/u04/wmsprd/oradata_sp17/SHPMSPBL_SP_DATA01_1.dbf' autoextend on next 2m;
-- The above file is in AUTOEXTEND ON
-- The filesystem /u04 has enough space
Database altered.
Few minutes after the above command was executed , there was a slight improvement in the processing. Don't know if this was related.
-- But the below query was still showing null for NEXT_EXTENT
SQL> select tablespace_name, next_extent, initial_extent from dba_tablespaces where tablespace_name like 'SHPMSPBL_SP_D%';
TABLESPACE_NAME NEXT_EXTENT INITIAL_EXTENT
---------------------------------------- ----------- --------------
SHPMSPBL_SP_DATA01 65536
Question1. Can NEXT_EXTENT be changed online (which the tablespace is being accessed) ?
Question2. Will increasing NEXT_EXTENT make any improvement for data processing ?
Question3. What does NEXT_EXTENT = NULL mean ?