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.
developer2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Create a synonym in the schema you are using:

create synonym your_current_schema.attr for  schemaname.atts;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) 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
0
developer2012Author 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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) 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;
0
developer2012Author 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.
0
slightwv (䄆 Netminder) 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.
0
johnsoneSenior Oracle DBACommented:
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.
0
slightwv (䄆 Netminder) Commented:
I don't like public synonyms.  Way to easy to confuse objects across multiple schemas.
0
johnsoneSenior Oracle DBACommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.