RE: SAME and separating disk and index tablespaces
Dave, during a 'db file sequential read', an index is _not_ accessed sequentially. An index is not a sequential structure, so reading from an index in order will cause multiple seeks on the index itself. And we're talking single user here regards, Hans Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 08 Oct 2003 16:29:25 -0800 Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created. I think the idea and philosophy behind SAME is noble - Use all available drives, so that you do not have localized hot-spots. But the implementation of SAME and how many volumes you need in your enviroment, is a function of your custom needs based on your system and application demands. When you over-simplify something, you lose the flexibility. The art factor here (which requires some planning) is in achieving a balance between simplicity, flexibility, performance, manageability and availability. Hope that helps, Gaja --- Hans de Git [EMAIL PROTECTED] wrote: Vikas, Spend an hour on reading this usenet thread: http://groups.google.nl/groups?hl=nllr=ie=UTF-8oe=UTF-8threadm=brjz8 .15%24707.245%40news.oracle.comrnum=1prev=/groups%3Fhl%3Dnl%26lr%3D%26 ie%3DUTF-8%26oe%3DUTF-8%26q%3Drogers%2Bseparate%2Bdata%2Bindex It will open your eyes about separating data/index. Still not sure about the redolog stream...Because of the sequential nature of redologfiles. I've read tests that 'prove' it doesn't matter much whether you separate your redolog from 'ordinary' datafiles or not. It does simplify things when you pure SAME. Regards, Hans Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 08 Oct 2003 09:54:30 -0800 Thanks Gaja ! Does it also make sense from a performance perspective (I/O issues due to concurrent access of index and data ) to separate them or is that point moot once you apply the SAME methodology ? -Original Message- Gaja Krishna Vaidyanatha Sent: Wednesday,
DB Parameters
Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Thanks for your help. Bye. ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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: DB Parameters
use bind variable -Original Message- Sent: 09 October 2003 16:29 To: Multiple recipients of list ORACLE-L Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Thanks for your help. Bye. ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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: Sinardy Xing 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: Find an unprintable character inside a column....
Steve, If you are patient, I guess that something like where dump(problem_column) like '%target hex%' should more or less answer your question. HTH SF - --- Original Message --- - From: Steve Main [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 08 Oct 2003 15:44:26 Hello list, I have an application that is choking on the following error, ...invalid character (Unicode: 0x19) was found in the element content... Does anyone know how I could go about searching for this invalid character? Thanks Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: SAME and separating disk and index tablespaces
Hans, Your statement is true except in the case of a fast full-index scan. But that's not my point. What I'm trying to say is: 1. In scenarios where response time is important, for example when you want to obtain the first n rows of a query result as quickly as possible, then access time may be as important as throughput. 2. Adding disks to a striped array only improves throughput, not access time. 3. Access time can be improved by parallel I/O execution on separate disk arrays. I'm trying to point out the difference between: 1. striping 10 disks into a single array 2. striping 10 disks into two arrays of 5 disks each In the first case, you get max throughput but because you only have one array, you cannot improve access time by parallelizing disk access. In the second case, you get half the throughput of the first case, but if you can parallelize disk access to both disks, your access time or response time to get the 1st row of the query result may be shorter. Regards, Dave [EMAIL PROTECTED] wrote: Dave, during a 'db file sequential read', an index is _not_ accessed sequentially. An index is not a sequential structure, so reading from an index in order will cause multiple seeks on the index itself. And we're talking single user here regards, Hans Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 08 Oct 2003 16:29:25 -0800 Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created. I think the idea and philosophy behind SAME is noble - Use all available drives, so that you do not have localized hot-spots. But the implementation of SAME and how many volumes you need in your enviroment, is a function of your custom needs based on your system and application demands. When you over-simplify something, you lose the flexibility. The art factor here (which requires some planning) is in achieving a balance between simplicity, flexibility, performance,
Re: SAME and separating disk and index tablespaces
Isn't this true when the query is a parallel query, i.e. when you're doing a parallel index range scan using a partitioned index? In such case, reading the index is the producer operation, and using the rowid to retrieve the row from the table is the consumer operation, and the data flow should be pipelined (inter-operation parallelism in Oracle speak) allowing concurrent access to both the index and the table. - Dave [EMAIL PROTECTED] wrote: If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. This implies that putting the index and table on separate logical drives will allow concurrent access to both the index and the table. From the perspective of a single transaction, this is not true. When an index is read and the resulting rowids are used to retrieve rows from a table: these operations do not occur concurrently. The index blocks are read, then the table blocks. Separating the table and index IO to different drives will not double the throughput or the access time. Given N drives, it would seem reasonable to expect the throughput for that single transaction to be faster if those N drives were in a single array, rather than 2 separate arrays assigned to two different logical volumes. This would be the case if the total data in the transaction were larger than the stripe size used if 2 arrays/volumes were used rather than 1. Jared Dave Hau [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/08/2003 04:19 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: SAME and separating disk and index tablespaces Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created. I think the idea and philosophy behind SAME is noble - Use all available drives, so that you do not have localized hot-spots. But the implementation of SAME and how many volumes you need in your enviroment, is a function of your custom needs based on your system and
RE: Find an unprintable character inside a column....
Yes, exactly Stephane - Non-printable characters like this are a proper pest in our environment, to the extent that I have exception reports running every night looking for them (cannot trust the users...). I have a small PL/SQL piece of code used to detect these things, if anyone wants it. peter edinburgh -Original Message- Sent: Thursday, October 09, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Steve, If you are patient, I guess that something like where dump(problem_column) like '%target hex%' should more or less answer your question. HTH SF - --- Original Message --- - From: Steve Main [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 08 Oct 2003 15:44:26 Hello list, I have an application that is choking on the following error, ...invalid character (Unicode: 0x19) was found in the element content... Does anyone know how I could go about searching for this invalid character? Thanks Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter 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: RE: Find an unprintable character inside a column....
Peter, i would be interested in that. can u mail it to me ? Jp. 09-10-2003 18:29:33, Robson, Peter [EMAIL PROTECTED] wrote: I have a small PL/SQL piece of code used to detect these things, if anyone wants it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna 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).
Re: Find an unprintable character inside a column....
Peter, i would be interested in that. can you mail it to me ? Dias Costa Robson, Peter wrote: Yes, exactly Stephane - Non-printable characters like this are a proper pest in our environment, to the extent that I have exception reports running every night looking for them (cannot trust the users...). I have a small PL/SQL piece of code used to detect these things, if anyone wants it. peter edinburgh -Original Message- Sent: Thursday, October 09, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Steve, If you are patient, I guess that something like where dump(problem_column) like '%target hex%' should more or less answer your question. HTH SF - --- Original Message --- - From: Steve Main [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 08 Oct 2003 15:44:26 Hello list, I have an application that is choking on the following error, ...invalid character (Unicode: 0x19) was found in the element content... Does anyone know how I could go about searching for this invalid character? Thanks Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dias Costa 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: DB Parameters
Hi, What is volume of ur table, and 4 records that u want to delete is what % of total records. One thing that u can try is introduce index hint in delete statement. The optimal solution can be find out only if know exact statement and volume of tables and indexes. Also can u check the size of tablespace used by this table? And calculate the total size this table should use with current volume of records. Cheers, Manoj Kumar Jha A transcendentalist engaged in auspicious activities does not meet with destruction either in this world or in the spiritual world; one who does good, is never overcome by evil. Walid Alkaakati [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] abeit.com cc: Sent by: Subject: DB Parameters [EMAIL PROTECTED] 10/09/03 01:59 PM Please respond to ORACLE-L Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Thanks for your help. Bye. ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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). DISCLAIMER: The information contained in this message is intended only and solely for the addressed individual or entity indicated in this message and for the exclusive use of the said addressed individual or entity indicated in this message (or responsible for delivery of the message to such person) and may contain legally privileged and confidential information belonging to Tata Consultancy Services. It must not be printed, read, copied, disclosed, forwarded,
RE: A quick note
At the risk of being dragged off and strung up (and keeping it brief as I am very busy), I really thought it was quite good. Maybe it is the change is as good as a rest syndrome. Quite intuitive and fairly easy to get dramatic performance gains by parameter tweaking and runstats (equiv. of analyzing but takes up much less time). Quite good command line tools for index advising and explains etc. Was particularly impressed at how easy it was to set up EEE (equiv. of Parallel Server I suppose) Downside is there is very little out there book-wise (h, wonder if there is a niche market there - DB2 for Oracle DBAs ??) and backups are potential pitfalls. We have had several issues using Flash Copy on a Shark and the archive logging is a bitch to set up. Just my 2p (bugger the Euro) worth. Lee -Original Message- Sent: 08 October 2003 22:45 To: Multiple recipients of list ORACLE-L So Lee... As an experienced Oracle guy, what are your thoughts about DB2? -Original Message- Sent: Wednesday, October 08, 2003 12:40 PM To: Multiple recipients of list ORACLE-L I suggest you buy your mushrooms from the supermarket like the rest of us instead of picking them in those strange fields my friend -Original Message- Sent: 08 October 2003 17:25 To: Multiple recipients of list ORACLE-L Hey good to see you back again Lee. The big change here is that all Oracle knowledge has been cyber-engineered into an automoton called Tanel Poder. Any query to the list is routed by a big fast connection into a huge server with hundreds of CPU's and unlimited memory An almost instantaneous reply is automatically generated , often complete with complex examples and code cuts to demonstrate the point. The best part is that is that the machine is programmed to exibit almost humanistic type responses (occasionally) John -Original Message- Robertson Lee - lerobe Sent: 08 October 2003 11:34 To: Multiple recipients of list ORACLE-L Hi everyone, I'm back in the land of the living after spending a year on a DB2 EEE project. I've just started working on a 9i RAC solution so the questions will soon be flooding in. I will be lucky if I can even remember to spell spqlusl , I mean sqlplus :-) Cheers Lee ** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robertson Lee - lerobe 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: Hallas, John, Tech Dev 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: Robertson Lee - lerobe 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: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services--
Re: RE: Cary's Book - new topic
Connor, you're the best! On 2003.10.08 21:09, Connor McDonald wrote: Cary, you're intention is good, but you need to take the more effective (Dirty Harry) approach: Customer: It's Slow Me: What is? Customer: The application. Make it fast. Me: Maybe I can make it fast, maybe I can't. You've got to ask yourself one question. Do I feel lucky today? Well do ya punk? (at which point out comes the invoice...) :-) --- Cary Millsap [EMAIL PROTECTED] wrote: These are the BEST projects to be on. You took a different path than I would have at the second Me line. I would shoot for: Customer: It's Slow Me: What is? Customer: The application. Make it fast. Me: Show me. Customer: Okay, come see. Then your job becomes to get a 10046/12 trace on what you're watching. From there, it's all downhill. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 2:24 PM To: Multiple recipients of list ORACLE-L how many projects actually have SLAs? Ive been on 5 projects and none of them have had them. Its always been. Customer: 'It's Slow' Me: What is? Customer: The application. Make it fast. Me: Define fast. Customer: As fast as possible. Do it now. From: Wolfgang Breitling [EMAIL PROTECTED] Date: 2003/10/07 Tue PM 02:59:55 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Cary's Book - new topic Good point. I suppose this gets into the realm of perceived response time. Some applications break long transactions into several user interactions to hide the real response time. The application still makes its SLA defined as 90% of transactions complete in 3 seconds while the real transaction takes a lot longer. However, the user is kept busy and you get into that perception thing. I know that if I see a traffic jam, I look for ways to detour around it. Even it I don't save any time (there is no way of telling really), I have at least the impression that I'm doing something, that I'm in charge, rather sitting passively in the jam crawling along, waiting for something the clear up. At 12:39 PM 10/7/2003, you wrote: Also, if we are to really address the business case as you suggest then the definition should also include the quality of the response. If the response is quick but incomplete and the user has to ask 10 questions to get at the one real answer he's after then what good is a fast response time? -Original Message- Sent: Tuesday, October 07, 2003 12:09 PM To: Multiple recipients of list ORACLE-L Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Cary Millsap 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). = Connor
Concurrent Manager
Hi , In Oracle Apps why concurrent manager and report server should be indatabase tier when other application servers are in application tier.? Thx
Re: SAME and separating disk and index tablespaces
- Original Message - the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. YOU can do that. But Oracle doesn't. Strictly: first indexes, then data pointed to by those indexes. So, no point from the performance perspective in separating. From other perspectives, heaps of reasons. But NOT from the performance perspective. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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 Db Parameters
Hi Can You please give an example on using bund varables .. THANKS
Re: EMC Snapshot Technology
I don't use snapshots, but have used EMC BCV's in the past and now use IBM's Flashcopy for backups. I backup 1TB db in ~20 mins using Flashcopy. Then I take it off to tape, i.e. filesystems monted on a TSM Backup server, hence no resources needed from the production server :). hth, Gene [EMAIL PROTECTED] 10/08/03 09:39PM We are implementing Oracle RAC on two Windows nodes, connected to an EMCSAN. We'll also have a failover sight. We are using S.A.M.E. disk configuration, with only one logical volume,and backups/archivelogs dumping to another volume.The SAN is an EMC Clariion CX400. I immediately vetoed snapshots, opting for RMAN, but I'm now taking asecond look. Does anyone use the snapshot technology as a solution for full backups? Thanks,Jeff-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: jwiegand INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: DB Parameters
Walid TRUNCATE Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 09, 2003 3:29 AM To: Multiple recipients of list ORACLE-L Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Thanks for your help. Bye. ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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: 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).
Re: RE: BROKEN_PIPE during Weblogic J2EE deployment
do they mean the ora -03113 end of file communication error(number may be off)? Alot of stuff causes that error. that is a very vague response on their part. From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/10/08 Wed PM 11:34:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: BROKEN_PIPE during Weblogic J2EE deployment Apparently BROKEN_PIPE is a pretty generic error. Quite a few problems can cause this error. One problem BEA mentions is if the database closes the connection. Does anyone have a suggestion for what I should reply to this? Are there any situations under which Oracle would close a connection? Would that event appear in any log? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, October 06, 2003 1:34 PM To: Multiple recipients of list ORACLE-L As Weblogic is not an Oracle product, it is not surprising that Metalink returned nothing. Try your query on Google. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, October 06, 2003 10:19 AM To: Multiple recipients of list ORACLE-L Our web administrator was deploying a new J2EE application to production and received the error broken_pipe. We are using the thin client. Apparently Weblogic tries to verify existence the tables the application uses. The deployment was otherwise successful in that the error occurred several times when deployment was retried but testing the app worked fine. They feel it was a database error, but I don't see anything on my end. Naturally they are nervous about this error just going into a critical new application. Apparently if the database was down, this is the error returned on the Weblogic side, but the database was fine and I couldn't find any errors. I searched for broken_pipe on Metalink and the search returned empty. Has anyone had any experience with this Weblogic error? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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: MacGregor, Ian A. 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: 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: [EMAIL PROTECTED] 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).
desupport date for 8i
FYI... Oracle extended the desupport date for 8i by a year, end of error correction support is now 31-dec-2004. They posted that end of ECS for 9i Rel 2 will be 31-dec-2005, I am doubtful about that, only 1 year lag time between two terminal releases? 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).
RE: how to keep statistics up to date for CBO
Bob,
I do the following:
First, alter all tables turning monitoring on: alter table {table name}
monitoring;
Monitoring says:
Specify MONITORING if you want Oracle to collect modification statistics on
table. These statistics are estimates of the number of rows affected by DML
statements over a particular period of time. They are available for use by
the optimizer or for analysis by the user.
Then use the following. It recalculates stats for those tables that have
been changed enough to warrant stats. The User_Tab_Modifications table will
hold a record if 10% of the table was changed. I've been using this for a
while now, and it seems to be working fine. As you can see, I have a
database table that I insert a record into so I can see how much work is
done. I'm happy with it. And I'm not gathering stats for tables that I
don't need to. I run this job daily.
Hope this helps.
PROCEDURE WTWDBA.Wtw_Gather_Statistics IS
/*
Procedure Name : Wtw_Gather_Statistics
Author : Tom Mercadante
Mercadante Systems Design
June 14, 2001
Purpose:
This Package will use the System DBMS_STATS package to gather statistics
for both tables and indexes.
*/
loc_table_name USER_TABLES.TABLE_NAME%TYPE;
loc_index_name USER_INDEXES.INDEX_NAME%TYPE;
tbl_count NUMBER := 0;
indx_count NUMBER := 0;
loc_start_time DATE;
CURSOR c1 IS
SELECT ut.table_name FROM USER_TABLES UT, USER_TAB_MODIFICATIONS UTM
WHERE UT.TABLE_NAME = UTM.table_name;
CURSOR c2 IS
SELECT index_name FROM USER_INDEXES
WHERE table_name = loc_table_name;
BEGIN
loc_start_time := SYSDATE;
-- Gather statistics on tables
OPEN c1;
LOOP
FETCH c1 INTO loc_table_name;
EXIT WHEN c1%NOTFOUND;
dbms_stats.gather_table_stats('WTWDBA',loc_table_name);
tbl_count := tbl_count + 1;
-- Gather statistics on indexes
OPEN c2;
LOOP
FETCH c2 INTO loc_index_name;
EXIT WHEN c2%NOTFOUND;
dbms_stats.gather_index_stats('WTWDBA',loc_index_name);
indx_count := indx_count + 1;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
-- insert a record into the job log
INSERT INTO WTW_JOB_LOG(JOB_NAME, RUN_DATE, START_TIME,
END_TIME, MSG_TXT)
VALUES('Wtw_Gather_Statistics',SYSDATE,loc_start_time,
SYSDATE,INITCAP('SCHEMA Stats Complete') ||
CHR(10) ||
tbl_count || INITCAP(' TABLES Analyzed ') ||
CHR(10) ||
indx_count || INITCAP(' INDEXES Analyzed'));
COMMIT;
END;
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, October 08, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
How does one keep CBO statistics for an applications base tables up to
date?
We are about to implement the CBO any must read documents.
Many thanks
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
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: Mercadante, Thomas F
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: RE: Cary's Book - new topic
Title: RE: RE: Cary's Book - new topic This comment coming from Mladen means something ... 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: Mladen Gogala [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 7:59 AM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Cary's Book - new topic Connor, you're the best! **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.**5
Shared Storage and backups
Oracle documentation discusses multiplexing control files, data files, redo log files across multiple volumes in a RAID array. How do you handle this if your using shared storage? Where you just have one or a few logical volumes. has anyone developed a backup plan for this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Concurrent Manager
Not sure why you would necessarily want the reports server on the database tier... Concurrent managers wake up periodically (like 60 or 90 seconds), query their tables to see if there are any jobs to run... run them if there are and then go back to sleep. If they are all on the same tier, the throughput is faster, and there is no network overhead. The more concurrent jobs that you have, the more that this has the potential to become an issue. Also, the database maintains the status of the concurrent managers (are they up, is it their work shift, are they sleeping...) and if the communication gets off (for example if you shut down the database, then something happens on the CM tier (Windows is good at things weirding out with the CM) to shut down the managers, then bring up the database... the database thinks the managers are up, they aren't, then sometimes when you try to start the managers, they won't because the database thinks they already are... and you have to run CMCLEAN to make the database forget the old status. WE have our concurrent managers on the apps tier, and are working towards migrating them to the database tier because performance is so horrendous. April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ \ \ \ \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message-From: Sultan Syed [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 7:11 AMTo: Multiple recipients of list ORACLE-LSubject: Concurrent Manager Hi , In Oracle Apps why concurrent manager and report server should be indatabase tier when other application servers are in application tier.? Thx The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
RE: RE: Cary's Book - new topic
But what !? Wedding bells? Mike -Original Message- Sent: 09 October 2003 12:59 To: Multiple recipients of list ORACLE-L This comment coming from Mladen means something ... 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- mailto:[EMAIL PROTECTED] ] Sent: Thursday, October 09, 2003 7:59 AM To: Multiple recipients of list ORACLE-L Connor, you're the best! 5 E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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: RE: how to keep statistics up to date for CBO
does monitoring have any real overhead in a high transaction system?
From: Mercadante, Thomas F [EMAIL PROTECTED]
Date: 2003/10/09 Thu AM 08:59:33 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: how to keep statistics up to date for CBO
Bob,
I do the following:
First, alter all tables turning monitoring on: alter table {table name}
monitoring;
Monitoring says:
Specify MONITORING if you want Oracle to collect modification statistics on
table. These statistics are estimates of the number of rows affected by DML
statements over a particular period of time. They are available for use by
the optimizer or for analysis by the user.
Then use the following. It recalculates stats for those tables that have
been changed enough to warrant stats. The User_Tab_Modifications table will
hold a record if 10% of the table was changed. I've been using this for a
while now, and it seems to be working fine. As you can see, I have a
database table that I insert a record into so I can see how much work is
done. I'm happy with it. And I'm not gathering stats for tables that I
don't need to. I run this job daily.
Hope this helps.
PROCEDURE WTWDBA.Wtw_Gather_Statistics IS
/*
Procedure Name : Wtw_Gather_Statistics
Author : Tom Mercadante
Mercadante Systems Design
June 14, 2001
Purpose:
This Package will use the System DBMS_STATS package to gather statistics
for both tables and indexes.
*/
loc_table_name USER_TABLES.TABLE_NAME%TYPE;
loc_index_name USER_INDEXES.INDEX_NAME%TYPE;
tbl_count NUMBER := 0;
indx_count NUMBER := 0;
loc_start_time DATE;
CURSOR c1 IS
SELECT ut.table_name FROM USER_TABLES UT, USER_TAB_MODIFICATIONS UTM
WHERE UT.TABLE_NAME = UTM.table_name;
CURSOR c2 IS
SELECT index_name FROM USER_INDEXES
WHERE table_name = loc_table_name;
BEGIN
loc_start_time := SYSDATE;
-- Gather statistics on tables
OPEN c1;
LOOP
FETCH c1 INTO loc_table_name;
EXIT WHEN c1%NOTFOUND;
dbms_stats.gather_table_stats('WTWDBA',loc_table_name);
tbl_count := tbl_count + 1;
-- Gather statistics on indexes
OPEN c2;
LOOP
FETCH c2 INTO loc_index_name;
EXIT WHEN c2%NOTFOUND;
dbms_stats.gather_index_stats('WTWDBA',loc_index_name);
indx_count := indx_count + 1;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
-- insert a record into the job log
INSERT INTO WTW_JOB_LOG(JOB_NAME, RUN_DATE, START_TIME,
END_TIME, MSG_TXT)
VALUES('Wtw_Gather_Statistics',SYSDATE,loc_start_time,
SYSDATE,INITCAP('SCHEMA Stats Complete') ||
CHR(10) ||
tbl_count || INITCAP(' TABLES Analyzed ') ||
CHR(10) ||
indx_count || INITCAP(' INDEXES Analyzed'));
COMMIT;
END;
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, October 08, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
How does one keep CBO statistics for an applications base tables up to
date?
We are about to implement the CBO any must read documents.
Many thanks
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
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: Mercadante, Thomas F
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: [EMAIL PROTECTED]
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
RE: EMC Snapshot Technology
Gene, What happens when you need to perform a recovery from the Flashcopy backup? Is Flashcopy done while the database is open or closed? If the database is open, then I assume that you would need to perform an incomplete recovery? Just curious. I am in the middle of setting up new IBM/AIX boxes, and we will using either EMC or IBM Shark disk. Flashcopy was mentioned as a possible solution for backups. But I don't like being boxed in to performing an incomplete recovery. Tom Mercadante Oracle Certified Professional -Original Message-From: Gene Sais [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 8:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: EMC Snapshot Technology I don't use snapshots, but have used EMC BCV's in the past and now use IBM's Flashcopy for backups. I backup 1TB db in ~20 mins using Flashcopy. Then I take it off to tape, i.e. filesystems monted on a TSM Backup server, hence no resources needed from the production server :). hth, Gene [EMAIL PROTECTED] 10/08/03 09:39PM We are implementing Oracle RAC on two Windows nodes, connected to an EMCSAN. We'll also have a failover sight. We are using S.A.M.E. disk configuration, with only one logical volume,and backups/archivelogs dumping to another volume.The SAN is an EMC Clariion CX400. I immediately vetoed snapshots, opting for RMAN, but I'm now taking asecond look. Does anyone use the snapshot technology as a solution for full backups? Thanks,Jeff-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: jwiegand INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: BROKEN_PIPE from Weblogic
Agreed. By the way, since my original posting, this error has occurred several more times. Yes, it is a catch-all error that sounds a lot like an ORA-3113. Since they using the Oracle thin JDBC driver, which talks directly to the listener, Net8 isn't involved. If the server is shut down or crashes, apparently this error is also returned, like an ORA-3113. But the server hasn't crashed or shut down. This is a catch-all error and BEA has a long list of things that can cause this error. The database can cause this error under a few circumstances. Like the database closed the connection. Naturally the developers turn to the DBA at that point. At this point I just want to know what to say when they ask did the Oracle server close the connection? In my years of working with Oracle I haven't found it to spontaneously close connections. But how can I prove that Oracle isn't closing the connection? Can anyone think of a log that would be written to if it did? Should the developers be capturing more information in their Java code? I can recall back in the days of novice C programmers writing Oracle database calls that new programmers would not check error conditions and if an error occurred the program would continue merrily along, processing garbage. Apparently some of our other Java programs are coded to retry the connection if this occurs. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 09, 2003 7:41 AM To: Multiple recipients of list ORACLE-L do they mean the ora -03113 end of file communication error(number may be off)? Alot of stuff causes that error. that is a very vague response on their part. From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/10/08 Wed PM 11:34:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: BROKEN_PIPE during Weblogic J2EE deployment Apparently BROKEN_PIPE is a pretty generic error. Quite a few problems can cause this error. One problem BEA mentions is if the database closes the connection. Does anyone have a suggestion for what I should reply to this? Are there any situations under which Oracle would close a connection? Would that event appear in any log? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, October 06, 2003 1:34 PM To: Multiple recipients of list ORACLE-L As Weblogic is not an Oracle product, it is not surprising that Metalink returned nothing. Try your query on Google. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, October 06, 2003 10:19 AM To: Multiple recipients of list ORACLE-L Our web administrator was deploying a new J2EE application to production and received the error broken_pipe. We are using the thin client. Apparently Weblogic tries to verify existence the tables the application uses. The deployment was otherwise successful in that the error occurred several times when deployment was retried but testing the app worked fine. They feel it was a database error, but I don't see anything on my end. Naturally they are nervous about this error just going into a critical new application. Apparently if the database was down, this is the error returned on the Weblogic side, but the database was fine and I couldn't find any errors. I searched for broken_pipe on Metalink and the search returned empty. Has anyone had any experience with this Weblogic error? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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: MacGregor, Ian A. 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
RE: DB Parameters
Lee? -Original Message- Walid Alkaakati Sent: Thursday, October 09, 2003 4:29 AM To: Multiple recipients of list ORACLE-L Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Thanks for your help. Bye. ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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: Henry Poras 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: desupport date for 8i
never mind found it, d Boivin, Patrice J wrote: FYI... Oracle extended the desupport date for 8i by a year, end of error correction support is now 31-dec-2004. They posted that end of ECS for 9i Rel 2 will be 31-dec-2005, I am doubtful about that, only 1 year lag time between two terminal releases? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: desupport date for 8i
Patrice, where did you find that, i'm not having much luck under desupport notices. joe Boivin, Patrice J wrote: FYI... Oracle extended the desupport date for 8i by a year, end of error correction support is now 31-dec-2004. They posted that end of ECS for 9i Rel 2 will be 31-dec-2005, I am doubtful about that, only 1 year lag time between two terminal releases? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: SAME and separating disk and index tablespaces
Title: RE: SAME and separating disk and index tablespaces Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- From: vikas kawatra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created. I think the idea and philosophy behind SAME is noble - Use all available drives, so that you do not have localized hot-spots. But the implementation of SAME and how many volumes you need in your enviroment, is a function of your custom needs based on your system and application demands. When you over-simplify something, you lose the flexibility. The art factor here (which requires some planning) is in achieving a balance between simplicity, flexibility, performance, manageability and availability. Hope that helps, Gaja --- Hans de Git [EMAIL PROTECTED] wrote: Vikas, Spend an hour on reading this usenet thread: http://groups.google.nl/groups?hl=nl==UTF-8=UTF-8=brjz8 .15%24707.245%40news.oracle.comrnum=1prev=/groups%3Fhl%3Dnl%26lr%3D%26
RE: how to keep statistics up to date for CBO
Thank you very much Tom. That gives me an excellent starting point. I'll
begin to implement this in a devel environment and get a feel for it.
Thanks again!
bob
-Original Message-
Sent: Thursday, October 09, 2003 9:00 AM
To: Multiple recipients of list ORACLE-L
Bob,
I do the following:
First, alter all tables turning monitoring on: alter table {table name}
monitoring;
Monitoring says:
Specify MONITORING if you want Oracle to collect modification
statistics on
table. These statistics are estimates of the number of rows affected by
DML
statements over a particular period of time. They are available for use
by
the optimizer or for analysis by the user.
Then use the following. It recalculates stats for those tables that
have
been changed enough to warrant stats. The User_Tab_Modifications table
will
hold a record if 10% of the table was changed. I've been using this for
a
while now, and it seems to be working fine. As you can see, I have a
database table that I insert a record into so I can see how much work is
done. I'm happy with it. And I'm not gathering stats for tables that I
don't need to. I run this job daily.
Hope this helps.
PROCEDURE WTWDBA.Wtw_Gather_Statistics IS
/*
Procedure Name : Wtw_Gather_Statistics
Author : Tom Mercadante
Mercadante Systems Design
June 14, 2001
Purpose:
This Package will use the System DBMS_STATS package to gather
statistics
for both tables and indexes.
*/
loc_table_name USER_TABLES.TABLE_NAME%TYPE;
loc_index_name USER_INDEXES.INDEX_NAME%TYPE;
tbl_count NUMBER := 0;
indx_count NUMBER := 0;
loc_start_time DATE;
CURSOR c1 IS
SELECT ut.table_name FROM USER_TABLES UT, USER_TAB_MODIFICATIONS UTM
WHERE UT.TABLE_NAME = UTM.table_name;
CURSOR c2 IS
SELECT index_name FROM USER_INDEXES
WHERE table_name = loc_table_name;
BEGIN
loc_start_time := SYSDATE;
-- Gather statistics on tables
OPEN c1;
LOOP
FETCH c1 INTO loc_table_name;
EXIT WHEN c1%NOTFOUND;
dbms_stats.gather_table_stats('WTWDBA',loc_table_name);
tbl_count := tbl_count + 1;
-- Gather statistics on indexes
OPEN c2;
LOOP
FETCH c2 INTO loc_index_name;
EXIT WHEN c2%NOTFOUND;
dbms_stats.gather_index_stats('WTWDBA',loc_index_name);
indx_count := indx_count + 1;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
-- insert a record into the job log
INSERT INTO WTW_JOB_LOG(JOB_NAME, RUN_DATE, START_TIME,
END_TIME, MSG_TXT)
VALUES('Wtw_Gather_Statistics',SYSDATE,loc_start_time,
SYSDATE,INITCAP('SCHEMA Stats Complete')
||
CHR(10) ||
tbl_count || INITCAP(' TABLES Analyzed
') ||
CHR(10) ||
indx_count || INITCAP(' INDEXES Analyzed'));
COMMIT;
END;
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, October 08, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
How does one keep CBO statistics for an applications base tables up to
date?
We are about to implement the CBO any must read documents.
Many thanks
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
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: Mercadante, Thomas F
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: Bob Metelsky
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
RE: SAME and separating disk and index tablespaces
Gaja Krishna Vaidyanatha scribbled on the wall in glitter crayon: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! however as was mentioned before, i do separate them for ease of operational maintainence. things like backups, rebuilds, and all the other things to recover from user input.;-) so call me old fashioned, just don't call me late for the food.;-) PS hi Gaja.;-) -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] The grand aim of all science is to cover the greatest number of empirical facts by logical deduction from the smallest number of hypotheses or axioms. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: where can I find sample db creation script (9.2.0.1.0 installation)?
Yes Im on Windows xp the script names and dir structure should be the same though... Here is buildall.sql buildall.sql spool \build.log SET TERMOUT OFF SET ECHO OFF connect internal @@build_db.sql @C:\oracle\ora81\RDBMS\admin\catalog.sql @C:\oracle\ora81\RDBMS\admin\catsnmp.sql connect internal @C:\oracle\ora81\RDBMS\admin\catexp7.sql @C:\oracle\ora81\RDBMS\admin\catproc.sql @@scott.sql connect internal @@demo.sql connect system/manager @C:\oracle\ora81\RDBMS\admin\catdbsyn.sql @C:\oracle\ora81\SQLPLUS\admin\pupbld.sql connect internal @C:\oracle\ora81\RDBMS\admin\caths.sql @C:\oracle\ora81\RDBMS\admin\oramts.sql connect internal alter rollback segment rb_temp offline; shutdown; spool off ## ## build_db.sql ## -- -- $Header: build_db.sql 1.1 94/10/18 15:55:37 gdudey Osddesktop/netware $ Copyr (c) 1994 Oracle -- -- This file must be run out of the directory containing the -- initialization file. startup nomount pfile=\%AVAILABLE_SID%\pfile\initsmpl.ora -- Create database create database %AVAILABLE_SID% controlfile reuse logfile '%seedl_path%\%AVAILABLE_SID%\redo01.log' size %seedl_size%M reuse, '%seedl_path%\%AVAILABLE_SID%\redo02.log' size %seedl_size%M reuse, '%seedl_path%\%AVAILABLE_SID%\redo03.log' size %seedl_size%M reuse, '%seedl_path%\%AVAILABLE_SID%\redo04.log' size %seedl_size%M reuse datafile '%seedd_path%\%AVAILABLE_SID%\system01.dbf' size %seedd_sysi_size%M reuse autoextend on next %seedd_syse_size%M maxsize %seedd_sysm_size%M character set %seed_charset%; create rollback segment rb_temp storage (initial 100 k next 250 k); -- Create additional tablespaces ... -- USERs: Create user sets this as the default tablespace -- TEMP: Create user sets this as the temporary tablespace -- RBS: For rollback segments create tablespace users datafile '%seedd_path%\%AVAILABLE_SID%\users01.dbf' size %seedd_usri_size%M reuse autoextend on next %seedd_usre_size%M maxsize %seedd_usrm_size%M; create tablespace rbs datafile '%seedd_path%\%AVAILABLE_SID%\rbs01.dbf' size %seedd_rbsi_size%M reuse autoextend on next %seedd_rbse_size%M maxsize %seedd_rbsm_size%M; create temporary tablespace temp tempfile '%seedd_path%\%AVAILABLE_SID%\temp01.dbf' size %seedd_tmpi_size%M reuse autoextend on next %seedd_tmpe_size%M maxsize %seedd_tmpm_size%M; create tablespace oem_repository datafile '%seedd_path%\%AVAILABLE_SID%\oemrep01.dbf' size %seedd_usri_size%M reuse autoextend on next %seedd_usre_size%M maxsize %seedd_usrm_size%M; create tablespace indx datafile '%seedd_path%\%AVAILABLE_SID%\indx01.dbf' size %seedd_tmpi_size%M reuse autoextend on next %seedd_tmpe_size%M maxsize %seedd_tmpm_size%M; -- next %seedd_usre_size%M maxsize %seedd_usrm_size%M; alter rollback segment rb_temp online; -- Change the SYSTEM users' password, default tablespace and -- temporary tablespace. alter user system temporary tablespace temp; alter user system default tablespace users; -- Create 16 rollback segments. Allows 16 concurrent users with open -- transactions updating the database. This should be enough. create public rollback segment rb1 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb2 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb3 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb4 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb5 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb6 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb7 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb8 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb9 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb10 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb11 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb12 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb13 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb14 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb15 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb16 storage(initial 50K next 250K) tablespace rbs; # -Original Message- Sent: Wednesday, October 08, 2003 10:40 PM To: Multiple recipients of list ORACLE-L installation)? Hi: I am on unix and I can not find the file you menetioned. Looks like your box is Microsoft one. essex$ pwd /oracle/product/9.2.0.1.0/rdbms/admin essex$ ls -l b* b*: No such file or directory Guang -- Please see the official
RE: desupport date for 8i
Patrice Oracle's goal is to create a major release every 18 months, and someone figured out they historically have been pretty much on track. So then desupport intervals should theoretically be about 18 months apart. My guess is that 8i is very popular among large clients so its desupport is being delayed due to popular demand. Of course, 10g is just around the corner, so out with the old, in with the new. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 09, 2003 7:49 AM To: Multiple recipients of list ORACLE-L FYI... Oracle extended the desupport date for 8i by a year, end of error correction support is now 31-dec-2004. They posted that end of ECS for 9i Rel 2 will be 31-dec-2005, I am doubtful about that, only 1 year lag time between two terminal releases? 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: 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).
RE: DB Parameters
No -Original Message- Sent: 09 October 2003 14:15 To: Multiple recipients of list ORACLE-L Lee? -Original Message- Walid Alkaakati Sent: Thursday, October 09, 2003 4:29 AM To: Multiple recipients of list ORACLE-L Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Thanks for your help. Bye. ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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: Henry Poras 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). ** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robertson Lee - lerobe 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).
Temp Tablespace
Title: RE: SAME and separating disk and index tablespaces All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message-From: Loughmiller, Greg [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 9:30 AMTo: Multiple recipients of list ORACLE-LSubject: RE: SAME and separating disk and index tablespaces Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of "human effort" in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older "rules of thumb" aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- From: vikas kawatra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that "thou shalt always separate indexes from tables" may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created. I
Re: DB Parameters
You can try with the _db_perf_silver_bullet=42, which is the straightforward approach, or you can be a backward hick like me and trace your session with 10046, see what events are you waiting for and adjust your application, OS and instance parameters to really solve the problem. On Thu, 2003-10-09 at 04:29, Walid Alkaakati wrote: Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Thanks for your help. Bye. ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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).
Re: OT - Microsoft Security Bulletin MS03-040 (828750)
You should try with RHN or Red Carpet. They work just fine. On Wed, 2003-10-08 at 18:19, [EMAIL PROTECTED] wrote: Microsoft issued this critical update on the 3rd, which I found out about, when I did a Windows Update from my IE. A number of security issues have been identified in Microsoft Internet Explorer that could allow an attacker to compromise a Microsoft Windows-based system and then take a variety of actions. For example, an attacker could run programs on your computer when you are viewing a Web page. This vulnerability affects all computers that have Internet Explorer installed. (You do not have to be using Internet Explorer as your Web browser to be affected by this issue.) You should help protect your computer by installing this update from Microsoft. And now, after applying this patch, I am no longer able to run Windows Update. Doesn't windows update run programs on my computer when I am viewing the web page? I wonder if they locked themselves out ;-) , or is the site down? I wish to know if I am the only one experiencing this. Regards Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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).
Re: Concurrent Manager
Thanks April,
RE: desupport date for 8i
Joe, Look at Doc IDs 250629.1 148054.1 - the extension is for some platforms only Regards -Original Message- Sent: 09 October 2003 14:29 To: Multiple recipients of list ORACLE-L Patrice, where did you find that, i'm not having much luck under desupport notices. joe Boivin, Patrice J wrote: FYI... Oracle extended the desupport date for 8i by a year, end of error correction support is now 31-dec-2004. They posted that end of ECS for 9i Rel 2 will be 31-dec-2005, I am doubtful about that, only 1 year lag time between two terminal releases? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Nigel Bishop 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: desupport date for 8i
My Headlines screen, KnowledgeBase section, top two items (I got two copies because I have two CSIs ? not sure). Or, note 148054.1 Patrice -Original Message- Sent: Thursday, October 09, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Patrice, where did you find that, i'm not having much luck under desupport notices. joe Boivin, Patrice J wrote: FYI... Oracle extended the desupport date for 8i by a year, end of error correction support is now 31-dec-2004. They posted that end of ECS for 9i Rel 2 will be 31-dec-2005, I am doubtful about that, only 1 year lag time between two terminal releases? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.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).
RE: A quick note
Lee - Interesting you were using IBM Flashcopy and you had problems. I am using FlashCopy v2 and have not seen any problems. Can you enlighten me what I should look out for? Thanks, Gene [EMAIL PROTECTED] 10/09/03 06:44AM At the risk of being dragged off and strung up (and keeping it brief as I amvery busy), I really thought it was quite good. Maybe it is the "change isas good as a rest" syndrome.Quite intuitive and fairly easy to get dramatic performance gains byparameter tweaking and runstats (equiv. of analyzing but takes up much lesstime). Quite good command line tools for index advising and explains etc.Was particularly impressed at how easy it was to set up EEE (equiv. ofParallel Server I suppose)Downside is there is very little out there book-wise (h, wonder if thereis a niche market there - DB2 for Oracle DBAs ??) and backups are potentialpitfalls. We have had several issues using Flash Copy on a Shark and thearchive logging is a bitch to set up.Just my 2p (bugger the Euro) worth.Lee-Original Message-Sent: 08 October 2003 22:45To: Multiple recipients of list ORACLE-LSo Lee... As an experienced Oracle guy, what are your thoughts about DB2?-Original Message-Sent: Wednesday, October 08, 2003 12:40 PMTo: Multiple recipients of list ORACLE-LI suggest you buy your mushrooms from the supermarket like the rest of usinstead of picking them in those strange fields my friend-Original Message-Sent: 08 October 2003 17:25To: Multiple recipients of list ORACLE-LHey good to see you back again Lee.The big change here is that all Oracle knowledge has been cyber-engineeredinto an automoton called Tanel Poder.Any query to the list is routed by a big fast connection into a huge serverwith hundreds of CPU's and unlimited memoryAn almost instantaneous reply is automatically generated , often completewith complex examples and code cuts to demonstrate the point.The best part is that is that the machine is programmed to exibit almosthumanistic type responses (occasionally) John-Original Message-Robertson Lee - lerobeSent: 08 October 2003 11:34To: Multiple recipients of list ORACLE-LHi everyone,I'm back in the land of the living after spending a year on a DB2 EEEproject. I've just started working on a 9i RAC solution so the questionswill soon be flooding in. I will be lucky if I can even remember to spellspqlusl , I mean sqlplus :-)CheersLee**The information contained in this communication isconfidential, is intended only for the use of the recipientnamed above, and may be legally privileged.If the reader of this message is not the intendedrecipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictlyprohibited.If you have received this communication in error,please re-send this communication to the sender anddelete the original message or any copy of it from yourcomputer system. Thank You.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official
RE: EMC Snapshot Technology
Tom - I can do either a cold or hot backup. It is all scripted. I prefer cold backups. IBM did try to sell us TDP for Oracle, get this, to open the db on the backup server to back it up using RMAN. The files on the backup server are just that files, no need for Oracle utilities to back them up. High Level Events of a Flash Copy Backup: - Backup Server exports volume group, umounts filesystems backed up. - Backup Server executes begin hot/cold backup script on remote production server. - Backup Server executes FlashCopy Establish Tasks. - Backup Server executes end hot/cold backup script on remote production server. - Backup Server recreates volume group, mounts filesystems. - Backup Server backs up filesystems to tape. Advantages: - Online Backup of Production DB on Backup Server. - Datafiles can used for partial restore or FlashCopy Reverse can be used for full restore. - Copy of Production DB can be used for testing, data warehouse, web purposes. - Resources used for VLDB is on Backup Server not Production DB Server. hth, Gene P.S. If you need more details, feel free to call me. "Mercadante, Thomas F" [EMAIL PROTECTED] 10/09/03 08:08AM Gene, What happens when you need to perform a recovery from the Flashcopy backup? Is Flashcopy done while the database is open or closed? If the database is open, then I assume that you would need to perform an incomplete recovery? Just curious. I am in the middle of setting up new IBM/AIX boxes, and we will using either EMC or IBM Shark disk. Flashcopy was mentioned as a possible solution for backups. But I don't like being boxed in to performing an incomplete recovery. Tom Mercadante Oracle Certified Professional -Original Message-From: Gene Sais [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 8:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: EMC Snapshot Technology I don't use snapshots, but have used EMC BCV's in the past and now use IBM's Flashcopy for backups. I backup 1TB db in ~20 mins using Flashcopy. Then I take it off to tape, i.e. filesystems monted on a TSM Backup server, hence no resources needed from the production server :). hth, Gene [EMAIL PROTECTED] 10/08/03 09:39PM We are implementing Oracle RAC on two Windows nodes, connected to an EMCSAN. We'll also have a failover sight. We are using S.A.M.E. disk configuration, with only one logical volume,and backups/archivelogs dumping to another volume.The SAN is an EMC Clariion CX400. I immediately vetoed snapshots, opting for RMAN, but I'm now taking asecond look. Does anyone use the snapshot technology as a solution for full backups? Thanks,Jeff-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: jwiegand INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: SAME and separating disk and index tablespaces
Title: RE: SAME and separating disk and index tablespaces Good stuff. Plus, watch this: If saving dozens of hours of labor cost actually does cost a full 5% performance penalty on access time, and if reads from disk account for 10% of total response time for a given user action, then saving dozens of hours of labor cost will actually penalize total response time of that user action by only of one percent. Probably not a bad tradeoff in many situations. The key is to know what your situation is. The way to figure that out? Broken record says: extended SQL trace data (10046 level 8 or 12). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis101: 10/28 Phoenix, 11/19 Sydney - Hotsos Symposium 2004: March 710 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Loughmiller, Greg Sent: Thursday, October 09, 2003 8:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- From: vikas kawatra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the
RE: EMC Snapshot Technology
Hey Tom, I'm in the same boat. Have you looked over some of the red papers at www.storage.ibm.com/ess??? If not, check this one out... Storage Management for SAP and Oracle8i on SUN SOLARIS Split Mirror Backup/Recovery with IBM's ESS. Granted its for Solaris, but I would think the concepts are the same. And of course, there are more about Oracle. I believe were running RAID 5, which has me a bit concerned being a unofficial member of the BAARF organization. The red papers speak to this problem, and supposedly it has been resolved. Something about the entire stripe being cached. Since I'm a SAN newbie, I'm starting with the Introduction to SAN paper, and reading my way up. I don't want to raise a red flag until I can prove it. Please keep me in mind with whatever path you take. And if you present your findings let me know, I would love to read'em. Thanks. Chris -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Mercadante, Thomas FSent: Thursday, October 09, 2003 9:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: EMC Snapshot Technology Gene, What happens when you need to perform a recovery from the Flashcopy backup? Is Flashcopy done while the database is open or closed? If the database is open, then I assume that you would need to perform an incomplete recovery? Just curious. I am in the middle of setting up new IBM/AIX boxes, and we will using either EMC or IBM Shark disk. Flashcopy was mentioned as a possible solution for backups. But I don't like being boxed in to performing an incomplete recovery. Tom Mercadante Oracle Certified Professional -Original Message-From: Gene Sais [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 8:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: EMC Snapshot Technology I don't use snapshots, but have used EMC BCV's in the past and now use IBM's Flashcopy for backups. I backup 1TB db in ~20 mins using Flashcopy. Then I take it off to tape, i.e. filesystems monted on a TSM Backup server, hence no resources needed from the production server :). hth, Gene [EMAIL PROTECTED] 10/08/03 09:39PM We are implementing Oracle RAC on two Windows nodes, connected to an EMCSAN. We'll also have a failover sight. We are using S.A.M.E. disk configuration, with only one logical volume,and backups/archivelogs dumping to another volume.The SAN is an EMC Clariion CX400. I immediately vetoed snapshots, opting for RMAN, but I'm now taking asecond look. Does anyone use the snapshot technology as a solution for full backups? Thanks,Jeff-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: jwiegand INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Avoiding full table scan
Title: Avoiding full table scan Hi All, I wish to avoid a full tablescan on the following data V. Zanen Zanen Van Zanen ... ... ... Lot's more data Select * from table where upper(name) like '%ZANEN%' I could create a function based index on upper(name) but this does not take care of the % and like operator. Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go?? TIA Jack
RE: how to keep statistics up to date for CBO
Title: RE: how to keep statistics up to date for CBO
Tom,
why would you want to collect table/index stats separately? Any reason? I prefer cascade=true with mine.
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: Bob Metelsky [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 09, 2003 9:49 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: how to keep statistics up to date for CBO
Thank you very much Tom. That gives me an excellent starting point. I'll
begin to implement this in a devel environment and get a feel for it.
Thanks again!
bob
-Original Message-
Sent: Thursday, October 09, 2003 9:00 AM
To: Multiple recipients of list ORACLE-L
Bob,
I do the following:
First, alter all tables turning monitoring on: alter table {table name}
monitoring;
Monitoring says:
Specify MONITORING if you want Oracle to collect modification
statistics on
table. These statistics are estimates of the number of rows affected by
DML
statements over a particular period of time. They are available for use
by
the optimizer or for analysis by the user.
Then use the following. It recalculates stats for those tables that
have
been changed enough to warrant stats. The User_Tab_Modifications table
will
hold a record if 10% of the table was changed. I've been using this for
a
while now, and it seems to be working fine. As you can see, I have a
database table that I insert a record into so I can see how much work is
done. I'm happy with it. And I'm not gathering stats for tables that I
don't need to. I run this job daily.
Hope this helps.
PROCEDURE WTWDBA.Wtw_Gather_Statistics IS
/*
Procedure Name : Wtw_Gather_Statistics
Author : Tom Mercadante
Mercadante Systems Design
June 14, 2001
Purpose :
This Package will use the System DBMS_STATS package to gather
statistics
for both tables and indexes.
*/
loc_table_name USER_TABLES.TABLE_NAME%TYPE;
loc_index_name USER_INDEXES.INDEX_NAME%TYPE;
tbl_count NUMBER := 0;
indx_count NUMBER := 0;
loc_start_time DATE;
CURSOR c1 IS
SELECT ut.table_name FROM USER_TABLES UT, USER_TAB_MODIFICATIONS UTM
WHERE UT.TABLE_NAME = UTM.table_name;
CURSOR c2 IS
SELECT index_name FROM USER_INDEXES
WHERE table_name = loc_table_name;
BEGIN
loc_start_time := SYSDATE;
-- Gather statistics on tables
OPEN c1;
LOOP
FETCH c1 INTO loc_table_name;
EXIT WHEN c1%NOTFOUND;
dbms_stats.gather_table_stats('WTWDBA',loc_table_name);
tbl_count := tbl_count + 1;
-- Gather statistics on indexes
OPEN c2;
LOOP
FETCH c2 INTO loc_index_name;
EXIT WHEN c2%NOTFOUND;
dbms_stats.gather_index_stats('WTWDBA',loc_index_name);
indx_count := indx_count + 1;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
-- insert a record into the job log
INSERT INTO WTW_JOB_LOG(JOB_NAME, RUN_DATE, START_TIME,
END_TIME, MSG_TXT)
VALUES('Wtw_Gather_Statistics',SYSDATE,loc_start_time,
SYSDATE,INITCAP('SCHEMA Stats Complete')
||
CHR(10) ||
tbl_count || INITCAP(' TABLES Analyzed
') ||
CHR(10) ||
indx_count || INITCAP(' INDEXES Analyzed'));
COMMIT;
END;
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, October 08, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
How does one keep CBO statistics for an applications base tables up to
date?
We are about to implement the CBO any must read documents.
Many thanks
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
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: Mercadante, Thomas F
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: Bob Metelsky
INET: [EMAIL PROTECTED]
Fat
RE: how to keep statistics up to date for CBO
Title: RE: how to keep statistics up to date for CBO
Raj,
Of
course you are correct. I didn't see the "cascade" option until just
recently. And I'm a bit lazy. And it's currently working just
fine.
Need
any other lazy-a**ed excuses? :)
Tom Mercadante Oracle Certified Professional
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]Sent: Thursday, October 09,
2003 10:45 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: how to keep statistics up to date for
CBO
Tom,
why would you want to collect table/index stats separately?
Any reason? I prefer cascade=true with mine.
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: Bob
Metelsky [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 09, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: how to keep statistics up to date for CBO
Thank you very much Tom. That gives me an excellent starting
point. I'll begin to implement this in a devel
environment and get a feel for it.
Thanks again! bob
-Original Message- Sent:
Thursday, October 09, 2003 9:00 AM To: Multiple
recipients of list ORACLE-L
Bob,
I do the following:
First, alter all tables turning monitoring on: alter
table {table name} monitoring;
Monitoring says: "Specify MONITORING
if you want Oracle to collect modification statistics
on table. These statistics are estimates of the number
of rows affected by DML statements over a particular period of time. They are available for
use by the optimizer or for
analysis by the user."
Then use the following. It recalculates stats for those
tables that have been changed
enough to warrant stats. The User_Tab_Modifications table
will hold a record if 10% of the
table was changed. I've been using this for a while now, and it seems to be working
fine. As you can see, I have a database table
that I insert a record into so I can see how much work is done. I'm happy with it. And I'm not gathering stats for
tables that I don't need to. I run this job
daily.
Hope this helps.
PROCEDURE WTWDBA.Wtw_Gather_Statistics IS /* Procedure Name :
Wtw_Gather_Statistics Author : Tom
Mercadante
Mercadante Systems Design
June 14,
2001 Purpose
: This Package will use the System
DBMS_STATS package to gather statistics
for both tables and indexes.
*/
loc_table_name USER_TABLES.TABLE_NAME%TYPE;
loc_index_name USER_INDEXES.INDEX_NAME%TYPE;
tbl_count NUMBER :=
0; indx_count NUMBER :=
0; loc_start_time DATE;
CURSOR c1 IS SELECT
ut.table_name FROM USER_TABLES UT, USER_TAB_MODIFICATIONS UTM WHERE UT.TABLE_NAME = UTM.table_name;
CURSOR c2 IS SELECT index_name
FROM USER_INDEXES WHERE table_name =
loc_table_name;
BEGIN
loc_start_time := SYSDATE;
-- Gather statistics on tables
OPEN c1; LOOP FETCH c1 INTO loc_table_name; EXIT WHEN c1%NOTFOUND;
dbms_stats.gather_table_stats('WTWDBA',loc_table_name); tbl_count := tbl_count + 1;
-- Gather statistics on indexes OPEN c2;
LOOP FETCH
c2 INTO loc_index_name; EXIT WHEN
c2%NOTFOUND;
dbms_stats.gather_index_stats('WTWDBA',loc_index_name); indx_count := indx_count + 1;
END LOOP; CLOSE c2;
END LOOP; CLOSE c1;
-- insert a record into the job log INSERT INTO WTW_JOB_LOG(JOB_NAME, RUN_DATE, START_TIME,
END_TIME, MSG_TXT)
VALUES('Wtw_Gather_Statistics',SYSDATE,loc_start_time,
SYSDATE,INITCAP('SCHEMA
Stats Complete') || CHR(10)
||
tbl_count || INITCAP('
TABLES Analyzed ') || CHR(10)
||
indx_count || INITCAP(' INDEXES Analyzed')); COMMIT;
END;
Tom Mercadante Oracle Certified
Professional
-Original Message- Sent:
Wednesday, October 08, 2003 4:54 PM To: Multiple
recipients of list ORACLE-L
How does one keep CBO statistics for an applications base
tables up to date?
We are about to implement the CBO any must read
documents.
Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- Author: Bob Metelsky
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: Temp Tablespace
Hi A.F.A.I.K the space is free for use by new queries. Oracle just does not release the extents to save resources on extent management. Just that your monitoring scripts keep yelling 100% used :-) Jack -Original Message- Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created. I think the idea and philosophy behind SAME is noble - Use all available drives, so that you do not have localized
RE: how to keep statistics up to date for CBO
Title: RE: how to keep statistics up to date for CBO Likewise all my options are shaken .. not stirred . 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: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 11:10 AMTo: Multiple recipients of list ORACLE-LSubject: RE: how to keep statistics up to date for CBO Raj, Of course you are correct. I didn't see the "cascade" option until just recently. And I'm a bit lazy. And it's currently working just fine. Need any other lazy-a**ed excuses? :) Tom Mercadante Oracle Certified Professional 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: Temp Tablespace
If it is a TABLESPACE TEMPORARY, query on v$SORT_SEGMENT to get the current size of the sort segment. Then issue an ALTER TABLESPACE tablespacename DEFAULT STORAGE (PCTINCREASE 0). Re-query V$SORT_SEGMENT and the segment would have been released. If there were current users in the segment, you would still see a TEMPORARY segment in DBA_SEGMENTS, though, and the disk-space would not be released till those users log out. Hemant At 06:14 AM 09-10-03 -0800, you wrote: All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- From: Loughmiller, Greg [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- From: vikas kawatra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as
Re: DB Parameters
I was tempted to point to the /*+ _try_harder */ hint (revealed by Jonathan Lewis at this year's IOUG), but that won't be available until Oracle 10. Until then we have to use the old methods of identifying where the time is spent and figure out what can be done to eliminate as much of it as possible. I have seen deletes take a long time if the table was heavily indexed. Removing the indexes before the delete and rebuilding afterwards was the solution there. At 02:29 AM 10/9/2003, you wrote: Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: where can I find sample db creation script (9.2.0.1.0 installation)?
Which oracle version is your buildall.sql for? I just queried my 9i db and got this: SQL select TABLESPACE_NAME from dba_tablespaces; TABLESPACE_NAME -- SYSTEM UNDOTBS1 TEMP CWMLITE DRSYS EXAMPLE INDX ODM TOOLS USERS XDB Where is Undo tablespace part in your buildall.sql? Guang -Original Message- Bob Metelsky Sent: Thursday, October 09, 2003 9:54 AM To: Multiple recipients of list ORACLE-L installation)? Yes Im on Windows xp the script names and dir structure should be the same though... Here is buildall.sql buildall.sql spool \build.log SET TERMOUT OFF SET ECHO OFF connect internal @@build_db.sql @C:\oracle\ora81\RDBMS\admin\catalog.sql @C:\oracle\ora81\RDBMS\admin\catsnmp.sql connect internal @C:\oracle\ora81\RDBMS\admin\catexp7.sql @C:\oracle\ora81\RDBMS\admin\catproc.sql @@scott.sql connect internal @@demo.sql connect system/manager @C:\oracle\ora81\RDBMS\admin\catdbsyn.sql @C:\oracle\ora81\SQLPLUS\admin\pupbld.sql connect internal @C:\oracle\ora81\RDBMS\admin\caths.sql @C:\oracle\ora81\RDBMS\admin\oramts.sql connect internal alter rollback segment rb_temp offline; shutdown; spool off ## ## build_db.sql ## -- -- $Header: build_db.sql 1.1 94/10/18 15:55:37 gdudey Osddesktop/netware $ Copyr (c) 1994 Oracle -- -- This file must be run out of the directory containing the -- initialization file. startup nomount pfile=\%AVAILABLE_SID%\pfile\initsmpl.ora -- Create database create database %AVAILABLE_SID% controlfile reuse logfile '%seedl_path%\%AVAILABLE_SID%\redo01.log' size %seedl_size%M reuse, '%seedl_path%\%AVAILABLE_SID%\redo02.log' size %seedl_size%M reuse, '%seedl_path%\%AVAILABLE_SID%\redo03.log' size %seedl_size%M reuse, '%seedl_path%\%AVAILABLE_SID%\redo04.log' size %seedl_size%M reuse datafile '%seedd_path%\%AVAILABLE_SID%\system01.dbf' size %seedd_sysi_size%M reuse autoextend on next %seedd_syse_size%M maxsize %seedd_sysm_size%M character set %seed_charset%; create rollback segment rb_temp storage (initial 100 k next 250 k); -- Create additional tablespaces ... -- USERs: Create user sets this as the default tablespace -- TEMP: Create user sets this as the temporary tablespace -- RBS: For rollback segments create tablespace users datafile '%seedd_path%\%AVAILABLE_SID%\users01.dbf' size %seedd_usri_size%M reuse autoextend on next %seedd_usre_size%M maxsize %seedd_usrm_size%M; create tablespace rbs datafile '%seedd_path%\%AVAILABLE_SID%\rbs01.dbf' size %seedd_rbsi_size%M reuse autoextend on next %seedd_rbse_size%M maxsize %seedd_rbsm_size%M; create temporary tablespace temp tempfile '%seedd_path%\%AVAILABLE_SID%\temp01.dbf' size %seedd_tmpi_size%M reuse autoextend on next %seedd_tmpe_size%M maxsize %seedd_tmpm_size%M; create tablespace oem_repository datafile '%seedd_path%\%AVAILABLE_SID%\oemrep01.dbf' size %seedd_usri_size%M reuse autoextend on next %seedd_usre_size%M maxsize %seedd_usrm_size%M; create tablespace indx datafile '%seedd_path%\%AVAILABLE_SID%\indx01.dbf' size %seedd_tmpi_size%M reuse autoextend on next %seedd_tmpe_size%M maxsize %seedd_tmpm_size%M; -- next %seedd_usre_size%M maxsize %seedd_usrm_size%M; alter rollback segment rb_temp online; -- Change the SYSTEM users' password, default tablespace and -- temporary tablespace. alter user system temporary tablespace temp; alter user system default tablespace users; -- Create 16 rollback segments. Allows 16 concurrent users with open -- transactions updating the database. This should be enough. create public rollback segment rb1 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb2 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb3 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb4 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb5 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb6 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb7 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb8 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb9 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb10 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb11 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb12 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb13 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb14 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb15 storage(initial 50K next 250K) tablespace rbs; create public rollback segment rb16
RE: BROKEN_PIPE from Weblogic
I don't think it's accurate to say that NET8 isn't involved when you're talking directly to the listener =8-) More correctly you are eliminating the NET8 client DLL's...but you could still have the connections dropping due to transient network failures. I have a particular problem with CISCO switches and TRU64 Alpha boxes in my environment. JDBC thick connections drop but because they are 'thick' I get a SQLNET.LOG file on the client that shows a TNS-12203 (unable to connect to destination) error. Other apps like Maximo report an ORA-3113 but also show the TNS-12203 error in their sqlnet.log files. When I do a level 16 listener trace I can see the connection being closed (read() error) but I've never been able to figure out why =8-( The only thing that moderates the problem is to make sure that the Alpha is set to 100MB/Full Duplex instead of auto-detect as well as hard-coding these values on the switch port. Ditto for the NT app servers. friends don't let friends auto-negotiate =8-) But I still see periodic TNS-12203's just less of them Try doing a level 16 trace from the listner end. Jeff Herrick On Thu, 9 Oct 2003, DENNIS WILLIAMS wrote: Agreed. By the way, since my original posting, this error has occurred several more times. Yes, it is a catch-all error that sounds a lot like an ORA-3113. Since they using the Oracle thin JDBC driver, which talks directly to the listener, Net8 isn't involved. If the server is shut down or crashes, apparently this error is also returned, like an ORA-3113. But the server hasn't crashed or shut down. [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeff Herrick 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 keep statistics up to date for CBO
One reason to collect index stats separately would be if you use estimate
for the collection of the table statistics. In that case I collect the
index stats separately with a compute. Index statistics collection is fast
enough to always go for exact statistics rather than sampling.
At 08:44 AM 10/9/2003, you wrote:
Tom,
why would you want to collect table/index stats separately? Any reason? I
prefer cascade=true with mine.
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: Bob Metelsky [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 9:49 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: how to keep statistics up to date for CBO
Thank you very much Tom. That gives me an excellent starting point. I'll
begin to implement this in a devel environment and get a feel for it.
Thanks again!
bob
-Original Message-
Sent: Thursday, October 09, 2003 9:00 AM
To: Multiple recipients of list ORACLE-L
Bob,
I do the following:
First, alter all tables turning monitoring on: alter table {table name}
monitoring;
Monitoring says:
Specify MONITORING if you want Oracle to collect modification
statistics on
table. These statistics are estimates of the number of rows affected by
DML
statements over a particular period of time. They are available for use
by
the optimizer or for analysis by the user.
Then use the following. It recalculates stats for those tables that
have
been changed enough to warrant stats. The User_Tab_Modifications table
will
hold a record if 10% of the table was changed. I've been using this for
a
while now, and it seems to be working fine. As you can see, I have a
database table that I insert a record into so I can see how much work is
done. I'm happy with it. And I'm not gathering stats for tables that I
don't need to. I run this job daily.
Hope this helps.
PROCEDURE WTWDBA.Wtw_Gather_Statistics IS
/*
Procedure Name : Wtw_Gather_Statistics
Author : Tom Mercadante
Mercadante Systems Design
June 14, 2001
Purpose:
This Package will use the System DBMS_STATS package to gather
statistics
for both tables and indexes.
*/
loc_table_name USER_TABLES.TABLE_NAME%TYPE;
loc_index_name USER_INDEXES.INDEX_NAME%TYPE;
tbl_count NUMBER := 0;
indx_count NUMBER := 0;
loc_start_time DATE;
CURSOR c1 IS
SELECT ut.table_name FROM USER_TABLES UT, USER_TAB_MODIFICATIONS UTM
WHERE UT.TABLE_NAME = UTM.table_name;
CURSOR c2 IS
SELECT index_name FROM USER_INDEXES
WHERE table_name = loc_table_name;
BEGIN
loc_start_time := SYSDATE;
-- Gather statistics on tables
OPEN c1;
LOOP
FETCH c1 INTO loc_table_name;
EXIT WHEN c1%NOTFOUND;
dbms_stats.gather_table_stats('WTWDBA',loc_table_name);
tbl_count := tbl_count + 1;
-- Gather statistics on indexes
OPEN c2;
LOOP
FETCH c2 INTO loc_index_name;
EXIT WHEN c2%NOTFOUND;
dbms_stats.gather_index_stats('WTWDBA',loc_index_name);
indx_count := indx_count + 1;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
-- insert a record into the job log
INSERT INTO WTW_JOB_LOG(JOB_NAME, RUN_DATE, START_TIME,
END_TIME, MSG_TXT)
VALUES('Wtw_Gather_Statistics',SYSDATE,loc_start_time,
SYSDATE,INITCAP('SCHEMA Stats Complete')
||
CHR(10) ||
tbl_count || INITCAP(' TABLES Analyzed
') ||
CHR(10) ||
indx_count || INITCAP(' INDEXES Analyzed'));
COMMIT;
END;
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, October 08, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
How does one keep CBO statistics for an applications base tables up to
date?
We are about to implement the CBO any must read documents.
Many thanks
bob
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.nethttp://www.orafaq.net
--
Author: Bob Metelsky
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.comhttp://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.nethttp://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.comhttp://www.fatcity.com
San
RE: how to keep statistics up to date for CBO
Title: RE: how to keep statistics up to date for CBO So Wolfgang, in that case, should _wait_ till table stats (which are probably estimate) are complete to perform index stats (which you recommend compute) or can that happen in parallel. 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: Wolfgang Breitling [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 11:40 AM To: Multiple recipients of list ORACLE-L Subject: RE: how to keep statistics up to date for CBO One reason to collect index stats separately would be if you use estimate for the collection of the table statistics. In that case I collect the index stats separately with a compute. Index statistics collection is fast enough to always go for exact statistics rather than sampling. **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.**5
Re: Temp Tablespace
Tom, What type of temporary? Dictionery managed or LMT. If dictionary managed then 2 solutions. a)make it offline and online again during offpeak period. 2)Make it permanent tablespace to avoid this situation. If LMT based (temp_datafiles) then check v$sort_usage instead of header table. If header table says 100% it is normal but if v$sort_usage indicates 100% then it is a problem and then you have to either bounce the db and if this is not the option then create another samller temp1 tablespace.point your user to that tempspace , drop your original temp tbs and recreate it and then point your user back to original temp tbs. HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 09 Oct 2003 06:14:27 -0800 All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best
RE: DB Parameters
Walid, It's unlikely that a database parameter will provide the solution to your problem. Trace the DELETE if you can (DBMS_SUPPORT.START_TRACE_IN_SESSION), to see what's taking so long. Odds are that your DELETE is poorly optimized SQL that's simply doing more work than it needs to be doing. BUT FIND OUT BEFORE YOU ASSUME THAT'S WHAT IT IS. If you find that your DELETE is consuming massive amounts of CPU service or OS read time, then take the query part of your DELETE out of your DELETE statement, and work with it until you get a decent execution plan. For example, if your statement were: DELETE FROM table WHERE predicate-list ...then focus on optimizing the query SELECT rowid FROM table WHERE predicate-list At this point, it's a standard SQL optimization exercise. The tools at your disposal include, but are not restricted to: - Can you use TRUNCATE to produce a functionally correct end-state? - Are the statistics on your table reasonable? - Does your SQL use all the join conditions that are necessary to allow the Oracle Query Optimizer to do its job of finding the best plan? - Does your table have all the check constraints on it that the Oracle Query Optimizer needs to do its job of finding the best plan? - Do the indexes exist that the Oracle Query Optimizer needs to do its job of finding the best plan? - Can you eliminate unnecessary indexes without harming other SQL statements? - And so on... As a last resort, you might consider using hints upon the SQL to coax the optimizer into using the plan you want, but I would advise doing this only in unusual circumstances. If all your other work is done properly, then I would submit that it's usually a good idea to eliminate hints from your SQL. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: 3/7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Walid Alkaakati Sent: Thursday, October 09, 2003 3:29 AM To: Multiple recipients of list ORACLE-L Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Thanks for your help. Bye. ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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: Cary Millsap 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).
Turning off undo for one session
We are trying to load 23Gb's of raster images into our 9.2.0.2/Rac Database, using ESRI's SDE GIS package, and as usual there is a big rush to load the data. If we try to load 14 images at one time, we run out of undo space (datafile is currently 2 Gb). I could simply add another datafile and not worry about, but I figure that's the easy way out. I have turned logging off for the table and the tablespace, but I can't figure out how to turn the creation of undo off for a particular session, or if it is even possible. Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren 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: DB Parameters
Does the table you are deleting has any child table(s)? If yes, then are the foreign keys in the child table(s) indexed? Guang -Original Message- Wolfgang Breitling Sent: Thursday, October 09, 2003 11:34 AM To: Multiple recipients of list ORACLE-L I was tempted to point to the /*+ _try_harder */ hint (revealed by Jonathan Lewis at this year's IOUG), but that won't be available until Oracle 10. Until then we have to use the old methods of identifying where the time is spent and figure out what can be done to eliminate as much of it as possible. I have seen deletes take a long time if the table was heavily indexed. Removing the indexes before the delete and rebuilding afterwards was the solution there. At 02:29 AM 10/9/2003, you wrote: Hi List , Can you help me please , What are the database parameters that i have to increase or modify in order to increase the speed of my ddl statments ,i have a statment that delete a table with 4 record but it takes about nine hours to accomplish !!! Is their any parameters on the server that i have to check ??,Increase rollback segment is not helpfull at all . Iam runningoracle 9.2.0.1.0 production . Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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: Fw: 'shared pool'
It's because of the SGA_MAX_SIZE parameter, which is, of course, rounded
so that the SGA can be aligned to the page boundary.
On Wed, 2003-10-08 at 19:59, [EMAIL PROTECTED] wrote:
Mladen ,is all this
documented anywhere or is it all very common knowledge ? I can't believe
that all you expert dbas would let Oracle take away tens of mbs of memory
without
telling you.
- Original Message -
To: [EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 04:11
Mladen , thanks for replying . Please tell me why does one get a
difference
between the values returned from v$sgastat and the shared pool size set
in
the parameter file ?
Please , explain this to me . Arup explained that the memory required to
manage the buffer
cache is also allocated from the shared pool. Is there any ratio or does
this mean that if I allocate x MB to my buffer cache, Oracle
allocates that x mb for the buffer cache , and an additional x mb or 32
mb
for
managing the buffer cache and places it in the shared pool ?
- Original Message -
From: Mladen Gogala [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 08, 2003 03:49
Subject: Re: 'shared pool'
Not so on my instance.
SQL select sum(bytes) from v$sgastat
2 where pool='shared pool';
SUM(BYTES)
--
251658240
SQL show parameter shared_pool
NAME TYPEVALUE
---
--
shared_pool_reserved_sizebig integer 10905190
shared_pool_size big integer 218103808
SQL select 251658240-218103808 from dual;
251658240-218103808
---
33554432
SQL show parameter db_cache_size
NAME TYPEVALUE
---
--
db_cache_sizebig integer 419430400
SQL
On Tue, 2003-10-07 at 17:54, [EMAIL PROTECTED] wrote:
List please try out the following :
1. select sum(bytes) from v$sgastat where pool='shared pool';
2. find out the size of the shared pool you have specified in your
parameter files.
3. subtract the two values
4. When I do the same as above the difference is exactly equal to my
db_cache_size.
So does this mean that if I allocate x MB to my buffer cache, Oracle
allocates that x mb for the buffer cache , and an additional x mb for
managing the buffer cache and places it in the shared pool ?
Please explain this to me.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Mladen Gogala
Oracle DBA
Note:
This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information. No
confidentiality or privilege is waived or lost by any mistransmission. If
you receive this message in error, please immediately delete it and all
copies of it from your system, destroy any hard copies of it and notify
the
sender. You must not, directly or indirectly, use, disclose, distribute,
print, or copy any part of this message if you are not the intended
recipient. Wang Trading LLC and any of its subsidiaries each reserve the
right to monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized to
state them to be the views of any such entity.
--
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
--
Mladen Gogala
Oracle DBA
Note:
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No confidentiality or privilege is
waived or lost by any mistransmission. If you receive this message in error, please
immediately delete it and all copies of it from your system, destroy any hard copies
of it and notify the sender. You must not, directly or indirectly, use, disclose,
distribute, print, or copy any part of this message if
Re: Turning off undo for one session
Undo cannot be turned off because without commit/rollback capability, RDBMS can no longer manage resources and cannot observe ACID properties. What you can do is to load files as BFILE type, which, essentially, means that you are copying them to file system and just recording the pathname into the database. If you use DBMS_LOB.READ, then LOB blocks are protected by the transaction mechanism and there is nothing you can do. On Thu, 2003-10-09 at 12:44, Browett, Darren wrote: We are trying to load 23Gb's of raster images into our 9.2.0.2/Rac Database, using ESRI's SDE GIS package, and as usual there is a big rush to load the data. If we try to load 14 images at one time, we run out of undo space (datafile is currently 2 Gb). I could simply add another datafile and not worry about, but I figure that's the easy way out. I have turned logging off for the table and the tablespace, but I can't figure out how to turn the creation of undo off for a particular session, or if it is even possible. Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administratorusing 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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).
Redos gone crazy--a job for audit?
Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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 keep statistics up to date for CBO
I don't see any reason why that couldn't happen concurrently. The table and its indexes are different physical segments. At 09:59 AM 10/9/2003, you wrote: So Wolfgang, in that case, should _wait_ till table stats (which are probably estimate) are complete to perform index stats (which you recommend compute) or can that happen in parallel. 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: Wolfgang Breitling [mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:40 AM To: Multiple recipients of list ORACLE-L Subject: RE: how to keep statistics up to date for CBO One reason to collect index stats separately would be if you use estimate for the collection of the table statistics. In that case I collect the index stats separately with a compute. Index statistics collection is fast enough to always go for exact statistics rather than sampling. ** 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. **5 Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temp Tablespace
Title: RE: SAME and separating disk and index tablespaces Hi: I had this situation last month and I finally bounced the db. "drop tablespace temp INCLUDING CONTENTS" might work if you want to wait long enough. I tried this and the db just hang for 30 min before I killed it. My temp ts was 10G. Here is a copy of an old email I sent to the list last month: Yes, you are right, I should have looked at ts# = 2. BTW, I tried several ways to resolve this issue by doing these and none of them worked, 1. alter tablespace temp default storage(pctincrease 0); -- a tip from Steve Adam's site 2. alter session set events 'immediate trace name DROP_SEGMENTS level 3'; 3. oradebug wakeup 6; -- 6 is sid for SMON process 4. Create a small table with a primary key constraint then drop the constraint. Iwas hoping that shadow process will change the associated index to a temporary segment and post smon to clear it up. 5. create a large table that will fail: CREATE TABLE junk ( c1 varchar2(1) ) tablespace DATA storage ( initial 2000M next 2000M) ; Finally I had to bounce the instance, that cleared up the segment in temp tablespace, so that it could be dropped. Guang -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of zhu chao Sent: Tuesday, September 16, 2003 10:25 PM To: Multiple recipients of list ORACLE-L Subject: Re: drop tablespace temp INCLUDING CONTENTS hang Hi, guang: First, I think step 5,6,7 are not necessary. Why bother change back to that named tablespace? To user, temporary tablespace name is totally transparent to them and to the application. You should use drop tablespace temp including contents, but the query you used maybe is wrong. your temp tablespace is TS#=2, but your query is :select count(*) from fet$ where ts# = 3; (you are using 3 here). Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 4:49 AM Hi: Oracle 8173 on Solaris 2.8. I am trying to convert temp tablespace to LMT. My plan is to 1.create another temp tablespace temp123 2.move all users to temp123 3.alter tablespace temp offline 4.drop tablespace temp 5.re-create temp tablespace as LMT 6.move all users to temp 7.drop tablespace temp123. But I am having problem in Step4. I found that there are still some objects in temp ts even if I put it offline: [EMAIL PROTECTED] select count(*) from dba_segments where tablespace_name='TEMP'; COUNT(*) -- 1 "drop tablespace temp" resulted in "ORA-01549 tablespace not empty, use INCLUDING CONTENTS option". However when I did drop tablespace temp INCLUDING CONTENTS; the sqlplus session just hang and I kept getting the same count(*) from the following query (from another session) [EMAIL PROTECTED] select count(*) from fet$ where ts# = 3; COUNT(*) -- 214 I eventually killed the orginal sqlplus session. I did "ALTER TABLESPACE TEMP coalesce" and it did not seem to help. So my question is: How can I drop an offlined temp tablespace when there is still segment(s) in it? I don't know if SMON would clean up the temp ts automatically (Some doc says SMON would not do to TEMP ts). I also read that I could issue alter session set events 'immediate trace name DROP_SEGMENTS level 3'; to mimic SMON, becuase [EMAIL PROTECTED] select name, ts# from ts$ where NAME='TEMP'; NAME TS# -- -- TEMP 2 But I am not ready to try it on our production system. Has anyone tries this or has any suggestions? I don't want to bounce db. Would the objects in TEMP ts be cleaned up by SMON if I wait for a couple of days? TIA. Guang -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Mercadante, Thomas FSent: Thursday, October 09, 2003 10:14 AMTo: Multiple recipients of list ORACLE-LSubject: Temp Tablespace All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional
RE: Redos gone crazy--a job for audit?
Title: RE: Redos gone crazy--a job for audit? log miner should give you what you want ... why not? On last friday something happened and in our database which usually averages about 100x100M archive logs, it started throwing 41 files between 2pm-3pm, 248 between 3pm-4pm, 95 between 4pm-5pm. Of course we couldn't analyze all files, but an analysis og a 10 minute interval at the beginning of archive franzy shows a clear set of 5 SQLS that repeated about 83000 times in 10 minutes. Once we gave it to development, they were able to identify the process which was using the code in question and it became easier. I'd start at-least half hour before the peak time and do a slow analysis. I have also found that instead of selecting from v$lgmnr_contents, I am more comfortable with doign a CTAS and then perform queries at my leisure for a detailed analysis. Go for log miner ... at-least it will tell you what caused the problem. HTH 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: Barbara Baker [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Subject: Redos gone crazy--a job for audit? Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb 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: Temp Tablespace
I think you are right Jack. With a Temporary Temp tablespace, there is nothing I can do to lower the HWM. And I don;t really think it's full - just the HWM shows it filled up. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Hi A.F.A.I.K the space is free for use by new queries. Oracle just does not release the extents to save resources on extent management. Just that your monitoring scripts keep yelling 100% used :-) Jack -Original Message- Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may
RE: alter session
Paul, Not true. It has no effect on privileges. If you can't access the table normally using alter session won't change that. Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Paul Drake [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 08, 2003 10:39 PMTo: Multiple recipients of list ORACLE-LSubject: Re: alter session it raises the DBA's blood pressure by 50 mm Hg. if found, it prevents the user from having an unlocked account. if found, it is possible that it gets the user a termination notice. you're in the sys schema for what purpose? testing recovery from dictionary corruption? Pd[EMAIL PROTECTED] wrote: List, what does the following do ?alter session set current_schema=sys;-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: <[EMAIL PROTECTED]INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!?The New Yahoo! Shopping - with improved product search
Re: Redos gone crazy--a job for audit?
Here is a query that will help you find the biggest consumer of redo blocks: select sid, value from (select s.sid,s.value from v$sesstat s, v$statname n where s.statistic#=n.statistic# and n.name='redo blocks written' order by value); However, 50MB redo blocks are wy to small. To paraphrase Orwell, 50MB good, 250MB better, 500MB mch better. On Thu, 2003-10-09 at 13:09, Barbara Baker wrote: Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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).
RE: alter session
That only affects how Oracle finds objects. If for instance you would have to access dba_users normally as sys.dba_users then using the alter session command means you can drop the 'sys.' portion thereof. It has no affect on your priviledges. Down side is that if you then want to reference one of your personal tables you have to say so. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, October 08, 2003 9:04 PM To: Multiple recipients of list ORACLE-L Exactly what it says: set the current schema to sys At 05:44 PM 10/8/2003, you wrote: List, what does the following do ? alter session set current_schema=sys; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick 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: Redos gone crazy--a job for audit?
It should have been redo entries, not redo blocks written. The only guy writing redo blocks is LGWR, so the previous query would always give a hume amount for SID=4 and 0 for everybody else. On Thu, 2003-10-09 at 13:09, Barbara Baker wrote: Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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).
RE: Redos gone crazy--a job for audit?
Just a quick thought, are any tablespaces left in Hot Backup Mode? Rich -Original Message- Barbara Baker Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Rich Gesler 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: RE: how to keep statistics up to date for CBO
None that I've noticed the database in question is processing something like 1B
transactions per day.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Thursday, October 09, 2003 9:14 AM
To: Multiple recipients of list ORACLE-L
does monitoring have any real overhead in a high transaction system?
From: Mercadante, Thomas F [EMAIL PROTECTED]
Date: 2003/10/09 Thu AM 08:59:33 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: how to keep statistics up to date for CBO
Bob,
I do the following:
First, alter all tables turning monitoring on: alter table {table name}
monitoring;
Monitoring says:
Specify MONITORING if you want Oracle to collect modification statistics on
table. These statistics are estimates of the number of rows affected by DML
statements over a particular period of time. They are available for use by
the optimizer or for analysis by the user.
Then use the following. It recalculates stats for those tables that have
been changed enough to warrant stats. The User_Tab_Modifications table will
hold a record if 10% of the table was changed. I've been using this for a
while now, and it seems to be working fine. As you can see, I have a
database table that I insert a record into so I can see how much work is
done. I'm happy with it. And I'm not gathering stats for tables that I
don't need to. I run this job daily.
Hope this helps.
PROCEDURE WTWDBA.Wtw_Gather_Statistics IS
/*
Procedure Name : Wtw_Gather_Statistics
Author : Tom Mercadante
Mercadante Systems Design
June 14, 2001
Purpose:
This Package will use the System DBMS_STATS package to gather statistics
for both tables and indexes.
*/
loc_table_name USER_TABLES.TABLE_NAME%TYPE;
loc_index_name USER_INDEXES.INDEX_NAME%TYPE;
tbl_count NUMBER := 0;
indx_count NUMBER := 0;
loc_start_time DATE;
CURSOR c1 IS
SELECT ut.table_name FROM USER_TABLES UT, USER_TAB_MODIFICATIONS UTM
WHERE UT.TABLE_NAME = UTM.table_name;
CURSOR c2 IS
SELECT index_name FROM USER_INDEXES
WHERE table_name = loc_table_name;
BEGIN
loc_start_time := SYSDATE;
-- Gather statistics on tables
OPEN c1;
LOOP
FETCH c1 INTO loc_table_name;
EXIT WHEN c1%NOTFOUND;
dbms_stats.gather_table_stats('WTWDBA',loc_table_name);
tbl_count := tbl_count + 1;
-- Gather statistics on indexes
OPEN c2;
LOOP
FETCH c2 INTO loc_index_name;
EXIT WHEN c2%NOTFOUND;
dbms_stats.gather_index_stats('WTWDBA',loc_index_name);
indx_count := indx_count + 1;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
-- insert a record into the job log
INSERT INTO WTW_JOB_LOG(JOB_NAME, RUN_DATE, START_TIME,
END_TIME, MSG_TXT)
VALUES('Wtw_Gather_Statistics',SYSDATE,loc_start_time,
SYSDATE,INITCAP('SCHEMA Stats Complete') ||
CHR(10) ||
tbl_count || INITCAP(' TABLES Analyzed ') ||
CHR(10) ||
indx_count || INITCAP(' INDEXES Analyzed'));
COMMIT;
END;
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, October 08, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
How does one keep CBO statistics for an applications base tables up to
date?
We are about to implement the CBO any must read documents.
Many thanks
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
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: Mercadante, Thomas F
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: [EMAIL PROTECTED]
INET: [EMAIL
RE: Avoiding full table scan
Title: Avoiding full table scan Jack, In a recent copy of SELECT magazine there is a discussion in defense of full table scans. I believe you might find it VERY interesting. Although I was aware of some of what the author spoke he put it in a vein that makes extreme sense. Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Jack van Zanen [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 10:49 AMTo: Multiple recipients of list ORACLE-LSubject: Avoiding full table scan Hi All, I wish to avoid a full tablescan on the following data V. Zanen Zanen Van Zanen ... ... ... Lot's more data Select * from table where upper(name) like '%ZANEN%' I could create a function based index on upper(name) but this does not take care of the % and like operator. Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go?? TIA Jack
RE: Turning off undo for one session
Thanks for the response, unfortunately the application requires the data to be stored in the database, loading the files as BFILE type and then using DBMS_LOB.READ is not possible. Darren -Original Message- Sent: Thursday, October 09, 2003 10:10 AM To: Multiple recipients of list ORACLE-L Undo cannot be turned off because without commit/rollback capability, RDBMS can no longer manage resources and cannot observe ACID properties. What you can do is to load files as BFILE type, which, essentially, means that you are copying them to file system and just recording the pathname into the database. If you use DBMS_LOB.READ, then LOB blocks are protected by the transaction mechanism and there is nothing you can do. On Thu, 2003-10-09 at 12:44, Browett, Darren wrote: We are trying to load 23Gb's of raster images into our 9.2.0.2/Rac Database, using ESRI's SDE GIS package, and as usual there is a big rush to load the data. If we try to load 14 images at one time, we run out of undo space (datafile is currently 2 Gb). I could simply add another datafile and not worry about, but I figure that's the easy way out. I have turned logging off for the table and the tablespace, but I can't figure out how to turn the creation of undo off for a particular session, or if it is even possible. Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administratorusing 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: Browett, Darren 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).
Using ' in Update statement
List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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: SAME and separating disk and index tablespaces
David and list, Some points to keep in mind in our discussion: * Throughput can be measured both by IOPS and MB./sec, it depends on the application, whether it is purely transactional or is just hauling loads of data. Access time normally is a function of the number of I/O operations the disk volume can support, the type of I/O demand (the mixture of random versus sequential I/O requests on both reads and writes) and the physical location of the data that is being accessed (outer tracks versus the inner tracks). Thus IOPS is very much relevant to our discussion, as statistically speaking, given the capacity of n IOPS, if x is the acutal number of IOPS being serviced by the volume, lower the value of x, lower will be the probability is for access times to be out of whack. * There is disk technology available today that supports multiple actuators, which has a definite positive impact on access times. Again, IOPS has a play in this too. * Almost all mirroring technologies that I have encountered provide for the following 2 important features: - Doubling of IOPS as compared to a non-mirrored enviroment (due to double the drives) - Intelligent servicing of I/O requests across the mirrored members (some use the round-robin algorithm, others use the least-busy algorithm). This is the single most salient point that should be highlighted in our discussion. I should have brought this up in my original posting. Better late than never! - Redundancy. This feature is an obvious one, which again is relevant to our discussion, because I don't think any of us are going to just implement a purely striped volume with NO mirroring. So assuming a mirrored volume (say 4-way stripe), that consumes a total of 8 drives, it can be argued (or even proved) that placing the objects in the volume based on IOPS, will suffice, regardless of whether the object type is - data, index, undo or temp. This is because, in a mirrored volume, say on an index scan, the I/O operation for the index block can be serviced from one member of the mirrored volume and subsequently the I/O operation for the data block can be serviced from the other member of the mirrored volume. So the cost of seeking different parts of the same disk, can be avoided. We don't eliminate any seeks, it just that the seeks that we do perform are much cheaper, as the amount of head movement is minimized. I think this provides pretty much a similar scenario as proposed by you of having 2 different volumes with 5 drives each, instead of having 1 volume with 10 drives. Again, I should have originally highlighted this in our discussion of adding more drives to a volume. Hope that clarifies things a bit. Cheers, Gaja --- David Hau [EMAIL PROTECTED] wrote: Hans, Your statement is true except in the case of a fast full-index scan. But that's not my point. What I'm trying to say is: 1. In scenarios where response time is important, for example when you want to obtain the first n rows of a query result as quickly as possible, then access time may be as important as throughput. 2. Adding disks to a striped array only improves throughput, not access time. 3. Access time can be improved by parallel I/O execution on separate disk arrays. I'm trying to point out the difference between: 1. striping 10 disks into a single array 2. striping 10 disks into two arrays of 5 disks each In the first case, you get max throughput but because you only have one array, you cannot improve access time by parallelizing disk access. In the second case, you get half the throughput of the first case, but if you can parallelize disk access to both disks, your access time or response time to get the 1st row of the query result may be shorter. Regards, Dave [EMAIL PROTECTED] wrote: Dave, during a 'db file sequential read', an index is _not_ accessed sequentially. An index is not a sequential structure, so reading from an index in order will cause multiple seeks on the index itself. And we're talking single user here regards, Hans Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 08 Oct 2003 16:29:25 -0800 Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more
Can someone please verify this for me?
I have a problem with the new procedure based roles, Secure Application Roles. The following is taken from an example in ASKTOM. Basically, I'm trying to setup a role that is enabled or not by a procedure. The original code from Tom: [EMAIL PROTECTED] l 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role identified by password'; 6* end; [EMAIL PROTECTED] create role new_role identified by password; Role created. [EMAIL PROTECTED] set role none; Role set. [EMAIL PROTECTED] select * from session_roles; no rows selected [EMAIL PROTECTED] set role new_role; set role new_role * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' [EMAIL PROTECTED] exec turn_on_role; PL/SQL procedure successfully completed. [EMAIL PROTECTED] select * from session_roles; ROLE -- NEW_ROLE [EMAIL PROTECTED] Now, if I try this using what I need: 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role'; 6* end; and then try to run it: exec turn_on_role; I get a ORA-6565 error: Cannot execute SET ROLE from within stored procedure Any ideas what am I missing here? 9.2.0.1, Win2K. Did the usual searches everywhere including Metaclick, nothing that I can relate to... TIA for any help. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Using ' in Update statement
It's in the documentation. Start with the concepts manual, then SQL*Plus manual and SQL reference. I'm sure that you'll run across the answer because that's where I have found the answer to the very same question. On Thu, 2003-10-09 at 13:59, Hamid Alavi wrote: List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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).
RE: Redos gone crazy--a job for audit?
Do you have the database in backup mode? Waleed -Original Message- Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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).
RE: encrypt traffic across internet?
Here's URL that shows how. http://www.akadia.com/services/ssh_connect_tunnels.html I've tried it, works well. Jared Richard Ji [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/08/2003 08:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: encrypt traffic across internet? Or you can tunnel sqlplus and many other programs through ssh. -Original Message- Sent: Wednesday, October 08, 2003 10:40 AM To: Multiple recipients of list ORACLE-L The question was posed to me about a simple web browser (non ssl), accessing an oracle database and shooting the information across the internet to a client, this info is NOT encrypted i assume. is that also true for like remote sqlplus connections and if you wanted it to be for web, you need to go via SSL and for sqlplus what can be used? Is this where ASO comes in(i think thats is, advanced security option?) thanks, joe -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: Richard Ji 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: Can someone please verify this for me?
Maybe you should try with DBMS_SESSION.SET_ROLE? On Thu, 2003-10-09 at 11:09, Nuno Souto wrote: I have a problem with the new procedure based roles, Secure Application Roles. The following is taken from an example in ASKTOM. Basically, I'm trying to setup a role that is enabled or not by a procedure. The original code from Tom: [EMAIL PROTECTED] l 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role identified by password'; 6* end; [EMAIL PROTECTED] create role new_role identified by password; Role created. [EMAIL PROTECTED] set role none; Role set. [EMAIL PROTECTED] select * from session_roles; no rows selected [EMAIL PROTECTED] set role new_role; set role new_role * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' [EMAIL PROTECTED] exec turn_on_role; PL/SQL procedure successfully completed. [EMAIL PROTECTED] select * from session_roles; ROLE -- NEW_ROLE [EMAIL PROTECTED] Now, if I try this using what I need: 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role'; 6* end; and then try to run it: exec turn_on_role; I get a ORA-6565 error: Cannot execute SET ROLE from within stored procedure Any ideas what am I missing here? 9.2.0.1, Win2K. Did the usual searches everywhere including Metaclick, nothing that I can relate to... TIA for any help. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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).
Re: Populating BLOB's from a SQL Script? - Solution
Mark,
I didn't fully understand you're earlier post, but now it seems that
the sqlunldr.pl script in the PDBA toolkit may do what you want.
http://www.oreilly.com/catalog/oracleperl/pdbatoolkit/
It will dump blobs to hex in a sqlldr compatible file, along with
the control and parameters script to reload with sqlldr.
HTH
Jared
Mark Richard [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/08/2003 08:09 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Populating BLOB's from a SQL Script? - Solution
Ok, I got no suggestions but have found a rather clunky way to achieve
this:
Assuming a blob exists in a table somewhere then a statement like select
dbms_lob.substr(data, 1000, 1) from table can be used to retrieve 1000
bytes worth of data as hex.
For a new field a simple insert into table (data) values
(hextoraw('the_hex_returned_above')); statement will insert the data in
it's original form.
To work with BLOB's larger than 1K a looping process is required. The
easiest way I've found to append data is a small pl/sql block:
declare
new_data blob;
begin
select data into new_data from table for update;
dbms_lob.writeappend(new_data, offset,
hextoraw('the_hex_returned'));
end;
So, to summarise. It's possible to convert BLOB's to hex, place the
results into a basic sql script and have then reloaded into a table.
Unfortunately you can only work on 1K of data at a time but a simple
program to generate the sql would overcome this problem. Net result = you
can deliver binary data via sql scripts would too much hassle.
Hopefully this helps somebody out there.
Regards,
Mark.
Mark Richard
[EMAIL PROTECTED]To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
ban.com.au cc:
Sent by: Subject: Populating BLOB's from a SQL Script?
[EMAIL PROTECTED]
.com
07/10/2003 12:04
Please respond to
ORACLE-L
Hi List,
I have been asked to look into the possibility of populating a BLOB column
(Oracle 8i currently) using an SQL Script. All the examples I've ever seen
involve reading the blob from a file and inserting it directly to the table
though.
The reasoning behind this is the desire to deliver a single script which
loads perhaps 50 images into a table. Performance isn't a problem, neither
is the size of the script.
Does anyone know of a way to do this? It is safe to assume the data
already exists in a table if that helps to create the script. I was
thinking perhaps a rawtohex type function may convert the binary data
into something that can exist within a script. Am I on the right track?
Regards,
Mark.
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for
delivery of the message to such person), you may not copy or deliver this
message to anyone.
In such a case, you should destroy this message and kindly notify the
sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3
9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not
relate to the official business of Transurban Infrastructure Developments
Limited and CityLink Melbourne Limited shall be understood as neither given
nor endorsed by them.
--
Please
RE: Using ' in Update statement
Do you mean a single quote? like this?: SQL update tablea set fielda = ' james''ste Camp ''first,''sec'' '; 1 row updated. SQL select * from tablea; FIELDA james'ste Camp 'first,'sec' 1 row selected. or: SQL update tablea set fielda = ' james'||chr(39)||'ste Camp '||chr(39)||'first,'||chr(39)||'sec'||chr(39)||' '; SQL select * from tablea; FIELDA james'ste Camp 'first,'sec' 1 row selected. -Original Message- Sent: Thursday, October 09, 2003 11:59 AM To: Multiple recipients of list ORACLE-L List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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: Eberhard, Jeff 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).
interesting dynamic pl/sql question
Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Redos gone crazy--a job for audit?
Well, that was an excellent idea. But sadly, that's not it. (We actually don't use hot backups, but I checked just in case someone mucked with it. No dice.) Thanks. Barb --- Khedr, Waleed [EMAIL PROTECTED] wrote: Do you have the database in backup mode? Waleed -Original Message- Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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). __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: more on Oracle Standard One
Title: Message I was told by our Oracle Rep it is the number of users using the front-end application. If you have 10 specific users using the application, then a 10-user license must be purchased. -Original Message-From: AK [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 2:35 PMTo: Multiple recipients of list ORACLE-LSubject: Re: more on Oracle Standard One Just curious , If Ibuy named user licence and use some application to connect using same user (Just like any app server with connection pooling ) does the cost multiply to no of user_connecting_to_app_server or no_of_users_actually_connected to database( mean schemas) ?? -ak - Original Message - From: Paul Drake To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 08, 2003 5:19 PM Subject: more on Oracle Standard One http://www.crn.com/sections/BreakingNews/dailyarchives.asp?ArticleID=44997 Oracle Standard Edition One, based on the current Oracle 9i code, will cost $5,995 and is limited to use on one-processor servers, the company said. Alternatively, it can be sold for $195 per named user with a minimum of five users. Support and maintenance add an additional 22 percent of license cost and Oracle's eBusiness discounts apply, an Oracle spokeswoman said. So support and updates cost $899 on the one-CPU license and $214 for five named users. 40% of list for Standard Edition. interesting. If I were a support analyst, I'd be groaning, as here comes a new market segment of users that no nothing about administration, that will need to be supported prior to the rollout of 10g. If I were an OEM such as Dell that was reselling Standard Edition pre-installed on new servers, I'd be figuring how to makeup the markup on the 15K USD version. has anyone heard anything concerning grid pricing strategies? Pd Do you Yahoo!?The New Yahoo! Shopping - with improved product search
RE: Can someone please verify this for me?
Strange... works fine here (same environment 9.2.0.1 on Win2K server): SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 13:43:23 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL create role new_role 2 / Role created. SQL create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 6 'set role new_role'; 7 end; 8 / Procedure created. SQL execute turn_on_role; PL/SQL procedure successfully completed. SQL Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Nuno Souto Sent: Thursday, October 09, 2003 10:09 AM To: Multiple recipients of list ORACLE-L I have a problem with the new procedure based roles, Secure Application Roles. The following is taken from an example in ASKTOM. Basically, I'm trying to setup a role that is enabled or not by a procedure. The original code from Tom: [EMAIL PROTECTED] l 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role identified by password'; 6* end; [EMAIL PROTECTED] create role new_role identified by password; Role created. [EMAIL PROTECTED] set role none; Role set. [EMAIL PROTECTED] select * from session_roles; no rows selected [EMAIL PROTECTED] set role new_role; set role new_role * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' [EMAIL PROTECTED] exec turn_on_role; PL/SQL procedure successfully completed. [EMAIL PROTECTED] select * from session_roles; ROLE -- NEW_ROLE [EMAIL PROTECTED] Now, if I try this using what I need: 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role'; 6* end; and then try to run it: exec turn_on_role; I get a ORA-6565 error: Cannot execute SET ROLE from within stored procedure Any ideas what am I missing here? 9.2.0.1, Win2K. Did the usual searches everywhere including Metaclick, nothing that I can relate to... TIA for any help. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: 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: Can someone please verify this for me?
It worked for me 9.2.0.2 Solaris 2.8 But I'm surprised since I always thought that roles are disabled in stored procs. Even it worked for me but it's still disabled in the stored proc after the execute immediate. Waleed -Original Message- Sent: Thursday, October 09, 2003 11:09 AM To: Multiple recipients of list ORACLE-L I have a problem with the new procedure based roles, Secure Application Roles. The following is taken from an example in ASKTOM. Basically, I'm trying to setup a role that is enabled or not by a procedure. The original code from Tom: [EMAIL PROTECTED] l 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role identified by password'; 6* end; [EMAIL PROTECTED] create role new_role identified by password; Role created. [EMAIL PROTECTED] set role none; Role set. [EMAIL PROTECTED] select * from session_roles; no rows selected [EMAIL PROTECTED] set role new_role; set role new_role * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' [EMAIL PROTECTED] exec turn_on_role; PL/SQL procedure successfully completed. [EMAIL PROTECTED] select * from session_roles; ROLE -- NEW_ROLE [EMAIL PROTECTED] Now, if I try this using what I need: 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role'; 6* end; and then try to run it: exec turn_on_role; I get a ORA-6565 error: Cannot execute SET ROLE from within stored procedure Any ideas what am I missing here? 9.2.0.1, Win2K. Did the usual searches everywhere including Metaclick, nothing that I can relate to... TIA for any help. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: Redos gone crazy--a job for audit?
Barb, Every time I have run into this situation, I have used the following approach and it has always worked. I've never validated it in all cases, so take it with a grain of salt. Redo is generated by block changes. Find the session that is generating the most # of block changes (v$sess_io.block_changes). Then trace back to the session info, sql, etc. Also check for tablespaces in hot backup mode. Daniel Barbara Baker wrote: Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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). begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: more on Oracle Standard One
Just curious , If Ibuy named user licence and use some application to connect using same user (Just like any app server with connection pooling ) does the cost multiply to no of user_connecting_to_app_server or no_of_users_actually_connected to database( mean schemas) ?? -ak - Original Message - From: Paul Drake To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 08, 2003 5:19 PM Subject: more on Oracle Standard One http://www.crn.com/sections/BreakingNews/dailyarchives.asp?ArticleID=44997 Oracle Standard Edition One, based on the current Oracle 9i code, will cost $5,995 and is limited to use on one-processor servers, the company said. Alternatively, it can be sold for $195 per named user with a minimum of five users. Support and maintenance add an additional 22 percent of license cost and Oracle's eBusiness discounts apply, an Oracle spokeswoman said. So support and updates cost $899 on the one-CPU license and $214 for five named users. 40% of list for Standard Edition. interesting. If I were a support analyst, I'd be groaning, as here comes a new market segment of users that no nothing about administration, that will need to be supported prior to the rollout of 10g. If I were an OEM such as Dell that was reselling Standard Edition pre-installed on new servers, I'd be figuring how to makeup the markup on the 15K USD version. has anyone heard anything concerning grid pricing strategies? Pd Do you Yahoo!?The New Yahoo! Shopping - with improved product search
RE: Temp Tablespace
Under the senario rely on v$sort_usage view. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 09 Oct 2003 09:24:24 -0800 I think you are right Jack. With a Temporary Temp tablespace, there is nothing I can do to lower the HWM. And I don;t really think it's full - just the HWM shows it filled up. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Hi A.F.A.I.K the space is free for use by new queries. Oracle just does not release the extents to save resources on extent management. Just that your monitoring scripts keep yelling 100% used :-) Jack -Original Message- Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data
RE: Redos gone crazy--a job for audit?
Sample the top sessions from v$sesstat for statname 'redo size' (statistic# 115 in my database) Then joining to v$sql should give you the sql that generates that redo. Waleed -Original Message- Sent: Thursday, October 09, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Well, that was an excellent idea. But sadly, that's not it. (We actually don't use hot backups, but I checked just in case someone mucked with it. No dice.) Thanks. Barb --- Khedr, Waleed [EMAIL PROTECTED] wrote: Do you have the database in backup mode? Waleed -Original Message- Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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). __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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).
Re: Redos gone crazy--a job for audit?
You can track the sql which is doing DML while redo being generated. Look at v$sqlarea or v$sql. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 09 Oct 2003 09:09:24 -0800 Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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). _ Frustrated with dial-up? Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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: Can someone please verify this for me?
Nope. Tried with that one as well, same result. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Maybe you should try with DBMS_SESSION.SET_ROLE? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Can someone please verify this for me?
Sure... there you go! SQL create role new_role identified by password; Role created. SQL 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 6 'set role new_role'; 7* end; SQL / Procedure created. SQL set role none; Role set. SQL select * from session_roles; no rows selected SQL exec turn_on_role; BEGIN turn_on_role; END; * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' ORA-06512: at SYSMAN.TURN_ON_ROLE, line 5 ORA-06512: at line 1 SQL ed Wrote file afiedt.buf 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 6 'set role new_role identified by password'; - 7* end; SQL / Procedure created. SQL exec turn_on_role; PL/SQL procedure successfully completed. SQL --- Nuno Souto [EMAIL PROTECTED] wrote: I have a problem with the new procedure based roles, Secure Application Roles. The following is taken from an example in ASKTOM. Basically, I'm trying to setup a role that is enabled or not by a procedure. The original code from Tom: [EMAIL PROTECTED] l 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role identified by password'; 6* end; [EMAIL PROTECTED] create role new_role identified by password; Role created. [EMAIL PROTECTED] set role none; Role set. [EMAIL PROTECTED] select * from session_roles; no rows selected [EMAIL PROTECTED] set role new_role; set role new_role * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' [EMAIL PROTECTED] exec turn_on_role; PL/SQL procedure successfully completed. [EMAIL PROTECTED] select * from session_roles; ROLE -- NEW_ROLE [EMAIL PROTECTED] Now, if I try this using what I need: 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role'; 6* end; and then try to run it: exec turn_on_role; I get a ORA-6565 error: Cannot execute SET ROLE from within stored procedure Any ideas what am I missing here? 9.2.0.1, Win2K. Did the usual searches everywhere including Metaclick, nothing that I can relate to... TIA for any help. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: interesting dynamic pl/sql question
You should be getting errors, because PL/SQL inside execute immediate knows nothing about mystorageArray (or i for that matter) declared in your stored procedure. Probably, you could get by using package variables (and referring to them properly: package_name.var_name, specifically inside your dynamic sql). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: 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: interesting dynamic pl/sql question
Title: RE: interesting dynamic pl/sql question Ryan, what errors are you getting? 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: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L Subject: RE: interesting dynamic pl/sql question You should be getting errors, because PL/SQL inside execute immediate knows nothing about mystorageArray (or i for that matter) declared in your stored procedure. Probably, you could get by using package variables (and referring to them properly: package_name.var_name, specifically inside your dynamic sql). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: 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). **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.**5
Re: Using ' in Update statement
I assume you mean quote update tablea set fielda =' james'ste Camp 'first,'sec' ' update tablea set fielda =' james''ste Camp ''first,''sec'' '; Just use 2 single quotes Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 02:29 PM Please respond to ORACLE-L It's in the documentation. Start with the concepts manual, then SQL*Plus manual and SQL reference. I'm sure that you'll run across the answer because that's where I have found the answer to the very same question. On Thu, 2003-10-09 at 13:59, Hamid Alavi wrote: List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: 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).
