CODEWITHSUNDEEP
oracle-database
JGleason
JGleason

Reputation: 3936

Can I Delete entire table without undo in Oracle?

I have a table that was working great until recently. I have a program that interacts with this table the steps are...

  1. Delete all records
  2. Load all records

Recently the number of records seems to have started to overload our SQL instance and when I try to run the delete command I get....

ORA-30036: unable to extend segment by 128 in undo tablespace 'name'

I tried using truncate but I don't have permissions. Is there a way I can delete without the undo tablespace? This is with SQL developer but I would also like it to work with the JDBC connection.

Upvotes: 1

Views: 1059

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21075

Basically you are trying to simulate the functionality of the TRANCATE TABLE in your DELETEstatement.

So you should definitively use TRUNCATE TABLE as it produces much less UNDO and also it deallocates the space used up to the MINEXTENTS.

If the table is not in the accessing user schema, you would need the privilege DROP ANY TABLE which may be problem to receive.

A simple workaround is to define a stored procedure in the schema of the table that executes (immediate) the TRUNCATE and only ask for the EXECUTE grant for this procedure.

Upvotes: 3

Related Questions