Geek Answers Handbook

Oracle 10g table size after deleting multiple rows

As part of the service, we remove a significant number of records from one table and want to get rid of this space for another table. The fact is that I check the size of the table, and it shows the same original size before deleting. What am I missing here?

To check the size, I look at dba_segments .

+4
source share
4 answers

There are several concepts you need to know about tables before you can really understand the use of space. I will try to give you a 5 second tour ...

The table consists of extents, they may vary in size, but allow us to simply assume that they are 1 MB.

When you create a table, usually 1 ext. Is added to it, therefore, although the table has no rows, it will occupy 1 MB.

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

If you take the new table as an example and start inserting rows, the HWM will move up, giving more and more of the first degree that the table will use. When the size is exhausted, the other will be highlighted and the process will be repeated.

Let's say that you fill in three extents of 1 MB and then delete all the rows - the table is empty, but Oracle does not move the HWM or release those extents used. Thus, the table will occupy 3 MB of disk space, even if it is empty, but it has 3 MB of free space. New inserts will enter this space below the HWM until it is full before the HWM moves again.

To restore space if your table uses ASSM, you can use the command:

 alter table t1 shrink space;

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

+13
source

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

Space is effectively used when deleting. Your database will not have any new free spaces in dba_free_space - it will have more blocks on freelides and more empty holes in the index structures.

+1
source

If you want to “return” space, the easiest way:

 ALTER TABLE table MOVE TABLESPACE different_tablespace; ALTER TABLE table MOVE TABLESPACE original_tablespace;

Providing you:

  • Some downtime in this case
  • A second table space with enough space to wrap the table.
+1
source 
 SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'YOUR TABLE NAME%';

You will get the correct answer.

-1
source

More articles:


All Articles