ORA-60019: Creating initial extent of size 14 in tablespace of extent size 13
During PeopleTools upgrade, there is a step to convert LONG datatypes to CLOB and BLOB. When using Oracle database, you may get this error: ORA-60019: Creatin
g initial extent of size 14 in tablespace of extent size 13
The numbers associated with the extend size may change according to the tablespace involved in the DDL statement but the basic error would be there – ORA-60019: Creating initial extent of size xx in tablespace of extent size yy.
You can get this error when running ALTER or CREATE DDL statements.
How to Resolve: ORA-60019: Creating initial extent of size 14 in tablespace of extent size 13
The issue occurs because space in Oracle is allocated in chunks called ‘extents’. Your existing tables have already been allocated extents containing enough space but you don’t have enough unallocated free space in the tablespace to add a whole new extent, which is needed for a new table or alter of the table.
Interestingly, the same SQL would work if you try it on a 11g database but not on 12c database. The difference between versions, which causes this error is that in Oracle 11g the default value for DB_SECUREFILE is PERMITTED while in Oracle 12c it is PREFERRED.
All LOB’s are treated as secured files as default from 12c onwards and hence it differs in security structure than the earlier releases before 12c. One option to make it work is that you can use a session level parameter as below to alter it as LOB. Use any of the below mentioned SQLs before running the SQL in error:
alter session set db_securefile=ignore;
If you’d like to provide a permanent fix, you can get your systems DBA involved and have them make the below change:
alter system set db_securefile=NEVER scope=both; or alter system set db_securefile=PERMITTED scope=both; or db_securefile='PERMITTED'
It seems that the minimum number of extents you should have for a table using a LOB segment with securefile needs to be a minimum of 14, and when tables contain a BLOB type column, you will have to make the extent size of your tablespace at least (14 * block_size) + 1 to fix the problem.