Oracle ORA-00001: violation of unique constraints and ORA-00054: Resource busy error resolution, ora-00001ora-00054

Oracle ORA-00001: violation of unique constraints and ORA-00054: Resource busy error resolution, ora-00001ora-00054

Oracle databases may report errors for common errors. During the year of work, some errors have been encountered and some pitfalls have been encountered. Thanks to the questions and solutions shared by those predecessors, just as the Chinese New Year is not busy these days, I will describe some errors, analyze the causes and solutions, and share them with you. Most of them come from other blogs and websites, most of which have been tested and validated. If you have any questions or corrections, thank you for your reference.

ORA-00001: violation of unique constraints:

Error Description: an error is reported when data is inserted into a table.

Cause of error: the inserted data involves fields with unique constraints that cannot be duplicated.

Solution: The primary keys of individual fields cannot be repeated. Change the data to be inserted.

ORA-00054: resources are busy:

Error Description: ORA-00054: the resource is busy, but specifies to get the resource in NOWAIT mode, or timeout is invalid.

Cause of error: improper execution of DML statements causes the database to lock the table to be operated. Oracle has added row-level locks to the returned result set, all other modification or deletion operations on the data must wait for the lock to be released. The external phenomenon is that other operations will be blocked, similarly, the transaction for this query will apply a table-Level Lock to the table and will not allow any ddl operations on the table; otherwise, a ora-00054 error will be reported.

Solution: execute the following statement:

Select l. session_id, o. owner, o. object_name

From v $ locked_object l, dba_objects o

Where l. object_id = o. object_id;

Select t2.username, t2.sid, t2.serial #, t2.logon _ time

From v $ locked_object t1, v $ session t2

Where t1.session _ id = t2.sid order by t2.logon _ time;

-- Get the locked sid and serial #

-- End the session

Alter system kill session 'sid, serial #';

Replace sid with serial # To the corresponding number.

ORA-00922: the option is missing or invalid:

Problem description: ORA-00922: options are missing or invalid?

Error cause: the statement syntax is incorrect. For example, the name is incorrect or the keyword is incorrect. For non-standard names, they are generally created using double quotation marks.

Solution:

Identifier naming rules:

1. It must start with a letter.

2. The length cannot exceed 30 single-byte characters.

3, can only include A-Z, a-z, 0-9, _, $ and #.

4. Two objects with the same name cannot be created under the same user.

5. Reserved Words and keywords cannot be used.

ORA-01034: Oracle not available:

Problem description: ora-01034 and ora-27101 often appear at the same time, are in the database login to report this error

Error cause: there are many reasons for ORA-01034 and ORA-27101: mainly because the current oracle service is unavailable, shared memory realm does not exist, because Oracle is not started or not started properly, the shared memory is not allocated to the current instance. Therefore, you can start the database by setting the Instance name and operating system authentication. In this way, the database is started normally. Another possibility is that, after logging on to the database, the database may not exit normally. For example, if the window is closed directly and there are unfinished actions in the database, an error "insufficient privilege" will be reported when you log on again, SQL> shutdown immediate; SQL> startup. Finally, in the virtual machine environment, check whether the database configuration file corresponds to MB of memory, or whether your settings are too large but you are not enough.

Solution:

1: Check whether Oracle listening and Oracle services are enabled. Start Oracle listener: In the cmd command line window, enter lsnrctl start and press enter to start the listener.

2: check what the sid of Oracle is. For example, when creating a database, the Instance name is "orcl". set the sid of Oracle manually. In the cmd command window, set ORACLE_SID = orcl.

3: Enter sqlplus/nolog. Press enter.

Enter conn/as sysdba; press ENTER

4: Enter startup and press Enter. This step starts the Oracle service. If startup is started, you can enter shutdown immediate. After shutdown is finished, enter startup.

5: the connection can be established after the command is run in a few seconds. At this time, you can enter "select * from user_tables;" to test whether the query results are available.