SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 Running StatsPack during a PEAK period and then analyzing the output at oraperf.com, I get : 33409 parses (673 hard parses), 498516 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.31 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 508 sessions logged on and at the end of the timing interval 0 more sessions where active. The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted in reducing the parse count from 32736 to 22550 During parsing 276280 msec of CPU were used and 1134430 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch 96% of the latch wait time is on the Library Cache Latch [85% of the Response Time was Wait Time, 71% of the Wait Time was Latch Wait time and 96% of the Latch Wait Time was Library Cache Latch, . this Wait Time analysis really does make sense !] Hemant At 10:14 PM 29-11-03 -0800, you wrote: I thought the session_cached_cursors is dynamic and scope is session? This is on 8.1.7. I have used: alter session set session_cached_cursors=500; -Original Message- Sent: Sunday, November 30, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Sami, 'cached_cursors' is not a valid hint, at least not in 9i. Or at least, I can find no reference to it. And 'cached cursors' as it appears in the SQL is not a valid hint syntax. You need to set the session_cached_cursors value in the init.ora, and bounce the database. This parameter cannot be set dynamically, at least as of 9i. Jared On Sat, 2003-11-29 at 14:44, Sami wrote: Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives better response time. But the same time w.r.t latch, session_cached_cursor=100 is giving positive impact. 1) session_cached_cursor=0 - more latches but good response time(2.60) 2) session_cached_cursor=100 - less # of latches but higher response time(2.87) Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 2000 1.76 1.77 0 0 0 0 Execute 2000 0.84 0.74 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.60 2.51 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0 TABLE ACCESS FULL T2 0 TABLE ACCESS FULL T1 SELECT /*+ cached cursors 100 */FIRST_NAME,LAST_NAME,CUSTOMERID, COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 2000 2.05 1.99 0 0 0 0 Execute 2000 0.82 0.74 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.87 2.73 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation ---
XML Sctructure
Hi friend, HI know that this forum is about Oracle, but i have a doubt and i think thay someone can help me . I have the xml sctructure below. XML : ?xml version=1.0? !-- Exemplo de XML Endereco-- !DOCTYPE lista_endereco SYSTEM lista_endereco.dtd lista_endereco endereco titulo rodolfo do Carmo Andrietta/titulo primeironomeEriovaldo/primeironome ultimonomeAndrietta/ultimonome logradouroR Um/logradouro cidadeSao Paulo/cidade estadoSP/estado cep01680-000/cep /endereco /lista_endereco DTD !-- lista_endereco.dtd -- !ELEMENT lista_endereco (endereco) !ELEMENT endereco (titulo, primeironome, ultimonome, logradouro, cidade, estado, cep) !ELEMENT titulo (#PCDATA) !ELEMENT primeironome (#PCDATA) !ELEMENT ultimonome (#PCDATA) !ELEMENT logradouro (#PCDATA) !ELEMENT cidade (#PCDATA) !ELEMENT estado (#PCDATA) !ELEMENT cep (#PCDATA) My question is : How can i do for use DTD and put more than 1 line in XML ? Because like that, if i put one more line in xml it says that file is invalid. Any clue ? Thanks. Eriovaldo -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: ecaforum 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: .NET, connection pooling and security .
We are going the OID way because of these problems. Anyway here is a wild idea: Tell the web guys to use the user userid (he probably logged to the web application) with a standard password that is common to all of them and is supplied by the web application, the user does not see it. If you have an information security guy, teach him how to add users and grant the application user role. The schema owner password need to be a closely held secret of the dba group. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 5:49 AM I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry 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: Yechiel Adar 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: Cobol redefine in SQL
I think there are two solutions to this problems: 1) Load the data into varchar and use views with substr to preset the data in orderly manner to the users. 2) Create a table that contain all the available fields. For each redefine populate only the relevant fields, with views for each redefine. You can use the multiple into table format of SQLLDR and load into the same table, each time with different fields. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 11:34 PM Hi all, We'll do a conversion from IMS to Oracle (817). The conversion team wants to move data on Oracle then processed it. Off course, in the file format, the cobol redefine command is used on a filler field (20 times). There is also a redefine command in a redefine command. For the younger ones, it means a zone (50 caracters) that can be defined in many ways depending on a key : 2 fields of 25 char or 5 fields of 10 char or The programmers can always use substring to death to work this out but I was wandering if there was a more elegant and a less error prone solution, maybe using a record type or a function ... Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 poste 7470 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette 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: Yechiel Adar 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: FRM-41072
Hello John From you description of the problem I suspect that you use 7.3 client. See note172179.1 on metalink about which client connect to which server. Yechiel AdarMehish - Original Message - From: John To: Multiple recipients of list ORACLE-L Sent: Saturday, November 29, 2003 8:14 PM Subject: FRM-41072 Do you know how to overcome this problem? Currently my forms work good with a 7.3.3 Database Server. Whilei get connected to the 9.2.0.1 database i receive this error "Cannot create the Group" My forms have been created by the Developer/2000 which i don;t own. I feel that i have something missed to migrate to the new DB Server. Could you help me a little? John
Re: FRM-41072
I have no access to metalink. So far, i had no problem get connected to the Oracle 9i R2 with a 7.3.3. client (ORANT). If i try to connect with an Oracle Client 9i r2 to an Oracle 7.3.3. then it fails - Original Message - From: Yechiel Adar To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 1:04 PM Subject: Re: FRM-41072 Hello John From you description of the problem I suspect that you use 7.3 client. See note172179.1 on metalink about which client connect to which server. Yechiel AdarMehish - Original Message - From: John To: Multiple recipients of list ORACLE-L Sent: Saturday, November 29, 2003 8:14 PM Subject: FRM-41072 Do you know how to overcome this problem? Currently my forms work good with a 7.3.3 Database Server. Whilei get connected to the 9.2.0.1 database i receive this error "Cannot create the Group" My forms have been created by the Developer/2000 which i don;t own. I feel that i have something missed to migrate to the new DB Server. Could you help me a little? John
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still seevery high LIBRARY CACHE LATCH contention and am considering upping the value again.Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggestedto the application team to put a custom ALTER SESSION trigger into the specific firstresponsibility form for users who do navigate between forms a lot and where we seehigh contention.Running Steve Adams's query, I getSQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE- - -session_cached_cursors 400 50%open_cursors 1024 36%CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES- --- --- 35.10% 63.09% 1.81%MAX_CACHEABLE_CURSORS- 5227Running StatsPack during a PEAK period and then analyzing the output at oraperf.com, I get :33409 parses (673 hard parses), 498516 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.31 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 508 sessions logged on and at the end of the timing interval 0 more sessions where active. The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted in reducing the parse count from 32736 to 22550During parsing 276280 msec of CPU were used and 1134430 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch96% of the latch wait time is on the Library Cache Latch [85% of the Response Time was Wait Time, 71% of the Wait Timewas Latch Wait time and 96% of the Latch Wait Time was Library Cache Latch, . this Wait Time analysis reallydoes make sense !]HemantAt 10:14 PM 29-11-03 -0800, you wrote: I thought the session_cached_cursors is dynamic and scope issession? This is on 8.1.7. I have used:alter session set session_cached_cursors=500;-Original Message-Sent: Sunday, November 30, 2003 12:24 AMTo: Multiple recipients of list ORACLE-LSami,'cached_cursors' is not a valid hint, at least not in 9i.Or at least, I can find no reference to it.And 'cached cursors' as it appears in the SQL is not avalid hint syntax.You need to set the session_cached_cursors value in theinit.ora, and bounce the database. This parameter cannotbe set dynamically, at least as of 9i.JaredOn Sat, 2003-11-29 at 14:44, Sami wrote: Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives better response time. But the same time w.r.t latch, session_cached_cursor=100 is giving positive impact. 1) session_cached_cursor=0 - more latches but good response time(2.60) 2) session_cached_cursor=100 - less # of latches but higher response time(2.87) Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 2000 1.76 1.77 0 0 0 0 Execute 2000 0.84 0.74 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.60 2.51 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0 TABLE ACCESS FULL T2 0 TABLE ACCESS FULL T1 SELECT /*+ cached cursors 100
RE: Parse Vs Execute
Jared, Sorry for the confusion. I did not use 'cached_cursors' as a hint. It is just to identify SQL statements in tkrpof output. I should have mentioned /*cached cursors 0 */ instead of /*+ cached cursors 0*/ Thanks Sami -Original Message- Jared Still Sent: Sunday, November 30, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Sami, 'cached_cursors' is not a valid hint, at least not in 9i. Or at least, I can find no reference to it. And 'cached cursors' as it appears in the SQL is not a valid hint syntax. You need to set the session_cached_cursors value in the init.ora, and bounce the database. This parameter cannot be set dynamically, at least as of 9i. Jared On Sat, 2003-11-29 at 14:44, Sami wrote: Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives better response time. But the same time w.r.t latch, session_cached_cursor=100 is giving positive impact. 1) session_cached_cursor=0 - more latches but good response time(2.60) 2) session_cached_cursor=100 - less # of latches but higher response time(2.87) Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2000 1.76 1.77 0 0 0 0 Execute 2000 0.84 0.74 0 0 0 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.60 2.51 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0TABLE ACCESS FULL T2 0TABLE ACCESS FULL T1 SELECT /*+ cached cursors 100 */FIRST_NAME,LAST_NAME,CUSTOMERID, COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2000 2.05 1.99 0 0 0 0 Execute 2000 0.82 0.74 0 0 0 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.87 2.73 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0TABLE ACCESS FULL T2 0TABLE ACCESS FULL T1 Program used to generate the above trace file. == alter session set SQL_TRACE=true; alter session set session_cached_cursors=0; declare type rc is ref cursor; C rc; n number :=0; begin n := dbms_utility.get_time; for i in 1 .. 2000 loop open C for select /*+ cached cursors 0 */ first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where p.t1id=e.t1id and p.business_country_id=c.countryabbrev; close C; end loop; dbms_output.put_line( dbms_utility.get_time - n ); end; / alter session set session_cached_cursors=100; declare type rc is ref cursor; C rc; n number :=0; begin n := dbms_utility.get_time; for i in 1 .. 2000 loop --open C for select /*+ cached_cursors 100 */ * from dual; open C for select /*+ cached cursors 100 */ first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where p.t1id=e.t1id and p.business_country_id=c.countryabbrev; close C; end loop; dbms_output.put_line( dbms_utility.get_time - n ); end; / SQL @x Session altered. Session altered. 394 PL/SQL procedure successfully completed. Session altered. 413 PL/SQL procedure successfully completed. SQL NameRun1 Run2 Diff LATCH.KCL lock element parent 1 2 1 LATCH.KCL name table latch
RE: XML Sctructure
More than one line of what? Do you want to try something like below? ?xml version=1.0? !-- Exemplo de XML Endereco-- !DOCTYPE lista_endereco SYSTEM lista_endereco.dtd lista_endereco endereco titulo rodolfo do Carmo Andrietta/titulo primeironomeEriovaldo/primeironome ultimonomeAndrietta/ultimonome logradouroR Um/logradouro cidadeSao Paulo/cidade estadoSP/estado cep01680-000/cep /endereco endereco titulo rodolfo do Carmo Andrietta/titulo primeironomeEriovaldo/primeironome ultimonomeAndrietta/ultimonome logradouroR Um/logradouro cidadeSao Paulo/cidade estadoSP/estado cep01680-000/cep /endereco /lista_endereco -Original Message- ecaforum Sent: Sunday, November 30, 2003 3:39 AM To: Multiple recipients of list ORACLE-L Hi friend, HI know that this forum is about Oracle, but i have a doubt and i think thay someone can help me . I have the xml sctructure below. XML : ?xml version=1.0? !-- Exemplo de XML Endereco-- !DOCTYPE lista_endereco SYSTEM lista_endereco.dtd lista_endereco endereco titulo rodolfo do Carmo Andrietta/titulo primeironomeEriovaldo/primeironome ultimonomeAndrietta/ultimonome logradouroR Um/logradouro cidadeSao Paulo/cidade estadoSP/estado cep01680-000/cep /endereco /lista_endereco DTD !-- lista_endereco.dtd -- !ELEMENT lista_endereco (endereco) !ELEMENT endereco (titulo, primeironome, ultimonome, logradouro, cidade, estado, cep) !ELEMENT titulo (#PCDATA) !ELEMENT primeironome (#PCDATA) !ELEMENT ultimonome (#PCDATA) !ELEMENT logradouro (#PCDATA) !ELEMENT cidade (#PCDATA) !ELEMENT estado (#PCDATA) !ELEMENT cep (#PCDATA) My question is : How can i do for use DTD and put more than 1 line in XML ? Because like that, if i put one more line in xml it says that file is invalid. Any clue ? Thanks. Eriovaldo -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: ecaforum 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: Sami 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[2]: Writing a delay in PL/SQL?
Saturday, November 29, 2003, 10:44:26 PM, Khedr, Waleed ([EMAIL PROTECTED]) wrote: KW I'm curious why? some testing? Yes. Testing. I want a Data Pump job to run long enough for me to be able to play around in interactive mode from several different clients. I only have 28MB of data, and the load runs too fast for me to do much of anything. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Writing a delay in PL/SQL?
As you've heard, dbms_lock.sleep(nn.nn) will do what you want. For short time intervals, it should do what you want - but bear in mind that it has a 2.4% error built in. Oracle Corp. seem to think that one second lasts 1024 milliseconds. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November 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] Sent: Sunday, November 30, 2003 1:34 AM I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: 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).
Re: Parse Vs Execute
How very irritating. But I don't think you mentioned in earlier posts (or at any rate I missed it) that you are running OPS/RAC, and there could be all sorts of less well-known side effects coming in there. Could you also take a snapshot of the v$dlm_misc figures, and the DLM-related session stats for the two different tests. Possibly the time difference is related to library cache coherence between instances rather than the usual local latch problems. There is also an oddity in your figures that I'm going to have to think about. I would have expected your test case to cache the cursors in the pl/sql cursor cache, and not use the session cursor cache anyway. I may be wrong, and perhaps the explicit open/close is changing things in ways I didn't expect; but if I'm right, then perhaps the test with session_cursor_cache is giving slower results because you are disabling a special pl/sql optimisation. I'll try to find some time to test around the problem over the next few days. BTW - it's a little unsafe to rely on any timing results that are produced whilst running sql_trace - I've found some VERY strange things happening to reported CPU usage when sql_trace = true. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November 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] Sent: Saturday, November 29, 2003 10:44 PM Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives better response time. But the same time w.r.t latch, session_cached_cursor=100 is giving positive impact. 1) session_cached_cursor=0 - more latches but good response time(2.60) 2) session_cached_cursor=100 - less # of latches but higher response time(2.87) Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2000 1.76 1.77 0 0 0 0 Execute 2000 0.84 0.74 0 0 0 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.60 2.51 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0TABLE ACCESS FULL T2 0TABLE ACCESS FULL T1 SELECT /*+ cached cursors 100 */FIRST_NAME,LAST_NAME,CUSTOMERID, COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2000 2.05 1.99 0 0 0 0 Execute 2000 0.82 0.74 0 0 0 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.87 2.73 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0TABLE ACCESS FULL T2 0TABLE ACCESS FULL T1 Program used to generate the above trace file. == alter session set SQL_TRACE=true; alter session set session_cached_cursors=0; declare type rc is ref cursor; C rc; n number :=0; begin n := dbms_utility.get_time; for i in 1 .. 2000 loop open C for select /*+ cached cursors 0 */ first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3
Re: Re[2]: Writing a delay in PL/SQL?
Another way would have been to use not autoextending datafiles and resumable operations during data load... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 4:54 PM Saturday, November 29, 2003, 10:44:26 PM, Khedr, Waleed ([EMAIL PROTECTED]) wrote: KW I'm curious why? some testing? Yes. Testing. I want a Data Pump job to run long enough for me to be able to play around in interactive mode from several different clients. I only have 28MB of data, and the load runs too fast for me to do much of anything. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Tanel Poder 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[4]: Writing a delay in PL/SQL?
Sunday, November 30, 2003, 10:34:25 AM, Tanel Poder ([EMAIL PROTECTED]) wrote: TP Another way would have been to use not autoextending datafiles and resumable TP operations during data load... I've actually done something similar, which worked for most of what I want to test. But now I want to observe a job actually running and doing something. Now I want to slow down the rate of insertion, but not stop it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
You still have to hit the library cache to execute a statement as it needs to be pinned in share mode, and unpinned when you finish with it. Library cache latch waits can be a symptom of excessive executions. Have you checked the library cache latch children to see if the load is evenly balanced, or whether there is a single library cache latch that is suffering most of the sleeps. Good news for 9.2 - v$sql, and a couple of others include the library cache child latch number, so you can see which objects are protected by the hot latch without having to use Steve's algorithm. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November 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] Sent: Sunday, November 30, 2003 1:29 PM What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 -- 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).
RE: Parse Vs Execute
Yes, you are correct. I was thinking of another cursor parm; I should have checked first. Jared On Sat, 2003-11-29 at 22:14, Richard Ji wrote: I thought the session_cached_cursors is dynamic and scope is session? This is on 8.1.7. I have used: alter session set session_cached_cursors=500; -Original Message- Sent: Sunday, November 30, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Sami, 'cached_cursors' is not a valid hint, at least not in 9i. Or at least, I can find no reference to it. And 'cached cursors' as it appears in the SQL is not a valid hint syntax. You need to set the session_cached_cursors value in the init.ora, and bounce the database. This parameter cannot be set dynamically, at least as of 9i. Jared On Sat, 2003-11-29 at 14:44, Sami wrote: Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives better response time. But the same time w.r.t latch, session_cached_cursor=100 is giving positive impact. 1) session_cached_cursor=0 - more latches but good response time(2.60) 2) session_cached_cursor=100 - less # of latches but higher response time(2.87) Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2000 1.76 1.77 0 0 0 0 Execute 2000 0.84 0.74 0 0 0 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.60 2.51 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0TABLE ACCESS FULL T2 0TABLE ACCESS FULL T1 SELECT /*+ cached cursors 100 */FIRST_NAME,LAST_NAME,CUSTOMERID, COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2000 2.05 1.99 0 0 0 0 Execute 2000 0.82 0.74 0 0 0 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.87 2.73 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0TABLE ACCESS FULL T2 0TABLE ACCESS FULL T1 Program used to generate the above trace file. == alter session set SQL_TRACE=true; alter session set session_cached_cursors=0; declare type rc is ref cursor; C rc; n number :=0; begin n := dbms_utility.get_time; for i in 1 .. 2000 loop open C for select /*+ cached cursors 0 */ first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where p.t1id=e.t1id and p.business_country_id=c.countryabbrev; close C; end loop; dbms_output.put_line( dbms_utility.get_time - n ); end; / alter session set session_cached_cursors=100; declare type rc is ref cursor; C rc; n number :=0; begin n := dbms_utility.get_time; for i in 1 .. 2000 loop --open C for select /*+ cached_cursors 100 */ * from dual; open C for select /*+ cached cursors 100 */ first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where p.t1id=e.t1id and p.business_country_id=c.countryabbrev; close C; end loop; dbms_output.put_line( dbms_utility.get_time - n ); end; / SQL @x Session altered. Session altered. 394 PL/SQL procedure
RE: OS Level Defrag
Thanks to all that replied. Fortunately, I have a good backup of the corrupted partition. Unfortunately, estimated recovery time will be 4 days. -Original Message- DENNIS WILLIAMS Sent: Saturday, 29 November 2003 7:54 AM To: Multiple recipients of list ORACLE-L Sujatha - If it helps, we have Tru64 and 8.1.6 here. My sys admin has defragged the disk quite a few times with no apparent ill effects. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 27, 2003 7:59 PM To: Multiple recipients of list ORACLE-L Hi, Does anyone here do an O/S level defrag of their Oracle filesystems??? Background: (Tru64/8.1.7.4) Sysadmin here were adamant that the Oracle domains were running out of extents and were highly fragmented (O/S level). DBA was adamant that the Oracle filesystems should not be defragmented. I lost the battle and the sysadmins are defragging the domains. I now have a corruption on a table partition with 100 million plus rows on a 50G datafile. I am wondering if the defrag has caused this corruption. The only way I can think of finding out is: Finding the approx date of the corruption using the query SELECT ROWID, LAST_COLUMN_OF_TABLE from TABLE_NAME(PARTITION); (which will do the full tablescan row by row). And then finding when the defrag utility was hitting the particular datafile that is corrupted. But this reasoning is flawed Does anyone have another method of trying to pinpoint if the O/S defrag caused the corruption Regards, Sujatha -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: Sujatha Madan 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: an article comparing Oracle to other databases
Well, there are plenty of objective detailed comparisons on www.oracle.com! :)) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Boivin, Patrice J Sent: Friday, November 28, 2003 3:04 AM To: Multiple recipients of list ORACLE-L http://www.ecommercetimes.com/perl/story/32200.html I have yet to see an objective, detailed comparison of Oracle, DB2 and SQL Server. From a technical (i.e. what can it do) as well as from an organisational (i.e. how is it to manage) point of view. Even 3rd party think tanks seem to walk on egg shells when evaluating software from major vendors, possibly to avoid alienating any of them. Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J 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: Pete Sharman 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: .NET, connection pooling and security .
Hi Jared, The users don't have to authenticate in the application because they've been setup in active directory. It may be similar to SAP, except we don't have the SAP developers in-house making production changes without telling anyone. That's why I wan't to lock it down. In the past the developers had full access to dev/qa/prod. I've removed full access to qa and prod. qa is the clean room before prod and prod is for application sql/dml only - not tweaking. They're looking for other alternative accesses. I've turned on auditing and have sent out emails to their mana-jerks when I see that they've accessed production with one of these user ids, but they don't see any problems and say it's all water under the bridge. I trust one or two of the developers to do some of the stuff (in dev first). They know the data better than I do, but not all developers are created equal... I seen some delete and update statements sent to me to run that are missing the where clauses... those people do stuff without telling me and then make a big stink about Oracle mysteriously losing data. I don't have the time to keep playing detective. I guess I should feel glad that this is the standard :) thanks. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:14 PM Steve, I'm not a web developer either, but I do know that this is a very common method of handling the database connections. Many 2 tier apps work this way as well. SAP for example. Unless you have influence on the architecture and can present a convincing argument, you best learn how to work with it. You don't give any details about the app either. Are users required to authenticate? If not, what would be the point of requiring db accounts for them? The number of users is important as well. Imagine a web app that services 250k users. Do you really want that many users in the data dictionary? Would you want the DDL overhead of creating/administering that many users? I'm considering some extremes, because there were no details provided. HTH Jared On Sat, 2003-11-29 at 19:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry 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: Jared Still 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:
RE: XML Sctructure
That works fine I don't know what the problem is .. as long as you have a unique root element, then there should be no problem. -Original Message- Sami Sent: Sunday, November 30, 2003 6:19 AM To: Multiple recipients of list ORACLE-L More than one line of what? Do you want to try something like below? ?xml version=1.0? !-- Exemplo de XML Endereco-- !DOCTYPE lista_endereco SYSTEM lista_endereco.dtd lista_endereco endereco titulo rodolfo do Carmo Andrietta/titulo primeironomeEriovaldo/primeironome ultimonomeAndrietta/ultimonome logradouroR Um/logradouro cidadeSao Paulo/cidade estadoSP/estado cep01680-000/cep /endereco endereco titulo rodolfo do Carmo Andrietta/titulo primeironomeEriovaldo/primeironome ultimonomeAndrietta/ultimonome logradouroR Um/logradouro cidadeSao Paulo/cidade estadoSP/estado cep01680-000/cep /endereco /lista_endereco -Original Message- ecaforum Sent: Sunday, November 30, 2003 3:39 AM To: Multiple recipients of list ORACLE-L Hi friend, HI know that this forum is about Oracle, but i have a doubt and i think thay someone can help me . I have the xml sctructure below. XML : ?xml version=1.0? !-- Exemplo de XML Endereco-- !DOCTYPE lista_endereco SYSTEM lista_endereco.dtd lista_endereco endereco titulo rodolfo do Carmo Andrietta/titulo primeironomeEriovaldo/primeironome ultimonomeAndrietta/ultimonome logradouroR Um/logradouro cidadeSao Paulo/cidade estadoSP/estado cep01680-000/cep /endereco /lista_endereco DTD !-- lista_endereco.dtd -- !ELEMENT lista_endereco (endereco) !ELEMENT endereco (titulo, primeironome, ultimonome, logradouro, cidade, estado, cep) !ELEMENT titulo (#PCDATA) !ELEMENT primeironome (#PCDATA) !ELEMENT ultimonome (#PCDATA) !ELEMENT logradouro (#PCDATA) !ELEMENT cidade (#PCDATA) !ELEMENT estado (#PCDATA) !ELEMENT cep (#PCDATA) My question is : How can i do for use DTD and put more than 1 line in XML ? Because like that, if i put one more line in xml it says that file is invalid. Any clue ? Thanks. Eriovaldo -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: ecaforum 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: Sami 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: nelson flores 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: .NET, connection pooling and security .
I like the idea of roles and that's what I'm trying to get at, but the app determines the user's authority prior to connecting to the database by looking at a key in active directory. The database connections have to connect as the highest level user possible, which is the application admin. I don't like that because it seems like a security hole. Like I said, I'm not real comfortable with Web security and may be making something out of nothing. thanks. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:44 PM Well ... in general it's the apps that manage the system security, and the DB users are there to prevent the app users from doing damage, but in general these two work in unison. I have not seen any decent ways of having the DB administer users without there being a serious overhead, in terms of administration duties, for the DBA (which is what Jared mentioned). I say that, given the information you provide, sticking with the two types of roles (owner and user) is the most adequate way. Why would you want to change this anyway? My 3.14159 pence worth. -Original Message- Jared Still Sent: Saturday, November 29, 2003 9:15 PM To: Multiple recipients of list ORACLE-L Steve, I'm not a web developer either, but I do know that this is a very common method of handling the database connections. Many 2 tier apps work this way as well. SAP for example. Unless you have influence on the architecture and can present a convincing argument, you best learn how to work with it. You don't give any details about the app either. Are users required to authenticate? If not, what would be the point of requiring db accounts for them? The number of users is important as well. Imagine a web app that services 250k users. Do you really want that many users in the data dictionary? Would you want the DDL overhead of creating/administering that many users? I'm considering some extremes, because there were no details provided. HTH Jared On Sat, 2003-11-29 at 19:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry 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: Jared Still 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: nelson flores
Re: .NET, connection pooling and security .
I'm going to start looking at OID. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 4:49 AM We are going the OID way because of these problems. Anyway here is a wild idea: Tell the web guys to use the user userid (he probably logged to the web application) with a standard password that is common to all of them and is supplied by the web application, the user does not see it. If you have an information security guy, teach him how to add users and grant the application user role. The schema owner password need to be a closely held secret of the dba group. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 5:49 AM I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry 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: Yechiel Adar 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: Steve Perry 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: .NET, connection pooling and security .
I like the idea of setting the client info. The consensus on the other stuff is that's just the way it is. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:34 PM On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Steve Perry 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: .NET, connection pooling and security .
Multi-Org in Oracle Applications works (well) with this client info setting and views having where clauses on client info. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 01, 2003 6:19 AM I like the idea of setting the client info. The consensus on the other stuff is that's just the way it is. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:34 PM On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Steve Perry 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
Re: .NET, connection pooling and security .
What is Multi-Org? Sounds like a brand of kitchen utensils? On 2003.12.01 00:39, Tanel Poder wrote: Multi-Org in Oracle Applications works (well) with this client info setting and views having where clauses on client info. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 01, 2003 6:19 AM I like the idea of setting the client info. The consensus on the other stuff is that's just the way it is. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:34 PM On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Steve Perry 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
Re: .NET, connection pooling and security .
Technically it's a means for doing row-level security in Oracle Apps (in functional side there's of course more). It's just a bunch of views on base tables. All base tables have org_id column in them and the views include a clause where they compare rows org_id to organization id taken from sessions client info. And Forms applications populate the client info during logon. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 01, 2003 7:54 AM What is Multi-Org? Sounds like a brand of kitchen utensils? On 2003.12.01 00:39, Tanel Poder wrote: Multi-Org in Oracle Applications works (well) with this client info setting and views having where clauses on client info. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 01, 2003 6:19 AM I like the idea of setting the client info. The consensus on the other stuff is that's just the way it is. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:34 PM On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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
