Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to understand a query . When I run the below query , no rows are fetched.

select * from Table

But when I run the query , a result is returned having multiple rows.

select * from Table@somestring

I am not sure what @ followed by somestring means and why that is used in PL/SQL.

share|improve this question
    
Does It return any Output? I yes then is it same as first Query's output? –  RJ1990 Jul 8 '13 at 5:10
    
Mate , the first query fethes no result. But the second query returns 4 rows. –  misguided Jul 8 '13 at 5:18
    
I worked on it 1 year before, I am guessing Table before @ is a username and somestring is your tablename. You can view username from PLSQL window in lefthand side panel in Users folder. –  RJ1990 Jul 8 '13 at 5:30
    
Table is definitely a table. Have checked the same in the database. Not sure about somestring though –  misguided Jul 8 '13 at 5:33
2  
Not sure why this is being marked as off-topic. Managing database links can be a DBA taks, but knowing what they are and how to use them is a programming topic. (This isn't PL/SQL-specific though, DB links are used in plain Oracle SQL too). –  Alex Poole Jul 8 '13 at 8:30
add comment

migrated from stackoverflow.com Jul 8 '13 at 8:45

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

It's part of a distributed query. The string after the @ is a database link.

You can see where the database link is pointing by querying the all_db_links view, or the user_ or dba_ equivalents depending on the owner and your privileges. It might refer to a TNS alias, in which case you could get further details from the tnsnames.ora file.

When you query table you're (usually) looking at a table in your own schema. When you query table@dblink you're looking at a table in a remote database. They are different objects so you'd expect different results. Your local table is empty, but the remote one has some data.

I say 'usually' because table could be a synonym pointing at another schema, or even to a schema on a remote database itself. And dblink could be pointing back to your own database - it would be unusual but I've seen it done as a way of accessing another schema (not something I'd recommend). Without seeing if there is a synonym or where the dblink is going it's hard to be definitive, but these are less common scenarios. In this case though you're clearly hitting two different tables, probably on different databases. (Well, unless there's a VPD and connecting to the same table as a different user gives a different view... but starting to go down the rabbit hole now...)

share|improve this answer
    
This info was very helpful :) Thanks for taking the time to explain . –  misguided Jul 8 '13 at 22:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.