Do two users access the same database or different?

Question

I installed Oracle on my system, so now orcl is the SID, which is the unique identifier of my database instance.

Now starter db was created as part of the installation. I created 2 users user1 and user2 using the system account.

Using SQL developer I am accessing the users, this shows me 2 different connections with all the database objects like tables, stored procedures views etc.

so

When using these 2 users, am I accessing the same database? I am giving all the ddl commands by logging into the user1 or user 2, does all this data goes into the same .dbf file?

The database instance can be connected to only one database, then does this essentially mean that everytime I create a new database, to make a database instance to point to that, I need to do a configuration change?

Answer 1

In my experience with Oracle, the typical unit of division is a schema. Schemas in Oracle are used more like you would use databases in SQL Server or PostgreSQL. They represent both users and a logical separation of objects. Physical separation would usually be done using tablespaces. Tablespaces are a group of physical files where data is stored. Schemas can share or use different tablespaces. Having one tablespace per schema is uncommon; they usually share a few tablespaces or often even just one.

With that in mind, to answer your questions more directly,

1) Like in any other database, you can specify the schema the object belongs to:

CREATE TABLE MY_SCHEMA.TABLE_X ( X NUMBER )

If the schemas on two CREATE statements are different, then it will create different objects. What's different in Oracle is that the default schema changes for every user. The default schema is always the currently connected schema/user. So if you omit the schema like so:

CREATE TABLE TABLE_X ( X NUMBER )

then the implied schema is the currently connected schema/user. So if I'm logged in as MY_SCHEMA, then the above is equivalent to the first example. When connecting as two different users, then the implied schema will be different and the DDL is not equivalent between the two users. So running the same statement would create two different objects if you do not specify a schema.

The two objects may be stored in the same physical file if they are in the same tablespace. (They are most likely in the USERS tablespace if you did not create one explicitly and did not specify a different default tablespace when creating the schemas.) Regardless, they are still two completely separate objects.

If you specify the schema explicitly like in the first example, then the DDL is equivalent regardless of who executes it (although permissions may prevent some users from executing it). So it would result in creating the object once, and attempting to create it a second time would result in an error unless you're using CREATE OR REPLACE or something similar.

2) I don't know the answer to this question, but as I said, in Oracle, the basic unit of separation is usually the schema, not a database. I believe the question you're asking is a large part of the reason why the schemas are used in the way they are. Having multiple actual databases on the same machine/instance is far more difficult in Oracle than in other databases (if not impossible), so it's much simpler to have a single database with many schemas.