Oracle same table name on different schema?

Question

Is it possible to have same table name on different schema with different data on the tables within the one database? I think rather than create multiple database (that hold same table name) i should create multiple schema instead.

Something like:

Schema 1:
  table A, table B
Schema 2:
  table B, table X

PS: table B hold different data for each schema

And most basic question did every schema (user) hold different table set? In SQL Server and MySQL every user on same database have same table set, the difference was only on roles, how about Oracle?

Thanks

Solution 1

Yes this is possible. A schema in Oracle is what a MySQL user would call a database.

However, you need to be careful when accessing either of the tables now if the user you connect with to the database instance has access to both. To unambiguously reference a table in a specific schema, use schema.table.

Solution 2

Here's the documentation on namespaces: https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-3C59E44A-5140-4BCA-B9E1-3039C8050C49

As jackrabbit says objects in different schemas have different namespaces.

Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

Within a schema things are a little more complex.

Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.

Comments

[Dels]

ok thanks, do the tablespace (default & temporary) for each schema need to be in different file?

[Rob van Laarhoven]

no problem at all to give user same default and temp tablespace