I'll try tracing the session.
Global.client_dim is just a table with client info and a column that
corresponds to client logins to enable row level security.
Thanks for the suggestions.
As stated earlier..i'll post the resolution.
chris
-----Original Message-----
Sent: Tuesday, November 11, 2003 4:39 PM
To: Multiple recipients of list ORACLE-L
You can flush shared pool, optionally, then enable SQL tracing and CBO
tracing and check the trace file, anyway you will be asked to do that
when you open a tar. What Oracle version do you use?
What's the object you're referencing in your query -- global.client_dim?
Is it a [partitioned] table, [m]view or synonym for some other object?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Chris Stephens wrote:
> SQL> select sys_context('userenv','session_user'),
> 2 dump(sys_context('userenv','session_user')), a.reports_login,
> 3 dump(a.reports_login)
> 4 from global.client_dim a
> 5 WHERE sys_context('userenv','session_user') = trim(a.REPORTS_LOGIN);
>
> SYS_CONTEXT('USERENV','SESSION_USER')
>
----------------------------------------------------------------------------
> ------------------------
> DUMP(SYS_CONTEXT('USERENV','SESSION_USER'))
>
----------------------------------------------------------------------------
> ------------------------
> REPORTS_LOGIN
> ------------------------------
> DUMP(A.REPORTS_LOGIN)
>
----------------------------------------------------------------------------
> ------------------------
> REPORTS_DELTA
> Typ=1 Len=13: 82,69,80,79,82,84,83,95,68,69,76,84,65
> REPORTS_DELTA
> Typ=1 Len=13: 82,69,80,79,82,84,83,95,68,69,76,84,65
>
>
> And
>
> 1 SELECT count(*)
> 2 FROM global.client_dim a
> 3* WHERE sys_context('userenv','session_user') =
> trim(a.REPORTS_LOGIN)
> SQL> /
>
> COUNT(*)
> ----------
> 0
>
>
> I'm going to open a tar on this.
> I will email the resolution. ...and check for any more suggestions! :)
>
> Chris
>
> -----Original Message-----
> Sent: Tuesday, November 11, 2003 2:24 PM
> To: Multiple recipients of list ORACLE-L
>
> Chris
>
> There is a contradiction below:
>
> Chris Stephens wrote:
>
>
>>SQL> select sys_context('userenv','session_user'),
>> 2 dump(sys_context('userenv','session_user')), a.reports_login,
>> 3 dump(a.reports_login)
>> 4 from global.client_dim a
>> 5 WHERE sys_context('userenv','session_user') = a.REPORTS_LOGIN;
>
>
> Produces some output.
>
> Your original query does not return anything.
>
> 1 SELECT count(*)
> 2 FROM global.client_dim a
> 3* WHERE sys_context('userenv','session_user') =
> trim(a.REPORTS_LOGIN)
>
> The obvious differences here are:
>
> . TRIM function
> . probably when you've tried to launch the original query you had pofile
> functions enabled, when you tried it second time it was disabled.
> . query rewrite is used (could be, right?)
>
> Could you please check the second and third items?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chris Stephens
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
