How do I host and separate multiple projects on one Oracle Database?

Question

From Difference between database vs user vs schema

I'm trying to understand the concepts of the schema, user and database.

My current understanding is you cannot create a schema separately. You can create a user and a schema will be automatically created.

Then my question is, what if you want to create 3 users and only use one schema by granting privileges to only one schema? Will there be 2 empty, unused schemata?

For example, in a project called "Killer Web", I have 3 application developers and I am the DBA.

So I created 3 users for each developer, dev1, dev2 and dev3. And I granted on these three devs, access and other privileges to the actual schema on which the 3 devs will actually work. Would that create 3 schemata, empty and unused?

If I wanted to create a schema called "KillerWeb", would I have had to create a "user" called "KillerWeb"?

How do I host multiple projects on one Oracle database and separate them?

In MySQL, I would just create a database for each project. (Not sure if this is the correct practice though.)

Answer 1

What if you want to create 3 users and only use one schema by granting privileges to only one schema? Will there be 2 empty, unused schemata?

[...] Would that create 3 schemata, empty and unused?

Essentially yes. You'll use the users to log in to the database, but some of them won't have any objects, i.e. they'll have empty schema. (This is not uncommon at all.)

_{But none of this "creates empty schemas" in a technical sense. A schema is just a concept in Oracle, it doesn't have a concrete existence. You have users, and they own objects. Users and objects exist in the database in a very concrete sense, "schema" does not. It is just a term used to describe a user and its objects.}

If I wanted to create a schema called "KillerWeb", would I have had to create a "user" called "KillerWeb"?

Yes, you need to create that user, even if no-one/nothing ever uses it to log in. (This not uncommon either. And you should actually prevent logins if no-one should.)

How do I host multiple projects on one Oracle database and separate them?

One option is creating a user for each application. By default, they won't be able to access each other's objects (unless you grant them the appropriate privileges).

Another option is Oracle's multitenant (additional cost, >= 12g only) option, a.k.a. pluggable databases, which gives you something in between one database with separate users and multiple, independent databases.

Or you could just give each app its own database.

How you decide between those depends on a lot of factors, including your budget, whether there is any shared data (and how you want/must share it), security considerations, backup/restore considerations, whether you suspect you'll want to split them up later, how you plan to scale, etc.