3 Replies Latest reply: Aug 22, 2013 12:39 PM by Mike Kutz RSS

    Parsing Schema and user data schema

    MarcosOrtega

      Hi there;

       

          We have an application that we intend  to migrate apex environment , but the real application  is like  the following;

       

          All users have his own data schema, so that allows us to have a full functional exp dmp from all tables from the users schema.

        

          So, bring this case to apex architecture, it's looks like i need to install apex at my oracle instance and create (lets say 10 workspaces if I have 10 users data schema one at each schema)  or have a full exp from the application workspace and a full import at each users data schema;

       

          Does some one have some like this environment ?

       

          What about  the apex bast practice , what would be the apex's optimal scenario ?

       

      Forwards Thanks;

       

      Marcos Ortega

        • 1. Re: Parsing Schema and user data schema
          Nick B

          Hey Marcos,

           

          Are you sure you want to keep the user/schema setup as it is now? I don't know the details of your requirements, but it sounds like you should use VPD instead of one schema per user. Anyway, I'll assume you want to migrate to APEX with the current user/schema setup.


          You should know that an APEX application can have only one parsing schema. That means you'll need to create a separate application per user. Sounds awful, right? Think of your maintainability: one change to the application means you have to update every user's application. Furthermore, I'd stick to a single workspace since it is perfectly possible to assign multiple schema's to one workspace. A workspace per user is less maintainable and not necessary in your case.

           

          Kind regards,

          Nick

          • 2. Re: Parsing Schema and user data schema
            MarcosOrtega

            Hi nickB

            Thank you for your answer; very good point;

             

            But;

              We really have  to many tables that it's lines  must be exclusively owned by one client , like (customers , payment billings , classified payments ) at all;

              thinking to fast ; none of ours tables would have lines shared by more than one client;

             

              Span lets say "some thing like clientID" over every single table structure to be possible to use VPD; ( I know VPD just a little , have  read few papers about it ) ;

             

              What about "Alter Session Set Current Schema" ; it could be a solution option ?

             

               So we use one "pseudo" application  schema to test and run application and as an real client user logged we just call "alter ... Set current Schema" ;

             

            Thanks;

             

            Marcos Ortega

            • 3. Re: Parsing Schema and user data schema
              Mike Kutz

                What about "Alter Session Set Current Schema" ; it could be a solution option ?

              I was thinking the same thing but realized that all it will take is one tiny slip-up in your code and your company goes under.

               

              Since everything is parsed by one schema...

              And since that parsing schema needs the ability to modify data for CompanyA and CompanyB and CompanyC and .... ... at the same time...

              The ALTER SESSION solution then implies that CompanyA employees can perform DML operations on CompanyB's tables...

              BAD

               

              I'd go with the VPD solution and be done with it.