Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle same table name on different schema?

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

like image 636
Dels Avatar asked Nov 06 '09 06:11

Dels


People also ask

Can we create table with same name in different schema?

If you are using the schemas for the purpose of securing the data in one table vs. the other and only giving users access to one of them, using the same name can be great. Also, you can do this to set a context for an application and use your connection information to allow you to re-use code.

Can a table be in multiple schemas?

Identical database object names can be used in different schemas in the same database without conflict. For example, both MY_SCHEMA and YOUR_SCHEMA can contain a table named MYTABLE. Users with the necessary permissions can access objects across multiple schemas in a database.

How can I compare two tables in different schemas in Oracle?

The database diff option in SQL Developer is a good way to do this (Tools -> Database Diff...) Create a connection for each user and you can compare them. This includes many options for how to do the comparison, a nice report of the differences, and the scripts to sync them up.

Can we have same table name in SQL?

No, you cannot give the same name for view and table in MySQL.


2 Answers

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.

like image 157
jackrabbit Avatar answered Nov 15 '22 17:11

jackrabbit


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.

like image 31
David Aldridge Avatar answered Nov 15 '22 16:11

David Aldridge