CODEWITHSUNDEEP
sqloracle-database
Jericho
Jericho

Reputation: 10953

What is free space in oracle tables

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.

Upvotes: 0

Views: 1039

Answers (2)

JosefN
JosefN

Reputation: 952

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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.

Upvotes: 2

Related Questions