RE: pl/sql help
Date: Thu, 30 Jul 2009 23:36:02 -0400
Good Point. I would still disable/rebuild indexes.
From: jack.van.zanen_at_gmail.com [mailto:jack.van.zanen_at_gmail.com] On Behalf
Of Jack van Zanen
Sent: Thursday, July 30, 2009 8:01 PM
Cc: JSweetser_at_icat.com; Oracle-L Group Subject: Re: pl/sql help
as a variant on step 3
create table with records you want to keep (ctas)
truncate the original
insert append the records back to original
Minimal undo as well and no need to re-create triggers, indexes etc etc
downside => downtime
2009/7/31 Kenneth Naim <kennaim_at_gmail.com>
There are a few issues with your program.
- the in list variable cannot be list of values, it should be one number and only a one number. You can multiple variables (up to 1000), for all clause, or dynamic sql but all these methods are over kill development wise and are slower and use more undo over all so I won't go into them.
- The developer had the right idea as a single statement will be faster than a loop but will use a lot more undo space. I would size the undo appropriate to the statement and if you want to reclaim the space later just create a new undo tablespace and swap them, and drop the big one.
- Depending on how many rows we are talking about, it will most likely be much faster to create a new table as select the rows you want to keep, then add the appropriate constraints, triggers, indexes, statistics etc. on it.
- If you don't want to go through step recommendation 3 then you should atleast drop the indexes on the table, then do the delete, then create the indexes. It will use 80+% less than undo and will be much faster; especially if you build the indexes in parallel/ nologging but remember to alter them logging noparallel (or whatever their parallel/logging state was from before dropping them).
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sweetser, Joe
Sent: Thursday, July 30, 2009 6:48 PM
To: Oracle-L Group
Subject: pl/sql help
RH Linux AS4
I am trying to write a pl/sql program to delete a bunch of records. This came up because a developer wrote a sql statement to delete everything but it was running out of undo space. I thought it would be easy to throw into a pl/sql program and commit along the way to avoid that issue.
The gist of it is that I am building a list of values to use in the delete statement and passing it in as a bind variable but am getting an error when executing it. The SQL statement is "delete from table where column in (num1, num2, num3, num4,...num50);" I am using a varchar to build the num1, num2, num3... list and then trying to pass the whole thing to sql statement on the execute immediate. When I do, I get an invalid number error. I think I am missing something obvious but can't figure it out.
Here's the output from when I execute it. I added the echo'ing of the sql statement and the list_of_values while trying to debug. I have tried putting the ()'s in the list_of_values but get the same error.
SQL> exec epicenter_delete.whack_it_all
delete from pol_predetail where ParentObjID in (:1)
10110,10111,10112,10113,10114,10115,10116,10117,10118,10119,10120,10121, 10122,10123,10124,10125,10126,10145,10146,10147,10148,10149,10150,10151, 10152,10153,10154,10155,10156,10157,10158,10159,10160,10161,10162,10163, 10164,10165,10166,10167,10168,10169,10170,10171,10190,10191,10192,10193, 10194,10195
BEGIN epicenter_delete.whack_it_all; END;
ERROR at line 1:
ORA-01722: invalid number ORA-06512: at "JOE.EPICENTER_DELETE", line 194 ORA-06512: at line 1
SQL> The program goes something like this:
PROCEDURE whack_it_all IS
SQL_Stmt VARCHAR2(512); -- holds sql stmt to executed list_of_values VARCHAR2(512); -- holds id's to be deleted
CURSOR c1 IS
FROM blah, blah, blah;
/* Initialize counters/variables */
tmp2_count := 0;
list_of_values := '';
FOR premium_detail_rec IN c1
list_of_values := list_of_values || premium_detail_rec.ParentObjID; tmp2_count := tmp2_count+1;
IF tmp2_count = 50
SQL_Stmt := 'delete from pol_predetail where ParentObjID in (:1)';
dbms_output.put_line (SQL_Stmt); dbms_output.put_line (list_of_values); EXECUTE IMMEDIATE SQL_Stmt using list_of_values; <----- this is line 194 list_of_values := ''; tmp2_count := 0; ELSE list_of_values := list_of_values || ',';END IF;
END LOOP; Any/all ideas/help appreciated.
Jack van Zanen
This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
http://www.freelists.org/webpage/oracle-l Received on Thu Jul 30 2009 - 22:36:02 CDT