I would strongly recommend multiple schemas - maybe more than two - for security and manageability purposes. The main one being security.

If everything is in a single schema, it becomes much harder to lock things down. For example, if you put an application audit trail into your application, I would suggest a private schema JUST FOR the audit trail. Your OLTP schema would be granted INSERT (but nothing else), your admin schema would be granted SELECT and maybe nothing else. A third schema might have a privileged routine that could read/write the audit trail (to purge old information for example). But you might have multiple schemas just to live with an audit trail.

Even more so with OLTP data and read only/read mostly data. It should definitely be segregated with just the minimum privileges granted to everyone.


In fact for your OLTP schema I would suggest at a minimum three schemas

schema1 - owns the tables

schema2 - owns views and code that expose the tables, granted minimum privs on schema1

schema3 - granted minimum privs on schema2. This is what your application logs in as, assuming three tier application server implementation


In that fashion, if someone 'hacks' into schema3 - there isn't much they can do. they cannot drop the application tables for example. they cannot do anything except run the application.

Using multiple schemas is a positive thing, use them for security and managing complex sets of objects.