ORA-01410 – Invalid rowid

One of the reason why invalid rowid can occur is if there is a truncate that occurs in between when an existing SQL is running.

Sample:
1) Sample table x with one column of datatype NUMBER.

SQL> select * FROM ajaffer.test;

X
———-
1000
1000
1000
1000
1000
1000
1000

7 rows selected.

2) Long running query which has a sleep for 60 seconds before it fetches the next row to demo SQL running for long period of time. In parallel Step (3) is running in another window.

declare
cursor l_cur IS SELECT * FROM ajaffer.test;
begin
for l_rec in l_cur
loop
dbms_output.put_line(l_rec.x);
sys.dbms_lock.sleep(60);
end loop;
end;
/

declare
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 4

(3) In another window at the time the above PL/SQL is running, the table amin.x was truncated the table and added new rows.

SQL> truncate table ajaffer.test;

Table truncated.

SQL> insert into ajaffer.test values ( 10000 );

1 row created.

SQL> insert into ajaffer.test values ( 10000 );

1 row created.

SQL> commit;

Commit complete.

Advertisements

How to force a trace file to be created when an ORA error occurs?

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:
— ORA-1652: unable to extend temp .. (for temp tablespace)
SQL> ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK LEVEL 3’;

It will write to the alert.log
Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off one would run the following SQL:
SQL> ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
SQL> alter system set event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on event for.

ORA-01031: insufficient privileges

Here are some of the scenarios when one gets ORA-01031:

1) Insufficient privileges error is when “oracle” user is not part of the “dba” group when one tries to connect as sysdba, so it’s required user oracle is part of the “dba” group As you see in the following example below:

$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Apr 5 16:26:06 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:
$ id
uid=501(oracle) gid=500(oinstall) groups=500(oinstall) context=user_u:system_r:unconfined_t

2) A user/schema tries to truncate table owned by another user/schema and if the user doesn’t have access one will receive ORA-01031.

— connect as user1 which is trying to truncate table owned by scott
SQL> connect user1
Password:

SQL> truncate table scott.table1
truncate table scott.table
*
ERROR at line 1:
ORA-01031: insufficient privileges

There are couple of ways to grant this access:
(1) grant user1 “DROP ANY TABLE” granting this access may be an issue as the user can drop a table in any schema so the work around.
(2) Another way to give this grant is to create a store procedure in scott’s schema that truncates the table and grant user1 execute access to the store procedure.