Oracle-L: Re: logon trigger

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 02 Oct 2002 07:43:26 -0800
Message-ID: <F001.004DE3F8.20021002074326@fatcity.com>

"Alter session ..." is not DML, so I think you need to use dynamic SQL:

create or replace trigger smload.logon after logon on database begin
SET TRANSACTION USE ROLLBACK SEGMENT RBBIG01;

EXECUTE IMMEDIATE "Alter session enable parallel query";
EXECUTE IMMEDIATE "Alter session set SORT_AREA_SIZE = 10485760";
EXECUTE IMMEDIATE "Alter session set OPTIMIZER_MODE = choose";

end;
/

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 02, 2002 11:23 AM

> Hi guys
>
> I am trying to create the following trigger.
>
> The user in question is logging in using siebel application and siebel
does
> not allow multiple SQL statements during login so we through this might
> solve the problem. My problem now is though, the set rollback works but
the
> alter session statements does not seem to want to work.
>
> The server needs to be in RBO since this is the only mode supported by
> siebel.
>
> Help appreciated.
>
> create or replace trigger smload.logon after logon on database
> begin
> SET TRANSACTION USE ROLLBACK SEGMENT RBBIG01;
> Alter session enable parallel query;
> Alter session set SORT_AREA_SIZE = 10485760;
> Alter session set OPTIMIZER_MODE = choose;
> end;
> /
>
> George
> ________________________________________________
> George Leonard
> Oracle Database Administrator
> Dimension Data (Pty) Ltd
> (Reg. No. 1987/006597/07)
> Tel: (+27 11) 575 0573
> Fax: (+27 11) 576 0573
> E-mail:george.leonard_at_za.didata.com
> Web: http://www.didata.co.za
>
> You Have The Obligation to Inform One Honestly of the risk, And As a
Person
> You Are Committed to Educate Yourself to the Total Risk In Any Activity!
> Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
Kill
> or Injure Themselves as They See Fit!
>
>
>
>

>
> This message contains information intended solely for the addressee,
> which is confidential or private in nature and subject to legal privilege.
> If you are not the intended recipient, you may not peruse, use,
> disseminate, distribute or copy this message or any file attached to this
> message. Any such unauthorised use is prohibited and may be unlawful. If
> you have received this message in error, please notify the sender
> immediately by e-mail, facsimile or telephone and thereafter delete the
> original message from your machine.
>
> Furthermore, the information contained in this message, and any
> attachments thereto, is for information purposes only and may contain the
> personal views and opinions of the author, which are not necessarily the
> views and opinions of Dimension Data (South Africa) (Proprietary) Limited
> or its subsidiaries and associated companies ("Dimension Data"). Dimension
> Data therefore does not accept liability for any claims, loss or damages
> of whatsoever nature, arising as a result of the reliance on such
> information by anyone.
>
> Whilst all reasonable steps are taken to ensure the accuracy and
> integrity of information transmitted electronically and to preserve the
> confidentiality thereof, Dimension Data accepts no liability or
> responsibility whatsoever if information or data is, for whatsoever
> reason, incorrect, corrupted or does not reach its intended destination.
>
>

>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: George Leonard (ZA)
> INET: George.Leonard_at_za.didata.com
>
> 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: ListGuru_at_fatcity.com (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.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Oct 02 2002 - 10:43:26 CDT