I have three different schemas, each comprise about 15 tables. I am planning to merge them into one schema as they are related in a loose way. They belong to three applications that work together (80% of the time) or separately (20%). What are the disadvantages and advantages of keeping them separate or joining them together?
The biggest disadvantage to this proposal is with security. If all database objects are in the same schema, you will not be able to stop one application from using or access another applications database objects. You can also run into problems with managing these applications. Should there ever be a need to shutdown access for one application but still allow access for the others, you can simply lock the account. But you won't be able to lock the account if all objects are in the same schema without affecting the other two applications.
The advantages of using one large schema is that it makes application development easier since you don't have to worry about permissions between schemas. And it can be easier to administer one schema over multiple schemas. But in my opinion, three schemas is not that much to deal with. Many databases have more than three schemas.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.