RE: logon trigger to start tracing
Oracle9i 9.2.0.3.0, O7_DICTIONARY_ACCESSIBILITY is FALSE. --- Jacques Kilchoer [EMAIL PROTECTED] wrote: What database version? What is your setting for O7_DICTIONARY_ACCESSIBILITY? I tried the following in an 8.1.7 database with O7_DICTIONARY_ACCESSIBILITY = FALSE create user x identified by ... ; grant create session, create trigger to x ; (logging on as SYSDBA) grant execute on sys.dbms_support to x ; CONNECT X ... create trigger schema_trace after logon on schema begin sys.dbms_support.start_trace (waits = false, binds = false) ; end ; / DISCONNECT logon as user X: trace file created logon as user Y: no trace file created logon as user Y, say alter sesssion set current_schema = X ; no trace file created No error messages in alert log -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED] In an attempt to catch all SQL issued by a report, I created a logon trigger in the report's logon schema. (As SYS, I granted the user EXECUTE on DBMS_SESSION before creating the trigger.) As the schema owner, I am able to execute DBMS_SESSION.SET_SQL_TRACE interactively. On logon, trace files are created, but they contain the following: *** SESSION ID:(27.4739) 2003-07-22 18:52:53.000 Skipped error 604 during the execution of RPT_PERF.TRACE_ALL *** 2003-07-22 18:52:53.000 ksedmp: internal or fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges ORA-06512: at SYS.DBMS_SESSION, line 126 ORA-06512: at line 2 When SQL statements are executed in the session, no further trace information is added to the file. Anyone know what's going on here? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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).
Re: logon trigger to start tracing
That's it! Thanks...sometimes I forget that a trigger is a stored object, too. Regards, --- Wolfgang Breitling [EMAIL PROTECTED] wrote: The logon user needs to have granted alter session privileges directly to her, not just through a role. = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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).
RE: logon trigger to start tracing
Title: RE: logon trigger to start tracing Paul, remember it is a trigger ... so it runs under executing user's privs ... 1. everyone needs a direct grant for execute on dbms_session 2. there needs to be a public synonym for dbms_session 3. yell Bingo! Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 23, 2003 10:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: logon trigger to start tracing Oracle9i 9.2.0.3.0, O7_DICTIONARY_ACCESSIBILITY is FALSE. *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: logon trigger to start tracing
--- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Paul, remember it is a trigger ... so it runs under executing user's privs ... 1. everyone needs a direct grant for execute on dbms_session Been there. 2. there needs to be a public synonym for dbms_session Done that. 3. yell Bingo! Did so...after receiving the correct answer! ;-) Thanks, those are all valid points. Raj __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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).
RE: logon trigger to start tracing
-Original Message- From: Wolfgang Breitling [mailto:[EMAIL PROTECTED] The logon user needs to have granted alter session privileges directly to her, not just through a role. I believe you that it's needed for DBMS_SESSION.SET_SQL_TRACE, but then why was I able to get a trace file by using DBMS_SUPPORT, creating the trigger as a user that did not have ALTER SESSION privilege? My example was: --- Jacques Kilchoer [EMAIL PROTECTED] wrote: What database version? What is your setting for O7_DICTIONARY_ACCESSIBILITY? I tried the following in an 8.1.7 database with O7_DICTIONARY_ACCESSIBILITY = FALSE create user x identified by ... ; grant create session, create trigger to x ; (logging on as SYSDBA) grant execute on sys.dbms_support to x ; CONNECT X ... create trigger schema_trace after logon on schema begin sys.dbms_support.start_trace (waits = false, binds = false) ; end ; / DISCONNECT logon as user X: trace file created logon as user Y: no trace file created logon as user Y, say alter sesssion set current_schema = X ; no trace file created No error messages in alert log -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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).
RE: logon trigger to start tracing
Never mind. I see that DBMS_SESSION has AUTHID CURRENT_USER (and of course DBMS_SUPPORT does not.) -Original Message- From: Jacques Kilchoer -Original Message- From: Wolfgang Breitling [mailto:[EMAIL PROTECTED] The logon user needs to have granted alter session privileges directly to her, not just through a role. I believe you that it's needed for DBMS_SESSION.SET_SQL_TRACE, but then why was I able to get a trace file by using DBMS_SUPPORT, creating the trigger as a user that did not have ALTER SESSION privilege? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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).
RE: logon trigger to start tracing
What database version? What is your setting for O7_DICTIONARY_ACCESSIBILITY? I tried the following in an 8.1.7 database with O7_DICTIONARY_ACCESSIBILITY = FALSE create user x identified by ... ; grant create session, create trigger to x ; (logging on as SYSDBA) grant execute on sys.dbms_support to x ; CONNECT X ... create trigger schema_trace after logon on schema begin sys.dbms_support.start_trace (waits = false, binds = false) ; end ; / DISCONNECT logon as user X: trace file created logon as user Y: no trace file created logon as user Y, say alter sesssion set current_schema = X ; no trace file created No error messages in alert log -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED] In an attempt to catch all SQL issued by a report, I created a logon trigger in the report's logon schema. (As SYS, I granted the user EXECUTE on DBMS_SESSION before creating the trigger.) As the schema owner, I am able to execute DBMS_SESSION.SET_SQL_TRACE interactively. On logon, trace files are created, but they contain the following: *** SESSION ID:(27.4739) 2003-07-22 18:52:53.000 Skipped error 604 during the execution of RPT_PERF.TRACE_ALL *** 2003-07-22 18:52:53.000 ksedmp: internal or fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges ORA-06512: at SYS.DBMS_SESSION, line 126 ORA-06512: at line 2 When SQL statements are executed in the session, no further trace information is added to the file. Anyone know what's going on here? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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).
Re: logon trigger to start tracing
The logon user needs to have granted alter session privileges directly to her, not just through a role. At 03:54 PM 7/22/2003 -0800, you wrote: In an attempt to catch all SQL issued by a report, I created a logon trigger in the report's logon schema. (As SYS, I granted the user EXECUTE on DBMS_SESSION before creating the trigger.) As the schema owner, I am able to execute DBMS_SESSION.SET_SQL_TRACE interactively. On logon, trace files are created, but they contain the following: *** SESSION ID:(27.4739) 2003-07-22 18:52:53.000 Skipped error 604 during the execution of RPT_PERF.TRACE_ALL *** 2003-07-22 18:52:53.000 ksedmp: internal or fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges ORA-06512: at SYS.DBMS_SESSION, line 126 ORA-06512: at line 2 When SQL statements are executed in the session, no further trace information is added to the file. Anyone know what's going on here? TIA = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
RE: logon trigger
Hi Mike Siebel has released a note whereby they approve CBO for the EIM process. Also what I have is a SM Data warehouse logon into the OLTP Siebel db. The activities this logon does is more akin to OLAP. So what I am doing is giving this logon a big sort area size, enabling parallel access for the user, setting it's session to CBO etc etc. 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:[EMAIL PROTECTED] 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! -Original Message- Sent: 02 October 2002 18:24 PM To: Multiple recipients of list ORACLE-L In looking at this and thinking about it...doesn't any DDL statement do an implied COMMIT? If so, the first EXECUTE IMMEDIATE will fire, commit, the SET TRANSACTION will be released, and the user will not be assured of using that rollback segment. Shouldn't the SET TRANSACTION be the last statement in the trigger? And if Siebel wants RBO, doesn't changing the optimizer at the session level mean that all that session's queries will be performed using CBO? Is Siebel OK with that? Cheers, Mike -Original Message- Sent: Wednesday, October 02, 2002 8:43 AM To: Multiple recipients of list ORACLE-L 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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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). *** 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
RE: logon trigger
Thx, I also found this last night. I will wait for the developers so arrive so that they can test their process. 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:[EMAIL PROTECTED] 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! -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: 02 October 2002 19:09 PM To: Multiple recipients of list ORACLE-L Subject: Re: logon trigger Altering current_schema, you will not see anything in user_tables, but you should be able to reference tables in thisnew schema without using siebel prefix. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - From: George Leonard (ZA) To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 02, 2002 12:18 PM Subject: RE: logon trigger Hi all Ok the trigger has been changed to this, It compiles. The problem now is that all the objects that need to be access is owner by the siebel user. I do not want to create synonyms. The tool being used can not append the siebel schema name in front of the objects, and it is expecting to log in as siebel. Any idea why the 'Alter session set CURRENT_SCHEMA = SIEBEL;' is not changing my current schema. I have tried executing Alter session set CURRENT_SCHEMA = SIEBEL; in a standard sqlplus window and then looking at the user_tables table and it is empty ? create or replace trigger olap1_logon_trigger after logon on smload.schema begin execute immediate '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;'; execute immediate 'Alter session set CURRENT_SCHEMA = SIEBEL;'; 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:[EMAIL PROTECTED] 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! -Original Message- From: JOE TESTA [mailto:[EMAIL PROTECTED]] Sent: 02 October 2002 16:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: re: logon trigger George here is how we did ours create or replace trigger olap1_logon_trigger after logon on olap1.schema begin execute immediate 'alter session set optimizer_mode = first_rows'; end; / Customize it to your liking. Joe *** 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. * *** This message contains information intended solely for the addressee, which is confidential or private
RE: logon trigger
Hi all Ok if I execute these commands/SQL in sqlplus it works. When I add to the trigger for after logon it does not work. I get the feeling it is only valid for the current block, begin - End. Any ideas to work around this. grant alter session to smload; drop trigger olap_logon_trigger; create or replace trigger olapl_logon_trigger after logon on smload.schema begin execute immediate 'Alter session enable parallel query;'; execute immediate 'Alter session set SORT_AREA_SIZE = 10485760;'; execute immediate 'Alter session set OPTIMIZER_MODE = choose;'; execute immediate 'Alter session set CURRENT_SCHEMA = SIEBEL;'; execute immediate 'SET TRANSACTION USE ROLLBACK SEGMENT RBBIG01;'; 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:[EMAIL PROTECTED] 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! -Original Message- From: George Leonard (ZA) [mailto:[EMAIL PROTECTED]] Sent: 03 October 2002 09:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: logon trigger Thx, I also found this last night. I will wait for the developers so arrive so that they can test their process. 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:[EMAIL PROTECTED] 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! -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: 02 October 2002 19:09 PM To: Multiple recipients of list ORACLE-L Subject: Re: logon trigger Altering current_schema, you will not see anything in user_tables, but you should be able to reference tables in thisnew schema without using siebel prefix. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - From: George Leonard (ZA) To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 02, 2002 12:18 PM Subject: RE: logon trigger Hi all Ok the trigger has been changed to this, It compiles. The problem now is that all the objects that need to be access is owner by the siebel user. I do not want to create synonyms. The tool being used can not append the siebel schema name in front of the objects, and it is expecting to log in as siebel. Any idea why the 'Alter session set CURRENT_SCHEMA = SIEBEL;' is not changing my current schema. I have tried executing Alter session set CURRENT_SCHEMA = SIEBEL; in a standard sqlplus window and then looking at the user_tables table and it is empty ? create or replace trigger olap1_logon_trigger after logon on smload.schema begin execute immediate '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;'; execute immediate 'Alter session set CURRENT_SCHEMA = SIEBEL;'; 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:[EMAIL PROTECTED] 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! -Original Message- From: JOE TESTA [mailto:[EMAIL PROTECTED]] Sent: 02 October 2002 16:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: re: logon trigger George here is how we did ours create or replace trigger olap1_logon_trigger after logon on olap1.schema begin execute immediate 'alter session set optimizer_mode = first_rows'; end; / Customize it to your liking. Joe *** 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
RE: logon trigger
FWIW, do not place the terminating semicolon in the execute string, ie, begin execute immediate 'Alter session set OPTIMIZER_MODE = choose' ; end ; / Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 10/03/02 02:43 AMcc: Please respond toSubject: RE: logon trigger ORACLE-L Hi all Ok if I execute these commands/SQL in sqlplus it works. When I add to the trigger for after logon it does not work. I get the feeling it is only valid for the current block, begin - End. Any ideas to work around this. grant alter session to smload; drop trigger olap_logon_trigger; create or replace trigger olapl_logon_trigger after logon on smload.schema begin execute immediate 'Alter session enable parallel query;'; execute immediate 'Alter session set SORT_AREA_SIZE = 10485760;'; execute immediate 'Alter session set OPTIMIZER_MODE = choose;'; execute immediate 'Alter session set CURRENT_SCHEMA = SIEBEL;'; execute immediate 'SET TRANSACTION USE ROLLBACK SEGMENT RBBIG01;'; 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:[EMAIL PROTECTED] 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! -Original Message- From: George Leonard (ZA) [mailto:[EMAIL PROTECTED]] Sent: 03 October 2002 09:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: logon trigger Thx, I also found this last night. I will wait for the developers so arrive so that they can test their process. 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:[EMAIL PROTECTED] 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! -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: 02 October 2002 19:09 PM To: Multiple recipients of list ORACLE-L Subject: Re: logon trigger Altering current_schema, you will not see anything in user_tables, but you should be able to reference tables in this new schema without using siebel prefix. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - From: George Leonard (ZA) To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 02, 2002 12:18 PM Subject: RE: logon trigger Hi all Ok the trigger has been changed to this, It compiles. The problem now is that all the objects that need to be access is owner by the siebel user. I do not want to create synonyms. The tool being used can not append the siebel schema name in front of the objects, and it is expecting to log in as siebel. Any idea why the 'Alter session set CURRENT_SCHEMA = SIEBEL;' is not changing my current schema. I have tried executing Alter session set CURRENT_SCHEMA = SIEBEL; in a standard sqlplus window and then looking at the user_tables table
RE: logon trigger
Do you happen to have that note number? Tony Aponte -Original Message- Sent: Thursday, October 03, 2002 2:58 AM To: Multiple recipients of list ORACLE-L Hi Mike Siebel has released a note whereby they approve CBO for the EIM process. Also what I have is a SM Data warehouse logon into the OLTP Siebel db. The activities this logon does is more akin to OLAP. So what I am doing is giving this logon a big sort area size, enabling parallel access for the user, setting it's session to CBO etc etc. 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:[EMAIL PROTECTED] 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! -Original Message- Sent: 02 October 2002 18:24 PM To: Multiple recipients of list ORACLE-L In looking at this and thinking about it...doesn't any DDL statement do an implied COMMIT? If so, the first EXECUTE IMMEDIATE will fire, commit, the SET TRANSACTION will be released, and the user will not be assured of using that rollback segment. Shouldn't the SET TRANSACTION be the last statement in the trigger? And if Siebel wants RBO, doesn't changing the optimizer at the session level mean that all that session's queries will be performed using CBO? Is Siebel OK with that? Cheers, Mike -Original Message- Sent: Wednesday, October 02, 2002 8:43 AM To: Multiple recipients of list ORACLE-L 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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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). *** 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
RE: logon trigger
Hi there Your' the man, the people here laughed when we saw this working and that was the problem. 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:[EMAIL PROTECTED] 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! -Original Message- Sent: 03 October 2002 19:24 PM To: Multiple recipients of list ORACLE-L FWIW, do not place the terminating semicolon in the execute string, ie, begin execute immediate 'Alter session set OPTIMIZER_MODE = choose' ; end ; / Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 10/03/02 02:43 AMcc: Please respond toSubject: RE: logon trigger ORACLE-L Hi all Ok if I execute these commands/SQL in sqlplus it works. When I add to the trigger for after logon it does not work. I get the feeling it is only valid for the current block, begin - End. Any ideas to work around this. grant alter session to smload; drop trigger olap_logon_trigger; create or replace trigger olapl_logon_trigger after logon on smload.schema begin execute immediate 'Alter session enable parallel query;'; execute immediate 'Alter session set SORT_AREA_SIZE = 10485760;'; execute immediate 'Alter session set OPTIMIZER_MODE = choose;'; execute immediate 'Alter session set CURRENT_SCHEMA = SIEBEL;'; execute immediate 'SET TRANSACTION USE ROLLBACK SEGMENT RBBIG01;'; 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:[EMAIL PROTECTED] 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! -Original Message- From: George Leonard (ZA) [mailto:[EMAIL PROTECTED]] Sent: 03 October 2002 09:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: logon trigger Thx, I also found this last night. I will wait for the developers so arrive so that they can test their process. 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:[EMAIL PROTECTED] 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! -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: 02 October 2002 19:09 PM To: Multiple recipients of list ORACLE-L Subject: Re: logon trigger Altering current_schema, you will not see anything in user_tables, but you should be able to reference tables in this new schema without using siebel prefix. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - From: George Leonard (ZA) To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 02, 2002 12:18 PM Subject: RE: logon trigger Hi all Ok the trigger has been changed to this, It compiles. The problem now is that all the objects that need to be access is owner by the siebel user. I do not want to create synonyms. The tool being used can not append the siebel schema name in front of the objects, and it is expecting to log in as siebel. Any idea why the 'Alter session set CURRENT_SCHEMA = SIEBEL;' is not changing my current schema. I have tried executing Alter session set CURRENT_SCHEMA = SIEBEL; in a standard sqlplus window
Re: logon trigger
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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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: [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.com -- Author: Igor Neyman 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).
re: logon trigger
George here is how we did ours" create or replace trigger olap1_logon_trigger after logon on olap1.schemabegin execute immediate 'alter session set optimizer_mode = first_rows';end;/ Customize it to your liking. Joe
Re: logon trigger
Hi, Use dynamic SQL (execute imediate). Also, consider placing your code into a stored proc called by the trigger. --- George Leonard (ZA) [EMAIL PROTECTED] a écrit : 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:[EMAIL PROTECTED] 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: [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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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).
RE: logon trigger
Hi all
Ok the trigger has been changed to this, It compiles.
The problem now is that all the objects
that need to be access is owner by the siebel user. I do not want to create
synonyms. The tool being used can not append the siebel schema name in front of
the objects, and it is expecting to log in as siebel.
Any idea why the 'Alter session set
CURRENT_SCHEMA = SIEBEL;' is not changing my current schema. I have tried
executing Alter session set CURRENT_SCHEMA = SIEBEL; in a standard sqlplus window and then looking at the user_tables
table and it is empty ?
create or replace trigger
olap1_logon_trigger
after logon on smload.schema
begin
execute immediate
'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;';
execute
immediate 'Alter session set CURRENT_SCHEMA = SIEBEL;';
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:[EMAIL PROTECTED]
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!
-Original Message-
From: JOE TESTA
[mailto:[EMAIL PROTECTED]]
Sent: 02 October 2002 16:52 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: re: logon trigger
George here is how we did
ours
create or replace trigger
olap1_logon_trigger
after logon on olap1.schema
begin
execute immediate 'alter session set optimizer_mode = first_rows';
end;
/
Customize it to your
liking.
Joe
***
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.
*
Re: logon trigger
Altering "current_schema", you will not see anything in
"user_tables", but you should be able to reference tables in thisnew
schema without using "siebel" prefix.
Igor Neyman, OCP DBA[EMAIL PROTECTED]
- Original Message -
From:
George Leonard (ZA)
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, October 02, 2002 12:18
PM
Subject: RE: logon trigger
Hi
all
Ok the trigger has
been changed to this, It
compiles.
The problem now is
that all the objects that need to be access is owner by the siebel user. I do
not want to create synonyms. The tool being used can not append the siebel
schema name in front of the objects, and it is expecting to log in as
siebel.
Any idea why the
'Alter session set CURRENT_SCHEMA = SIEBEL;' is not changing my current
schema. I have tried executing Alter session set CURRENT_SCHEMA = SIEBEL; in a
standard sqlplus window and then looking at the
user_tables table and it is empty
?
create or replace trigger
olap1_logon_trigger
after logon
on smload.schema
begin
execute
immediate '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;';
execute immediate 'Alter session set
CURRENT_SCHEMA = SIEBEL;';
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:[EMAIL PROTECTED]
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!
-Original
Message-From: JOE TESTA
[mailto:[EMAIL PROTECTED]] Sent: 02 October 2002 16:52 PMTo: [EMAIL PROTECTED]Cc:
[EMAIL PROTECTED]Subject: re: logon
trigger
George here is how we did
ours"
create or replace trigger
olap1_logon_trigger after logon on olap1.schemabegin
execute immediate 'alter session set optimizer_mode =
first_rows';end;/
Customize it to your
liking.
Joe
***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 thismessage. Any
such unauthorised use is prohibited and may be unlawful. Ifyou have
received this message in error, please notify the senderimmediately by
e-mail, facsimile or telephone and thereafter delete theoriginal message
from your machine. Furthermore, the information contained in this
message, and anyattachments thereto, is for information purposes only and
may contain thepersonal views and opinions of the author, which are not
necessarily theviews and opinions of Dimension Data (South Africa)
(Proprietary) Limitedor its subsidiaries and associated companies
("Dimension Data"). DimensionData therefore does not accept liability for
any claims, loss or damagesof whatsoever nature, arising as a result of
the reliance on suchinformation by anyone. Whilst all reasonable
steps are taken to ensure the accuracy andintegrity of information
transmitted electronically and to preserve theconfidentiality thereof,
Dimension Data accepts no liability orresponsibility whatsoever if
information or data is, for whatsoeverreason, incorrect, corrupted or does
not reach its intended destination.
*
RE: logon trigger
In looking at this and thinking about it...doesn't any DDL statement do an implied COMMIT? If so, the first EXECUTE IMMEDIATE will fire, commit, the SET TRANSACTION will be released, and the user will not be assured of using that rollback segment. Shouldn't the SET TRANSACTION be the last statement in the trigger? And if Siebel wants RBO, doesn't changing the optimizer at the session level mean that all that session's queries will be performed using CBO? Is Siebel OK with that? Cheers, Mike -Original Message- Sent: Wednesday, October 02, 2002 8:43 AM To: Multiple recipients of list ORACLE-L 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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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).
RE: LOGON TRIGGER
Title: RE: LOGON TRIGGER
Here what we've done to alter the optimizer goal at logon time.
CREATE OR REPLACE TRIGGER change_optimizer_on_startup
AFTER LOGON ON DATABASE
DECLARE
p_osuser_txt V$SESSION_CONNECT_INFO.OSUSER%TYPE;
BEGIN
BEGIN
SELECT OSUSER
INTO p_osuser_txt
FROM V$SESSION
WHERE AUDSID = (SELECT USERENV('SESSIONID') FROM DUAL);
EXCEPTION
WHEN OTHERS THEN
p_osuser_txt:=null;
END;
IF p_osuser_txt IN ('psoftfs') THEN
BEGIN
EXECUTE IMMEDIATE 'alter session set optimizer_mode=rule';
EXCEPTION
WHEN OTHERS THEN
SYS.DBMS_SYSTEM.KSDWRT(2,TO_CHAR(SYSDATE,'dd-mon-rr hh:mm:ss')||SQLERRM);
END;
END IF;
END;
-Original Message-
From: Buecherl Dieter (BUE) [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 31, 2001 12:27 PM
To: Multiple recipients of list ORACLE-L
Subject: LOGON TRIGGER
Hi, all
we are using Oracle 8.1.7 and WebSphere on Solaris.
We use a connection pool that allows 20 sessions
from the app server to the database.
In order to prevent anyone to connect to the datebase other
than the app server, we would like to implement a security
feature based on a LOGON TRIGGER that checks (client IP,
application, etc.) and counts the active sessions.
After 20 sessions have logged in, we want to issue an
'ALTER SYSTEM ENABLE RESTRICTED SESSION'
to lock out any additional connection requests .
Any comments on this?
TIA, Dieter
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Buecherl Dieter (BUE)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
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).
