Oracle Exception Rollup ____oracle

For continuous updates, see the pages on https://hnuhell.gitbooks.io/oracle_errmg/content/or https://hnuhell.github.io/Oracle_ERRMG/. ORA-00000 to ORA-00877 ORA-00000: Normal successful completion (operation)

Ora-00000:normal, Successful completion reason 1: normal execution completed. "Partial Validation"

Normal exit. Analysis: This exception is most of the program does not execute SQL statements or successful execution of SQL statements, but artificial or because of logic error, do not have to use the relevant method program to obtain Oracle's fault message, get this exception, the essence is Oracle told no exception, guess is the default value of exception information for this. The following two types of cases are currently found:

Stored procedures, pl/sql blocks, and so on, use SQLERRM to get exceptions, as shown in the following example. "Verified"

DECLARE
   v_sqlcode number;
   V_SQLERRM varchar2 (4000);
 Begin/
   *
      ...
      Related execution code ...
   * *

   V_sqlcode: = Sqlcode;
   V_SQLERRM: = SQLERRM;
   Dbms_output.put_line (' This unusual code: ' | | v_sqlcode | | | chr (10) | | ' This unusual message: ' | | V_SQLERRM);

 Exception when
   others then
     rollback;
     V_sqlcode: = Sqlcode;
     V_SQLERRM: = SQLERRM;
     Dbms_output.put_line (' This unusual code: ' | | v_sqlcode | | | chr (10) | | ' This unusual message: ' | | V_SQLERRM);
 End;
 /

Using the OCI C program, use the Erhms () function (Ocierrorget ()) to obtain an Oracle error message. "Not validated, network Rollup"

Measures: None. "If you need to get the exception, you don't have to do anything; if it is logically incorrect, you need to adjust the exception or remove it when you encounter this exception." 】

None reason 2:hosts file configuration error. "Not verified, network Rollup" Analysis: This error is usually due to the database is replicated, the Hosts file IP corresponding host name and the current host name inconsistent cause even the hosts file lost, will cause the database startup times this fault. Measures: Verify the Hosts file is wrong or missing, to modify or Add.

The directory in which hosts files are in different systems:

  Windows XP/2000/VISTA/7/8/8.1/10 ==> C:\windows\system32\drivers\etc\
  Linux and other Unix-like operating systems ==>/etc/

ORA-00001: Violation of a unique constraint (string.string=>[owner].[ Constraint name])

Ora-00001:unique constraint (string.string) violated reason 1:update or INSERT statement attempted to insert a duplicate key. For trusted Oracle configured in DBMS Mac mode, you may see this information if duplicate entries exist at different levels. "Verified"

An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, the May I/if a duplicate entry exists at a different level.

Analysis: This exception, as shown in the following example, is typically a violation of a unique constraint on a table or a PRIMARY key constraint, which restricts the uniqueness of one or more column values in a table and cannot insert duplicate data.

Action: Remove the unique constraint limit or do not insert duplicate values.

Either remove the unique restriction or does not insert the key.

If the analysis determines that a unique constraint or PRIMARY KEY constraint is not required here, you can use the following statement to delete the constraint

  --Query constraints and index information SELECT A.owner constraint owner, a.constraint_name constraint name, case a.constraint_type when ' P ' Then ' Primary key ' when ' U ' then ' the Unique key ' when ' C ' then ' C
            Heck constraint on a table ' when ' R ' then ' referential integrity ' when ' V ' then ' With CHECK option, in a view ' when ' O ' then ' with Read only, on a view ' when ' H ' th En ' Hash expression ' when ' F ' then ' Constraint that involves a REF column ' W Hen ' S ' then ' supplemental logging ' else ' unkown ' end constraint type, b.table_n
         AME table name, b.column_name column name, c.index_name index name, c.uniqueness unique index/*, D.table_name table name, D.column_name Column Name * * FROM user_constraints A, user_cons_columns B, user_indexes c/*, User_ind_columns d*/ E A.constraint_name = b.Constraint_name and a.index_name = c.index_name/*and c.index_name = d.index_name*/and A.owner = B.owner
		
  and A.owner = C.table_owner and A.owner = & "[owner]" and a.constraint_name = & "[constraint name]";
  -Because if the unique index corresponding to the constraint is manually created, the index is not deleted when the constraint is deleted, and the index that is implicitly created by Oracle is automatically deleted.
  --therefore, adding drop Index ensures that the index is definitely deleted.
  ALTER TABLE [table name] drop constraint [constraint name] DROP index; --if it is a PRIMARY key constraint, it is possible to encounter a condition that is used as a foreign key. Then the deletion will still be reported =>ora-02273: This unique/primary key has been referenced by some foreign key--after the error to understand whether there is a problem, whether you need to remove this primary key and foreign keys, and then consider using the following statement to delete the primary KEY constraint,
  The foreign KEY constraint is deleted at the same time ALTER TABLE [table name] drop constraint [constraint name] Cascade DROP INDEX;
 ALTER TABLE [table name] Drop PRIMARY key cascade DROP index;

