I have a locally managed tablespace (TESTSP) created with AUTOALLOCATE clause. Initilaly it was created with 1MB datafile and then added another 1MB datafile.
Now when I try to add records (total size less than 40KB) into the table in this tablespace I am getting the following error:
"ORA-01653: unable to extend table USR.TEST1 by 256 in tablespace TESTSP".
I queried the dictionary tables and it seems the table/tablespace is using only one of the datafiles (only 1024K is occupied). I have almost 1MB of free space in the tablespace.
DBA_EXTENTS shows 16 extents with 64KB each.
Now, my question is about the AutoAllocate clause of the Locally Managed Tablespace.
1, Am I missing anything here?
2, When I add more data files after the creation of the Tablespace, wouldn't it be taken into consideration?
3, If adding a datafile, doesn't help, is the only other way to "Migrate the locally-managed tablespace to a dictionary management" and then alter the extent parameter?.
4, could anyone of you point me to some good documentation on how the autoallocate clause works. (I went thru the oracle manuals, but I couldn't get much info on why the insert fails even though there is enough space)