I recently created a DB with ABC_TBS with 2GB of SIZE and import data into few table that consumed 10MB of storage in other tablespace USER
Now I was trying to migrate the data to a new table X (ABC_TBS tablespace) with OLD table A (ABC_TBS tablespace) + B (USER tablespace) with outer join. The create statement went fine.
On INSERT the Tablespace was used completely like 2GB, and gave a error saying
Error report:
SQL Error: ORA-01658: unable to create INITIAL extent for segment in tablespace CTB_TBS
01658. 00000 - "unable to create INITIAL extent for segment in tablespace %s"
*Cause: Failed to find sufficient contiguous space to allocate INITIAL
extent for segment being created.
*Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
tablespace or retry with a smaller value for INITIAL
Rollback
When I check the tablespace size it says, almost used, but before migrating it had like 2000 MB free
| Tablespace | USED (MB) | FREE (MB) | TOTAL(MB) | Pct.Free |
| ABC_TBS | 2044 | 4 | 2048 | 0 |
Now when I see the USAGE of INDEX and TABLE in ABC_TBS its hardly 7 MB
| OWNER | TABLE_NAME | USED(MB) | TABLESPACE |
| ABC | TABLE1 | 2 | ABC_TBS |
| ABC | TABLE1_N1_IND | 2 | ABC_TBS |
| ABC | TABLE1_N2_IND | 2 | ABC_TBS |
| ABC | TABLE2_U1_IND | 1 | ABC_TBS |
| ABC | TABLE2 | 0 | ABC_TBS |
| ABC | TABLE1_U1_IND | 0 | ABC_TBS |
| ABC | TABLE1_N3_IND | 0 | ABC_TBS |
| ABC | TABLE3 | 0 | ABC_TBS |
Now my doubt is, if the data never got inserted to NEW table, how did 2000 MB of space get consumed ? How can I reclaim it ? I have no problem adding new DATAFILE, but my mystery is where did the 2GB go ???