If the analysis determines that a value is duplicated, then you need to troubleshoot duplicate value conflicts between table data and a predefined SQL statement, duplicate value conflicts between SQL statements executed within the same transaction, and remove the insertion or update of duplicate values.

NOTE: A unique constraint is the same as a PRIMARY KEY constraint: Restricts the uniqueness of constrained columns through a unique index, ensuring that any operation that causes the columns in the table to have duplicate values between rows and lines without creating a unique index in advance implicitly creates a unique constraint with the same name when creating a unique constraint or a PRIMARY KEY constraint A constraint must have an index (cannot delete an index if the constraint exists) =>ora-02429: Cannot delete an index that forces a unique/primary key The index does not necessarily have a constraint (deleting the constraint but not deleting the index will still limit the uniqueness of the indexed column's values) Unique constraints differ from primary KEY constraints: A UNIQUE constraint allows null values on the column or multiple columns, but a PRIMARY key constraint cannot exist null values a table can create only one primary KEY constraint, but can create multiple unique constraints The primary key can be extended as a foreign key, the unique constraint is not ORA-00017: The session is required to set trace events

Ora-00017:session requested to set trace event reason 1: The current session is required to set up a trace incident through another session "ORA12_ERRMG"

The session is requested to set a trace event by another.

Action: Internal use;

This is used internally; No action is required. ORA-00018: Maximum number of sessions exceeded

Ora-00018:maximum number of sessions exceeded reason 1: All session state objects are in use. "Partial Validation"

All sessions state objects are in use. Analysis: Obviously, the number of sessions in the system has reached the set sessions value, so the session you are preparing to create cannot be created successfully, and this session includes a user-generated session that connects to the database, a session generated by the background process, and various types of DML that involve a hard parsing data dictionary base table. A recursive session generated by a DDL statement.

Measures: Increase sessions initialization parameter values. "Whether you need to increase the sessions value is also a matter of judgment, whether it is because the value is too small and the real scene needs a larger value." 】

Increase the value of the SESSIONS initialization parameter.

If the judgment determines that the sessions value is too small, you will need to modify it to increase this parameter value:

   /* ALTER system SET PARAMETER name = value [scope= application Range]; Scope Knowledge: Scope=both, indicates that the modification takes effect immediately and modifies the SPFile file to ensure that the database will take effect after the reboot if (starting with SPFile this is the default); Scope=memory, which means that the modification will take effect immediately but will not be repaired.
                                           SPFile the file, so it is invalidated after reboot (Pfile this is the default and can only be set); Scope=spfile, which means that only the SPFile file is modified, and it does not take effect until the database is restarted (the corresponding static parameter can only set this value, and setting other values will error:
   Ora-02095:specified initialization parameter cannot be modified). */-View the "whether Alter system modified" column value, modify the select name parameter name based on the result, case type when 1 then ' Boo
            Lean ' when 2 Then ' String ' if 3 then ' when 4 then
           ' Parameter file ' when 5 Then ' Reserved ' is 6 then ' big Integer '
         Else ' unknown ' end parameter type, value "session-level (if modifiable) or instance-level parameter values", Display_value display value, Isses_modifiable "Whether alter session can be modified", Case issys_modifiable when ' IMMEDIATE 'Then ' alter system set ' is available either Pfile or SPFile startup name | |
            ' =& ' | | ' The new parameter value; ' Change the parameters and take effect immediately. ' When ' DEFERRED ' then ' is available either pfile or SPFile, "alter system set" | | name | |
            ' =& ' | | ' The new parameter value; ' Change the parameters and will take effect in subsequent sessions. ' When ' FALSE ' then case a.is_spfile when 0 Then ' uses Pfile to start, manual modification is required pfile The corresponding parameter values in the file are restarted. ' Else ' starts with spfile and can be used with ' alter system set ' | | name | | ' =& ' | |
               name | | ' Scope=spfile; ' Change the parameters. The changes will take effect in subsequent instances (the current database needs to be restarted).
         ' End Else '? ' End "can be modified by alter system," isinstance_modifiable "whether the values between different instances are different" from V$parameter, (select COUNT (1) is_
 SPFile from V$parameter t where T.name = ' spfile ') a WHERE name = ' Sessions ';

