SQL Statement Hang
Statements hanging or waiting always has a reason, lock release. Let's see some cases.
UPDATE TABLE Hangs
You may saw something is blocking when updating table.
SQL> update hr.employees set first_name = 'Scott' where last_name = 'Rowe';
ALTER TABLE ADD Hangs
Or trying to add a column by ALTER TABLE.
SQL> alter table employees add (whatsapp varchar2(50));
ALTER TABLESPACE Hangs
Even when we make a tablespace READ ONLY.
SQL> alter tablespace example read only;
You won't see any error, warning or alert. Just an indefinite waiting.
This is because the statement is going to acquire some kind of lock, it needs the releasing of the original lock. Which means to unlock the table.
Unlock Table
To solve the hanging, you should terminate all open transactions related to the statement, a COMMIT or ROLLBACK might be necessary by the transaction owner.
Sometimes, users can't close the transactions by themselves at this moment. You have ways to kill their sessions if necessary.