2 Replies Latest reply: Jun 12, 2014 11:33 AM by Steve.1234555-Oracle RSS

    unable to query table APEX_APPLICATION_ALL_AUTH

    Steve.1234555-Oracle

      Hello,

       

      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.

       

      I'm able to run the following query to get the row count of the table:

      select count(*)

      from APEX_APPLICATION_ALL_AUTH

       

      > 67 rows

       

      However, when I try to retrieve all the rows it runs for 5 to 10 mins then gives an Apache error:

      select *

      from APEX_APPLICATION_ALL_AUTH

       

      >

      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.

       

       

      Ideas on how to resolve this?

       

      Steve

        • 1. Re: unable to query table APEX_APPLICATION_ALL_AUTH
          fac586

          Steve_1234555 wrote:

           

          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:

          select count(*)

          from APEX_APPLICATION_ALL_AUTH

           

          > 67 rows

           

          However, when I try to retrieve all the rows it runs for 5 to 10 mins then gives an Apache error:

          select *

          from APEX_APPLICATION_ALL_AUTH

           

          >

          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
          
          • 2. Re: unable to query table APEX_APPLICATION_ALL_AUTH
            Steve.1234555-Oracle

            Hello,

             

            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.

             

            Thanks!

            Steve