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
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.
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.
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.
No, you cannot give the same name for view and table in MySQL.
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
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With