Why losing
space when deleting records?
We have an application that logs many large
records to a database table.
We log about 15000 records, then write these
records out to a file and delete all rows from the table. Then we start
filling up the table with records again. But after each delete, we cannot
get as many rows into the table. The second can only get 10000, repeating
several times gets us to be only able to insert 3500 records, and the last
run we are unable to insert any records because it keeps saying cannot
extend table. In other words, each time we run, we have less and less room
to extend the table from its original size.
Is there something we need to do to make
the space in the table available for all 15000 records each time?
Ans:
Two things are possible :
- either you play with the PCTFREE
and PCTUSED parameters of your table. When you insert, Oracle fills the
blocks up to (100 - PCTFREE), saving some sapce for subsequent updates
which might require some additional space. In your case this may be 0 (I
am not sure 0 is a valid value - let's say 1). But when you delete rows,
Oracle will not try to insert rows again until you have come below the
PCTUSED mark. I don't know what happens in your case, it may be that inserts
are still going on concurrently with the deletes, but I suspect that in
a way or another you have trouble getting under it. If I were you, I would
put PCTUSED at 99.
- the best solution by far is not
to use DELETE, but the TRUNCATE TABLE DDL statement, much faster - the
only problem is that is doesn't allow any rollback and, like with any DDL
statement, you have more stringent locking.
--
Stéphane Faroult
Oriole Corporation