say that i have an Oracle SQL Db (Oracle Database 11g Enterprise), and i have two schemas: schema1 and schema2 (same instance, clustered). I have a table that contains data that should be shared across these two schemas.
How many ways do i have to do this except for:
Thank you in advance
The only other option is:
This would definitely be my preferred option. It is better than option 1 because it allows schema1 (owner) to have more control over what schema2 can see (e.g. filter rows, expose a subset of columns). It also allows schema1 to change the table without schema2 (necessarily) having to change their calling code. Much safer.
Note that if schema2 is in a separate database then there are use cases for option 2. I would be inclined to use a materialized view rather than a table, because that means Oracle does most of the work entailed by synchronisation. This means schema2 can query the data even if the schema1's database is down. (MViews can also be used even if the two schemas are in the same database, it's just the case is less compelling).
Option 2 is a bad choice if schema2 needs to update, insert or delete the shared data. In that scenario always go for an interface.
If you do decide to apply option 1 and grant privileges on the table directly please have schema2 create a synonym and reference that in its code. Do not hardcode schema names in programs.