11

Looking at V$SQL in my database, I have just found a strange query that looks like :

UPDATE "MYTABLE" "A1" SET "SOMECOLUMN" = (
    SELECT "A2"."ANOTHERCOLUMN" 
    FROM "ANOTHERTABLE"@! "A2" 
    WHERE "A2".ROWID=:B1
)

Does anyone know the meaning of the syntax @!

I have never seen something like it before in Oracle

Thanks

Alexander
  • 3,129
  • 2
  • 19
  • 33
jeleb
  • 633
  • 6
  • 14
  • A saw this sequence "@!" on Oracle 9i when querinng V$sql. Usually this was at the end of the statement. I think this was some kind of bug. – ibre5041 Feb 04 '14 at 10:08
  • Hmm Iguess it wouldn't cause any issues if it was equivalent to a newline, wild guess would be the query with a newline after "ANOTHERTABEL" got translated as "ANOTHERTABEL"@! – dbza Aug 21 '14 at 22:20
  • Related http://stackoverflow.com/questions/11671303/what-does-mean-in-a-from-statement – isalgueiro Aug 27 '14 at 09:50

2 Answers2

3

It's a query that has originated on a remote database. The database where you've seen this query in V$SQL has been referenced in the query on the remote database using the @DB_NAME syntax

The remote database has pushed the query to your database for execution, but to answer the query, your database needs to pull some information back from the remote database. This is where the @! comes in, basically it's a reference back to the database where the query originated from

For example, create a test database link, even to the same database, and run this:

alter system flush shared_pool;
select sysdate from dual@myself;
select sql_text from gv$sql where sql_fulltext like '%@!%';

SQL_TEXT
--------
SELECT SYSDATE@! FROM "DUAL" "A1"
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
James
  • 3,252
  • 1
  • 18
  • 33
  • That sounds interesting. Do you have any reference for that –  Aug 31 '14 at 19:15
  • @a_horse_with_no_name I added a simple example. Oddly, I cannot find any references to this behavior. And I cannot find any real-world examples among the 300 databases I have access to, even though they have many database links. – Jon Heller Aug 31 '14 at 19:23
  • 1
    It's mentioned in an asktom article, near the bottom https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:633023740029, but as Tom mentions it's undocumented, and unsupported (when used by anyone but Oracle themselves). Also he doesn't describe it being used as I described above - I heard the explanation above as an anecdote from a colleague, so it might be wrong given what the asktom article says. I'll do a little more research. – James Aug 31 '14 at 20:13
-1

Often @ in databases are related to a database link. E.g. in Oracle I use create public database link mylink connect to remote_username identified by mypassword using 'tns_service_name';

if the after this user (remote_username) has a table ANOTHERTABLE you could use: SELECT "A2"."ANOTHERCOLUMN" FROM "ANOTHERTABLE"@mylink "A2" WHERE "A2".ROWID=1

How the ! sign is used here is unclear for me. Normally the ! sign is something you use to access the shell where your database client is running.

I don't know is this helped you. Good luck.

glunder
  • 1
  • 1