Important Oracle Error Codes

Here let us see some important Oracle error codes -

1) ORA-00001: unique constraint (constraint_name) violated

Cause -

You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

Option #1 Drop the unique constraint.

Option #2 Change the constraint to allow duplicate values.

Option #3 Modify your SQL so that a duplicate value is not created.

2) ORA-00051: time-out occurred while waiting for resource

Cause - 

You were waiting for a resource and you timed out. This may have been caused by an Oracle instance abnormally terminating.

Option #1 Restart the Oracle instance.

3) ORA-00068: invalid value num for parameter num, must be between num and num

Cause - 

The value for the initialization parameter must be between the numbers specified in this error message.

Option #1 Update the initialization parameter accordingly.

4) ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed

Cause -

While trying to archive a redo log file, the ARCH process encountered an error.

Option #1 This error can be caused when the destination device does not have enough storage space to store the redo log file.

Option #2 Check that the initialization parameter ARCHIVE_LOG_DEST in the initialization file is set correctly.

5) ORA-00018: maximum number of sessions exceeded

Cause -

You tried to execute a statement that requested a resource. Since the maximum number of sessions have been reached, Oracle will not process any new resource requests.

Option #1 You can wait a few minutes and try to re-execute the statement(s).

Option #2 You can shut down Oracle, increase the SESSIONS parameter in the initialization parameter file, and restart Oracle.

6) ORA-00054: resource busy and acquire with NOWAIT specified

Cause -

You tried to execute a LOCK TABLE or select or update command with the NOWAIT keyword but the resource was unavailable.

Or you tried to DROP a COLUMN using the ALTER TABLE command and received the error.

Option #1 Wait and try the command again after a few minutes.

Option #2 Execute the command without the NOWAIT keyword.

Option #3 If the error occurred while trying to DROP a COLUMN, be sure to backup the data. Then TRUNCATE the table and execute the DROP COLUMN command again

7) ORA-00071: process number must be between 1 and num

Cause - 

You tried to specify a process number that was not valid.

Option #1 Specify a process number that is between 1 and the number listed in the error message.

8) ORA-00301: error in adding log file <name> - file cannot be created

Cause - 

You tried to create a redo log file, but the creation failed.

Option #1 Check if there is enough storage space on the device.

Option #2 Check if the device is available.

Option #3 Check if the file name is correct.

9) ORA-00020: maximum number of processes num exceeded

Cause - 

You tried to execute a statement that requested a resource. Since the maximum number of processes have been reached, Oracle will not process any new requests.

Option #1 You can wait a few minutes and try to re-execute the statement(s).

Option #2 You can shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

10) ORA-00057: maximum number of temporary table locks exceeded

Cause -

The number of temporary tables has reached or exceeded the number of temporary table locks allowed by Oracle.

Option #1 This error is most commonly caused by a SQL statement that has performed a very large sort. The sort has created a number of temporary tables that has exceeded the limit specified in Oracle.

To correct this problem, increase the TEMPORARY_TABLE_LOCKS parameter in Oracle and perform a restart in Oracle.

11) ORA-00078: cannot dump variables by name

Cause -

You tried to dump a variable by name, but your system does not support this option.

Option #1 Try the PEEK command.

12) ORA-00304: requested INSTANCE_NUMBER is busy

Cause -

You tried to start an Oracle instance by using an INSTANCE_NUMBER parameter value that is already in use.

Option #1 Modify the INSTANCE_NUMBER initialization parameter in the initialization file to use another number. Then try restarting the instance.

Option #2 Wait for recovery to finish for that instance number.

13) ORA-00023: session references process' private memory; cannot detach session

Cause -

You tried to detach a session but it contains references to the process memory (PGA) through any of: an open network connection, a very large context area, or operating system privileges.

Option #1 If the session has an open network connection or a very large context area, you can try closing the session's database links and cursors.

Option #2 If the session has operating system privileges, you will not be able to detach the session.

14) ORA-00058: DB_BLOCK_SIZE must be num to mount this database not num

Cause -

You tried to start a database whose DB_BLOCK_SIZE parameter value in the initialization file does not match the value used when the database was created.

