After Logon Trigger and Import
Hi, I put the 'alter session set skip_unusable_indexes=true' in the logon trigger of a particular user and tested it in the sql*plus session. It is working fine there. I tested it by making an index unusable and inserting the data into the table. But, when I tried to import (using the same user) the data into that table, It gives an error saying that 'Index is in unusable state'. Does logon trigger fire for the Import? Is there any way to verify that the skip_unusable_indexes is set to 'true' for a particular session. Thanks in advance for your help. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: After Logon Trigger and Import
Sorry, I forgot to mention the OS and Oracle Version. It is Hp-UX v11 and Oracle 8.1.7.4 Thanks. Best Regards, Prasad 860 843 8377 Prasada R Gunda To: [EMAIL PROTECTED] 08/05/2003 12:21 cc: PM Subject: After Logon Trigger and Import(Document link: Prasada R Gunda) Hi, I put the 'alter session set skip_unusable_indexes=true' in the logon trigger of a particular user and tested it in the sql*plus session. It is working fine there. I tested it by making an index unusable and inserting the data into the table. But, when I tried to import (using the same user) the data into that table, It gives an error saying that 'Index is in unusable state'. Does logon trigger fire for the Import? Is there any way to verify that the skip_unusable_indexes is set to 'true' for a particular session. Thanks in advance for your help. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: After Logon Trigger and Import
Thanks very much Arup for your suggestion. Actually, Tables and indexes are already exist in the schema. I just have to copy the data from one environment to other environment. So I thought I will make the indexes unusable, load the data and rebuild the indexes with nologging, parallel and compute statistics. Best Regards, Prasad 860 843 8377 "Arup Nanda" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> om> cc: Sent by: Subject: Re: After Logon Trigger and Import [EMAIL PROTECTED] .com 08/05/2003 08:29 PM Please respond to ORACLE-L Prasad, You can use SKIP_UNUSABLE_INDEXES=Y as an import parameter. But then again, why do that? Why not just impirt with INDEXES=N and then rebuild the indexes in parallel and with NOLOGGING? HTH. Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, August 05, 2003 1:19 PM > > Sorry, I forgot to mention the OS and Oracle Version. It is Hp-UX v11 and > Oracle 8.1.7.4 > > Thanks. > > Best Regards, > Prasad > 860 843 8377 > > > > Prasada R Gunda >To: [EMAIL PROTECTED] > 08/05/2003 12:21 cc: > PM Subject: After Logon Trigger and Import(Document link: Prasada R Gunda) > > > > > > Hi, > > I put the 'alter session set skip_unusable_indexes=true' in the logon > trigger of a particular user and tested it in the sql*plus session. It is > working fine there. I tested it by making an index unusable and inserting > the data into the table. > > But, when I tried to import (using the same user) the data into that table, > It gives an error saying that 'Index is in unusable state'. > > Does logon trigger fire for the Import? Is there any way to verify that > the skip_unusable_indexes is set to 'true' for a particular session. > > Thanks in advance for your help. > > Best Regards, > Prasad > 860 843 8377 > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and we
Re: After Logon Trigger and Import
Prasad, You can use SKIP_UNUSABLE_INDEXES=Y as an import parameter. But then again, why do that? Why not just impirt with INDEXES=N and then rebuild the indexes in parallel and with NOLOGGING? HTH. Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, August 05, 2003 1:19 PM > > Sorry, I forgot to mention the OS and Oracle Version. It is Hp-UX v11 and > Oracle 8.1.7.4 > > Thanks. > > Best Regards, > Prasad > 860 843 8377 > > > > Prasada R Gunda >To: [EMAIL PROTECTED] > 08/05/2003 12:21 cc: > PM Subject: After Logon Trigger and Import(Document link: Prasada R Gunda) > > > > > > Hi, > > I put the 'alter session set skip_unusable_indexes=true' in the logon > trigger of a particular user and tested it in the sql*plus session. It is > working fine there. I tested it by making an index unusable and inserting > the data into the table. > > But, when I tried to import (using the same user) the data into that table, > It gives an error saying that 'Index is in unusable state'. > > Does logon trigger fire for the Import? Is there any way to verify that > the skip_unusable_indexes is set to 'true' for a particular session. > > Thanks in advance for your help. > > Best Regards, > Prasad > 860 843 8377 > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > 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: Arup Nanda 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
> -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
--- "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
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
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
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
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).
logon trigger to start tracing
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).
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: AFTER database logon trigger keeps sessions open
Title: RE: AFTER database logon trigger keeps sessions open
Try putting a
dbms_session.close_database_link('utilities_itport02_dblink');
after the insert. also aren't we missing a commit?? I'd also make this a autonomous transaction ...
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L
Subject: AFTER database logon trigger keeps sessions open
Hi All,
I have a after database logon trigger on server/instance 1. All it does is
insert a record into instance on server2 via a database link.
This creates a session on server2. So every user who logs on to server1 a
session is created on server2. The session does not
go away. Does anyone know why it does not after the user logs on?
Below is after logon trigger
DECLARE
-- str varchar2(200);
CURSOR temp_rec IS
SELECT user AS user_name,
NVL(UPPER(SUBSTR(osuser,1,30)),'SERVER') AS os_user_name,
RTRIM(NVL(SUBSTR(machine,instr(machine,'\') +1,12),'SERVER'),CHR(0))
AS machine_name,
sid AS session_id,
serial# AS serial_no,
SYSDATE AS logon_time,
SYS_CONTEXT('userenv','ip_address') AS ip_address,
NVL(SUBSTR(program,1,64),'INTERNAL') AS program_name,
i.instance_name AS instance_name,
i.host_name AS host_name,
i.version AS version
FROM v$session s,v$instance i
WHERE s.username = user
AND s.logon_time = (SELECT MAX(x.logon_time)
FROM v$session x
WHERE x.username = user);
BEGIN
FOR rec IN temp_rec LOOP
INSERT INTO [EMAIL PROTECTED]
(user_name,
os_user_name,
machine_name,
session_id,
serial_no,
logon_time,
ip_address,
program_name,
instance_name,
host_name,
version)
VALUES (rec.user_name,
rec.os_user_name,
rec.machine_name,
rec.session_id,
rec.serial_no,
rec.logon_time,
rec.ip_address,
rec.program_name,
rec.instance_name,
rec.host_name,
rec.version);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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 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.*2
RE: AFTER database logon trigger keeps sessions open
You can use: execute immediate 'alter session close database link
utilities_itport02_dblink';
After you're done with the insertion
Regards,
Waleed
-Original Message-
Sent: Tuesday, March 11, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L
Hi All,
I have a after database logon trigger on server/instance 1. All it does is
insert a record into instance on server2 via a database link.
This creates a session on server2. So every user who logs on to server1 a
session is created on server2. The session does not
go away. Does anyone know why it does not after the user logs on?
Below is after logon trigger
DECLARE
-- str varchar2(200);
CURSOR temp_rec IS
SELECT user AS user_name,
NVL(UPPER(SUBSTR(osuser,1,30)),'SERVER') AS os_user_name,
RTRIM(NVL(SUBSTR(machine,instr(machine,'\') +1,12),'SERVER'),CHR(0))
AS machine_name,
sid AS session_id,
serial# AS serial_no,
SYSDATE AS logon_time,
SYS_CONTEXT('userenv','ip_address') AS ip_address,
NVL(SUBSTR(program,1,64),'INTERNAL') AS program_name,
i.instance_name AS instance_name,
i.host_name AS host_name,
i.version AS version
FROM v$session s,v$instance i
WHERE s.username = user
AND s.logon_time = (SELECT MAX(x.logon_time)
FROM v$session x
WHERE x.username = user);
BEGIN
FOR rec IN temp_rec LOOP
INSERT INTO [EMAIL PROTECTED]
(user_name,
os_user_name,
machine_name,
session_id,
serial_no,
logon_time,
ip_address,
program_name,
instance_name,
host_name,
version)
VALUES (rec.user_name,
rec.os_user_name,
rec.machine_name,
rec.session_id,
rec.serial_no,
rec.logon_time,
rec.ip_address,
rec.program_name,
rec.instance_name,
rec.host_name,
rec.version);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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: Khedr, Waleed
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).
AFTER database logon trigger keeps sessions open
Hi All,
I have a after database logon trigger on server/instance 1. All it does is
insert a record into instance on server2 via a database link.
This creates a session on server2. So every user who logs on to server1 a
session is created on server2. The session does not
go away. Does anyone know why it does not after the user logs on?
Below is after logon trigger
DECLARE
-- str varchar2(200);
CURSOR temp_rec IS
SELECT user AS user_name,
NVL(UPPER(SUBSTR(osuser,1,30)),'SERVER') AS os_user_name,
RTRIM(NVL(SUBSTR(machine,instr(machine,'\') +1,12),'SERVER'),CHR(0))
AS machine_name,
sid AS session_id,
serial# AS serial_no,
SYSDATE AS logon_time,
SYS_CONTEXT('userenv','ip_address') AS ip_address,
NVL(SUBSTR(program,1,64),'INTERNAL') AS program_name,
i.instance_name AS instance_name,
i.host_name AS host_name,
i.version AS version
FROM v$session s,v$instance i
WHERE s.username = user
AND s.logon_time = (SELECT MAX(x.logon_time)
FROM v$session x
WHERE x.username = user);
BEGIN
FOR rec IN temp_rec LOOP
INSERT INTO [EMAIL PROTECTED]
(user_name,
os_user_name,
machine_name,
session_id,
serial_no,
logon_time,
ip_address,
program_name,
instance_name,
host_name,
version)
VALUES (rec.user_name,
rec.os_user_name,
rec.machine_name,
rec.session_id,
rec.serial_no,
rec.logon_time,
rec.ip_address,
rec.program_name,
rec.instance_name,
rec.host_name,
rec.version);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
AW: Change NLS variables in logon trigger...
Title: Nachricht
Hi
Helmut,
did you grant
"ALTER SESSION" to your database users?
regards
Volker Schoen
INPLAN RUHR
Informationstechnik
GmbH Tel.:
+49 208 / 65 91 - 950 Fax: +49 208 / 65 91 - 980 E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de
-Ursprüngliche Nachricht-Von: Daiminger, Helmut
[mailto:[EMAIL PROTECTED]] Gesendet: Donnerstag, 30.
Januar 2003 08:39An: Multiple recipients of list
ORACLE-LBetreff: Change NLS variables in logon
trigger...
Hi there!
I' having a logon trigger on 9.2.0.2 which sets
DBMS_SESSION.set_nls('nls_language','''GERMAN''');
DBMS_SESSION.set_nls('nls_territory','''GERMANY''');
But I'm getting the following error message:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot SET NLS in a trigger ORA-06512: at "SYS.DBMS_SESSION", line 141 ORA-06512: at line 2
This trigger works perfectly fine on 7.3.4 and 8.x.x
Error ORA-4092 just says that I can't do a commit or rollback
in a trigger...
This 9.2.0.2 on HP-UX.
Any ideas?
Thanks, Helmut
Change NLS variables in logon trigger...
Title: Change NLS variables in logon trigger...
Hi there!
I' having a logon trigger on 9.2.0.2 which sets
DBMS_SESSION.set_nls('nls_language','''GERMAN''');
DBMS_SESSION.set_nls('nls_territory','''GERMANY''');
But I'm getting the following error message:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot SET NLS in a trigger
ORA-06512: at "SYS.DBMS_SESSION", line 141
ORA-06512: at line 2
This trigger works perfectly fine on 7.3.4 and 8.x.x
Error ORA-4092 just says that I can't do a commit or rollback in a trigger...
This 9.2.0.2 on HP-UX.
Any ideas?
Thanks,
Helmut
Re: Follow-up: It's NOT possible to set role in db's logon trigger
Roy,
I missed the first run of the question, so you've
probably had this answer already.
You can set a role inside a procedure if
the procedure is declared with invoker
rights (authid current_user) although the
procedure cannot then be run in a logon
trigger.
However, in Oracle 9, you could define the
role as an application role protected by
a package. If COTS attaches to the database
using OCI, then you could consider using
the PROXY_USER features. In this case,
COTS connects as "itself", then becomes
the end-user, without knowing the end-user
password. Your package could then set
the role based on the fact that
sys_context('userenv','proxy_user')
was 'COTS'.
When the user logs in normally, their
'proxy_user' value will be null. The only
way that they could switch on the role
would be to write their own OCI program
that logged on as COTS first - which means
they'd have to know the COTS password
anyway, so your data would have been
unprotected anyway.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March
USA_(FL)_May
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 27 January 2003 23:48
trigger
In case anyone cares--it looks like it is *not* possible to set a role
in an
after logon trigger. Had I only looked at metalink:
AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles
Enabled
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p
_datab
ase_id=NOT&p_id=106140.1
Bummer, that.
Thanks again to all who responded.
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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).
Follow-up: It's NOT possible to set role in db's logon trigger
In case anyone cares--it looks like it is *not* possible to set a role in an after logon trigger. Had I only looked at metalink: AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOT&p_id=106140.1 Bummer, that. Thanks again to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, January 13, 2003 7:42 AM To: 'ORACLE-L' Greetings all, I'm trying to support a COTS application that is back-end agnostic & makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues & I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on & off in conjunction with users opening & closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field & am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database & if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client & then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: 8.1.6: possible to set role in db's logon trigger?
Woah--free code! A thousand thanks--this looks really close to what I'd
like to do. If I can wrestle some extra privs on our test db I'll report
back as to whether I was able to get this going on 8.1.6.
Thanks also to Lisa & Thomas for responding.
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Monday, January 13, 2003 9:05 AM
To: Multiple recipients of list ORACLE-L
Roy,
this is in 9202 ... check the custom code for hash joins ... it has been
working fine for us for > 2 months ...
CREATE OR REPLACE TRIGGER "SYSTEM".DBT_USERS_LOGON
AFTER LOGON ON DATABASE
--
DECLARE
CURSOR cur_sess IS
SELECT *
FROM v$session
WHERE AUDSID = USERENV('SESSIONID')
AND USERNAME NOT IN ('HEARTBEAT');
--
recSess cur_sess%ROWTYPE;
--
PRAGMA AUTONOMOUS_TRANSACTION;
--
BEGIN
OPEN cur_sess;
FETCH cur_Sess INTO recSess;
CLOSE cur_sess;
--
INSERT INTO USER_LOGON_AUDIT
(SESS_AUDSID, DB_USER, OS_USER, TERMINAL, PROGRAM,
TRIGGER_EVENT,LOGON_TIME, LOGOFF_TIME)
VALUES
(USERENV('SESSIONID'), UPPER(ora_login_user), UPPER(recSess.osuser),
recSess.terminal, recSess.program, ORA_SYSEVENT, SYSDATE, NULL);
COMMIT;
--
IF UPPER(ORA_LOGIN_USER) = 'AFF_QUERY' AND
UPPER(recSess.machine) = 'IMAPPROD1' THEN
EXECUTE IMMEDIATE 'alter session set hash_join_enabled=false';
END IF;
--
EXCEPTION
WHEN OTHERS THEN
NULL;
END DBT_USERS_LOGON;
/
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Pardee, Roy E
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: 8.1.6: possible to set role in db's logon trigger?
Title: RE: 8.1.6: possible to set role in db's logon trigger?
Roy,
this is in 9202 ... check the custom code for hash joins ... it has been working fine for us for > 2 months ...
CREATE OR REPLACE TRIGGER "SYSTEM".DBT_USERS_LOGON
AFTER LOGON ON DATABASE
--
DECLARE
CURSOR cur_sess IS
SELECT *
FROM v$session
WHERE AUDSID = USERENV('SESSIONID')
AND USERNAME NOT IN ('HEARTBEAT');
--
recSess cur_sess%ROWTYPE;
--
PRAGMA AUTONOMOUS_TRANSACTION;
--
BEGIN
OPEN cur_sess;
FETCH cur_Sess INTO recSess;
CLOSE cur_sess;
--
INSERT INTO USER_LOGON_AUDIT
(SESS_AUDSID, DB_USER, OS_USER, TERMINAL, PROGRAM,
TRIGGER_EVENT,LOGON_TIME, LOGOFF_TIME)
VALUES
(USERENV('SESSIONID'), UPPER(ora_login_user), UPPER(recSess.osuser),
recSess.terminal, recSess.program, ORA_SYSEVENT, SYSDATE, NULL);
COMMIT;
--
IF UPPER(ORA_LOGIN_USER) = 'AFF_QUERY' AND
UPPER(recSess.machine) = 'IMAPPROD1' THEN
EXECUTE IMMEDIATE 'alter session set hash_join_enabled=false';
END IF;
--
EXCEPTION
WHEN OTHERS THEN
NULL;
END DBT_USERS_LOGON;
/
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
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.*2
Re: 8.1.6: possible to set role in db's logon trigger?
To solve your first problem, correctly identifying the current session from v$session, in your login trigger include: client_info_str := 'SOME_LITTERAL_' || LTRIM(dbms_random.value,'.'); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str); Then you can: SELECT program, username, osuser, terminal, machine INTO loc_program, loc_username, loc_osuser,loc_terminal,loc_machine FROM V$SESSION WHERE client_info=client_info_str; I've never tried the SET ROLE in a login trigger but I don't know why it wouldn't work. "Pardee, Roy E" @lmco.com> cc: Sent by: root Subject: 8.1.6: possible to set role in db's logon trigger? 01/13/2003 10:43 AM Please respond to ORACLE-L Greetings all, I'm trying to support a COTS application that is back-end agnostic & makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues & I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on & off in conjunction with users opening & closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field & am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database & if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client & then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Thomas Day 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') an
RE: 8.1.6: possible to set role in db's logon trigger?
Title: RE: 8.1.6: possible to set role in db's logon trigger? Hi Roy, Note 122230.1 will answer your first question about session id's. Lisa Koivu Oracle Dogbone Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Pardee, Roy E [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 10:44 AM To: Multiple recipients of list ORACLE-L Subject: 8.1.6: possible to set role in db's logon trigger? Greetings all, I'm trying to support a COTS application that is back-end agnostic & makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues & I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on & off in conjunction with users opening & closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field & am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database & if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client & then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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).
8.1.6: possible to set role in db's logon trigger?
Greetings all, I'm trying to support a COTS application that is back-end agnostic & makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues & I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on & off in conjunction with users opening & closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field & am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database & if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client & then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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
Mkb, think about the JOE considerations !!
-Original Message-
Sent: Wednesday, October 30, 2002 5:19 PM
To: Multiple recipients of list ORACLE-L
Ramon,
Thank you. I like the ideas in this trigger. I will
certainly try this.
mkb
--- "Ramon E. Estevez" <[EMAIL PROTECTED]>
wrote:
> Hi, mkb
>
> I made this one, hope it works for you as it did for
> me.
>
> It validates also some roles that were assigned to
> the DBA group.
>
>
> Luck,
>
> Ramonaa
>
>
> CREATE OR REPLACE TRIGGER sys.Block_Tools_T
> after logon on database
>
> Declare
> user_name varchar2(30);
> program_name varchar2(40);
> machine_name varchar2(40);
> logon_date date;
> sesiones integer := 0;
> autorizado varchar2(1) := 'N';
> external_tool boolean := false;
>
> cursor c1 is select username, program , machine,
> sysdate
> from v$session
> where audsid=userenv('sessionid');
> Begin
> open c1;
> fetch c1 into user_name, program_name,
> machine_name, logon_date;
> close c1;
>
> select count(*) into sesiones
> from v$session
> where username = user_name
> andmachine = machine_name;
>
> select 'S' into autorizado from dba_role_privs
> where grantee = user_name
> and granted_role in
>
('DBA_JUNIOR','DBA_SENIOR','INTEGRIDAD','ANALISTA_TEM');
>
> if upper(program_name) LIKE '%PLUS%' or
> upper(program_name) LIKE '%TOAD%' or
> upper(program_name) LIKE 'SQLNAV%' then
> external_tool := true;
> end if ;
> if (external_tool and autorizado = 'N') then
> raise_application_error(-20001,'Usted No puede conectarse
> utilizando esta aplicacion.');
> elsif (autorizado = 'N' and sesiones >= 1) then
> raise_application_error(-20001,'No puede hacer
> mas conexiones desde
> esta terminal.');
> end if;
>
> Exception when others then
> raise_application_error(-20001,'No tiene ningun privilegio
> asignado, contacte el depto de seguridad de sistemas.');
> End;
> /
>
>
>
> -Original Message-
> Sent: Tuesday, October 29, 2002 12:29 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Sorry in advance, but I didn't think I'd need this.
>
> There was a discussion about 4 weeks ago if memory
> serves correct, about denying users logging on to
> Oracle directly either through SQL*Plus or other
> tools
> such as TOAD. Tried searching the archives but
> getting too many hits.
>
> Someone posted trigger code that did this. Anyone
> have a copy of this?
>
> Thanks
>
> mkb
>
>
> __
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: mkb
> 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: Ramon E. Estevez
> 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!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mkb
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: Ramon E. Estevez
INET: [EMAIL PROTECTED]
Fat City Network Se
RE: Logon Trigger
Thanks Joe.
-Original Message-
Sent: Wednesday, October 30, 2002 4:18 PM
To: Multiple recipients of list ORACLE-L
Ramon , a few quick comments.
1. Never create an object owned by SYS. It just isn't good to
contaminate the SYS schema with NON oracle related objects.
2. Anything owned by SYS will NOT be exported, refer to #1.
3. Create a new userid to store those kinds of objects so during an
export it will be picked up and refer to #1. :)
joe
Ramon E. Estevez wrote:
>Hi, mkb
>
>I made this one, hope it works for you as it did for me.
>
>It validates also some roles that were assigned to the DBA group.
>
>
>Luck,
>
>Ramonaa
>
>
>CREATE OR REPLACE TRIGGER sys.Block_Tools_T
>after logon on database
>
>Declare
> user_name varchar2(30);
> program_name varchar2(40);
> machine_name varchar2(40);
> logon_date date;
> sesiones integer := 0;
> autorizado varchar2(1) := 'N';
> external_tool boolean := false;
>
> cursor c1 is select username, program , machine, sysdate
> from v$session
> where audsid=userenv('sessionid');
>Begin
> open c1;
> fetch c1 into user_name, program_name, machine_name, logon_date;
> close c1;
>
> select count(*) into sesiones
> from v$session
> where username = user_name
> andmachine = machine_name;
>
> select 'S' into autorizado from dba_role_privs
> where grantee = user_name
> and granted_role in
>('DBA_JUNIOR','DBA_SENIOR','INTEGRIDAD','ANALISTA_TEM');
>
> if upper(program_name) LIKE '%PLUS%' or
> upper(program_name) LIKE '%TOAD%' or
> upper(program_name) LIKE 'SQLNAV%' then
> external_tool := true;
> end if ;
> if (external_tool and autorizado = 'N') then
> raise_application_error(-20001,'Usted No puede conectarse
>utilizando esta aplicacion.');
> elsif (autorizado = 'N' and sesiones >= 1) then
> raise_application_error(-20001,'No puede hacer mas conexiones
>desde esta terminal.');
> end if;
>
> Exception when others then
>raise_application_error(-20001,'No tiene ningun privilegio
>asignado, contacte el depto de seguridad de sistemas.'); End;
>/
>
>
>
>-Original Message-
>Sent: Tuesday, October 29, 2002 12:29 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Sorry in advance, but I didn't think I'd need this.
>
>There was a discussion about 4 weeks ago if memory
>serves correct, about denying users logging on to
>Oracle directly either through SQL*Plus or other tools
>such as TOAD. Tried searching the archives but
>getting too many hits.
>
>Someone posted trigger code that did this. Anyone
>have a copy of this?
>
>Thanks
>
>mkb
>
>
>__
>Do you Yahoo!?
>HotJobs - Search new jobs daily now
>http://hotjobs.yahoo.com/
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joe Testa
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: Ramon E. Estevez
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
Ramon,
Thank you. I like the ideas in this trigger. I will
certainly try this.
mkb
--- "Ramon E. Estevez" <[EMAIL PROTECTED]>
wrote:
> Hi, mkb
>
> I made this one, hope it works for you as it did for
> me.
>
> It validates also some roles that were assigned to
> the DBA group.
>
>
> Luck,
>
> Ramonaa
>
>
> CREATE OR REPLACE TRIGGER sys.Block_Tools_T
> after logon on database
>
> Declare
> user_name varchar2(30);
> program_name varchar2(40);
> machine_name varchar2(40);
> logon_date date;
> sesiones integer := 0;
> autorizado varchar2(1) := 'N';
> external_tool boolean := false;
>
> cursor c1 is select username, program , machine,
> sysdate
> from v$session
> where audsid=userenv('sessionid');
> Begin
> open c1;
> fetch c1 into user_name, program_name,
> machine_name, logon_date;
> close c1;
>
> select count(*) into sesiones
> from v$session
> where username = user_name
> andmachine = machine_name;
>
> select 'S' into autorizado from dba_role_privs
> where grantee = user_name
> and granted_role in
>
('DBA_JUNIOR','DBA_SENIOR','INTEGRIDAD','ANALISTA_TEM');
>
> if upper(program_name) LIKE '%PLUS%' or
> upper(program_name) LIKE '%TOAD%' or
> upper(program_name) LIKE 'SQLNAV%' then
> external_tool := true;
> end if ;
> if (external_tool and autorizado = 'N') then
> raise_application_error(-20001,'Usted No puede
> conectarse
> utilizando esta aplicacion.');
> elsif (autorizado = 'N' and sesiones >= 1) then
> raise_application_error(-20001,'No puede hacer
> mas conexiones desde
> esta terminal.');
> end if;
>
> Exception when others then
> raise_application_error(-20001,'No tiene ningun
> privilegio asignado,
> contacte el depto de seguridad de sistemas.');
> End;
> /
>
>
>
> -Original Message-
> Sent: Tuesday, October 29, 2002 12:29 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Sorry in advance, but I didn't think I'd need this.
>
> There was a discussion about 4 weeks ago if memory
> serves correct, about denying users logging on to
> Oracle directly either through SQL*Plus or other
> tools
> such as TOAD. Tried searching the archives but
> getting too many hits.
>
> Someone posted trigger code that did this. Anyone
> have a copy of this?
>
> Thanks
>
> mkb
>
>
> __
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: mkb
> 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: Ramon E. Estevez
> 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!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mkb
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
Ramon , a few quick comments.
1. Never create an object owned by SYS. It just isn't good to
contaminate the SYS schema with NON oracle related objects.
2. Anything owned by SYS will NOT be exported, refer to #1.
3. Create a new userid to store those kinds of objects so during an
export it will be picked up and refer to #1. :)
joe
Ramon E. Estevez wrote:
Hi, mkb
I made this one, hope it works for you as it did for me.
It validates also some roles that were assigned to the DBA group.
Luck,
Ramonaa
CREATE OR REPLACE TRIGGER sys.Block_Tools_T
after logon on database
Declare
user_name varchar2(30);
program_name varchar2(40);
machine_name varchar2(40);
logon_date date;
sesiones integer := 0;
autorizado varchar2(1) := 'N';
external_tool boolean := false;
cursor c1 is select username, program , machine, sysdate
from v$session
where audsid=userenv('sessionid');
Begin
open c1;
fetch c1 into user_name, program_name, machine_name, logon_date;
close c1;
select count(*) into sesiones
from v$session
where username = user_name
andmachine = machine_name;
select 'S' into autorizado from dba_role_privs
where grantee = user_name
and granted_role in
('DBA_JUNIOR','DBA_SENIOR','INTEGRIDAD','ANALISTA_TEM');
if upper(program_name) LIKE '%PLUS%' or
upper(program_name) LIKE '%TOAD%' or
upper(program_name) LIKE 'SQLNAV%' then
external_tool := true;
end if ;
if (external_tool and autorizado = 'N') then
raise_application_error(-20001,'Usted No puede conectarse
utilizando esta aplicacion.');
elsif (autorizado = 'N' and sesiones >= 1) then
raise_application_error(-20001,'No puede hacer mas conexiones desde
esta terminal.');
end if;
Exception when others then
raise_application_error(-20001,'No tiene ningun privilegio asignado,
contacte el depto de seguridad de sistemas.');
End;
/
-Original Message-
Sent: Tuesday, October 29, 2002 12:29 PM
To: Multiple recipients of list ORACLE-L
Sorry in advance, but I didn't think I'd need this.
There was a discussion about 4 weeks ago if memory
serves correct, about denying users logging on to
Oracle directly either through SQL*Plus or other tools
such as TOAD. Tried searching the archives but
getting too many hits.
Someone posted trigger code that did this. Anyone
have a copy of this?
Thanks
mkb
__
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joe Testa
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, mkb
I made this one, hope it works for you as it did for me.
It validates also some roles that were assigned to the DBA group.
Luck,
Ramonaa
CREATE OR REPLACE TRIGGER sys.Block_Tools_T
after logon on database
Declare
user_name varchar2(30);
program_name varchar2(40);
machine_name varchar2(40);
logon_date date;
sesiones integer := 0;
autorizado varchar2(1) := 'N';
external_tool boolean := false;
cursor c1 is select username, program , machine, sysdate
from v$session
where audsid=userenv('sessionid');
Begin
open c1;
fetch c1 into user_name, program_name, machine_name, logon_date;
close c1;
select count(*) into sesiones
from v$session
where username = user_name
andmachine = machine_name;
select 'S' into autorizado from dba_role_privs
where grantee = user_name
and granted_role in
('DBA_JUNIOR','DBA_SENIOR','INTEGRIDAD','ANALISTA_TEM');
if upper(program_name) LIKE '%PLUS%' or
upper(program_name) LIKE '%TOAD%' or
upper(program_name) LIKE 'SQLNAV%' then
external_tool := true;
end if ;
if (external_tool and autorizado = 'N') then
raise_application_error(-20001,'Usted No puede conectarse
utilizando esta aplicacion.');
elsif (autorizado = 'N' and sesiones >= 1) then
raise_application_error(-20001,'No puede hacer mas conexiones desde
esta terminal.');
end if;
Exception when others then
raise_application_error(-20001,'No tiene ningun privilegio asignado,
contacte el depto de seguridad de sistemas.');
End;
/
-Original Message-
Sent: Tuesday, October 29, 2002 12:29 PM
To: Multiple recipients of list ORACLE-L
Sorry in advance, but I didn't think I'd need this.
There was a discussion about 4 weeks ago if memory
serves correct, about denying users logging on to
Oracle directly either through SQL*Plus or other tools
such as TOAD. Tried searching the archives but
getting too many hits.
Someone posted trigger code that did this. Anyone
have a copy of this?
Thanks
mkb
__
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mkb
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: Ramon E. Estevez
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).
Logon Trigger
Sorry in advance, but I didn't think I'd need this. There was a discussion about 4 weeks ago if memory serves correct, about denying users logging on to Oracle directly either through SQL*Plus or other tools such as TOAD. Tried searching the archives but getting too many hits. Someone posted trigger code that did this. Anyone have a copy of this? Thanks mkb __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb 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 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 to Subject: 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
cur
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 tra
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 sess
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 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 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 ***
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 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
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
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 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
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
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
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
"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
use
execute immediate 'alter session enable parallel query';
Paul
-Original Message-
Sent: Wednesday, October 02, 2002 11:24 AM
To: Multiple recipients of list ORACLE-L
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:
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).
logon trigger
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).
RE: How to read client characterset in logon trigger?
select sys_context('USERENV','LANGUAGE') FROM DUAL ???
-Original Message-
Sent: Wednesday, July 24, 2002 11:05 AM
To: Multiple recipients of list ORACLE-L
Help me out here, is there a procedure or statement I can use to record
client character set settings in a logon trigger?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Post, Ethan
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
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).
RE: How to read client characterset in logon trigger?
I have seen forms do this all the time, i.e. without actually issuing the 'alter session ...' command, you can see they being done. I think it must be something to do with sqlnet. Have you tried it yet? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, July 24, 2002 2:07 PM To: Multiple recipients of list ORACLE-L I suppose that would work if an alter session is used but what if the client is simply set up to use a different character set, for example if NLS_LANG is set to something other than the database character set. export NLS_LANG=Blah sqlplus foo/bar Now what character set is being used? I presume "Blah". Ethan Post (972) 577-6552 [EMAIL PROTECTED] perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Wednesday, July 24, 2002 12:36 PM To: Multiple recipients of list ORACLE-L SELECT SUBSTR(sql_text, 1, 2000) FROM sys.V_$OPEN_CURSOR WHERE UPPER (sql_text) LIKE 'ALTER SESSION%' with appropriate sid. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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). *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.*2
RE: How to read client characterset in logon trigger?
I suppose that would work if an alter session is used but what if the client is simply set up to use a different character set, for example if NLS_LANG is set to something other than the database character set. export NLS_LANG=Blah sqlplus foo/bar Now what character set is being used? I presume "Blah". Ethan Post (972) 577-6552 [EMAIL PROTECTED] perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Wednesday, July 24, 2002 12:36 PM To: Multiple recipients of list ORACLE-L SELECT SUBSTR(sql_text, 1, 2000) FROM sys.V_$OPEN_CURSOR WHERE UPPER (sql_text) LIKE 'ALTER SESSION%' with appropriate sid. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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).
RE: How to read client characterset in logon trigger?
SELECT SUBSTR(sql_text, 1, 2000) FROM sys.V_$OPEN_CURSOR WHERE UPPER (sql_text) LIKE 'ALTER SESSION%' with appropriate sid. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, July 24, 2002 1:05 PM To: Multiple recipients of list ORACLE-L Help me out here, is there a procedure or statement I can use to record client character set settings in a logon trigger? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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). *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.*2
How to read client characterset in logon trigger?
Help me out here, is there a procedure or statement I can use to record client character set settings in a logon trigger? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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).
RE: After logon trigger
Hi Jared,
I just now Tried it and it was working.
But i have tried it in 8.1.6 and it has failed to execute for the users
granted the DBA Role.
Anyways when i checked at
http://asktom.oracle.com/pls/ask/f?p=4950:8:329387::NO::F4950_P8_DISPLAYID,F
4950_P8_CRITERIA:1844531724208,%7BAfter%7D%20and%20%7BLogon%7D
This iw what he said.
This is the correct behavior and is designed to make it so your database
doesn't
get into a state whereby NO ONE can log in (815 can get to that state). It
is a
feature.
My suggestion, don't use the DBA role. Create your own ROLE (create many of
them in fact) YOUR_DBA and use that instead. DBA is just a role (but a
special
one). If you don't use "DBA", you will be able to treat your DBA users as
if
they were any ordinary user.
Your comments pls.
Best Regards,
Ganesh R
Tel : +971 (4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 7456019
-Original Message-
Sent: Thursday, December 13, 2001 1:56 AM
To: Multiple recipients of list ORACLE-L
I've done many tests and this is not right. It fires for users with DBA.
The only thing I remember (may be I'm dreaming )when I first used it in
8.1.5 on NT is that the logon trigger did not run under the user who is
logging on but under the pseudo process number 1.
This is not the case any more.
Regards
Waleed
-Original Message-
Sent: Wednesday, December 12, 2001 1:41 PM
To: Multiple recipients of list ORACLE-L
Ganesh,
Are you sure about logon triggers not firing for users
with the DBA role enabled?
I've used them on 8.1.6 and 8.1.7, and they fire on
my DBA account.
Jared
Ganesh Raja
ulf.com> cc:
Sent by: Subject: RE: After logon
trigger
[EMAIL PROTECTED]
om
12/11/01 11:30
PM
Please respond
to ORACLE-L
Create it Once not every time some one Logs in...
The table is notsimilar to temporary tables in SQl... They are Different..
The Table is visible to all sessions if u say they are Global temporary
Tables.
Yes Logon triggers Fire Every time the User logon... But the second time
onwards u will get a Error... The Logon triggers dont fire for Users with
the DBA Role Enabled.
HTH
Best Regards,
Ganesh R
Tel : +971 (4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 7456019
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 12, 2001 11:00 AM
To: Multiple recipients of list ORACLE-L
Subject: After logon trigger
Hi
I am trying to use an after logon trigger to create a session temp
table rather that create it at the time the procedure that uses it
calls it. Two reasons for doing this is for performance ? the other is
so that I can reference it in a cursor.
1) Do logon triggers not work if I logon with toad or similar tools
2) If 1 is wrong why when I have the following trigger in the database
cant I see the table or use it after logging on
TRIGGER create_temp_table
AFTER LOGON ON DATABASE
DECLARE
v_CreateString VARCHAR2(100);
BEGIN
v_CreateString := 'CREATE GLOBAL TEMPORARY TABLE t_tmp_stddev ( v_part
NUMBER, readings NUMBER) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE v_CreateString;
END;
TIA
Peter McLarty E-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.Mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane, AustraliaMobile: +61 (0)402 094 238
--Facsimile: +61 (0)7 3303
3048
--
This transmission is for the intended addressee only and is
confidential information. If you have received this transmission in
error, please delete it and notify the sender. The contents of this
e-mail are the opinion of the writer only and are not endorsed by the
Mincom Group of companies unless expressly stated otherwise.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
Please see the officia
RE: After logon trigger
I've done many tests and this is not right. It fires for users with DBA.
The only thing I remember (may be I'm dreaming )when I first used it in
8.1.5 on NT is that the logon trigger did not run under the user who is
logging on but under the pseudo process number 1.
This is not the case any more.
Regards
Waleed
-Original Message-
Sent: Wednesday, December 12, 2001 1:41 PM
To: Multiple recipients of list ORACLE-L
Ganesh,
Are you sure about logon triggers not firing for users
with the DBA role enabled?
I've used them on 8.1.6 and 8.1.7, and they fire on
my DBA account.
Jared
Ganesh Raja
ulf.com> cc:
Sent by: Subject: RE: After logon
trigger
[EMAIL PROTECTED]
om
12/11/01 11:30
PM
Please respond
to ORACLE-L
Create it Once not every time some one Logs in...
The table is notsimilar to temporary tables in SQl... They are Different..
The Table is visible to all sessions if u say they are Global temporary
Tables.
Yes Logon triggers Fire Every time the User logon... But the second time
onwards u will get a Error... The Logon triggers dont fire for Users with
the DBA Role Enabled.
HTH
Best Regards,
Ganesh R
Tel : +971 (4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 7456019
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 12, 2001 11:00 AM
To: Multiple recipients of list ORACLE-L
Subject: After logon trigger
Hi
I am trying to use an after logon trigger to create a session temp
table rather that create it at the time the procedure that uses it
calls it. Two reasons for doing this is for performance ? the other is
so that I can reference it in a cursor.
1) Do logon triggers not work if I logon with toad or similar tools
2) If 1 is wrong why when I have the following trigger in the database
cant I see the table or use it after logging on
TRIGGER create_temp_table
AFTER LOGON ON DATABASE
DECLARE
v_CreateString VARCHAR2(100);
BEGIN
v_CreateString := 'CREATE GLOBAL TEMPORARY TABLE t_tmp_stddev ( v_part
NUMBER, readings NUMBER) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE v_CreateString;
END;
TIA
Peter McLarty E-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.Mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane, AustraliaMobile: +61 (0)402 094 238
--Facsimile: +61 (0)7 3303
3048
--
This transmission is for the intended addressee only and is
confidential information. If you have received this transmission in
error, please delete it and notify the sender. The contents of this
e-mail are the opinion of the writer only and are not endorsed by the
Mincom Group of companies unless expressly stated otherwise.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
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).
RE: After logon trigger
Ganesh, Are you sure about logon triggers not firing for users with the DBA role enabled? I've used them on 8.1.6 and 8.1.7, and they fire on my DBA account. Jared Ganesh Raja ulf.com> cc: Sent by: Subject: RE: After logon trigger [EMAIL PROTECTED] om 12/11/01 11:30 PM Please respond to ORACLE-L Create it Once not every time some one Logs in... The table is notsimilar to temporary tables in SQl... They are Different.. The Table is visible to all sessions if u say they are Global temporary Tables. Yes Logon triggers Fire Every time the User logon... But the second time onwards u will get a Error... The Logon triggers dont fire for Users with the DBA Role Enabled. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 7456019 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 12, 2001 11:00 AM To: Multiple recipients of list ORACLE-L Subject: After logon trigger Hi I am trying to use an after logon trigger to create a session temp table rather that create it at the time the procedure that uses it calls it. Two reasons for doing this is for performance ? the other is so that I can reference it in a cursor. 1) Do logon triggers not work if I logon with toad or similar tools 2) If 1 is wrong why when I have the following trigger in the database cant I see the table or use it after logging on TRIGGER create_temp_table AFTER LOGON ON DATABASE DECLARE v_CreateString VARCHAR2(100); BEGIN v_CreateString := 'CREATE GLOBAL TEMPORARY TABLE t_tmp_stddev ( v_part NUMBER, readings NUMBER) ON COMMIT PRESERVE ROWS'; EXECUTE IMMEDIATE v_CreateString; END; TIA Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.Mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 --Facsimile: +61 (0)7 3303 3048 -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: After logon trigger
Ganesh, at-least in 8i whenever user system logs-in, I can see that in after logon trigger. I know for sure that system has DBA role enabled and I know for sure the trigger fires, as whenever I log in as system, I get an entry in USER_LOGON_AUDIT table, which is populated by AFTER LOGON trigger on database. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *1 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: After logon trigger
Logon triggers do work ... are there any errors in alert.log file? We do use temp tables, but we create them only when we need them in the transaction. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 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. *2
RE: After logon trigger
Create it Once not every time some one Logs in... The table is notsimilar to temporary tables in SQl... They are Different.. The Table is visible to all sessions if u say they are Global temporary Tables. Yes Logon triggers Fire Every time the User logon... But the second time onwards u will get a Error... The Logon triggers dont fire for Users with the DBA Role Enabled. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 7456019 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Wednesday, December 12, 2001 11:00 AMTo: Multiple recipients of list ORACLE-LSubject: After logon triggerHi I am trying to use an after logon trigger to create a session temp table rather that create it at the time the procedure that uses it calls it. Two reasons for doing this is for performance ? the other is so that I can reference it in a cursor. 1) Do logon triggers not work if I logon with toad or similar tools 2) If 1 is wrong why when I have the following trigger in the database cant I see the table or use it after logging on TRIGGER create_temp_table AFTER LOGON ON DATABASE DECLARE v_CreateString VARCHAR2(100); BEGIN v_CreateString := 'CREATE GLOBAL TEMPORARY TABLE t_tmp_stddev ( v_part NUMBER, readings NUMBER) ON COMMIT PRESERVE ROWS'; EXECUTE IMMEDIATE v_CreateString; END; TIAPeter McLarty E-mail: [EMAIL PROTECTED]Technical Consultant WWW: http://www.Mincom.comAPAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, Australia Mobile: +61 (0)402 094 238 -- Facsimile: +61 (0)7 3303 3048-- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise.
After logon trigger
Hi I am trying to use an after logon trigger to create a session temp table rather that create it at the time the procedure that uses it calls it. Two reasons for doing this is for performance ? the other is so that I can reference it in a cursor. 1) Do logon triggers not work if I logon with toad or similar tools 2) If 1 is wrong why when I have the following trigger in the database cant I see the table or use it after logging on TRIGGER create_temp_table AFTER LOGON ON DATABASE DECLARE v_CreateString VARCHAR2(100); BEGIN v_CreateString := 'CREATE GLOBAL TEMPORARY TABLE t_tmp_stddev ( v_part NUMBER, readings NUMBER) ON COMMIT PRESERVE ROWS'; EXECUTE IMMEDIATE v_CreateString; END; TIA Peter McLarty E-mail: [EMAIL PROTECTED] Technical Consultant WWW: http://www.Mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, Australia Mobile: +61 (0)402 094 238 -- Facsimile: +61 (0)7 3303 3048 -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise.
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).
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).
