Hi Everybody.
I am working on this one piece of the much larger PL/SQL program. It's just to deal with the deletes at the end of the script.
It's a procedure that runs very slow and generates tons of redo.
The table itself has around 500 Million rows, and that's one of the reasons but more than that the procedure was written when that table was much smaller.
Here is the Version of Oracle I am on:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Here is the test table I am using, basically here is the DDL.
CREATE TABLE TEST_SCH.BK_U_C_D_TEST
(
GENERIC_ID NUMBER NOT NULL,
USER_ID NUMBER NOT NULL,
CID NUMBER NOT NULL,
NOTE VARCHAR2(4000 BYTE),
CREATION_DATE DATE NOT NULL,
CREATED_BY VARCHAR2(50 BYTE) NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
LAST_UPDATED_BY VARCHAR2(50 BYTE) NOT NULL,
OGG_KEY_ID RAW(16),
OGG_TS TIMESTAMP(6)
)
/
--------------------------------------------------------------------------------
declare
cursor curr_rec is
select rowid rid
from TEST_SCH.BK_U_C_D_TEST where USER_ID ='123456789';
type brecord is table of rowid index by binary_integer;
brec brecord;
begin
open curr_rec;
FOR vqtd IN 1..500 loop
fetch c bulk collect into brec limit 20000;
forall vloop in 1 .. brec.count
delete from TEST_SCH.BK_U_C_D_TEST where rowid = brec(vloop);
exit when curr_rec%notfound;
commit;
dbms_lock.sleep(15);
end loop;
close curr_rec;
end;
/
For some reason I cannot get this to compile, even though I know from the syntax, it's correct, but when I run it it gives me this error...
ORA-06550: line 10, column 7:
PLS-00201: identifier 'C' must be declared
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored
Can someone please help me hammer this out? What do I need to do with the "c"??
Thanks in Advance!
Sandrine.