We help IT Professionals succeed at work.

Check out this week's podcast, "Dairy Farms to Databases: Community's Hand in Technology"Listen Now

x

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

developer2012
on
357 Views
Last Modified: 2014-08-18
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.