Option #1 Make sure that you are mounting the correct database.

Option #2 Make sure that you are using the correct initialization file.

Option #3 You may need to change the DB_BLOCK_SIZE parameter in the initialization file.

15) ORA-00100: no data found

Cause -

An application tried to access data that was either inaccessible or does not exist.

Option #1 This error must be handled within the application. Modify the application accordingly.

16) ORA-00361: cannot remove last log member name group num

Cause -

You tried to remove the last member of a redo log group.

Option #1 You can try deleting the log using the DROP log file command.

17) ORA-00028: your session has been killed

Cause -

Your session has been killed by a privileged user (such as a DBA).

Option #1 Check with your DBA. Your session may have been killed due to a scheduled outage or other activity

18) ORA-00060: deadlock detected while waiting for resource

Cause -

You tried to execute a statement, but your session was deadlocked because another session had the same resource locked. The statement(s) that you tried to execute have been rolled back.

Option #1 You can wait a few minutes and try to re-execute the statement(s) that were rolled back.

Option #2 You can execute a ROLLBACK and re-execute all statements since the last COMMIT was executed.

19) ORA-00107: failed to connect to network listener process

Cause -

The network listener process was not been successfully started.

Option #1 Make sure that the network configuration file is set up correctly.

Option #2 Make sure that the client-side address is set up correctly.

Option #3 Make sure that the listener initialization parameter file is set up correctly.

20) ORA-00401: the value for parameter name is not supported by this release

Cause -

You tried to specify a value in the initialization file that is not supported by this release of Oracle

Option #1 You need to enter a value that is supported by your version of Oracle, or remove the parameter value from the initialization file so that the default value is used.

21)  ORA-00034: cannot COMMIT or ROLLBACK in current PL/SQL session

Cause -

You tried to issue a COMMIT or ROLLBACK command from a PLSQL function, procedure, or package. However, COMMIT and ROLLBACK have been disabled with the following command:

                Alter SESSION Disable COMMIT IN PROCEDURE;

Option #1 Do not issue a COMMIT or ROLLBACK command from your PLSQL function, procedure, or package while disabled.

Option #2 Enable COMMIT or ROLLBACK for this PLSQL session.

22) ORA-00063: LOG_FILES initialization parameter exceeded

Cause -

The value of the LOG_FILES parameter must be as large as the highest number log that exists.

Option #1 Increase the LOG_FILES parameter value in the initialization file and then perform an Oracle warm start.

23) ORA-00201: control file version num incompatible with Oracle version num

Cause -

The control file was created by a version of Oracle that is different than the version of Oracle that you are currently running.

Option #1 Restart Oracle with a version of Oracle that is the same as the control file.

Option #2 Create a new control file using the CREATE CONTROLFILE command.

24) ORA-00900: invalid SQL statement

Cause -

The statement that you've tried to execute is not a valid SQL statement.

25) ORA-00902: invalid datatype

Cause -

You tried to execute a CREATE TABLE or ALTER TABLE statement that contained an invalid datatype.

26) ORA-00903: invalid table name

Cause -

You tried to execute a SQL statement that included an invalid table name or the table name does not exist.

27) ORA-00904: invalid identifier

Cause -

You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.

28) ORA-00905: missing keyword

You tried to execute a statement, but you missed a required keyword.

29) ORA-00906: missing left parenthesis

You tried to execute a statement, but you missed entering a required left parenthesis.

30) ORA-00907: missing right parenthesis

You entered a left parenthesis, but missed the closing right parenthesis; or you entered invalid data within the parentheses.

All parentheses must be entered in pairs.

31) ORA-00908: missing NULL keyword

You tried to execute a SQL statement, but you missed entering the NULL keyword

This error can occur if you try to execute a SQL statement using the IS NULL condition or IS NOT NULL condition, but miss entering the NULL keyword.

32) ORA-00909: invalid number of arguments

You tried to call an Oracle function, but you used the incorrect number of arguments for the function.

33) ORA-00910: specified length too long for its datatype

You tried to create a VARCHAR2 or CHAR column, but you specified a length that exceeded Oracle's limits.

