I am trying to fix a procedure in one of our applications. In order to fix it properly I need to find the schema owner. The users log in as myapp_unam or perhaps myapp_tst_uname and I need to find the name of the schema owner which could be myapp or myapp_tst or even something else. Since each user owns synonyms to the schema owner I was trying to look at user_synonyms to find the schema owner for the table that I am currently working on. Any help would be appreciated. Thanks.
Who are your database users?
select username from dba_users.
Who owns tables in the database?
select distinct owner from dba_tables.
Chances are your list of owners will be much smaller than the list of users. Your owner is also a user, but a user isn't necessarily an owner.
You can also cross check with select distinct owner from dba_objects.
Thanks for you reply Stecal. I was able to resolve this by looking at User_Tab_Privs where table_Name = tname that I am currently working on and Privilege = 'SELECT' I was able to uniquely Identify the schema owner. Because of the way our system is implemented we could have basically the same schema running twice in an instance, one test and one production.
Another option, if you use Enterprise Manager, you can right click on an object and choose either "Show Dependencies" or "Show Dependents"
OCP DBA 8i, 9i, 10g
Click Here to Expand Forum to Full Width