What is free space in oracle tables

Question

I've just read some sql tutorials, and I found something known as "freespaces" in Oracle tables from LINK. What do they mean by freespace? I know if we use varchar(10) instead of varchar2(10), unused space will be available unnecessarily in varchar. But what do they mean by freespace when it comes to TRUNCATE?

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

See also: http://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql — David Aldridge, Dec 22 '13 at 09:45

score 2 · Answer 1 · answered Dec 21 '13 at 17:28

2

The free space in a table isn't at the row level. It is at the page level.

The truncate table command basically removes all the pages assigned to a table. This empties the table and releases the pages back to the page management system.

If you merely delete rows, some pages might still remain associated with the table. A page contains records. A table may be able to store additional records on existing pages. That would be unused space in the table.

answered Dec 21 '13 at 17:28

Gordon Linoff

1,242,037
58
646
786

I never heard anything about "page level" in Oracle DB. Maybe other RDBMS use this phrase but Oracle does not as far as I know. – Wernfried Domscheit Dec 21 '13 at 18:02
1

I checked Oracle doc, there it says: "At the finest level of granularity, Oracle Database stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk." So, they refer to _page_ but the common expression in Oracle is **data block** – Wernfried Domscheit Dec 21 '13 at 18:08

JosefN · Answer 2 · 2013-12-22T08:01:28.273

2

When you drop table from Oracle db, the space is still occupied because there is possibility to "undelete" so data are actually only marked as deleted but not deleted from a disk. When you truncate table the all data are really deleted so the space on disk occupied by them is free. There is also possibility to purge dropped table and release space. For more info pls see: http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

edited Dec 22 '13 at 08:01

answered Dec 21 '13 at 17:29

JosefN

952
6
8

It would be better to use the correct expressions like "COMMIT" or "ROLLBACK", it's easier for beginners in order do understand. – Wernfried Domscheit Dec 21 '13 at 18:04
@Wernfried pls see link in my answer. Thx – JosefN Dec 21 '13 at 23:30

What is free space in oracle tables

2 Answers2