I have created the tablespace with small size 5m and assigned the same to user as a default tablespace so, created the table and keep on inserting the data. in the mean while checking free space and used percentage in tables like dba_free_space and dba_tablespace_usage_metrics respectively for particular tablsepace.
I am happy that during insertion free space has been decreasing and used percentage has been increasing, at particular moment got error like 'Unable to extend file........'
so, immediately i checked, free space is low and used percentage is near to 100.
I deleted all the data from the table and checked the free space and used percentage, now also it is showing the same result as earlier(ree space is low and used percentage is near to 100) .
Could any one of you please let me know why it is not changing the values?
Space in a tablespace is allocated in chunks called extents. Those extents are assigned to segments (things like tables, partitions, and other things that consume space).
When you delete data, space is freed up in the extent but the extent is still assigned to the segment. You wouldn't expect the free space in the tablespace to decrease assuming that you are measuring free space as the size of the new extents that you could allocate in the tablespace. If you insert more data into the table, Oracle will use up the free space in the extent before requesting a new extent for the table's segment. Allocating space is an expensive operation so Oracle generally does not want to go to the expense of releasing an extent back to the tablespace when it is likely that the segment is going to need a new extent in the future.
If you really want to return the extents to the tablespace, you would need to TRUNCATE the table, not just delete all the data.