sqlplus / as sysdba, ORA-01031: insufficient privileges

One of the reason of this error is OS user must be belong to "osdba" group defined in group as defined in the "$ORACLE_HOME/rdbms/lib/config.s" or "$ORACLE_HOME/rdbms/lib/config.c". Typically this is set to "dba".

1. Check your OS user group e.x my group is dbap (not dba)
   %id
   uid=1030(oracle) gid=1030(dbap)
2. Check if this group is in /etc/group
   cat /etc/groupgrep dbap
3. Check config.s or config
   cd $ORACLE_HOME/rdbms/lib
   ls -lrt config.*
   cat config.s #It can be also config.c
4. correct group name in config.s file (example Change both ocurrences of dba to dbap )
   
   Sun SPARC Solaris
   .ascii "dbap\0"
   
   IBM AIX/Intel Solaris:
   .string "dbap"
5. To effect any changes to the groups and to be sure you are using the groups defined in this file relink the Oracle executable.Be sure to shutdown all databases before relinking
   
   mv config.o config.o.orig
   make -f ins_rdbms.mk ioracle

Incorrectly stored dates in a database

What is incorrect date

• You might sometimes find a DATE column in a database with incorrectly stored data like '00-000-0000'
• It can be unexpected errors like ORA-01841
• Or the result is correct but if you try to use it in where clause the result is incorrect

How can these dates end up in the database

• This issue is about your client side code and database provider
• if your client application uses OCI or Pro*C, then you can use the DATE external data type in OCI and Pro*C to insert illegal values into the database

Detection of these incorrect values

Use dump function on this data example (there are to rows looks similiar but their dump is not the same CIl_NCZ_VERILIS_TARIHI is a date cloumn an values kept as dd/mm/yyyy )

select CIl_NCZ_VERILIS_TARIHI,dump(a.CIl_NCZ_VERILIS_TARIHI,16) from aaa a;

30/11/2001 Typ=12 Len=7: 50,63,b,1e,1,1,1

30/11/2001 Typ=12 Len=7: 78,65,b,1e,1,1,1

How to find bad rows in a date column?

To find the rowid's of a date column giving ORA-01841 you can use a approach like:

DECLARE
CURSOR c1 IS SELECT rowid, DUMP (incorrectdate) as DUMP FROM incorrecttable;
v_incorrectdate incorrecttable.incorrectdate%TYPE;
date_error_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(date_error_detected,-1841);
BEGIN
FOR row_rec IN c1
LOOP
dbms_output.put('ROWID: 'row_rec.rowid' DUMP:'row_rec.dump);
BEGIN
SELECT incorrectdate INTO v_incorrectdate FROM incorrecttable WHERE rowid=row_rec.rowid;
dbms_output.put_line(' DATE:'v_incorrectdate);
EXCEPTION
WHEN date_error_detected THEN dbms_output.put_line(' DATE: Got ORA-01841 on this date');
END;
END LOOP;
END;
/