developer2012
asked on
how to execute query without having to declare schema all the time?
Hi Everyone,
I am working in Oracle and Whenever I execute the query I have to give the schema name.
For example. --> select * from atts; (returns an error stating the table or view does not exist) and if I modify the query to select * from schemaname.atts; it works fine.
Can you please help in resolving this issue?
Thanks.
I am working in Oracle and Whenever I execute the query I have to give the schema name.
For example. --> select * from atts; (returns an error stating the table or view does not exist) and if I modify the query to select * from schemaname.atts; it works fine.
Can you please help in resolving this issue?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I create a synonym, do I have to mention the table name as well? Because I have created a synonym.
Something like this
Create synonym t1 for t; and it did not work.
And what happens if I have 100 tables do I have to create separately for 100 tables?
Just wondering. Thanks
Something like this
Create synonym t1 for t; and it did not work.
And what happens if I have 100 tables do I have to create separately for 100 tables?
Just wondering. Thanks
>> do I have to mention the table name as well?
Synonyms are at the object level. You also have to specify the schema if the object you are creating the synonym on, or the synonym itself is in a different schema.
If you are loged in as Fred and want to reference a table in barney:
create synonym fred_table for fred.table;
If you are logged in as fred and want to create a synonym for barney:
create synonym barney.fred_table for table;
If you are logged in as someone else, provide the schema on BOTH sides:
create synonym barney.fred_table for fred.table;
Synonyms are at the object level. You also have to specify the schema if the object you are creating the synonym on, or the synonym itself is in a different schema.
If you are loged in as Fred and want to reference a table in barney:
create synonym fred_table for fred.table;
If you are logged in as fred and want to create a synonym for barney:
create synonym barney.fred_table for table;
If you are logged in as someone else, provide the schema on BOTH sides:
create synonym barney.fred_table for fred.table;
ASKER
Okay I log in to the system as 'SYSTEM' and created a synonym
create synonym system3 for t.system; Though, the synonym gets created but I still don't get the data without specifying the synonym name.
Thanks.
create synonym system3 for t.system; Though, the synonym gets created but I still don't get the data without specifying the synonym name.
Thanks.
You should NOT create objects in the SYSTEM (or SYS) schemas.
You have to specify the synonym name. You don't have to provide the schema name.
You have to specify the synonym name. You don't have to provide the schema name.
If you want them for only the user you are connected as, then use CREATE SYNONYM connected as your user. This would be the preferred method because creating a synonym can change name resolution and break an application.
If you want synonyms for all users, then you need to connect as a privileged user and use CREATE PUBLIC SYNONYM.
If you want synonyms for all users, then you need to connect as a privileged user and use CREATE PUBLIC SYNONYM.
I don't like public synonyms. Way to easy to confuse objects across multiple schemas.
I have worked with third party applications that required public synonyms, so sometimes no way around them. If the database only hosts one application, then public synonyms usually don't get in the way.
Here is one of the good things about public synonyms. In a development environment a developer can make a copy of a production table in their own schema, do modifications to that table and test. Without prefacing the table with the schema name, they get their own local copy of the new table but see all the other tables without having to make copies, or their own synonyms. This was a side effect of some of the applications we were using, but it made like easier sometimes.
Here is one of the good things about public synonyms. In a development environment a developer can make a copy of a production table in their own schema, do modifications to that table and test. Without prefacing the table with the schema name, they get their own local copy of the new table but see all the other tables without having to make copies, or their own synonyms. This was a side effect of some of the applications we were using, but it made like easier sometimes.
ALTER SESSION SET CURRENT_SCHEMA = <schema name>
http://docs.oracle.com/cd/B28359_01/server.111/b28310/general009.htm