Ever getting ORA-01659: unable to allocate MINEXTENTS beyond 8 in tablespace XYZ?
I got this last weekend when creating a table with several GB level partitions.
The error indicates that it can not find contiguous spaces to create initial extents * minextents.
How do we know how many contiguous spaces we have on the tablespace, I tried to search the web but only got lengthy complex PL/SQL scripts. It ends with that I have to write my own. I post it here hoping someday it may help somebody.
SELECT
tablespace_name,
file_id,
round(sum(bytes)/1024/1024/1024, 3) contiguous_free_space_gb,
sum(blocks) no_of_blocks,
min(block_id) as starting_block,
round((sum(sum(bytes)) over ())/1024/1024/1024, 3) total_ts_free_space_gb
FROM
(
SELECT
tablespace_name,
file_id,
block_id,
bytes,
blocks,
nvl(block_id - min(block_id) over (partition by file_id) - sum(blocks) over (partition by file_id order by block_id rows between unbounded preceding and 1 preceding), 0) gap_blocks
FROM dba_free_space
WHERE tablespace_name = '&ts_name'
)
GROUP BY gap_blocks, tablespace_name, file_id
ORDER BY sum(bytes) desc
