SELECT BLOCKS, BYTES
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'USER'
ORDER BY BLOCKS DESC;
will give you all free space fragments in the tablespace USER
in descending order of size. To defragment the tablespace export
the objects from the tablespace, drop them, then reimport them.
Luca Minudel wrote:
>
> I've USER tablespace made of 1 datafile of 100 MByte, 25MByte are free (auto-extent is off).
> When I create a new table with an Initial Extent of 9MByte I get error ORA-1658: "Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created."
> How can I check tablespace fragmentation? How can I reduce tablespace fragmentation when it's fragmented?
>
Received on Tue May 26 1998 - 02:46:32 CDT
