Radha Sri Seshu Kolla, August 23, 2016 - 10:01 am UTC
I changed programe as per your advice. Now i am updating DEPT table, even then i am getting error. Even though there is no logic in my update query, I want to know the syntax.
1 Declare
2 Cursor C1 is select * from emp;
3 Cursor C2(P_deptno dept.deptno%type) is
4 select deptno,dname,loc from dept where deptno=P_deptno for update of dname,loc;
5 dept_Rec dept%rowtype;
6 i emp%rowtype;
7 Begin
8 Open C1;
9 loop
10 Fetch C1 into i;
11 Exit when C1%notfound;
12 insert into sample3(empno,ename,sal,comm,deptno) values (i.empno,i.ename,i.sal,i.comm,i.deptno)
13 dbms_output.put_line('First Cursor'||sql%rowcount);
14 Open C2(i.deptno);
15 loop
16 Fetch C2 into dept_Rec;
17 Exit when C2%notfound;
18 Update dept set dname='MIS',loc='Hyd' where current of c2;
19 Rollback;
20 dbms_output.put_line('Second Cursor'||sql%rowcount);
21 Close C2;
22 end loop;
23 end loop;
24* End;
SQL> /
First Cursor1
Second Cursor0
Declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 16
August 23, 2016 - 1:22 pm UTC
You've closed the cursor inside the loop! You need to place
close c2
Outside the loop fetching the values from it. And take out the rollback statement too while you're at it. You'll hit "fetch out of sequence" errors while this is there.
Radha Sri Seshu Kolla, August 24, 2016 - 9:51 am UTC
Thank you very much. i got the logic, but why rollback should be out of loop. Why it is not working when it is inside the loop?
August 24, 2016 - 10:38 am UTC
Committing or rolling back ends a transaction. This releases the cursor. But you're still trying to fetch values from it using the loop!
ORA-01410: invalid ROWID while gathering stats using DBMS_STATS
Allen Stephen, August 21, 2020 - 5:14 pm UTC
Hi Chris,
I face the below error on a LIST-RANGE composite partitioned table while gathering GLOBAL stats. The table statistics is locked but we use FORCE option. There are no TRUNCATE/DROP partitions that happen when we gather statistics, but we get this error continuously.
Logs:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> DECLARE
2 in_Table_Name VARCHAR2 (30 BYTE) := 'APP_TABLE';
3 in_Granularity VARCHAR2 (30 BYTE) := 'GLOBAL';
4 in_Partition_Name VARCHAR2 (30 BYTE) := NULL;
5 in_Columns VARCHAR2 (4000 BYTE)
6 := 'COL1,COL2,COL3,COL4';
7 v_estimate_percent NUMBER := DBMS_STATS.AUTO_SAMPLE_SIZE;
8 v_method_opt VARCHAR2 (4000 BYTE);
9 v_Owner VARCHAR2 (30 BYTE)
10 := SYS_CONTEXT ('userenv', 'current_schema');
11 BEGIN
12 IF in_Columns IS NOT NULL
13 THEN
14 v_method_opt :=
15 'FOR COLUMNS ' || REPLACE (in_Columns, CHR (10), NULL);
16 ELSE
17 v_method_opt := 'FOR ALL COLUMNS SIZE AUTO';
18 END IF;
19
20 DBMS_STATS.SET_TABLE_PREFS (v_Owner,
21 in_Table_Name,
22 'INCREMENTAL',
23 'TRUE');
24
25
26 DBMS_STATS.gather_table_stats (ownname => v_Owner,
27 tabname => in_Table_Name,
28 partname => in_Partition_Name,
29 estimate_percent => v_estimate_percent,
30 method_opt => v_method_opt,
31 degree => 8,
32 granularity => in_Granularity,
33 cascade => TRUE,
34 force => TRUE);
35 END;
36 /
DECLARE
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at "SYS.DBMS_STATS", line 36897
ORA-06512: at "SYS.DBMS_STATS", line 36531
ORA-06512: at "SYS.DBMS_STATS", line 35443
ORA-06512: at "SYS.DBMS_STATS", line 34767
ORA-06512: at "SYS.DBMS_STATS", line 33890
ORA-06512: at "SYS.DBMS_STATS", line 32261
ORA-06512: at "SYS.DBMS_STATS", line 31906
ORA-06512: at "SYS.DBMS_STATS", line 31468
ORA-06512: at "SYS.DBMS_STATS", line 31423
ORA-06512: at "SYS.DBMS_STATS", line 31300
ORA-06512: at "SYS.DBMS_STATS", line 31189
ORA-06512: at "SYS.DBMS_STATS", line 31408
ORA-06512: at "SYS.DBMS_STATS", line 31820
ORA-06512: at "SYS.DBMS_STATS", line 31820
ORA-06512: at "SYS.DBMS_STATS", line 32247
ORA-06512: at "SYS.DBMS_STATS", line 33479
ORA-06512: at "SYS.DBMS_STATS", line 34719
ORA-06512: at "SYS.DBMS_STATS", line 35183
ORA-06512: at "SYS.DBMS_STATS", line 36245
ORA-06512: at "SYS.DBMS_STATS", line 36740
ORA-06512: at line 26
Can you please guide me on how to troubleshoot this issue? Thank you.
August 24, 2020 - 2:36 pm UTC
What's the DDL for your table? Does this happen when you unlock stats? Can you gather stats for partitions?
Invalid rowid while gathering stats
Allen Stephen, August 26, 2020 - 5:46 am UTC
Hi Chris,
It is a huge table with 359 columns and the number of rows is 15357211731.
I tried gathering global stats after unlocking the stats and it failed with invalid rowid error. I tried gathering for partition stats as well after unlocking and it worked well for all partitions except for one partition which failed with the same error. I also tried to delete the stats at the table level and the partition level and tried gathering again after unlocking it, but still face the same error. The sample table DDL is given below. The table has 14 partitions and each of these partitions has subpartitions between 300 & 1000.
CREATE TABLE APP_TABLE
(
ACT_CD VARCHAR2 (24 BYTE) NOT NULL,
RUN_ID INTEGER NOT NULL,
REC_CREATION_TS TIMESTAMP (6) DEFAULT SYSDATE NOT NULL,
COL4 INTEGER NOT NULL,
COL5 FLOAT (126) DEFAULT 0,
.......
COL359 FLOAT (126) DEFAULT 0
)
COLUMN STORE COMPRESS FOR QUERY HIGH
LOGGING
PARTITION BY LIST (ACT_CD)
SUBPARTITION BY RANGE (RUN_ID)(
PARTITION
P_V21_DUMMY
VALUES ('DUMMY')
(
SUBPARTITION
VALUES LESS THAN (-999)
COLUMN STORE COMPRESS FOR QUERY HIGH),
PARTITION
P_V21_2019_12_CA
VALUES ('2019-12 CA')
(
SUBPARTITION
VALUES LESS THAN (-999)
COLUMN STORE COMPRESS FOR QUERY HIGH,
SUBPARTITION P_V21_1
VALUES LESS THAN (1)
COLUMN STORE COMPRESS FOR QUERY HIGH),
PARTITION
P_V21_2020_03_CA
VALUES ('2020-03 CA')
(
SUBPARTITION
VALUES LESS THAN (-999)
COLUMN STORE COMPRESS FOR QUERY HIGH,
SUBPARTITION P_V21_2
VALUES LESS THAN (2)
COLUMN STORE COMPRESS FOR QUERY HIGH),
PARTITION
P_V21_2020_06_CA
VALUES ('2020-06 CA')
(
SUBPARTITION
VALUES LESS THAN (-999)
COLUMN STORE COMPRESS FOR QUERY HIGH,
SUBPARTITION P_V21_3
VALUES LESS THAN (3)
COLUMN STORE COMPRESS FOR QUERY HIGH),
PARTITION
P_V21_2020_09_CA
VALUES ('2020-09 CA')
(
SUBPARTITION
VALUES LESS THAN (-999)
COLUMN STORE COMPRESS FOR QUERY HIGH,
SUBPARTITION P_V21_4
VALUES LESS THAN (4)
COLUMN STORE COMPRESS FOR QUERY HIGH))
NOCACHE
MONITORING;
August 26, 2020 - 9:42 am UTC
except for one partition which failed with the same errorIs there anything different about the definition (DDL) for this partition? Can
you select all the rows in this partition? Are all the indexes valid? Is there any corruption of the rows/indexes for this partition?
Check to see if a trace file was created when you got the error. If not, run these to force one:
alter session set events '1410 trace name ERRORSTACK level 3';
alter session set events '10236 trace name context forever, level 1';
alter session set tracefile_identifier='ORA1410';
Then gather stats. If you look in the location on the database server returned by:
select value from v$diag_info
where name = 'Diag Trace';
You'll see a file called *ORA1410.trc. That will contain the statement that throws the error.
If you're on 12.2 or higher, you can get this information from a view:
select * from v$diag_trace_file_contents
where trace_filename like '%ORA1410.trc';
Invalid rowid while gathering stats
Allen Stephen, August 28, 2020 - 2:36 am UTC
There is nothing wrong with the DDL as it is similar to other partitions. There are no indexes at all. I have requested for checking RMAN block corruption and yet to receive the results. We traced as you mentioned and found the SQL which was failing.
----- Error Stack Dump -----
ORA-01410: invalid ROWID
----- Current SQL Statement for this session (sql_id=f9fsctud85dba) -----
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad */ substrb(dump("RUN_ID",16,0,64),1,240) val,
rowidtochar(rowid) rwid from "APP_TABLE" t where rowid in (chartorowid('AAEdqJAAJAANCsAAAA'),chartorowid('AAEdqKABDAAODQAAAA'),chartorowid('AAEdqLABoAAA0FpAAA'),chartorowid('AAEdqMABKAACaIAAAA'),chartorowid('AAEdqNABjAAAHcAAAA'),chartorowid('AAEdqOABKAAA2AAAAA'),chartorowid('AAEdqQAADAAFyWQAAA'),chartorowid('AAEdqTAAAAAH/QEAAA'),chartorowid('AAEdqUABAAANvsSAAA'),chartorowid('AAEdqXAADAAGQmQAAA'),chartorowid('AAEdqZABAAANwMSAAA'),chartorowid('AAEdqbABAAANwiAAAA'),chartorowid('AAEdqeAAAAAH/CAAAA'),chartorowid('AAEdqfAADAAJjYAAAA'),chartorowid('AAEdqgABAAANxSAAAA'),chartorowid('AAEdqiABAAANxcSAAA'),chartorowid('AAEdqlABAAANx8SAAA'),chartorowid('AAEdqmAADAAIZgAAAA'),chartorowid('AAEdqnAADAAIcMQAAA'),chartorowid('AAEdqoAADAAIi2QAAA'),chartorowid('AAEdqrAADAAJf6QAAA'),chartorowid('AAEdqsABAAANz4AAAA'),chartorowid('AAEdqtAADAAIPaAAAA'),chartorowid('AAEdqvAADAAIhQAAAA'),chartorowid('AAEdqwAADAAJa4QAAA'),chartorowid('AAFIWBAAHAAFGYBAAA'),chartorowid('AAFIWQAAHAACC8EAAA'),chartorowid('AAFIWRAAHAABRKFAAA'),chartorowid('AAFIWTAAHAAFHiFAAA'),chartorowid('AAFIWeAAHAACAaFAAA'),chartorowid('AAFIWgAAGAALBMFAAA'),chartorowid('AAFIWhAAHAAE/uFAAA'),chartorowid('AAFIXQAAIAAACGFAAA'),chartorowid('AAFIXWABAAADDdsAAA'),chartorowid('AAFIXZAAGAAJVGQAAA'),chartorowid('AAFT+xABAAADDgAAAA'),chartorowid('AAFT+yAAHAAJDCAAAA'),chartorowid('AAFT/0AAHAABSIQAAA'),chartorowid('AAFTWZAAGAAJTYCAAA'),chartorowid('AAFTWhAAFAAHK6FAAA'),chartorowid('AAFTZfAAHAADHoBAAA'),chartorowid('AAFTZjAAHAACAQCAAA'),chartorowid('AAFTZlAAGAAGAICAAA'),chartorowid('AAFTa/AAGAALBICAAA'),chartorowid('AAFTbBAAFAAHLEFAAA'),chartorowid('AAFTbIAAHAADJIEAAA'),chartorowid('AAFTf0AAEAAOVgAAAA'),chartorowid('AAFTf4AAHAAMAAFAAA'),chartorowid('AAFTfiAAGAAMBiFAAA'),chartorowid('AAFTgBAAGAAGAOFAAA'),chartorowid('AAFTgTAAEAAOWAAAAA'),chartorowid('AAFTgWAAGAALr6CAAA'),chartorowid('AAFTgbAAHAAHGYCAAA'),chartorowid('AAFTgiAAHAALHaFAAA'),chartorowid('AAFTjtAAHAAEmAFAAA'),chartorowid('AAFTnLAAHAAEmGTAAA'),chartorowid('AAFUADAAHAACTKCAAA'),chartorowid('AAFUArAAHAAKUOFAAA'),chartorowid('AAFUB4ABAAAEBIEAAA'),chartorowid('AAFUBuABAAAEAwAAAA'),chartorowid('AAFXs/AAHAABS4CAAA'),chartorowid('AAFY3jAAHAAPB6BAAA'),chartorowid('AAFY3zAAHAAPAyFAAA'),chartorowid('AAFfQ5AAHAADJyCAAA'),chartorowid('AAFfQ6AAHAAEDCCAAA'),chartorowid('AAFfQpAAHAAHIYCAAA'),chartorowid('AAFfQsAAIAAJBQCAAA')) order by "RUN_ID"
We tried to query the table with these rowids and they threw the same "Invalid rowid" error. One interesting thing we found was that when we tried querying the table for these rowids multiple times ignoring the error, the error went away eventually and the query retrieved data from the table. Then we tried gathering stats and it went through fine. I am not sure if these invalid rowids are fixed when we query the table again and again?? Is there any better way of identifying these invalid rowids and fix them? Will a reorg of the table help? Please let me know. Thank you.
August 31, 2020 - 4:01 am UTC
That sounds like a dbms_stats bug to me. I found some similar stuff on MOS but none that matched directly to yours.
You should make a call to Support and provide that diagnostic trace youv'e collected.