Hi Brian, I hope you could provide a recommendation to the following.
- We have a legacy system (three applications, each application has its own database). The databases are relatively small and old data gets purged yearly.
- We are migrating to Oracle 10g with a Web-based front-end (in-house development).
- Users in these applications will access one or more of these three applications. Users are organized by sites.
- There are currently about 360 tables combined in the three databases. Total users may be a few thousand (not sure what the total concurrent users will be).
My thoughts are along these two options:
- Create one schema for each application (three schemas).
- Create only one schema for all three applications (one schema).
What would you recommend and why? If you recommend option 1, how would you best organize users/roles since users will access one or more of these three applications?
Thanks a bunch in advance.
I do occasionally have an application access more than one schema. This is not a problem. Simply figure out what object privileges are needed in each schema and then grant those privileges to a role. Then grant that role to the application users.
This was first published in March 2007