If the determination of the sessions value is reasonable, you need to analyze to determine the reason for the large number of sessions, whether the relevant program code established the connection was not released. or other reasons. "To be perfected"

Note: Parameter sessions:

Property	Description
Parameter type	Integer
Default value	Derivation formula: (1.1 * processes) + 5) [11GR1,11GR2] (1.5 * processes) + 22
can be modified (no reboot in time)	No [11GR2,12CR1] can be modified with alter system
Take value range	1~2^31 [11GR1,11GR2] 1~2^16 (1~65536)
Basic parameters	Is

Sessions specifies the maximum number of sessions that can be created in the system. Because each login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always explicitly set this parameter to an estimate of the maximum number of concurrent users + background processes + Number of recursive sessions (approximately 10% of the total).

Oracle uses the default value of this parameter as its minimum value. Setting the sessions value to the [1~ default value] does not trigger an error because Oracle ignores this value and uses the default value directly.

The default values for the Enqueue_resources and transactions parameters are derived from the sessions. Therefore, if you increase the value of sessions, you should consider whether the values of Enqueue_resources and transactions are also adjusted. (Note that the enqueue_resources has been discarded since Oracle Database 10g Release 2 (10.2). ）

In a shared server environment, the value of processes can be quite small. Therefore, Oracle recommends that you adjust the value of sessions to approximately 1.1 * total connections. ORA-00019: Maximum number of license sessions exceeded

Ora-00019:maximum number of Session licenses exceeded reason 1: All license sessions are in use. "Partial Validation"

All licenses are in use. Analysis: Obviously, the concurrent user session in the system has reached the set license_max_sessions value, so the user session being prepared for creation cannot be created.

Action: Increases the value of the License_max_sessions initialization parameter. "Whether you need to increase the license_max_sessions value is also a matter of judgment, whether it is because the value is too small and the real scene needs a larger value." 】

Increase the value of the LICENSE MAX SESSIONS initialization parameter. If the judgment determines that the license_max_sessions value is too small, you need to modify the value of this parameter, "see the ora-00018=> reason 1=> measure to modify the name = ' Sessions ' in the SQL statement to name = ' License_max_sessions ' Can ' if you decide that the license_max_sessions value is reasonable, then you need to analyze the reason for the large number of sessions, whether the related program code establishes the connection is not released. or other reasons. "To be perfected"

Note: parameter license_max_sessions:

Property	Description
Parameter type	Integer
Default value	0
can be modified (no reboot in time)	can be modified with alter system
Take value range	Number of 0~ license sessions
Basic parameters	Whether
Oracle Real-time application cluster	Multiple instances can have different values, but the sum of all instances of the installation database should be less than or equal to the total number of sessions licensed by the database.

license_max_sessions Specifies the maximum number of concurrent user sessions allowed. Once this limit is reached, only users with restricted session permissions can connect to the database. A user who cannot connect receives a warning message indicating that the system has reached its maximum capacity.

A value of 0 indicates that concurrent use (session) permissions are not enforced. If you set this parameter to a number other than 0, you may also need to set license_sessions_warning (see "license_sessions_warning").

Do not enable concurrent use licenses and user licenses at the same time, that is, the license_max_sessions and license_max_users two parameter values should be set to zero at least one. ORA-00020: Maximum number of processes exceeded (string=>[max processes))

Ora-00020:maximum Number of processes (string) exceeded reason 1: All process state objects are in use. "Partial Validation"

All process state objects are in use. Analysis: Obviously, the number of processes in the system has reached the set processes value, so the user session you are preparing to create cannot be created.

Action: Increases the value of the processes initialization parameter. "Whether you need to increase the processes value is also a matter of judgment, whether it is because the value is too small and the real scene needs a larger value." 】

Increase the value of the processes initialization parameter. If it is determined that the processes value is too small, you need to modify it to increase the value of this parameter, "see the ora-00018=> reason 1=> measures to modify the name = ' Sessions ' in the SQL statement to name = ' Processes '" If the determination of the processes value is reasonable, you need to analyze to determine the cause of a large number of processes, whether the relevant program code established the connection is not released. or other reasons. "To be perfected"

Note: Parameter processes:

Property	Description
Parameter type	Integer
Default value	40~ operating system dependencies [10GR2,11GR1] [11GR2,12CR1] This value is derived and typically depends on the number of cores reported in the alert log.
can be modified (no reboot in time)	Whether
Take value range	Number of 6~ operating system dependencies
Basic parameters	Is
Oracle Real-time application cluster	Multiple instances can have different values.

processes specifies the maximum number of operating system user processes that can connect to Oracle at the same time. Its value should allow all background processes to run, such as locks, job queue processes, and parallel execution processes.

This parameter derives the default value for the sessions and transactions parameters. Therefore, if you change the value of the processes, you should evaluate whether you want to adjust the values of these derived parameters. ORA-01500 to ORA-02098 ORA-01722: Invalid number

Ora-01722:invalid Number Reason 1: The specified numbers are not valid

The specified number was invalid. Analysis: The fundamental point is that the statements to be executed are implicitly converted by human explicit conversion or Oracle judgment, intentionally or unintentionally, by converting a value of a non-numeric type that cannot be converted to a numeric type into a numeric type failure. Here are a few examples:

Explicit conversions:

  --To_number, To_binary_float, to_binary_double conversion strings for numeric
  Select To_number (' 2017 ') from dual;
  Select To_binary_float (' 8.935M ') from dual;
  Select To_binary_double (' 0.001s ') from  dual;

Implicit conversions:

  ---------involving the assignment of a numeric type column or function requiring a conversion of the result of a function numeric type-----------insert/update/merge, such as a table ora_01722_1 (
  A number, B char (a), C VARCHAR2 (300));
  --insert assignment, cannot be implicitly converted to numeric value, error insert into Ora_01722_1 (a) VALUES (' 111 test string ');
  --insert assignment, you can implicitly convert to numeric value, do not error insert into Ora_01722_1 (A, B) values (' ", ' BBB ');
  Commit

  --update assignment, cannot implicitly convert to numeric value, error update ora_01722_1 set a = ' 222 test string '; --Decode from the third column of the value 1 determines the function output is a numeric type--when the match to ' a ' returns 1, the result is numeric and does not error the Select decode (' A ', ' a ', 1, ' B ', 2, ' C ', ' three ', 0) test1 fro
  M dual;

  --When the match to ' C ' returns ' three ', the result is not numeric and cannot be converted to numeric value, error select decode (' C ', ' A ', 1, ' B ', 2, ' C ', ' three ', 0) test2 from dual; --NVL the number entered in the first column determines that the result it outputs at this time is also a numeric value--When the first value is not empty, after attempting to output the value as the result of this NVL function operation, we found that it could not be converted to a numeric value, error select NVL (& input value, ' test ')

  from dual; --NVL2 is determined by 2 in the second column that its output at this time will also be a numeric value--When the result is 2, it speaks the following value to test the conversion ...

  Therefore, regardless of whether or not NULL is entered, the Select NVL2 (& whether null, 2, ' test ') from dual; ---------involves comparisons with numeric types or other operations-----------not compared to ' three ', without error select decode (1, 1, ' One ', 2,' Two ', ' three ', ' three ', ' zero ' from dual;
  --Compare to ' three ', error select decode (3, 1, ' One ', 2, ' two ', ' three ', ' three ', ' zero ') from dual;
  --arithmetic operations with numeric values, cannot be implicitly converted to numeric values, error select ' A ' + 2 from dual;
  --compared to Ora_01722_1 's numeric type column A, converting the right to a numeric value failed with the error select * from Ora_01722_1 t where t.a = ' a ';  --ora_01722_1 column B is compared to the numeric type, the left is converted to a numeric value failure, the error select * from Ora_01722_1 t where t.b