OS: AIX v6
DB: 11.2.0.1
Hi Guys,
I trying to update some two column in a table and i continually get the ORA-01410 error in my script.
The table stats are:
Total rows : 24,445,779
Rows to update : 3,936,042
I cannot do single UPDATE statement because it would: 1) generate too many TX locks and kill the db and 2) perhaps exhaust the undo.
And CTAS would require some downtime of which I could only afford 2 hours on a weekend.
Therefore I have coded the following sql script to update these column and get the ORA-01410 error.
SQL> DECLARE
2 CURSOR usr_csr IS
3 SELECT i.instance_id,
4 ist.state_datetime,
5 NVL(u.username, '?' ) username,
6 i.ROWID row_id
7 FROM instance_arch i, instance_states_arch ist, t_users u
8 WHERE i.current_state = ist.inst_state_id
9 AND ( i.create_date IS NULL OR i.user_name IS NULL )
10 AND u.userid(+) = ist.state_initiator
11 ORDER BY i.ROWID;
12
13 TYPE usr_crs_rec IS TABLE OF usr_csr%ROWTYPE
14 INDEX BY PLS_INTEGER;
15
16 i_rec usr_crs_rec;
17 max_rec PLS_INTEGER := 10000;
18 i PLS_INTEGER;
19 i_loop PLS_INTEGER:=0;
20 i_tot PLS_INTEGER:=0;
21 i_null PLS_INTEGER:=0;
22 i_fixd PLS_INTEGER:=0;
23 BEGIN
24 OPEN usr_csr;
25
26 WHILE 1 = 1 LOOP
27 FETCH usr_csr
28 BULK COLLECT INTO i_rec
29 LIMIT max_rec;
30
31 EXIT WHEN usr_csr%NOTFOUND;
32
33 i_loop := i_loop + 1;
34 i_tot := i_tot + i_rec.COUNT;
35
36 FOR i IN 1 .. i_rec.COUNT
37 LOOP
38 IF i_rec( i ).username = '?' THEN
39 i_null := i_null + 1;
40 ELSE
41 i_fixd := i_fixd + 1;
42 END IF;
43 END LOOP;
44
45 FORALL i IN 1 .. i_rec.COUNT
46 UPDATE instance
47 SET create_date = i_rec( i ).state_datetime
48 , user_name = i_rec( i ).username
49 WHERE rowid = i_rec( i ).row_id;
50
51 COMMIT;
52 END LOOP;
53 DBMS_OUTPUT.PUT_LINE('*** Fixed User Names *** '||i_loop);
54 DBMS_OUTPUT.PUT_LINE('*** Fixed: '||i_fixd);
55 DBMS_OUTPUT.PUT_LINE('*** Null : '||i_null);
56 DBMS_OUTPUT.PUT_LINE('*** Total: '||i_tot );
57 COMMIT;
58 END;
59 /
DECLARE
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 45
I cannot figure it out, this same script executed flawlessly for a table of ~ 1 Mill rows.
All suggestion, corrections, links, jokes and sarcasm are accepted.
:)