Notifications
Clear all

[Solved] How to delete an oracle table in 1% chunks?

2 Posts
3 Users
0 Reactions
2,162 Views
0
Topic starter

I have a oracle table called MY_TABLE and want to delete it in 1% chunks with a commit after 1% is deleted. I also want to give user output after every 1% is deleted. How do you do this in PLSQL?

Sergey Soshnikov 2020-08-06 12:08:00

if you just want to delete all then truncate is also an option

1 Answer
0

The following PL/SQL block will run an update statement on batches sized at 10,000. Each batch will report a message of completion.

DECLARE
  batch_size NUMBER := 10000;
  cnt NUMBER := 1;
BEGIN
      -- WHILE (TRUE) can be omitted: "loop ... end loop;" 
      --  already is an endless loop
      LOOP
         update partner set ent_name='a' where ent_name='b'
           AND rownum <= batch_size;  
         exit when sql%notfound; -- notice that exit accepts "when condition"
         --IF sql%notfound THEN  -- you can avoid a if/endif by using "exit when"
         --   EXIT;
         -- END IF;
         dbms_output.put_line('Partner: Ent Name Batch: #'||cnt);
         cnt := cnt + 1;
         COMMIT;
   END LOOP;
   commit; -- you missed this commit for the last iteration
END;
/