4 Replies Latest reply: Nov 8, 2012 3:36 PM by Vite DBA RSS

    APEX Schema vs Application Schema

    MDK999
      What is standard Production practice of schema creation?

      Do I need to have seperate schema for APEX objects and workspace AND have it connected to my application production schema where all application related tables resides?

      Please advice.

      Thanks
        • 1. Re: APEX Schema vs Application Schema
          Pete Mahon
          Hi,

          You can create your objects in any schema you wish (one schema per workspace), as long as you add the relevant schema to APEX so that it can parse correctly. See this page if you need to change the schema you are parsing in.

          You must have a separate schema to the one APEX creates as the APEX user is locked following installation.

          Hope this helps.
          Regards, Pete
          • 2. Re: APEX Schema vs Application Schema
            Vite DBA
            Hi,

            I'm not sure that you understand the architecture of Apex, and maybe a brief explanation will answer your question.

            When you create an Apex workspace, you associate that workspace with an Oracle schema that is the parsing schema, which means that all the code you produce as part of your application is executed as this schema. This schema then usually becomes the owner of all your Oracle database objects such as tables, views, indexes, packages etc. (it can be more complicated with multiple schemas, but lets keep it simple for the moment)

            Next, you come to create an Apex application against that schema. The Apex objects that you create, applications, pages, regions, items etc, are not actually stored in that schema. They are associated to that schema by the workspace and are actually stored in a metadata structure in the Apex schema, which you never touch directly, but interact with through the actual Apex application. If you logged onto your parsing schema through SQLPlus and tried to find the Apex objects by querying the normal database metadata views you won't find anything.

            Getting back to the multiple schemas thing, for small to medium applications that are purely for Apex, I would recommend that you keep every thing in one schema and use that schema as your parsing schema. For more complex applications, from a purely database design point of view, it may be preferable to split your application into several sub-applications in several schemas. In this case you will need to create a parsing schema or use one of the existing schemas as a parsing schema and then maintain a system of grants and synonyms between the schemas as well as identify all the schemas to Apex, though only one can be the parsing schema.

            There is a theory going around that promotes a separation between the schemas that own the database objects and the schema that an application connects as. This is primarily for security and safety so that developers don't damage the database application and end users don't gain unauthorised access through techniques such as SQL injection. This can be a concern with very large development projects with large and/or public user bases. I tend to think though that this is not the typical target for Apex applications (whether Apex is a suitable API in this case is another discussion) and applying this sort architecture is overkill in most cases.

            I hope that I understood your query correctly and that this helps.

            Regards
            Andre
            • 3. Re: APEX Schema vs Application Schema
              adrianp
              Hi MDK999.

              I usually create a schema (and tablespace) for each application for two main reasons:
              1. Avoid object naming conflicts
              2. Allows tablespace (application-specific) recovery

              Best regards,
              Adrian
              • 4. Re: APEX Schema vs Application Schema
                Vite DBA
                Hi Adrian,

                Yes I agree with your approach.

                My main point was that for most small to medium applications, splitting a single application over several different schemas for whatever practical or philosohpical reason is usually not worth the trouble.

                Regards
                Andre