Enter a maximum length for the VARCHAR2 or CHAR column that falls within Oracle's limits.

34) ORA-00911: invalid character

You tried to execute a SQL statement that included a special character.

Option #1 This error occurs when you try to use a special character in a SQL statement. If a special character other than $, _, and # is used in the name of a column or table, the name must be enclosed in double quotations.

Option #2 This error may occur if you've pasted your SQL into your editor from another program. Sometimes there are non-printable characters that may be present. In this case, you should try retyping your SQL statement and then re-execute it.

Option #3 This error occurs when a special character is used in a SQL WHERE clause and the value is not enclosed in single quotations.

35) ORA-00913: too many values

You tried to execute a SQL statement that required two sets of equal values, but you entered more items in the second set than was in the first set.

Option #1 This error often occurs when you are performing a INSERT statement and enter more values in the VALUES clause than the number of columns that you listed.

Option #2 This error can also occur when your subquery in the WHERE clause returns too many columns.

36) ORA-00917: missing comma

You tried to execute a statement, but you missed a required comma.

37) ORA-00918: column ambiguously defined

You tried to execute a SQL statement that joined two or more tables, where a column with the same name exists in both tables.

Prefix the column with the table name and then re-execute the statement.

38) ORA-00919: invalid function

You tried to call a function, but the name you used was not a valid Oracle function.

Correct the spelling of the Oracle function that you wish to call.

OR

Try using the dual table if you wish to call a function without querying a table.

39) ORA-00920: invalid relational operator

You tried to execute a SQL statement, but the WHERE clause contained an invalid relational operator.

40) ORA-00920: invalid relational operator

You tried to execute a SQL statement, but the WHERE clause contained an invalid relational operator.

Correct the WHERE clause. Valid relational operators are as follows:

=                     !=                            ^=                           <>                           <                             <=

>                     >=                           ALL                              ANY

BETWEEN                  NOT BETWEEN                 EXISTS                     NOT EXISTS

IN                                NOT IN                               IS NULL                     IS NOT NULL

LIKE                          NOT LIKE

41) ORA-00923: FROM keyword not found where expected

You tried to execute a SELECT statement, and you either missed or misplaced the FROM keyword

42) ORA-00924: missing BY keyword

You tried to execute a GROUP BY, ORDER BY, CONNECT BY, or GRANT (with IDENTIFIED) statement and missed the BY keyword.

43) ORA-00925: missing INTO keyword

You tried to execute a INSERT statement and missed the INTO keyword.

44) ORA-00926: missing VALUES keyword

You tried to execute an INSERT statement and missed the VALUES keyword.

45) ORA-00927: missing equal sign

You tried to execute a statement, but missed an equal sign. This can happen in either the SET clause of a UPDATE statement or in a search condition.

46) ORA-00928: missing SELECT keyword

Probably tried to create an Oracle VIEW, but missed the SELECT keyword.

47) ORA-00929: missing period

This is an internal error message. (Contact customer support.)

48) ORA-00930: missing asterisk

This is an internal error message. (Contact customer support.)

49) ORA-00931: missing identifier

This is an internal error message. (Contact customer support.)

50) ORA-00932: inconsistent datatypes

Tried to perform an operation between two different datatypes, but the datatypes are not compatible.

51) ORA-00933: SQL command not properly ended

Probably tried to execute a SQL statement with an inappropriate clause.

52) ORA-00934: group function is not allowed here

Probably tried to execute a SQL statement that included one of the group functions (ie: MIN Function, MAX Function, SUM Function, COUNT Function) in either the WHERE clause or the GROUP BY clause

53) ORA-00935: group function is nested too deeply

This is an internal error message. (Contact customer support.)

54) ORA-00936: missing expression

Probably tried to execute a SQL statement but you omitted a part of the syntax. For e.g.

Select from employee;

55) ORA-00937: not a single-group group function

Probably tried to execute a SELECT statement that included a GROUP BY function (ie: MIN Function, MAX Function, SUM Function, COUNT Function), but was missing the GROUP BY clause.

blog comments powered by Disqus