| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Login Triggers + Sys_Context
I tested with sys_context and I like it. It is useful, BUT (a big BUT) it is only instance specific at-least in 9202 (I doubt it has changed in 9204). So, if you are single instance (i.e. non-rac non-ops) you should be able to utilize sys_context ...
actually you should be able to create a package spec with a record declaration. Then populate this information in the logon trigger once. This means, the data in package spec remains valid for user session and is not available for anyone else to see.
If you run Oracle Forms/Reports then you might run into the problems as there is a much old long standing bug that won't let oracle forms to access a packaged variable directly ... you must use a function to return that value.
I have worked on both solutions, I let go sys_context because it won't work in RAC environment, but package spec works just nice. Just be care ful when you make changes to package spec to add more attributes, you want to first disable the logon trigger, make changes and then compile the trigger first. If the trigger remains invalid, no one will be able to log in.
Raj
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Maryann Atkinson
Sent: Monday, March 22, 2004 4:55 PM
To: oracle-l_at_freelists.org
Subject: Re: Login Triggers + Sys_Context
Remember this?
Instead of using packaged variables, has anyone tried anything close to this BUT with utilizing the Sys_Context area instead?
Thanks,
maa
At 02:16 PM 3/12/2004, you wrote:
>Maryann,
>Then what you want is possibly packaged variables. You must create a
>package holding these variables, plus a function to get their values
>from the DB which will be called only once, possibly from the
>initialisation section of the package. You can call a function from the
>package in the initialization trigger, but this isn't mandatory. In
>fact, the very first reference to something in the package will cause
>the initialisation section to fire.
>
> That said, I doubt that caching such information will do much to
>improve a single query of death which run for 6 hours. I'd look
>elsewhere. Those techniques are useful for avoiding 600,000 select
>sys_context('USERENV', 'whatever') from dual per hour.
>
>SF
>
>
>Maryann Atkinson wrote:
> >
> > The queries we have which are slow, have some where clauses,
> > among others that is, to which we embed user information,
> > like privs, level, status etc, ie most likely personal info for each user.
> >
> > What I was hoping is to fire a login trigger that would save this info
> > in memory somehow, and then the queries could take it from there
> > instead of issuing other queries to find it...
> >
> > But thanks for the materialized views examples,
> > we are going to have to think and perhaps compare
> > before we figure it out...
> >
> > Thanks,
> > maa
> >
> > At 12:59 PM 3/12/2004, you wrote:
> >
> > >Materialized views are much, much easier to deal with in this
> > >situation. In addition to the fact that they're available to everyone all
> > >the time, they get refreshed automatically, you don't have to teach users
> > >where to go to get the data, and you don't execute huge queries when you
> > >don't need the results.
> > >
> > >
> > >Justin Cave
> > >Distributed Database Consulting, Inc.
> > >http://www.ddbcinc.com/askDDBC
> > >
> > >-----Original Message-----
> > >From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> > >On Behalf Of Nick Gabrielatos
> > >Sent: Friday, March 12, 2004 10:39 AM
> > >To: oracle-l_at_freelists.org
> > >Subject: Re: Login Triggers
> > >
> > >Thanks, that helps quite a bit.
> > >Let me ask you this, though,
> > >you think we should use cashed tables and materialized views instead of
> > >login triggers?
> > >
> > >Wouldnt a login trigger have an advantage?
> > >
> > >thanks,
> > >maa
> > >
> > >At 11:43 AM 3/12/2004, you wrote:
> > > >Maryan, materialized views are pre-built queries whose results are
> > > >stored in a table and refreshed periodically. Here is slightly modified
> > > >example from the manuals:
> > > >
> > > >CREATE TABLE sales_sum_table
> > > > (month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2))
> > > > STORAGE ( BUFFER_POOL KEEP);
> > > >
> > > >CREATE MATERIALIZED VIEW sales_sum_table
> > > > ON PREBUILT TABLE
> > > > REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/(24*60)
> > > > ENABLE QUERY REWRITE
> > > > AS SELECT t.calendar_month_desc AS month,
> > > > c.cust_state_province AS state,
> > > > SUM(s.amount_sold) AS sales
> > > > FROM times t, customers c, sales s
> > > > WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
> > > > GROUP BY t.calendar_month_desc, c.cust_state_province
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 23 2004 - 05:53:07 CST
 |
 |