Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle APEX - How to deal with Unresponsive Page when executing in SQL Command ?

Jasper TanglibApr 19 2021

Hi,
In Oracle Apex 20.2, when I execute a simple Delete query, the page hangs and just gives off a pop up that says the page is unresponsive.
Code:
DELETE FROM wwv_flow_files where ID = 19575854524840376
Pop Up:
User:
Not only does this occur when I execute a simple delete query but I also have other codes that triggers this issue such as loops and inserts.
I have already tried to do the following:
Log off from the workspace and relogged in.
Close all browsers and relogged in.
Restarted laptop.
After doing all of the above, I am still encountering the same issue. I have encountered this before but simply relogging into the workspace usually solved it but this time it has become more persistent.

How can I fix this issue?

Any ideas or suggestions are appreciated.
-Jazz

This post has been answered by Jasper Tanglib on Apr 20 2021
Jump to Answer

Comments

Jasper Tanglib

Tried to clear browser's cache and tried to use another browser (Firefox). I does not work.

Jasper Tanglib

I am able to delete other files like
image.pngimage.png
But for some reason I cannot delete this specific file I uploaded named Process.zip, When I try to delete this rows, it would just hang or the page just freezes forever.
image.png
I am working with files called Process.zip and I have successfully uploaded and deleted this kind of file before. Just this day it decides to be freezing now.

jariola

I don't know what is issue with your browser. But you shouldn't directly update, insert or delete anything from APEX internal tables. That isn't supported.

Billy Verreynne

Do the DELETE statement - then find that session in v$session and look at its wait and event states.

Jasper Tanglib

@billy-verreynne , I found out about the term locks and this is very new to me and I think this is the cause of my issue. It's something like a session that's still running so I think I'll need to find out where that is and then kill it.
I remember I did execute a WHILE LOOP on those rows that will never exit the loop.
I have tried to query FROM v$session, or v$lock, or dba_objects but I'm only getting table or view does not exist.
image.pngCan you help how I could query this in Oracle Apex?

Billy Verreynne

Your Oracle schema needs select privs on the data dictionary.
Talk to your DBA about providing this grant, or to assist in determining the wait and events states of the problem session.
Wearing my DBA hat, I have no issues granting developers access to v$ virtual performance views in dev environments - it provides them with better insights into the technical workings of Oracle.

Jasper Tanglib

Would the query my DBA need to execute be this? grant select any dictionary to schema_name;

Billy Verreynne

Would the query my DBA need to execute be this? grant select any dictionary to schema_name;
Yes.

Jasper Tanglib

Thank you @billy-verreynne. I'll be trying this approach as it also seems to be the only solution I have seen through my extensive research on this today.

Jasper Tanglib
Answer

Just a reference for anyone reading this thread.
We were not sure how to specifically locate the locked rows and table and was taking much time so a workaround that my DBA did was restart the Autonomous database and that seemed to kill all the sessions on the locked rows so now it's working well.
I think this is not recommended if you have multiple developers and your app should be running all the time.

Marked as Answer by Jasper Tanglib · Apr 20 2021
Billy Verreynne

Jasper, the v$session view has columns indicating the session (if any), blocking the current session.
Remember that hitting the db reset button, effectively destroys the runtime context and data required for root cause analysis - and usually leaves you blind from preventing the problem in future.

1 - 11

Post Details

Added on Apr 19 2021
11 comments
1,889 views