When trying to insert 500k records into a table which already holds 19.5M records, I get the following error.
ORA-01653: unable to extend table BILL.MEASUREMENT_DATA by 1574802 in tablespace BILLING
I have tried the following:
1) Add datafile to tablespace billing
2) Add rollback segments
3) Coalescing ALL tablespaces
4) Adding additional hard drive space (although there are ~100 GB free)
None of these have worked.
Check in DBA_TABLES field maxextent for this table.
alter table ... storage(maxextents unlimited);
increase size of next (extent) for this table.
Making datafile grow to unlimited or adding another datafile should solve this problem . Make sure your tablespace level parameters . Make no of extents unlimited .
I neglected to mention...
I have changed all tablespaces to maxextents unlimited, autoextend on. However, I have not changed the tables. Let me try...
I just set maxextents unlimited and no dice. However, i did notice the following from dba_tables
TABLE_NAME NEXT_EXTENT MAX_EXTENTS
------------------------------ ----------- -----------
MEASUREMENT_DATA 3225194496 2147483645
that was AFTER i set maxextents unlimited and restart the db...
ORA-01653 has nothing to do with maxextents setting (either on tablespace or on table). It simply means that Oracle tries to allocate another extents to insert new rows, but there is not enough room for this new extent in any of the tablespace's datafile.
No wonder you get this error, the size of this new extent it tries to allocate is 3GB! So you'll have to add a new file of the size at lesat 3GB or enlarge one of the existing files by 3 GB.
I don't suggest any of the above option. 3GB for a single extent is simply too much! You better change your next extent size for this table to something managable and set PCTINCREASE for this table to 0 (I bet now it is set to the default 50). So what you need to do is:
ALTER TABLE my_big_table STORAGE (NEXT 128M PCTINCREASE 0);
Yo can change 128M in the above example to whatever size you thing will serve you best.
Haa ..3gb .This must be a big dataware house project ..
Thank You, it is fixed.
Although, pctincrease was already set to 0. I did what you said and set next 128M. That fixed it. Although I am rather confused as to how the next extent got so large... How can something like this happen/what can i do to prevent this in the future?
SELECT extent_id, bytes FROM user_extents
WHERE segment_name = 'MY_BIG_TABLE'
ORDER BY extent_id;
You'll get the size of each extent as they were allocated by Oracle. I guess you'll find out that at the beginning each extent is bigger from the previous one by 50 % (or whatever PCTINCREASE was set to at that time). Now at some time, someone changed the PCTINCREASE to 0, but forgot to set NEXT to some managable size, so it remained at 3GB.