Q
Problem solve Get help with specific problems with your technologies, process and projects.

Two schemas on a single database

Suppose I have 2 schemas/users on a single database (s1 and s2) and s1 executes procedure prc1 in s2 which updates the table t1. Also imagine both s1 and s2 have the table t1 (same structure). Which table will be updated?

Suppose I have 2 schemas/users on a single database (s1 and s2) and s1 executes procedure prc1 in s2 which updates the table t1. Also imagine both s1 and s2 have the table t1 (same structure). Which table will be updated?

My vote is for table t1 in schema s2.

I would like to know what are the privileges that control this condition? i.e., what are the privileges to be given for executing the procedure and updating the tables in another schema?
A procedure sets its dependancies at compile time, not run time, so once a procedure is compiled, that doesn't change.

There is also an order of dependancy checking. Oracle first looks in the current schema for an object, then any private synonyms and finally any public synonyms.

So, if you compile a procedure and you have a table of name 'x' in your schema, it doesn't matter if anyone else has a table of that name. You procedure will always use the same 'x' table.

If you compile a procedure and you do not have a table named 'x' in your schema, Oracle will look to see if you have any private synonyms named 'x' and finally any public synonyms named 'x.' If it finds one it will always use that schema's 'x' even if you create a table in your schema afterwards.

The safest ways to ensure which schema's table you are using is to either prepend the schema owner to the table name, i.e. s2.t1, or to use "alter session set current_schema = s2;"

To use a table in another schema, you would need at least select permission on that table. To create a procedure using a table in another schema, you would need select granted directly to your user name and not to a role.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close