Re: Odd sysdate construction

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Wed, 12 Jan 2005 13:44:16 -0800
Message-ID: <41E59A30.6000004@oracle.com>

Vladimir Begun wrote:

> Freeman, Donald wrote:
> 
>>select SYSDATE@! from dual;
>>This appeared in a client query (actually it was trunc(sysdate@!) )
>>What is the @!?   Its valid in the Oracle takes it but I can't find any =
>>documentation as to what it is. It seems to act the same as plain old =
>>sysdate. But it's the only place where @! Is allowed.
> 
> Please review:
> http://www.orafaq.net/maillist/oracle-l/2003/10/17/1450.htm

I got a message from one of the list's participants, and I believe it's necessary to provide a clarification:

• DROP USER t1 CASCADE
• DROP USER t2 CASCADE

CREATE USER t1 IDENTIFIED BY t1;
CREATE USER t2 IDENTIFIED BY t2;
GRANT CREATE SESSION, CREATE DATABASE LINK TO t1; GRANT CREATE SESSION TO t2;

CREATE OR REPLACE TRIGGER sys.trg$t1

   AFTER LOGON
   ON T1.SCHEMA
BEGIN
   EXECUTE IMMEDIATE 'alter session set events = ''10046 trace name context forever, level 12'' tracefile_identifier=''t1'''; END;
/
CREATE OR REPLACE TRIGGER sys.trg$t2

   AFTER LOGON
   ON T2.SCHEMA
BEGIN
   EXECUTE IMMEDIATE 'alter session set events = ''10046 trace name context forever, level 12'' tracefile_identifier=''t2'''; END;
/
CONNECT t1/t1
CREATE DATABASE LINK t1_t2 CONNECT TO t2 IDENTIFIED BY t2 USING 'fake';
-- fake is a fake connect string

SELECT sysdate FROM dual_at_t1_t2;
SELECT user FROM dual_at_t1_t2;

• after this point you'd get the trace files:
• 1) original trace file with produced by session t1
• SELECT sysdate FROM dual_at_t1_t2;
• 2) the trace file produced by session t2 that does
• callback from the remote server to local one, something
• similar to:
• PARSING IN CURSOR #2 len=33 dep=0 uid=85 oct=3 lid=85 tim=1903060877517 hv=2617363369 ad='10a2ce18'
• SELECT SYSDATE@! FROM "DUAL" "A1"
• END OF STMT

--

Vladimir Begun
The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 12 2005 - 15:45:57 CST