Today, when I drop a user, it's time for hang to stop and don't care. After an hour, the result has not been deleted. There are not many objects in this user, and they are not big either, so I checked the alert Log and found the following information:
ORA-00060: Deadlock detected. More info in file/u01/app/Oracle/diag/rdbms/tlgdb/trace/tlgdb_ora_14577.trc.
ORA-00060: Deadlock detected. More info in file/u01/app/oracle/diag/rdbms/tlgdb/trace/tlgdb_ora_14577.trc.
ORA-00060: Deadlock detected. More info in file/u01/app/oracle/diag/rdbms/tlgdb/trace/tlgdb_ora_14577.trc.
ORA-00060: Deadlock detected. More info in file/u01/app/oracle/diag/rdbms/tlgdb/trace/tlgdb_ora_14577.trc.
In this way, information is constantly emerging. The deadlock, trc, and many of the information is as follows:
[10:37:46] deadlock detected (ORA-00060)
[10:37:46]
[10:37:46] [Transaction Deadlock]
[10:37:46]
[10:37:46] The following deadlock is not an ORACLE error. It is
[10:37:46] deadlock due to user error in the design of an application
[10:37:46] or from issuing incorrect ad-hoc SQL. The following
[10:37:46] information may aid in determining the deadlock:
[10:37:46]
[10:37:47] Deadlock graph:
[10:37:47] --------- Blocker (s) -------- --------- Waiter (s )---------
[10:37:47] Resource Name process session holds waits
[10:37:47] TX-000a000a-00002c81 36 16X36 16 S
[10:37:47]
[10:37:47] session 16: DID 0001-0024-00003E8F session 16: DID 0001-0024-00003E8F
[10:37:47]
[10:37:47] Rows waited on:
[10:37:47] Session 16: no row
[10:37:47]
[10:37:47] ----- Information for the OTHER waiting sessions -----
[10:37:47] ----- End of information for the OTHER waiting sessions -----
[10:37:47]
[10:37:47] Information for THIS session:
[10:37:47]
[10:37:47] ----- Current SQL Statement for this session (SQL _id = 510jn7rgn8wyk )-----
[10:37:47] insert into "OGG01". "GGS_STICK" (PROPERTY, VALUE) VALUES ('lv _ version',: B1)
Can an insert statement be deadlocked? It's not very clear here. If you know it, you can give some advice, because hang lived during drop, so here we thought of doing hanganalyze analysis. Here we use level 3 hanganalyze analysis, this is also prompted by roger. For details about the knowledge points and analysis process of hanganalyze, refer to the Manageability Monitor. MMON is mainly used for AWR, ADDM, MMON writes the statistical results from SGA to the system table. This is the role of this process, but it is related to deleting a user. The reason is still unclear.