[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-18
10
Medium Priority
?
305 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.
0
Comment
Question by:developer2012
  • 5
  • 2
  • 2
9 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40267923
Create a synonym in the schema you are using:

create synonym your_current_schema.attr for  schemaname.atts;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40267929
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
 

Author Comment

by:developer2012
ID: 40267944
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40267958
>> 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
 

Author Comment

by:developer2012
ID: 40267972
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40267978
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
 
LVL 35

Expert Comment

by:johnsone
ID: 40268021
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40268034
I don't like public synonyms.  Way to easy to confuse objects across multiple schemas.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40268043
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question