how to execute query without having to declare schema all the time?

developer2012
developer2012 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Create a synonym in the schema you are using:

create synonym your_current_schema.attr for  schemaname.atts;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Looks like you can also alter your session:
ALTER SESSION SET CURRENT_SCHEMA = <schema name>

http://docs.oracle.com/cd/B28359_01/server.111/b28310/general009.htm

Author

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>> 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;

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.
johnsoneSenior Oracle DBA

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I don't like public synonyms.  Way to easy to confuse objects across multiple schemas.
johnsoneSenior Oracle DBA

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial