0

I have data file of 32G in which 18G occupied ( still has 14GB free space left) , the insert got failed with ORA-01658 : Unable to create INITIAL extent for segment in tablespace <TS_NAME>.

IS there any other solution than adding a data file? I already have 14GB space then why its causing space issue?

Is there any way I can adjust initial extent size to avoid this issue now and in future?

Thank You

I tried finding how to adjust initial extent size but some people mentioned we shouldn't alter that.

psa
  • 1
  • more free space? bigger harddrive...... – nbk Jan 18 '23 at 20:28
  • 1
    Just because you have 14G of free space doesn't mean that there's enough *contiguous* free space in the file to create your initial extent on the new table. For instance, if you've got a lot of objects that have been created and dropped. You may need to go through a tablespace reorganization to get the file defragmented enough to add the table. Also, is the data file *already* using 32GB of allocated space, or is it set to autoextend *up to* 32GB or otherwise dynamically allocated? I ask because if the underlying filesystem is out of space then the file won't be able to grow. – pmdba Jan 18 '23 at 20:31
  • Also check if your recyclebin is enabled and needs to be emptied. That can also consume space. – pmdba Jan 18 '23 at 20:33
  • ALTER TABLESPACE ADD DATAFILE '+path/db_file01.dbf' SIZE 32767M REUSE AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M This is the syntax I used to create TS. This is new schema which I created and running informatica workflow job which is copying table from one schema to this new schema where no update/delete performed before which might cause defragmentation – psa Jan 18 '23 at 21:04
  • Please share the insert statement and the create table DDL that you are inserting into. – Paul W Jan 18 '23 at 21:50
  • Using Informatica WF to load the data from source table to target table. – psa Jan 18 '23 at 22:14

0 Answers0