DB down on executing delete query

Question

Hi All,

Is there a possibility that a simple delete query can bring down a DB?

We executed a delete query (single row deletion) and that query hung. When multiple people tried executing the same delete again, the Oracle DB is down. Multiple tables reference this table and a cascade delete was not used. Should a cascade delete have been used?

Please provide some pointers / possible reasons for the DB to go down on executing this sql while we try to dig out more info.

thanks, RJ

Answer 1

Are you sure it's down and it's not just a transaction that has locked your tables?

Answer 2

Check the log files of the server. My guess is that the delete modified a lot of rows and you ran out of UNDO log.

Answer 3

the delete stmts hung, the db reached max number of allowed processes (which were waiting); and the db stopped establishing new connections. We killed the hanging processes and the db is up and running now. :)

thanks