Re: pl/sql help

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

Jack

2009/7/31 Kenneth Naim <kennaim_at_gmail.com>

> There are a few issues with your program.
>
> 1. 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.
> 2. 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.
> 3. 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.
> 4. 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).
>
> Ken
>
>
> -----Original Message-----
> 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
>
> Greetings gurus,
>
> 10.2.0.2
> 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
>
>
> Elapsed: 00:00:00.92
> 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
>
> <snip>
>
> CURSOR c1 IS
> SELECT ParentObjID
> FROM blah, blah, blah;
>
> <snip>
>
> BEGIN
>
> /* Initialize counters/variables */
> tmp2_count := 0;
> list_of_values := '';
>
> FOR premium_detail_rec IN c1
> LOOP
> list_of_values := list_of_values || premium_detail_rec.ParentObjID;
> tmp2_count := tmp2_count+1;
> IF tmp2_count = 50
> THEN
> 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.
>
> -joe
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
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