Oracle 10g Table size after deletion of multiple rows

Question

As part of maintenance, we remove a considerable amount of records from one table and wish to dispose of this space for other table. The thing is that I'm checking the size of the table and it shows the same original size before the delete. What am I missing here?

To check the size, I'm looking at the dba_segments.

score 13 · Accepted Answer · answered Jun 30 '11 at 20:24

There are some concepts you need to know about tables before you can really understand space usage. I will try and give you the 5 second tour ...

A table is made up of extents, these can vary in size, but lets just assume they are 1MB chunks here.

When you create a table, normally 1 extent is added to it, so even though the table has no rows, it will occupy 1MB.

When you insert rows, Oracle has a internal pointer for the table known as the high water mark (HWM). Below the HWM, there are formatted data block, and above it there are unformated blocks. It starts at zero.

If you take a new table as an example, and start inserting rows, the HWM will move up, giving more and more of that first extent to be used by the table. When the extent is all used up, another one will be allocated and the process repeats.

Lets says you fill a three 1MB extents and then delete all the rows - the table is empty, but Oracle does not move the HWM back down, or free those used extents. So the table will take 3MB on disk even though it is empty, but there is 3MB of free space to be reused in it. New inserts will go into that space below the HWM until it is filled up again before the HWM is move again.

To recover the space, if your table is using ASSM, you can use the command:

alter table t1 shrink space;

If you are not using ASSM, the you need to think about a table reorg to recover the space.

This is very interesting and helpful, I appreciate for taking the time to explain this. — danboh, Jun 30 '11 at 20:40
I love this answer! I got it!... my question would be, the table size it could not be the real one even if you query the table "all_tables"? — jcho360, Jan 05 '16 at 19:07

score 1 · Answer 2 · edited Oct 25 '14 at 06:42

1

Take a look at this site about the table size after deleting rows.

Space is effectively reused when you delete. Your database will not show any new free space in dba_free_space -- it will have more blocks on freelists and more empty holes in index structures.

edited Oct 25 '14 at 06:42

Unihedron

10,902
13
62
72

answered Jun 30 '11 at 20:18

woliveirajr

9,433
1
39
49

Thanks. What would be a good way of calculating the saved space then after the deletion? – danboh Jun 30 '11 at 20:24
that a look at @Stephen answer below, about the `shrink` command, it could help you reclaim the space imediatlly. Read further [here](http://www.dba-oracle.com/t_alter_table_shrink_space_command.htm) – woliveirajr Jun 30 '11 at 20:29
Yeah, I had tried that already, but the table has long columns so it doesn't work. – danboh Jun 30 '11 at 20:48
1

Ouch, long columns make everything more difficult. To get approximately how much space you would reclaim in the table *for reuse only by that table*, you can multiply the average row length (will be in the table stats) by the number of rows you are deleting. If you really need the space back, the alter table move option mentioned in cagcowboy's answer below may be about your only option, or an export / import, but both those options will result in downtime and need plenty of spare free space. – Stephen ODonnell Jun 30 '11 at 21:41
yes, `alter table` every time is not a good option during high-database-access times. Just worry about the "lost" space when it's really a problem (and if you really need that lost space, problably you'll have other problems in a short time...) – woliveirajr Jul 01 '11 at 11:22

score 1 · Answer 3 · edited Oct 25 '14 at 06:41

1

If you want to "reclaim" the space, the easiest method is:

ALTER TABLE table MOVE TABLESPACE different_tablespace;

ALTER TABLE table MOVE TABLESPACE original_tablespace;

Providing you have:

Some downtime in which to do it
A second tablespace with enough space to transfer the table into.

edited Oct 25 '14 at 06:41

Unihedron

10,902
13
62
72

answered Jun 30 '11 at 20:35

cagcowboy

30,012
11
69
93

score -1 · Answer 4 · edited Mar 09 '13 at 14:55

-1

SELECT SUM(BYTES) 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME LIKE 'YOUR TABLE NAME%';

You will get the right answer.

edited Mar 09 '13 at 14:55

Taryn

242,637
56
362
405

answered Mar 09 '13 at 05:15

UMESH

1

Oracle 10g Table size after deletion of multiple rows

4 Answers4