Some common Oracle related FAQs

Jephe Wu - http://linuxtechres.blogspot.com


1. When compiling package body, it gives error like this: (Oracle 11g 64bit on CentOS 5.5 64bit)
ORA-04045: errors during recompilation/revalidation of schema_name.package_name
ORA-00600: internal error code, arguments: [kglgtbo2], [0x077F53600], [0x77F53600], [], [], [], [], []

Solution:  clean shutdown database then startup again
sqlplus / as sysdba
sql> alter system checkpoint;
sql> shutdown immediate;
sql> startup;

2. After downloading SQL Developer on Windows 7 pro, double click sqldevleoper, it gives error like this:
The program can’t start because MSVCR71.dll is missing from your computer. Try reinstalling the program to fix this program.

Solution:  download msvcr71.dll from dll-files.com, then put it under c:\windows\system32 (according to
http://i.justrealized.com/2009/how-to-fix-missing-msvcr71dll-problem-in-windows/ )

3. Schema statistics
exec dbms_stats.gather_schema_stats('JEPHE', cascade=>true);

4. SP2-0027: Input is too long (> 2499 characters) - line ignored
The script includes a SQL command that is over 2500 characters in length on one line

Solutions (from Oracle support)
1. Format the script or command so that any of your lines does not exceed the line length of 2500 characters.  Modify script (or command) by inserting a carriage return at a point where the first line does not exceed 2500 characters. Remember that each line of SQL command cannot exceed 2500 characters, so insert as many carriage returns as needed.
or
You can make the input blocks smaller, and concatenate them.

For example:
insert into table values ('<a number of characters up to 2499>'
|| '<more characters up to 2499>');

2. Then execute script (or command).

Note:  Another option is to use a different tool, like SQL Developer, which does not have this limitation. 

Check which lines are greater than 2499 characters:
i=1;lines=`wc -l filename.sql | awk '{print $1}'`; while [ $i -le $lines ]; do echo  -n $i; echo -n " ";sed -n $i'p' filename.sql | wc -c ;i=$[$i+1];done | sort -t" " -k 2 -n