I'm looking for a dictionary table I query to find all objects in my schema with an authorization scheme linked. I believe APEX_APPLICATION_ALL_AUTH is good for that.
The description of the APEX_APPLICATION_ALL_AUTH view is "All authorization schemes for all components by Application". It thus has nothing to do with "objects" in a "schema" (which are database rarther than APEX concepts). It's also not possible to apply an APEX authorization scheme to [database] objects in a [database] schema.
Can you express your requirements clearly using standard APEX terminology (workspace, application, page, region, item, component etc)?
I'm able to run the following query to get the row count of the table:
> 67 rows
However, when I try to retrieve all the rows it runs for 5 to 10 mins then gives an Apache error:
Failure of server APACHE bridge:
No backend server available for connection: timed out after 10 seconds or idempotent set to OFF or method not idempotent.
The optimizer has probably been able to get the results for the count query using indexes on the tables underlying the view, but the other query is timing out trying to retrieve information for all of the columns from the tables themselves.
Where are you running this: on a local APEX instance or a hosted instance with many different workspaces like apex.oracle.com?
Ideas on how to resolve this?
Add a WHERE clause filter to restrict the query to the workspace associated with the schema, and potentially one or more specific applications:
select * from apex_application_all_auth where workspace = 'FOO' and application_id = 100
Sorry, I was trying to find any APEX item, page, component, etc that had an authorization scheme linked (didn't mean database objects). I was able to run the query by adding the where clause as you suggested (with workspace and application_id filters). It ran in a few seconds.