Problem solve Get help with specific problems with your technologies, process and projects.

Why does table retain high number of rows?

I have question about a storage problem in Oracle8i (on Sun Server Cluster and Oracle Parallel Server). External C++ application every second inserts rows into table A (through PL/SQL procedure). Every 10 minutes, the database runs a job (procedure) -- open cursor from table A, calculate data, insert into table B and delete records from table A. Table A has data from 2,000 to 20,000 rows (depending on input frequency and time to run job).

Table A allocates in time more extents, and after a month, the space allocated for this table about 1 GB. But I don't know why after month table A still has 2,000 to 20,000 rows. Table A is slow. It looks like space after delete cannot be used for new inserts. Where is the bug?
The problem is that as records are added to table A, the highwater mark for the table continues to increase. Since the procedure only deletes from table A and doesn't truncate from it, the highwater mark continues to remain at the highest level it has ever achieved. In order to reset the highwater mark, you need to truncate the table instead of deleting from it. See if you can't modify your code to truncate the table instead.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.