Oracle Common Errors and resolutions summary ORA-20000 ORA-03113 ORA-14452 ORA-08103

Errors encountered during Oracle usage and PLSQL script debugging and solutions. Update constantly...

 

 

 

 

 

ORA-20000 to solve the problem:

 

ORA-20000: Because of overflow, the general method is to set a value larger.

Set long 10000;
Set linesize 10000;
Sets serverout on size 10000;

 

Below are several possible situations.

 

1 ORU-10027: Buffer Overflow limit of 2000 bytes;

 

Method 1: Set serveroutput on size 10000000 // set the value to a greater value. The default value is 2000 bytes.

Method 2: exec dbms_output.enable (999999999999999999999); // default value: 2000 bytes

 

2 ORU-10028: Line Length Overflow, limit of 255 chars per line;

Oracle 10g release2 removes the limit of 255 bytes. In earlier versions, the limit is 255 bytes.

 

The above settings do not work. In versions earlier than 10.2, you can use the substr function to solve this problem for a small amount of content. The length is uncertain. no solution has been found yet.

 

 

 

 

 

 

 

 

 

ORA-03113, ORA-03114

ORA-03113: End-of-file on communication channel, ORA-03114: not connected to Oracle errors may occur when a multi-Table query full outer join. One of the reasons is the underlying Oracle bug.

Solution:

Method 1 patch the database and upgrade it to 10.2.0.4.0 or later.

Method 2 if the database cannot be upgraded, you can also use left Outer Join and right outer join queries, and then Union all to obtain the results of full outer join.

 

 

ORA-14452

ORA-14452 attempt to create, alter or drop an index on temporary table already in use

 

An error occurs when you create, modify, or delete a temporary table because the table is still in use by session. Or there is data in the table.

Possible solutions

Method 1: Check whether a temporary table at the transaction level is used.

Method 2: trancate table data before deleting a table.

Method 3 kill the used session (will affect the operation of other sessions)

 

Find the session using the temporary table:

SQL> select ss. Sid, ss. Serial #, ss. Program, ss. osuser, ss. schemaname, ss. Machine, ss. Terminal

From v $ lock l, user_objects o, V $ session SS

Where ss. Sid = L. Sid and L. id1 = O. object_id and O. object_name = 'ttemp _ khzldr'

 

 

Based on the SID and serial #, kill the session.

SQL> alter system kill session 'sid, serial #';

 

ORA-08103

ORA-08103: Object no longer exists

 

Generate: the stored procedure uses a temporary table.

Correction: Data in the temporary table is used after submission. Check whether automatic commit is set, or whether the table data is cleared or deleted before the transaction is committed.