Re: Rollback Segments
Bambi, HLI and OCI - that reminds me of another nice one from the dark ages. Some of you around here, who consider yourself old in terms of Oracle may remember, the oci call osql3(), which was later superceeded by oparse() (version 6/7-ish, I think). Well, the reason for the old call being called osql3() was that it was new with version 3! In version two, osql() was found, but it was limited to 6000 bytes of SQL statement text, where osql3() made it possible to go to to 64k. Rgds, Bjørn. On Tuesday 19 February 2002 20:18, you wrote: Bjorn, you old coot, you've got me beat. I do remember the UFI prompt though... User Friendly Interface... and the real geeks would program their own SQL*Plus environment using HLI (now OCI) with the OROL option which would allow the rollback of a single statement rather than a whole transaction. Now, in my day, we had fire but we had to make our own coal and we kept warm on the hides of old terminals that used to roam the plains. What were things like in your day? Bambi. -Original Message- Sent: Tuesday, February 19, 2002 5:23 AM To: Multiple recipients of list ORACLE-L There must be somebody beside myself remembering version 3, which did not have read consistency - the great new feature of version 4. In 3, doing UFI insert into emp select * from emp; would cause anything from having 28 rows in emp till having and endless loop in the kernel only finishing when your database file ran full... Yep - we are some old bitter men around here... /Bjørn. On Tuesday 19 February 2002 06:43, you wrote: I remember the BI.ORA (Before-Image) file, IOR and ODS in Oracle 5. Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Rachel Carmichael [EMAIL PROTECTED] 19/02/2002 06:18 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: RE: Rollback Segments UFI no, but the rest... that's where I started in Oracle -- version 5 --- Conboy, Jim [EMAIL PROTECTED] wrote: Holy cow Mladen, what a memory! Does anybody else remember (or admit to) using UFI? Jim ** ...does anybody still remember VAX/VMS, ORACLE$BI, IOR and ODT?... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Conboy, Jim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
testing -- NO MESSAGE
Kind Regards, Hatzistavrou Yannis -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hatzistavrou Giannis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Binary Search in Oracle
SELECT id,parentid from taxonomy START WITH parentid='x003' CONNECT BY id = PRIOR parentid On Thu 21. February 2002 08:38, you wrote: the syntax is something like SELECT id,parentid from taxonomy START WITH parentid='x003' CONNECT BY PRIOR id = parentid which returns all related rows under a particular row does anyone know how to get all related rows above a particular row Thank You, Gavin - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, February 21, 2002 12:03 PM Does any one remember the syntax for a binary search say you have an id column and a parentid column Oracle can fetch all the ids either ascending or descending given a particular id. This is performed a s a binary tree search anyone remembers the syntax ? Thank you Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'mello INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Options instead of ODBC + MS-Access [Slightly? Off-topic]
A developer here has put together a reporting package which uses ODBC to interface to an 8.1.7 DB on W2K server and utilises MS-Access on client to extract data and generate nice GUI final presentation of data. The data extracted has various computations performed. The developer has asked what other Oracle or 3rd party options might be used to realise the same end results perhaps in a more efficient manner. Their concern is that as data volumes grow the performance will degredate substantially. Anyone any ideas?. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Options instead of ODBC + MS-Access [Slightly? Off-topic]
lots of vendors in that marketspace. a few of the heavy hitters are: Actuate Software Corporation - e-reporting suite Seagate - crystal reports Cognos - impromptu -Original Message- Sent: Thursday, February 21, 2002 6:48 AM To: Multiple recipients of list ORACLE-L A developer here has put together a reporting package which uses ODBC to interface to an 8.1.7 DB on W2K server and utilises MS-Access on client to extract data and generate nice GUI final presentation of data. The data extracted has various computations performed. The developer has asked what other Oracle or 3rd party options might be used to realise the same end results perhaps in a more efficient manner. Their concern is that as data volumes grow the performance will degredate substantially. Anyone any ideas?. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A question for people who also know Sybase
Title: RE: A question for people who also know Sybase Melissa, Finally made itwork last evening. The interfaces file (like our tnsnames.ora) that is used by the Sybase client to connect to servers has 2 different formats that can be used:TLI andnon-TLI. Was using TLI format for all servers. When I used a non-TLI format to these 2 servers, the connection went thru and the gateway started working. So I guess this is some kind of setup issue with the Sybase server. If any of you have questions to configure Oracle Transparent Gateway with Sybase, I will be glad to help. The documentation is not really helpful. Thanks Prakash -Original Message-From: Godlewski, Melissa [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 20, 2002 3:29 PMTo: '[EMAIL PROTECTED]'Subject: RE: A question for people who also know Sybase Prakash, Have you looked to see if there is some kind of ip deny or accept in place? -Original Message- From: Bala, Prakash [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 2:48 PM To: Multiple recipients of list ORACLE-L Subject: RE: A question for people who also know Sybase Jared, I tried the first 3 and didn't get any answer. Since we are an Oracle shop and not a Sybase shop, we don't have any Sybase support. So I posted here because somebody would have used Oracle Transparent Gateway with Sybase. -Original Message- Sent: Wednesday, February 20, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Ok, this really is an Oracle list, believe it or not. Why not?: a) do a google search b) join a sybase list c) hit the sybase tech support site d) file a support request with sybase. Jared "Bala, Prakash" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/20/02 09:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: A question for people who also know Sybase Hi, Oracle 8.1.7.2 and Sybase 12.0 client on Solaris 2.8 We are trying to access 7 remote Sybase databases (all on NT) using a Sybase client (Adaptive Server Enterprise 12.0) installed on Solaris. Have converted the IP addresses and the port numbers to Hexadecimal and have made the necessary changes to the 'interfaces' file on our Unix box. We are able to successfully connect to 5 databases and retrieve data using isql. But we get the following error for the remaining 2 sites when I issue the isql command: CT-LIBRARY error: ct_connect(): network packet layer: internal net library error: Net-Lib protocol driver call to connect two endpoints failed We are able to ping these 2 servers and also do the 'traceroute' successfully. Also, I am able to connect to these 2 locations using the Sybase client on my workstation. This verifies that the IP address, port#, user-id and password are correct. Just the connection through Unix fails and have to make this work so that our Oracle database can talk to Sybase using the Oracle Gateway. Oracle Gateway is able to retrieve data from the remaining 5 Sybase servers. TIA! Prakash --
Re:RE: Options instead of ODBC + MS-Access [Slightly? Off-to
Sean, We too have used Access in the past and still do today. Dennis does bring up some very good points your developer sounds very competent since he/she is bringing this up vs. trying to gloss over the problem. Our major salability and consistency problem with Access were and remain two fold. 1) the OBDC driver being used. Some of the MicroSoft drivers for Oracle have this nasty habit of never closing connections. It is not hard to find your process limit being hit. The option here is to use MTS but the better solution is to get everybody to use a decent driver. I can look up the one we use if it will help. 2) People storing data from Oracle into Access. This one is a real pain in the (I won't go there). What happen to us in this arena is that the developer attached to the Oracle tables and simply scanned them into local Access tables, then did whatever data processing he wanted. Worked rather well while the data volume was low, but as it picked up the application slowed dramatically I never heard the end of the slow database complaints. Thankfully our network admin could prove to the developer that the problem was local. The other problem was that once the Oracle table was scanned the application continued to use the same data until it was shut down. Made the reports look a little funny as the day progressed. Dick Goulet Reply Separator Author: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2/21/2002 5:53 AM Sean - I think that Access tends to be a wonderfully cheap and easy Oracle front-end for exactly the situation you described. Naturally, if your site has standardized on a different tool, then it isn't good, or if the staff is highly competent in another tool. I think the developer's concern is an excellent opportunity for you to introduce the subject of scalability. Think about it. The scalability problem in this situation isn't in Access, but in the Oracle data model and in the SQL statements that Access issues (okay technically that part is in Access). If the data model is well-designed and the SQL statements aren't doing something like full-table scans, then it should scale well. Okay, the other gotcha might be if the size of the data you are extracting from Oracle will eventually overwhelm Access. If someone else on the list knows any other Access points of concern, perhaps they will share them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 21, 2002 6:48 AM To: Multiple recipients of list ORACLE-L A developer here has put together a reporting package which uses ODBC to interface to an 8.1.7 DB on W2K server and utilises MS-Access on client to extract data and generate nice GUI final presentation of data. The data extracted has various computations performed. The developer has asked what other Oracle or 3rd party options might be used to realise the same end results perhaps in a more efficient manner. Their concern is that as data volumes grow the performance will degredate substantially. Anyone any ideas?. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
RE: Options instead of ODBC + MS-Access [Slightly? Off-topic]
Has the developer considered using Oracle Objects for OLE? The are generally much more efficient than the ODBC stuff. Gives lots of advantages over ODBC. Can be used in compiled code and in VBA with MSOffice apps. Rodd On Thu, 2002-02-21 at 08:28, Ji, Richard wrote: BRIO -Original Message- Sent: Thursday, February 21, 2002 8:53 AM To: Multiple recipients of list ORACLE-L lots of vendors in that marketspace. a few of the heavy hitters are: Actuate Software Corporation - e-reporting suite Seagate - crystal reports Cognos - impromptu -Original Message- Sent: Thursday, February 21, 2002 6:48 AM To: Multiple recipients of list ORACLE-L A developer here has put together a reporting package which uses ODBC to interface to an 8.1.7 DB on W2K server and utilises MS-Access on client to extract data and generate nice GUI final presentation of data. The data extracted has various computations performed. The developer has asked what other Oracle or 3rd party options might be used to realise the same end results perhaps in a more efficient manner. Their concern is that as data volumes grow the performance will degredate substantially. Anyone any ideas?. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Options instead of ODBC + MS-Access [Slightly? Off-topic]
You might want to make sure your developer is hip to Access' pass-through queries is thinking straight about when it makes sense to do calculations in Access, as opposed to having them done on the server. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 5:53 AM To: Multiple recipients of list ORACLE-L Sean - I think that Access tends to be a wonderfully cheap and easy Oracle front-end for exactly the situation you described. Naturally, if your site has standardized on a different tool, then it isn't good, or if the staff is highly competent in another tool. I think the developer's concern is an excellent opportunity for you to introduce the subject of scalability. Think about it. The scalability problem in this situation isn't in Access, but in the Oracle data model and in the SQL statements that Access issues (okay technically that part is in Access). If the data model is well-designed and the SQL statements aren't doing something like full-table scans, then it should scale well. Okay, the other gotcha might be if the size of the data you are extracting from Oracle will eventually overwhelm Access. If someone else on the list knows any other Access points of concern, perhaps they will share them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 21, 2002 6:48 AM To: Multiple recipients of list ORACLE-L A developer here has put together a reporting package which uses ODBC to interface to an 8.1.7 DB on W2K server and utilises MS-Access on client to extract data and generate nice GUI final presentation of data. The data extracted has various computations performed. The developer has asked what other Oracle or 3rd party options might be used to realise the same end results perhaps in a more efficient manner. Their concern is that as data volumes grow the performance will degredate substantially. Anyone any ideas?. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Anybody against using views?
We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:Anybody against using views?
Ron, Kind of depends on what the view is being used for. I've several examples of views that are real PIGS with regards to cpu and temp space usage (Sorry, I can't share them as they belong to a third party application). I've also eliminated a few of these PIGS, these I could share, by some other database trickery. My favorite reason NOT to use views is when the view does some data manipulation based on some strange criteria (been forced into a couple of those boy do they create confusion). I'm also not too fond of views whose sole purpose in life is to rename a column or two from the original table. Otherwise, to perform common joins and the like, their great. Dick Goulet Reply Separator Author: Smith; Ron L. [EMAIL PROTECTED] Date: 2/21/2002 8:18 AM We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
Just yesterday a developer was having a performance problem with a complicated report was taking about 7 minutes to complete. She was grabbing the table data and doing all the sorting, summing, etc. in the report. I created four views to feed the report. It now runs in less than a minute. In this case, a view makes sense. I also caught developers using the same SQL over and over. I created views from their statements and it makes their life easier. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]] We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Anybody against using views?
IMO views are often used as a substitute for creating reporting structures. Using views makes for easy report/SQL creation, but tends to be a tuning and performance nightmare. It's hard to tune, and will likely never perform well. I'm going through similar issues here right now. A number of users need to do reporting on production data. No way, no how will they be allowed to do it on the production database. It's a manufacturing database and performance is critical to this system. I've done some prototypes of the tables they need to report on. Basically a copy of the production tables in another database. Those that have a long refresh cycle ( 1+ days ) get bitmap indexes on most columns. Those that need to be close to realtime get Btree indexes instead and will be refreshed every few minutes ( refresh time pending negotiation with users :). This is not exactly a data mart as I would like to have it: no star schemas. But it's what I have time for right now, gets the reports off of production and is *much* faster to query. HTH Jared Smith, Ron L. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
I like views... right now I'm working on the one out my office window... a spectacular view of the Tobacco Root range across the Gallatin Valley. :-) Regarding database views, you can't say they're entirely good or bad. They may simplify things for reporting but they could complicate your ability to tune the SQL... it just depends. Ad hoc end user queries on a separate non-OLTP reporting server is one thing, ad hoc end user reporting or batch reports with views of production source data on an OLTP server is could be quite another. The idea of having business object views of data and turning end users loose with a reporting tool without DBA supervision can be both appealing and frightening. But duhvelopers SHOULD know what they're doing. ;-) Looking out the window in Bozeman, Montana... Steve Orr -Original Message- Sent: Thursday, February 21, 2002 9:18 AM To: Multiple recipients of list ORACLE-L We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle error ORA-04031
I'm getting an ORA-04031: unable to allocate 8192 bytes of shared memory (large
pool,unknown object,sort subheap,sort key) error, and am having a hard time
solving the issue.
The SQL being ran is:
SELECT COUNT(DISTINCT mail) theCount
FROM Demo D
WHERE (EXISTS (SELECT 1 FROM mails WHERE mail = d.mail))
AND (D.countryID IN ('US'))
AND ((EXISTS (SELECT 1 FROM Interests I WHERE D.id = I.demoID AND I.interestID=31))
OR (EXISTS (SELECT 1 FROM Interests I WHERE D.id = I.demoID AND I.interestID=84)) )
AND unsub_date IS NULL
AND return_date IS NULL
demo is a 33+M record table, Mails is 10+M and Interests is 40+M.
There are indexes on demo.mail, Mails.mail, demo.id, and interests(interestid,demoid).
I tried doubling the large pool to see if that would help, but the same query runs
fine on 8i. This is currently on 9i.
Oh, there is also a degree of parallelism on each table, each set to 4.
Any advice would be appreciated.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
Trouble with some views is, with certain front ends (Business Objects likes
this particularly well) the only way to get it to accept the queries is to
create MULTIPLE views on the same table and do recursive self joins that
way. It makes for very untidy SQL that is practically un-tune-able. I
would say that to use or not to use is very subjective and situational. I
would use views in Jerome's situation, but Synonyms are better in others...
and redesign beats both in a lot.
-Original Message-
Sent: Thursday, February 21, 2002 11:04 AM
To: Multiple recipients of list ORACLE-L
Just yesterday a developer was having a performance problem with a
complicated report was taking about 7 minutes to complete. She was grabbing
the table data and doing all the sorting, summing, etc. in the report. I
created four views to feed the report. It now runs in less than a minute. In
this case, a view makes sense.
I also caught developers using the same SQL over and over. I created views
from their statements and it makes their life easier.
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]]
We have several applications that use views extensively. On the other
hand
there are several apps that use no views at all. We have a new developer
who wants to use views when writing reports in Crystal Reports. The
application administrator is leery of using views and ask the DBA group
what
we think. I can see several reasons to use views and a few reasons not to
use them. I was just wondering what the rest of the group thought.
Ron Smith
DBA
Kerr-McGee Corp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Whittle Jerome Contr NCI
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
begin 666 InterScan_Disclaimer.txt
M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S
M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E
M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL
M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@
M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C
M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA
M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA
M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S
M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I
M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!)
M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@
M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S
M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL
M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S
M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R
M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE
%;G0N#0H
end
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: April Wells
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
If you're using views properly, they're wonderful and allow a phenomenal amount of flexibility to the designer, but like all powerful beings, they must use their power for good (complex query manipulation) rather than evil (resource hogging). HTH, Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
Per my morning's experience today... some DEvelopers know what they are
doing... many duhvelopers to every developer though... and the phrase oh I
tested it means little sometimes.
Steve... you actually get to TUNE the SQL that gets rolled out to people?
Lucky you... I inherited most of mine, and our system is almost entirely
ad-hoc... one table has 16 views... all of them are select * from where
account_no = b1 so the joins work. They wonder why some of the user queries
run for HOURS...
Listening to the wind howl over the Texas Panhandle...
April Wells
=)
-Original Message-
Sent: Thursday, February 21, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L
I like views... right now I'm working on the one out my office window... a
spectacular view of the Tobacco Root range across the Gallatin Valley. :-)
Regarding database views, you can't say they're entirely good or bad. They
may simplify things for reporting but they could complicate your ability to
tune the SQL... it just depends. Ad hoc end user queries on a separate
non-OLTP reporting server is one thing, ad hoc end user reporting or batch
reports with views of production source data on an OLTP server is could be
quite another. The idea of having business object views of data and turning
end users loose with a reporting tool without DBA supervision can be both
appealing and frightening. But duhvelopers SHOULD know what they're doing.
;-)
Looking out the window in Bozeman, Montana...
Steve Orr
-Original Message-
Sent: Thursday, February 21, 2002 9:18 AM
To: Multiple recipients of list ORACLE-L
We have several applications that use views extensively. On the other hand
there are several apps that use no views at all. We have a new developer
who wants to use views when writing reports in Crystal Reports. The
application administrator is leery of using views and ask the DBA group what
we think. I can see several reasons to use views and a few reasons not to
use them. I was just wondering what the rest of the group thought.
Ron Smith
DBA
Kerr-McGee Corp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
begin 666 InterScan_Disclaimer.txt
M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S
M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E
M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL
M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@
M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C
M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA
M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA
M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S
M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I
M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!)
M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@
M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S
M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL
M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S
M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R
M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE
%;G0N#0H
end
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: April Wells
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RAMAN Question
Greetings, We are at Oracle 8.1.7 on Sun Solaris 7. Can we backup all the archive logs at any point of time but not deleting all these archive logs as we like to keep the archive logs for 2 days(SYSDATE-1) in the disk so that we don't need to restore the archive logs from rman backup in case of some recovery up to point within last 2 days. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
One of the mains reasons we used views at one place I worked was the table drop factor. We never created a table that we didn't immediately create a select * from ... view on. We then granted the privileges on the views instead of the tables so that if we ever had to drop and recreate the table all of our grants would stay intact on the view. Now that you can rebuild the table without dropping it I don't think this approach has much merit but it is something to consider in older databases. Just one more point of view :) --Michael -Original Message- Sent: Thursday, February 21, 2002 12:29 PM To: Multiple recipients of list ORACLE-L I like views... right now I'm working on the one out my office window... a spectacular view of the Tobacco Root range across the Gallatin Valley. :-) Regarding database views, you can't say they're entirely good or bad. They may simplify things for reporting but they could complicate your ability to tune the SQL... it just depends. Ad hoc end user queries on a separate non-OLTP reporting server is one thing, ad hoc end user reporting or batch reports with views of production source data on an OLTP server is could be quite another. The idea of having business object views of data and turning end users loose with a reporting tool without DBA supervision can be both appealing and frightening. But duhvelopers SHOULD know what they're doing. ;-) Looking out the window in Bozeman, Montana... Steve Orr -Original Message- Sent: Thursday, February 21, 2002 9:18 AM To: Multiple recipients of list ORACLE-L We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenkins, Michael - EDS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: Anybody against using views?
Bambi, There are times when I really don't like people using a view to do complex queries. I'll give you an example of one PIG I have, although I'll re-write it to just provide the idea of what they did. The original definition is claimed by the vendor as proprietary. Anyway, here is the basics of what their doing: create view oops as select a.name, b.dept, c.value from (select name, empid, deptid from emp group by name, empid, deptid) a, (select deptid, dept_name from dept group by deptid, dept_name) b, (select empid, sum(value)value from sales group by empid) c where a.empid = c.empid and a.deptid = b.deptid group by a.name, b.dept, c.value; Got any idea what this does to the database? Of course I forgot to mention that each of thise dynamic tables has a corolated subquery + other stuff built in as well. The vendor claims that this runs very well in their test database, which it does (total space = 128Kbytes of disk, the tables have 10 rows each). Problem is that in our production database which has 1.5GB of disk space (1 row in test = 50 rows in production or more) in active use it runs really badly. Dick Goulet Reply Separator Author: Bellows; Bambi [EMAIL PROTECTED] Date: 2/21/2002 9:38 AM If you're using views properly, they're wonderful and allow a phenomenal amount of flexibility to the designer, but like all powerful beings, they must use their power for good (complex query manipulation) rather than evil (resource hogging). HTH, Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bellows, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Instance Monitoring Tools
Can anyone give me an idea of what are the best Oracle instance monitoring tools on the market? Thanks, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RAMAN Question
Greetings That is possible as well with RMAN, You can backup as many times you like. The delete of the archive files can be done in connection with the backup or as a separate command /greetings Mandal, Ashoke wrote: Greetings, We are at Oracle 8.1.7 on Sun Solaris 7. Can we backup all the archive logs at any point of time but not deleting all these archive logs as we like to keep the archive logs for 2 days(SYSDATE-1) in the disk so that we don't need to restore the archive logs from rman backup in case of some recovery up to point within last 2 days. Thanks, Ashoke -- /regards Peter Gram Phone : +45 2527 7107 Fax : +45 4466 8856 Miracle A/S Kratvej 2 2760 Målev http://miracleas.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Gram INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ROWID datatype columns and primary keys
Can someone explain to me why some developers like to create ROWID datatype columns in their tables? I am wondering why they sometimes do that instead of using primary keys. I searched for info on this on the Web, but nothing. ROWID access is probably faster than index access, I guess. I vaguely remember my Oracle instructor saying about four years ago that using ROWIDs was bad practice in most cases, but I can't remember exactly why he said that. ROWIDs are not reliable, when exports/imports take place and between COMMITS if many users access the same table, if the row could be dropped and re-created. Are there other reasons why someone might not want to use ROWID columns? I am just fishing for opinions. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
Hello Ron I do not know this package because we use another reporter (business Objects). If it is like our there are clear pro and con for using views: Pro: you CONTROL the access to oracle. Con: YOU control the access to oracle. The pro means that you write the selects and can tune them and optimize the access to the DB. The con means that you do a lot of work for them and you will maintain the views each time there are changes. Instead of setting up the connections between tables in the application, you will set it up in views and the developer will simply do select on the view. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]] Sent: Thu, February 21, 2002 6:18 PM To: Multiple recipients of list ORACLE-L Subject: Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
mts_dispatchers question
In my init.ora file I have a line that I am not sure what it means and am hoping someone can clarify it please. This is a parameter setting; mts_dispatchers = (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer) Now the protocol I can understand, but when looking in TFM I don't see anything for the attribute PRE. Can anyone tell me exactly what this mts_dispatcher parameter setting is doing? I am assumming that I am now using MTS. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Instance Monitoring Tools
We use BMC-Patrol. Works great for us.. - Kirti -Original Message- Sent: Thursday, February 21, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Can anyone give me an idea of what are the best Oracle instance monitoring tools on the market? Thanks, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ROWID datatype columns and primary keys
In my previous job, ROWIDs were used left-and-right for performance benefits (accessing a row using ROWIDs is the fastest)and were integral part of the Appl Design at that time. Other than performance, I do not know why one would want to use it. However, it prevented us (Tech Support, SDBA) from table reorgs via export/import. So, we eventually faced performance problems due to other issues. Later, the Appl was redesigned for newer versions of oracle without relying on the ROWIDs.. - Kirti -Original Message- Sent: Thursday, February 21, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Can someone explain to me why some developers like to create ROWID datatype columns in their tables? I am wondering why they sometimes do that instead of using primary keys. I searched for info on this on the Web, but nothing. ROWID access is probably faster than index access, I guess. I vaguely remember my Oracle instructor saying about four years ago that using ROWIDs was bad practice in most cases, but I can't remember exactly why he said that. ROWIDs are not reliable, when exports/imports take place and between COMMITS if many users access the same table, if the row could be dropped and re-created. Are there other reasons why someone might not want to use ROWID columns? I am just fishing for opinions. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle error ORA-04031
[[EMAIL PROTECTED]] I'm getting an ORA-04031: unable to allocate 8192 bytes of shared memory (large pool,unknown object,sort subheap,sort key) error, and am having a hard time solving the issue. http://www.cryer.co.uk/brian/oracle/ORA04031.htm -- James Manning [EMAIL PROTECTED] GPG Key fingerprint = B913 2FBD 14A9 CE18 B2B7 9C8E A0BF B026 EEBB F6E4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Manning INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ROWID datatype columns and primary keys
Oracle changed the ROWID size between Oracle7 and Oracle8. It went from 6 bytes to 10 bytes. So if a developer wrote code that only sized the ROWID to 6 bytes, it's probably not going to work when converted to Oracle8 or 9. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Boivin, Patrice J [SMTP:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Subject: ROWID datatype columns and primary keys Can someone explain to me why some developers like to create ROWID datatype columns in their tables? I am wondering why they sometimes do that instead of using primary keys. I searched for info on this on the Web, but nothing. ROWID access is probably faster than index access, I guess. I vaguely remember my Oracle instructor saying about four years ago that using ROWIDs was bad practice in most cases, but I can't remember exactly why he said that. ROWIDs are not reliable, when exports/imports take place and between COMMITS if many users access the same table, if the row could be dropped and re-created. Are there other reasons why someone might not want to use ROWID columns? I am just fishing for opinions. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ROWID datatype columns and primary keys
I am sure someone has a reason for creating a column with a ROWID datatype but I cannot think of it. Every row has a ROWID column anyway so why create another one. That column would have to be kept update on almost any kind of DDL performed on that table. I cannot imagine populating that field with any other value than actual ROWID which you already have. Rick Boivin, Patrice J To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: mpo.gc.ca Subject: ROWID datatype columns and primary keys Sent by: [EMAIL PROTECTED] 02/21/2002 01:18 PM Please respond to ORACLE-L Can someone explain to me why some developers like to create ROWID datatype columns in their tables? I am wondering why they sometimes do that instead of using primary keys. I searched for info on this on the Web, but nothing. ROWID access is probably faster than index access, I guess. I vaguely remember my Oracle instructor saying about four years ago that using ROWIDs was bad practice in most cases, but I can't remember exactly why he said that. ROWIDs are not reliable, when exports/imports take place and between COMMITS if many users access the same table, if the row could be dropped and re-created. Are there other reasons why someone might not want to use ROWID columns? I am just fishing for opinions. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ROWID datatype columns and primary keys
ROWID's have their place. My preference is that they are used in PL/SQL or other code as a means of quickly locating or relocating a row in a table. Using them procedurally at runtime is generally considered a valid use of ROWID data types. Storing ROWID data is generally considered a bad practice for the reasons you mention. Jared Boivin, Patrice J [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 10:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ROWID datatype columns and primary keys Can someone explain to me why some developers like to create ROWID datatype columns in their tables? I am wondering why they sometimes do that instead of using primary keys. I searched for info on this on the Web, but nothing. ROWID access is probably faster than index access, I guess. I vaguely remember my Oracle instructor saying about four years ago that using ROWIDs was bad practice in most cases, but I can't remember exactly why he said that. ROWIDs are not reliable, when exports/imports take place and between COMMITS if many users access the same table, if the row could be dropped and re-created. Are there other reasons why someone might not want to use ROWID columns? I am just fishing for opinions. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
I would recommend against multiple layers of views, people can become addicted to them -- sometimes you can see five or more layers of views, then people wonder why the server is so slow. Especially if these views rely on database links. 2 or 3 layers of views is OK, I suppose. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systemes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Region des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ROWID datatype columns and primary keys
Patrice, The only reason I can think of creating a column with a datatype of ROWID, is in order to store a rowid. Why you need to store the rowid escapes me as the rowid is available as a pseudocolumn anyway. It is also dangerous to store this rowid in a column, as it can change. During and import/export as you said, but also on partitioned tables if the partitioning key value changes and the table has been setup to allow the row movement Just my 2c Paul -Original Message- Sent: Thursday, February 21, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Can someone explain to me why some developers like to create ROWID datatype columns in their tables? I am wondering why they sometimes do that instead of using primary keys. I searched for info on this on the Web, but nothing. ROWID access is probably faster than index access, I guess. I vaguely remember my Oracle instructor saying about four years ago that using ROWIDs was bad practice in most cases, but I can't remember exactly why he said that. ROWIDs are not reliable, when exports/imports take place and between COMMITS if many users access the same table, if the row could be dropped and re-created. Are there other reasons why someone might not want to use ROWID columns? I am just fishing for opinions. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
It seems to me that you're not dinging views per se here--you're against the dev's intended use of production data. So if those same SELECT statements that make up the view were instead baked into the crystal report file sent anew every time the report was executed, it'd be the same problem (maybe worse, since now you're parsing the SQL planning execution more frequently?). You buy that? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 9:09 AM To: Multiple recipients of list ORACLE-L IMO views are often used as a substitute for creating reporting structures. Using views makes for easy report/SQL creation, but tends to be a tuning and performance nightmare. It's hard to tune, and will likely never perform well. I'm going through similar issues here right now. A number of users need to do reporting on production data. No way, no how will they be allowed to do it on the production database. It's a manufacturing database and performance is critical to this system. I've done some prototypes of the tables they need to report on. Basically a copy of the production tables in another database. Those that have a long refresh cycle ( 1+ days ) get bitmap indexes on most columns. Those that need to be close to realtime get Btree indexes instead and will be refreshed every few minutes ( refresh time pending negotiation with users :). This is not exactly a data mart as I would like to have it: no star schemas. But it's what I have time for right now, gets the reports off of production and is *much* faster to query. HTH Jared Smith, Ron L. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
Well, I am dinging views in the sense that they are easily abused for reporting purposes. Recreating the data on another server lets me create indexes that I wouldn't want to create on the production server, and indexes that I can't create in production, such as bitmap indexes. The queries will be faster, and more importantly, they won't be hitting production. Jared Pardee, Roy E [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 10:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Anybody against using views? It seems to me that you're not dinging views per se here--you're against the dev's intended use of production data. So if those same SELECT statements that make up the view were instead baked into the crystal report file sent anew every time the report was executed, it'd be the same problem (maybe worse, since now you're parsing the SQL planning execution more frequently?). You buy that? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 9:09 AM To: Multiple recipients of list ORACLE-L IMO views are often used as a substitute for creating reporting structures. Using views makes for easy report/SQL creation, but tends to be a tuning and performance nightmare. It's hard to tune, and will likely never perform well. I'm going through similar issues here right now. A number of users need to do reporting on production data. No way, no how will they be allowed to do it on the production database. It's a manufacturing database and performance is critical to this system. I've done some prototypes of the tables they need to report on. Basically a copy of the production tables in another database. Those that have a long refresh cycle ( 1+ days ) get bitmap indexes on most columns. Those that need to be close to realtime get Btree indexes instead and will be refreshed every few minutes ( refresh time pending negotiation with users :). This is not exactly a data mart as I would like to have it: no star schemas. But it's what I have time for right now, gets the reports off of production and is *much* faster to query. HTH Jared Smith, Ron L. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the
RE: Anybody against using views?
uh-oh... a PROGRAMMER has been lurking... :) -Original Message- Sent: Thursday, February 21, 2002 1:24 PM To: Multiple recipients of list ORACLE-L It seems to me that you're not dinging views per se here--you're against the dev's intended use of production data. So if those same SELECT statements that make up the view were instead baked into the crystal report file sent anew every time the report was executed, it'd be the same problem (maybe worse, since now you're parsing the SQL planning execution more frequently?). You buy that? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 9:09 AM To: Multiple recipients of list ORACLE-L IMO views are often used as a substitute for creating reporting structures. Using views makes for easy report/SQL creation, but tends to be a tuning and performance nightmare. It's hard to tune, and will likely never perform well. I'm going through similar issues here right now. A number of users need to do reporting on production data. No way, no how will they be allowed to do it on the production database. It's a manufacturing database and performance is critical to this system. I've done some prototypes of the tables they need to report on. Basically a copy of the production tables in another database. Those that have a long refresh cycle ( 1+ days ) get bitmap indexes on most columns. Those that need to be close to realtime get Btree indexes instead and will be refreshed every few minutes ( refresh time pending negotiation with users :). This is not exactly a data mart as I would like to have it: no star schemas. But it's what I have time for right now, gets the reports off of production and is *much* faster to query. HTH Jared Smith, Ron L. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
RE: Instance Monitoring Tools
StatsPack, V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, trace files, and a good DBA... only the last item is not free. Take the money you save on tools and give it to the DBA. Sorry if this sounds flippant but is there anything which the tools provide via a GUI interface that isn't already available via the data dictionary, Oracle utilities and packages, and O/S utilities? In fact, most of the tools get their data from the same aforementioned sources. I've also gotten monitoring information from Oracle's supplied MIB's via SNMP just like OEM does but I used the pathologically eclectic rubbish lister tool. :-) Tools are not a substitute... if I give you a hammer it doesn't make you a master carpenter. I guess it's a matter of preference. I like making my own tools, or borrowing scripts from others and adding them to my toolbox. Of course the commercial tools are more complete than my grab bag of stuff which is under perpetual development and I sometimes spend a lot of time trying to find a script amid the chaos of the unkempt toolbox. ;-) I know there one of those whatchamagizmos in there now where is it? Living in Big Sky Country without buffer zones... Steve Orr -Original Message- Sent: Thursday, February 21, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Can anyone give me an idea of what are the best Oracle instance monitoring tools on the market? Thanks, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:ROWID datatype columns and primary keys
Patrice, ROWID access to data is the fastest way to get from here to there since it, the rowid, tells Oracle right where the data is. The biggest problem that I see with rowid's is that 1) they changed from Oracle 7 to 8 most likely will change again in the future, 2) when you re-org the table they change, and 3) people sometimes like to manipulate the rowid's which can have some very strange results. My favorite use or rowid's is to simulate a for update of cursor when I need to do a commit and/or rollback in the middle of the cursor. I've also used them when I want to delete a specific row of data and don't have any other unique way to id it. I do not, and very strongly do not, recommend manipulating rowid's for any reason, period. Extract them from the database and use them as provided. Dick Goulet Reply Separator Author: Boivin; Patrice J [EMAIL PROTECTED] Date: 2/21/2002 10:18 AM Can someone explain to me why some developers like to create ROWID datatype columns in their tables? I am wondering why they sometimes do that instead of using primary keys. I searched for info on this on the Web, but nothing. ROWID access is probably faster than index access, I guess. I vaguely remember my Oracle instructor saying about four years ago that using ROWIDs was bad practice in most cases, but I can't remember exactly why he said that. ROWIDs are not reliable, when exports/imports take place and between COMMITS if many users access the same table, if the row could be dropped and re-created. Are there other reasons why someone might not want to use ROWID columns? I am just fishing for opinions. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Instance Monitoring Tools
How would you rate OEM's Instance Monitor and DBArtisan tools with BMC-Patrol. Thanks Srini -Original Message- Sent: Thursday, February 21, 2002 1:43 PM To: Multiple recipients of list ORACLE-L We use BMC-Patrol. Works great for us.. - Kirti -Original Message- Sent: Thursday, February 21, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Can anyone give me an idea of what are the best Oracle instance monitoring tools on the market? Thanks, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
You use that word like it's a *bad* thing to be. 8^) -Roy (Who was originally tempted to say: Look DBA, that SQL's coming to your server--we can do it easy, or we can do it hard, but it's coming. Do you want to have to sleuth out why your db is dog-slow every day at 3:30 when my users are running the report I gave them, or do you want to see what I'm planning to do up front have a chance to kibbitz? But who also knows better than to say things like that to the DBA.) Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 10:59 AM To: Multiple recipients of list ORACLE-L uh-oh... a PROGRAMMER has been lurking... :) -Original Message- Sent: Thursday, February 21, 2002 1:24 PM To: Multiple recipients of list ORACLE-L It seems to me that you're not dinging views per se here--you're against the dev's intended use of production data. So if those same SELECT statements that make up the view were instead baked into the crystal report file sent anew every time the report was executed, it'd be the same problem (maybe worse, since now you're parsing the SQL planning execution more frequently?). You buy that? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 9:09 AM To: Multiple recipients of list ORACLE-L IMO views are often used as a substitute for creating reporting structures. Using views makes for easy report/SQL creation, but tends to be a tuning and performance nightmare. It's hard to tune, and will likely never perform well. I'm going through similar issues here right now. A number of users need to do reporting on production data. No way, no how will they be allowed to do it on the production database. It's a manufacturing database and performance is critical to this system. I've done some prototypes of the tables they need to report on. Basically a copy of the production tables in another database. Those that have a long refresh cycle ( 1+ days ) get bitmap indexes on most columns. Those that need to be close to realtime get Btree indexes instead and will be refreshed every few minutes ( refresh time pending negotiation with users :). This is not exactly a data mart as I would like to have it: no star schemas. But it's what I have time for right now, gets the reports off of production and is *much* faster to query. HTH Jared Smith, Ron L. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a
RE: ROWID datatype columns and primary keys
A rowid column can be put to very good use in transitional tables for batch processing or temporary tables. For example, check out the CHAINED_ROWS table that Oracle creates via the utlchain.sql script. I've seen this technique in the commercial Banner Utilities application from SCT. It may be a rare but appropriate usage. Steve Orr -Original Message- Sent: Thursday, February 21, 2002 12:04 PM To: Multiple recipients of list ORACLE-L Patrice, The only reason I can think of creating a column with a datatype of ROWID, is in order to store a rowid. Why you need to store the rowid escapes me as the rowid is available as a pseudocolumn anyway. It is also dangerous to store this rowid in a column, as it can change. During and import/export as you said, but also on partitioned tables if the partitioning key value changes and the table has been setup to allow the row movement Just my 2c Paul -Original Message- Sent: Thursday, February 21, 2002 1:18 PM To: Multiple recipients of list ORACLE-L Can someone explain to me why some developers like to create ROWID datatype columns in their tables? I am wondering why they sometimes do that instead of using primary keys. I searched for info on this on the Web, but nothing. ROWID access is probably faster than index access, I guess. I vaguely remember my Oracle instructor saying about four years ago that using ROWIDs was bad practice in most cases, but I can't remember exactly why he said that. ROWIDs are not reliable, when exports/imports take place and between COMMITS if many users access the same table, if the row could be dropped and re-created. Are there other reasons why someone might not want to use ROWID columns? I am just fishing for opinions. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
I have a schema that *loves* views ... they have a view that is a join of 4 views which individually are joins of views and tables. Can you see optimizer going nuts so when we tested CBO, this schema owner exclaimed 'CBO doesn't work!', so we were back to using RBO. I have finally convinced them to stop creating a 'create view v_tablename as select * from tablename' ... that was my first step. So, I have nothing against views, but only when used in moderation. Cheers Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Re: Instance Monitoring Tools
I know of a couple of tools that show more info than the fixed views ;-) Orr, Steve wrote: StatsPack, V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, trace files, and a good DBA... only the last item is not free. Take the money you save on tools and give it to the DBA. Sorry if this sounds flippant but is there anything which the tools provide via a GUI interface that isn't already available via the data dictionary, Oracle utilities and packages, and O/S utilities? In fact, most of the tools get their data from the same aforementioned sources. I've also gotten monitoring information from Oracle's supplied MIB's via SNMP just like OEM does but I used the pathologically eclectic rubbish lister tool. :-) Tools are not a substitute... if I give you a hammer it doesn't make you a master carpenter. I guess it's a matter of preference. I like making my own tools, or borrowing scripts from others and adding them to my toolbox. Of course the commercial tools are more complete than my grab bag of stuff which is under perpetual development and I sometimes spend a lot of time trying to find a script amid the chaos of the unkempt toolbox. ;-) I know there one of those whatchamagizmos in there now where is it? Living in Big Sky Country without buffer zones... Steve Orr -Original Message- Sent: Thursday, February 21, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Can anyone give me an idea of what are the best Oracle instance monitoring tools on the market? Thanks, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Instance Monitoring Tools
Never worked with those. Patrol was already here when I joined. And the reason was that it was available for all the H/W platfoems the company used. - Kirti -Original Message- Sent: Thursday, February 21, 2002 1:24 PM To: Multiple recipients of list ORACLE-L How would you rate OEM's Instance Monitor and DBArtisan tools with BMC-Patrol. Thanks Srini -Original Message- Sent: Thursday, February 21, 2002 1:43 PM To: Multiple recipients of list ORACLE-L We use BMC-Patrol. Works great for us.. - Kirti -Original Message- Sent: Thursday, February 21, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Can anyone give me an idea of what are the best Oracle instance monitoring tools on the market? Thanks, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
and I should have finished with and he seems to know what he's talking about.. -Original Message- Sent: Thursday, February 21, 2002 2:30 PM To: Multiple recipients of list ORACLE-L You use that word like it's a *bad* thing to be. 8^) -Roy (Who was originally tempted to say: Look DBA, that SQL's coming to your server--we can do it easy, or we can do it hard, but it's coming. Do you want to have to sleuth out why your db is dog-slow every day at 3:30 when my users are running the report I gave them, or do you want to see what I'm planning to do up front have a chance to kibbitz? But who also knows better than to say things like that to the DBA.) Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 10:59 AM To: Multiple recipients of list ORACLE-L uh-oh... a PROGRAMMER has been lurking... :) -Original Message- Sent: Thursday, February 21, 2002 1:24 PM To: Multiple recipients of list ORACLE-L It seems to me that you're not dinging views per se here--you're against the dev's intended use of production data. So if those same SELECT statements that make up the view were instead baked into the crystal report file sent anew every time the report was executed, it'd be the same problem (maybe worse, since now you're parsing the SQL planning execution more frequently?). You buy that? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 9:09 AM To: Multiple recipients of list ORACLE-L IMO views are often used as a substitute for creating reporting structures. Using views makes for easy report/SQL creation, but tends to be a tuning and performance nightmare. It's hard to tune, and will likely never perform well. I'm going through similar issues here right now. A number of users need to do reporting on production data. No way, no how will they be allowed to do it on the production database. It's a manufacturing database and performance is critical to this system. I've done some prototypes of the tables they need to report on. Basically a copy of the production tables in another database. Those that have a long refresh cycle ( 1+ days ) get bitmap indexes on most columns. Those that need to be close to realtime get Btree indexes instead and will be refreshed every few minutes ( refresh time pending negotiation with users :). This is not exactly a data mart as I would like to have it: no star schemas. But it's what I have time for right now, gets the reports off of production and is *much* faster to query. HTH Jared Smith, Ron L. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Instance Monitoring Tools
I am usually anti tools due to the price mostly. However while I have quite a suite of components to quickly surround my Oracle DB's with monitoring, alerting, performance charting etc...I have no such toolkit for other databases. However, I am being confronted with situations where I will also be responsible for DB2, SYBASE and Informix databases. Large corporate customers want to see an integrated monitoring and reporting environment. In this case an off the shelf software solution can be my only choice. - Ethan -Original Message- Sent: Thursday, February 21, 2002 1:44 PM To: Multiple recipients of list ORACLE-L I know of a couple of tools that show more info than the fixed views ;-) Orr, Steve wrote: StatsPack, V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, trace files, and a good DBA... only the last item is not free. Take the money you save on tools and give it to the DBA. Sorry if this sounds flippant but is there anything which the tools provide via a GUI interface that isn't already available via the data dictionary, Oracle utilities and packages, and O/S utilities? In fact, most of the tools get their data from the same aforementioned sources. I've also gotten monitoring information from Oracle's supplied MIB's via SNMP just like OEM does but I used the pathologically eclectic rubbish lister tool. :-) Tools are not a substitute... if I give you a hammer it doesn't make you a master carpenter. I guess it's a matter of preference. I like making my own tools, or borrowing scripts from others and adding them to my toolbox. Of course the commercial tools are more complete than my grab bag of stuff which is under perpetual development and I sometimes spend a lot of time trying to find a script amid the chaos of the unkempt toolbox. ;-) I know there one of those whatchamagizmos in there now where is it? Living in Big Sky Country without buffer zones... Steve Orr -Original Message- Sent: Thursday, February 21, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Can anyone give me an idea of what are the best Oracle instance monitoring tools on the market? Thanks, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Anybody against using views?
A thing about views, that nobody else seem to have mentioned is that the view text is not stored in the dictionary cache. Hence, each time you hard parse a sql statement with a view, Oracle will query view$ to get the text. Hence, if you are at the limit of scalability or performance due to hard parses, views make things worse. /Bjørn. On Thursday 21 February 2002 17:18, you wrote: We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Problem with standby database listener
Fellow dba's, We have come across a weird problem with a standby database. Every timewe issue "alter database mount standby database"on thestandby database, the primary database fails to connect through listener. It becomes OK after primary database is shutdown and restarted of course this can is notan acceptable solution since the standby will be opened every week for reporting. Anyone experience this before? Any direction would be appreciated. Thanks Stephen AndertScottsdale, Arizona
RE: Anybody against using views?
How did you determine that it's not stored in the DC? The DC is not a data store but it's a cache. Just curios. Thanks, Waleed -Original Message- Sent: Thursday, February 21, 2002 3:48 PM To: Multiple recipients of list ORACLE-L A thing about views, that nobody else seem to have mentioned is that the view text is not stored in the dictionary cache. Hence, each time you hard parse a sql statement with a view, Oracle will query view$ to get the text. Hence, if you are at the limit of scalability or performance due to hard parses, views make things worse. /Bjørn. On Thursday 21 February 2002 17:18, you wrote: We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with standby database listener
I have 8.1.7.0 on Solaris 8 and also had this problem. Somehow the way I get around this is to go back to the Primary db and reset the log_archive_dest_2 to whatever the identifier of the standby db is. (even if the log_archive_dest_2 is already set). Then I do the standby recovery, then managed recovery. This probably explains why yours work after you bounce your Primary db. You must have the standby db location set in the init.ora file. What I do, however, is force a reset of the standby db location after I startup the standby db. This way I don't have to bounce my Primary db. Everything's scripted. Clear as mud? HTH. Ross -Original Message- Sent: Friday, 22 February 2002 7:59 To: Multiple recipients of list ORACLE-L Fellow dba's, We have come across a weird problem with a standby database. Every time we issue alter database mount standby database on the standby database, the primary database fails to connect through listener. It becomes OK after primary database is shutdown and restarted of course this can is not an acceptable solution since the standby will be opened every week for reporting. Anyone experience this before? Any direction would be appreciated. Thanks Stephen Andert Scottsdale, Arizona -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ross Collado INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN Question
Ashoke If I understand you correctly, you are using RMAN to backup to disk, rather than to a media manager. You are asking whether you can archive these in RMAN, plus leave them on disk so they are readily available for recovery. I have used this configuration, and I have found that RMAN leaves the archive logs on disk after it has backed them up. Also, I believe but have not tested this, that in case of a recovery, RMAN first looks to the original unarchived disk location for the archive logs and if it can't find them there, it will retrieve them from its backup location (disk file or tape if you are using a media manager), and put them where they should be, then continue the recovery. After thinking about this, I decided that using RMAN to backup the archive logs wasn't buying me anything. Just making my setup more complicated. If I was backing up to a media manager, then having RMAN back up the archive logs would be great because it would be moving them to tape. As it is, RMAN isn't compressing them before moving them to disk. My regular nightly tape backup will give me a safety copy on tape. If I feel I need a disk copy, I will just compress the files to another location and end up with something significantly smaller. If I have overlooked something in my logic, please point that out to me because I am just beginning to learn RMAN and am frequently interrupted by other DBA duties, so learning is going very slowly. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 21, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Greetings, We are at Oracle 8.1.7 on Sun Solaris 7. Can we backup all the archive logs at any point of time but not deleting all these archive logs as we like to keep the archive logs for 2 days(SYSDATE-1) in the disk so that we don't need to restore the archive logs from rman backup in case of some recovery up to point within last 2 days. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Missing datafile that cannot be recovered (cloned) and SMON probl
I have a challenge for you. We have 3 temporary segments (in a permanent user data tablespace) which smon cannot clean up. The errors in the alert log are; Errors in file /NEWTST_DB/oratst/tstdb/8.1.7/admin/TST2/bdump/smon_3117_tst2.trc: ORA-0: Message not found; product=RDBMS; facility=ORA The errors in the trc file are pretty much the same. Let me tell you how we got to this point: 1) We cloned our production database to a test instance. In creating the new control file for test (which we had to do to rename the database and rename the datafiles), we failed to include in the datafile list one of the newly added datafiles in production (we used an old create statement, forgetting that the production layout had changed). (The datafile name was 1 of 5 datafiles which make up a user tablespace). The controlfile create command succeeded and we were none the wiser. UNTIL... We started to get errors a few days later when trying to insert into a table which resides in the tablespace. We got errors to the fact of 'datafile 306 does not exist, cannot insert. At that point, oracle decided to put a stub in the datafile system tables for our missing datafile. So now we have the dreaded '/TST/oradata/MISSING00306' entry. 2) I ran a query against the dba_extents table to find the objects which have data in the missing datafile. There were three tables. I know the proper recovery method is to restore the datafile and recover archive logs. We cannot do this for two reasons. The first is the fact that this is a cloned database and was opened with resetlogs. The second is the fact that the datafile was never in the controlfile create statement. Thus it's header information still points to the production database name, while all the other datafiles now have the new test database name in their header information. Oracle will not let you add it now to the existing datafiles. Nor will it let you recreate the controlfile with the filename included. All datafile headers must contain the same database name when creating the controlfile. The next suggestion would be to reclone. We cannot do that either as we've already performed too much work in the test instance to trash it. 3) So, I decided to drop the objects which were referenced in the missing datafile and recreate them. That way the objects will be built in a good tablespace and the users can insert into them. This worked, sort of. The drop seemed to succeed, even though it produced the errors above and dropped my connection. But I was able to log back in and successfully recreate the objects. I assumed oracle was trying to update the freelists header in the datafile and bombed when it could not find the datafile. It did remove the table names from the segment information, as I was able to create new tables with the same names. 4) Ok, so now I have good tables and the users are working fine. So what's the problem? Well, I queried the extents view again to see if anything was still pointing the missing datafile. yes. It seems oracle modified my old tables (pre-rebuilt) to temporary segments! I now have the same extent usage and # extents for 3 temporary segments in the same locations. That's ok, because I can still use my new tables without problems. The problem is SMON. SMON is trying to coalesce/drop temporary segments and spewing the; ORA-0: Message not found; product=RDBMS; facility=ORA error every 10 seconds! Is there any way to drop these temporary segments? Any way to drop the references to the missing datafiles? I have already done a 'alter datafile offline drop;' and it succeeded, but smon is still getting errors. How do I shut SMON up? How do I get rid of these temporary segments (which have data in a missing datafile)? I've bounced the instance also. I believe my only recourse now is to drop the tablespace which contained the missing datafile, which should remove all references to the datafile, correct? I don't even know if that's possible as it's over 20gb and I have no way to move all the objects out it (contains Apps tables and lobs and such). Anyone have any bright ideas or is our instance hopelessly confused? __ Glenn Travis Database Administrator Business Intelligence Support [EMAIL PROTECTED] 919-531-0434 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Re: Dropping users from Oracle 9i seed database.
first off, research what all of those users are, then you'll have your answer. joe Yuval Arnon wrote: Hi, Using Oracle Configuration Manager a handful of users are created. Which of these users can be safely dropped without affecting Oracle health. List of users. AURORA$JIS$UTILITY$ CTXSYS HR LBACSYS MDSYS OE OLAPSYS ORDSYS OSE$HTTP$ADMIN OUTLN PM QS QS_CBADM QS_CS QS_ES QS_OS QS_WS SCOTT SH WKSYS TIA Yuval. -- Joe Testa, Oracle DBA Nothing new to put here, hmm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to make deletes faster.
Is it replicated? Any indexes? -Original Message- Sent: Thursday, February 21, 2002 5:13 PM To: Multiple recipients of list ORACLE-L Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Anybody against using views?
Steve Adams has some info about this, but doesn't say how he determined this. http://www.ixora.com.au/q+a/0104/03174106.htm Khedr, Waleed wrote: How did you determine that it's not stored in the DC? The DC is not a data store but it's a cache. Just curios. Thanks, Waleed -Original Message- Sent: Thursday, February 21, 2002 3:48 PM To: Multiple recipients of list ORACLE-L A thing about views, that nobody else seem to have mentioned is that the view text is not stored in the dictionary cache. Hence, each time you hard parse a sql statement with a view, Oracle will query view$ to get the text. Hence, if you are at the limit of scalability or performance due to hard parses, views make things worse. /Bjørn. On Thursday 21 February 2002 17:18, you wrote: We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Dropping users from Oracle 9i seed database.
Yuval, Since you're already checking this out, would you mind running this script and sending me the output? ( provided the password for these accounts are all defaults ) It would be useful for detecting default passwords in Oracle installed accounts. Thanks, Jared select username, password from dba_users where username in ( 'AURORA$JIS$UTILITY$', 'CTXSYS', 'HR', 'LBACSYS', 'MDSYS', 'OE', 'OLAPSYS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_CBADM', 'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'SCOTT', 'SH', 'WKSYS' ); Yuval Arnon [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 02:18 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Dropping users from Oracle 9i seed database. Hi, Using Oracle Configuration Manager a handful of users are created. Which of these users can be safely dropped without affecting Oracle health. List of users. AURORA$JIS$UTILITY$ CTXSYS HR LBACSYS MDSYS OE OLAPSYS ORDSYS OSE$HTTP$ADMIN OUTLN PM QS QS_CBADM QS_CS QS_ES QS_OS QS_WS SCOTT SH WKSYS TIA Yuval. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to make deletes faster.
How'bout 1) add a char(1) column...call it DELETE_ME and allow NULLS 2) index the DELETE_ME column 3) every night run an update like UPDATE yourbigwhackintable SET DELETE_ME = 'Y' WHERE the row satisfies your delete condition 4) DELETE FROM yourbigwhackintable WHERE DELETE_ME = 'Y' This is assuming of course you have free reign over the app as far as adding a column and the index. The index won't store the NULLs of the non-deleteable rows if you were worrying about index size BTW. Note..this technique is not 'mine'...I got it from a book (can't remember which one!) so your mileage may vary! Cheers Jeff Herrick Jeff Herrick Associates On Thu, 21 Feb 2002, sonia pajerowski wrote: Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Herrick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to make deletes faster.
that init.ora parm is an undocumented one, i'm surprised your database even came up. its _make_delete_faster=true, kinda like _make_sql_run_faster. joe sonia pajerowski wrote: Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Joe Testa, Oracle DBA Nothing new to put here, hmm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with standby database listener
Could it be that the standby is auto-registering itself with the listener for the production database? We've had a couple of incidents where a development DBA copied the init.ora file from a production database configured forMTS. But the listener parameters were not changed and all new connections were redirected to the development database. I suspect that the standby is auto-registering itself with the listener and bumping of production. Bouncing the production server then does it back to the standby. HTH Tony Aponte -Original Message-From: Stephen Andert [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 21, 2002 3:59 PMTo: Multiple recipients of list ORACLE-LSubject: Problem with standby database listener Fellow dba's, We have come across a weird problem with a standby database. Every timewe issue "alter database mount standby database"on thestandby database, the primary database fails to connect through listener. It becomes OK after primary database is shutdown and restarted of course this can is notan acceptable solution since the standby will be opened every week for reporting. Anyone experience this before? Any direction would be appreciated. Thanks Stephen AndertScottsdale, Arizona
RE: How to make deletes faster.
Sonia - Have you considered Oracle's Partitioning Option? Since you mention that your table is non-partitioned, I assume you have considered this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 21, 2002 4:13 PM To: Multiple recipients of list ORACLE-L Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anybody against using views?
This is from the Steve's page: Oracle does not cache view definitions in the library cache or dictionary cache. If you run this sql after selecting from any view: select * from V$DB_OBJECT_CACHE where type = 'VIEW' You will find views get cached like any other objects and the view will listed as cached. Waleed -Original Message- Sent: Thursday, February 21, 2002 6:03 PM To: Multiple recipients of list ORACLE-L Steve Adams has some info about this, but doesn't say how he determined this. http://www.ixora.com.au/q+a/0104/03174106.htm Khedr, Waleed wrote: How did you determine that it's not stored in the DC? The DC is not a data store but it's a cache. Just curios. Thanks, Waleed -Original Message- Sent: Thursday, February 21, 2002 3:48 PM To: Multiple recipients of list ORACLE-L A thing about views, that nobody else seem to have mentioned is that the view text is not stored in the dictionary cache. Hence, each time you hard parse a sql statement with a view, Oracle will query view$ to get the text. Hence, if you are at the limit of scalability or performance due to hard parses, views make things worse. /Bjørn. On Thursday 21 February 2002 17:18, you wrote: We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rollback Segments
And how about the customer who used 'ior i' to 'start up the database' when he should have used 'ior w'??!?!? (for those who have forgotten: ior i - initialize (create) the V5 Db, ior w - warm start the database, ior s - shutdown) Welcome Anjo to this list. We wouldn't have been able do our jobs well without your exposition on 'Wait Events'. John Kanagaraj 5.1.17, 5.1.22, 6.0.27, 6.0.30, 6.0.33, 7.0.14, 7.0.16, 7.1.4, 7.1.6, 7.2.3, 7.3.2, 7.3.4, 8.0.4, 8.0.5, 8.0.6, 8.1.5, 8.1.6, 8.1.7 and finally 9.0.1!! (milestones on my memory lane) -Original Message- Sent: Tuesday, February 19, 2002 2:43 AM To: Multiple recipients of list ORACLE-L How about iag/iap ? And rpt ? Oh and SQL*Menu ? And there were about 14 enqueue/locks in Oracle Version 5 as far as I can remember. Anjo Kolk Brings back memories of joining Oracle Europe in 1985 ;-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 6:43 AM I remember the BI.ORA (Before-Image) file, IOR and ODS in Oracle 5. Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Rachel Carmichael [EMAIL PROTECTED] 19/02/2002 06:18 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: RE: Rollback Segments UFI no, but the rest... that's where I started in Oracle -- version 5 --- Conboy, Jim [EMAIL PROTECTED] wrote: Holy cow Mladen, what a memory! Does anybody else remember (or admit to) using UFI? Jim ** ...does anybody still remember VAX/VMS, ORACLE$BI, IOR and ODT?... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Conboy, Jim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access /
RE: How to make deletes faster.
I have only one cursor which selects the max sequence number and all the records before that sequence number are deleted. I was just wondering if oracle 9i has a truncate like option to delete records with nologging option. We are in the process of partitioning but it might take couple of weeks to implement that option Thanks Sonia P. --- Aponte, Tony [EMAIL PROTECTED] wrote: How are you selecting the rows to be deleted? Is it in one cursor driving a loop with incremental commits or is it done via batch cycles of 10,000-row delete ... from ...where commit; delete ... from ...where commit; .? Tony Aponte -Original Message- From: sonia pajerowski [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 5:13 PM To: Multiple recipients of list ORACLE-L Subject: How to make deletes faster. Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sql question
Hi all, I have a SQL question. Suppose I have a table called RANGE looks like this: begin end 1 9 1019 2029 Then I have a table NUMBERS that's full of bunch of numbers like this: num 1 2 3 4 ... 98 99 100 I want to write a SQL that returns the number that are within the ranges defined in the RANGE table. So number 1 through 29 should be returned. Can someone help me with this? Thanks. Rich _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oracle dba INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: password question
The encrypted password is available in dba_users, but it's not possible to decrypt it. --- Ghadge,Sameer [EMAIL PROTECTED] wrote: Hi, Oracle stores password in encrypted format, is it possible (suppose i have access to dba_users table) to retrieve and descrypt the password. thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql question
To use your example column names: select num from numbers where num between (select min(begin) from range) and (select max(end) from range); --- oracle dba [EMAIL PROTECTED] wrote: Hi all, I have a SQL question. Suppose I have a table called RANGE looks like this: begin end 1 9 1019 2029 Then I have a table NUMBERS that's full of bunch of numbers like this: num 1 2 3 4 ... 98 99 100 I want to write a SQL that returns the number that are within the ranges defined in the RANGE table. So number 1 through 29 should be returned. Can someone help me with this? Thanks. Rich _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oracle dba INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:password question
I've tried a few times, especially when passwords get embedded into code for which the developer either accidentally or on purpose looses the source. I have not succeeded, nor have I heard of anyone who has. And you can be assured OTS will not help you either. Dick Goulet Reply Separator Author: Ghadge;Sameer [EMAIL PROTECTED] Date: 2/21/2002 7:38 PM Hi, Oracle stores password in encrypted format, is it possible (suppose i have access to dba_users table) to retrieve and descrypt the password. thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: password question
Sameer, The obvious answer you can't decrypt the password. Else a number of people would think harder about buying Oracle. It's a one-way hash -- you can't get the original value back. It is possible to temporarily reset a user's password to something else, become the user with your own password and reset the password back to the original value, without knowing what the original password was. e.g. suppose a user's encrypted password string is 'ABCDEFGHIJKLMNOP', read this string from DBA_USERS, store it someplace (a variable, a table ;), execute ALTER USER username identified by mypassword, login as the user CONNECT username/mypassword, do your SQLs as that user, reset the user's password ALTER USER username identified by values 'ABCDEFGHIJKLMNOP' Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Ghadge,Sameer [EMAIL PROTECTED] 22/02/2002 11:38 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: password question Hi, Oracle stores password in encrypted format, is it possible (suppose i have access to dba_users table) to retrieve and descrypt the password. thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql question
Rich, Are you sure that that is what you want ? Suppose your range values were something like : begin end 1 9 1519 2329 ie, the RANGE table shows that 10-14 and 20-22 are invalid (not allowed) values. Your problem statement and the SQL that Paul provides for the problem statement would return numbers like 10, 11, 20,21 which are, actually, invalid. You'd have to write a cursor to loop through the valid ranges ?? Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Paul Baumgartel [EMAIL PROTECTED] 22/02/2002 12:43 PM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: Re: sql question To use your example column names: select num from numbers where num between (select min(begin) from range) and (select max(end) from range); --- oracle dba [EMAIL PROTECTED] wrote: Hi all, I have a SQL question. Suppose I have a table called RANGE looks like this: begin end 1 9 1019 2029 Then I have a table NUMBERS that's full of bunch of numbers like this: num 1 2 3 4 ... 98 99 100 I want to write a SQL that returns the number that are within the ranges defined in the RANGE table. So number 1 through 29 should be returned. Can someone help me with this? Thanks. Rich _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oracle dba INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
data dictionary View about jobs
Hi, All, Is there any Oracle data dict view to see whether the dbms jobs is on the job queue or not. Why I ask this is that I use dbms_job.remove to remove one job, later on, I check it using user_jobs, it's still there. Thus issue dbms_job.remove again, this time, this execution seems hung there. I log into sys and issue dbms_job.remove, I got the following error ORA-23421: job number 41 is not a job in the job queue ORA-06512: at SYS.DBMS_SYS_ERROR, line 86 ORA-06512: at SYS.DBMS_IJOB, line 525 ORA-06512: at SYS.DBMS_JOB, line 166 ORA-06512: at line 2 Could anyone tell me why this happens? or is there any dict view to check the job queue? Thanks in advance, Chuan Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
oracle on linux vs oracle on NT
pros and cons of each please... stability, performance, management, etc... for small scale oracle financials implementation (125 users max) and what would be the best and most reliable linux distribution to use. on Compaq proliant hardware no experience on oracle on linux but with experience with NT (very disappointing) Any input will be very much appreciated. =) -- Maria Aurora VT de la Vega (OCP) Database Specialist Philippine Stock Exchange, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: password question
Title: RE: password question One way hash, yes, but can use username to forceably crack the password (same idea as unix CRACK password cracking program). Hash is consistent which is why you can pick up the password string and drop it to another database (same username) and have the password work on the new machine. A non Oracle example would be to perform the following at the unix prompt: echo 'some test string' | md5 With the hash, you could create several variations and test against the known or 'captured' hash. Again, brute force method. Jon Baker Database Architect [EMAIL PROTECTED] www.netsec.net -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:18 AM To: Multiple recipients of list ORACLE-L Subject: Re: password question Sameer, The obvious answer you can't decrypt the password. Else a number of people would think harder about buying Oracle. It's a one-way hash -- you can't get the original value back. It is possible to temporarily reset a user's password to something else, become the user with your own password and reset the password back to the original value, without knowing what the original password was. e.g. suppose a user's encrypted password string is 'ABCDEFGHIJKLMNOP', read this string from DBA_USERS, store it someplace (a variable, a table ;), execute ALTER USER username identified by mypassword, login as the user CONNECT username/mypassword, do your SQLs as that user, reset the user's password ALTER USER username identified by values 'ABCDEFGHIJKLMNOP' Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Ghadge,Sameer [EMAIL PROTECTED] 22/02/2002 11:38 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: password question Hi, Oracle stores password in encrypted format, is it possible (suppose i have access to dba_users table) to retrieve and descrypt the password. thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: oracle on linux vs oracle on NT
Go for Suse Linux and Oracle Apps 11i. Pretty stable and performance is very good. Have tried it on HPNetserver with 4 CPUs/2GB RAM/170GB Disk storage on 2 raidsets. -Original Message- Sent: Friday, February 22, 2002 2:09 PM To: Multiple recipients of list ORACLE-L pros and cons of each please... stability, performance, management, etc... for small scale oracle financials implementation (125 users max) and what would be the best and most reliable linux distribution to use. on Compaq proliant hardware no experience on oracle on linux but with experience with NT (very disappointing) Any input will be very much appreciated. =) -- Maria Aurora VT de la Vega (OCP) Database Specialist Philippine Stock Exchange, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cabansay, Yoyong INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle8i - Oracle9i issue??????
Title: Oracle8i - Oracle9i issue?? Hello Guru's I wonder if any of you have had this problem, we have a external company developing a software package in C#. On their Oracle9i test system, the software works fine, however once brought accross to our systems, which is an Oracle8i ( 817) system the software gives a (ORA - 00933 SQL command not properly ended) error, but funny enough only on the one window. I am told by them that they do straight select from a table, saving the returned columns in variables etc. The only difference is they don't have any semi-colons (;) at the end of the their queries. But catch 22 it works on the other screens(windows). Hope you folks can help TIA Denham This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
RE: password question
But where do you get the known or captured hash ? Only a DBA can query DBA_USERS for PASSWORD. A regular user cannot query DBA_USERS and cannot see PASSWORD in ALL_USERS. If you are already a DBA on the target database you really don't need to find out the password for another user. Supposing you grab a site's FULL Export dump. I guess you can then do a FULL Import and get the captured hash. But why do you need it now that you have the FULL Database with you anyway ? Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Jon Baker [EMAIL PROTECTED]22/02/2002 02:08 PM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: RE: password question One way hash, yes, but can use username to forceably crack the password (same idea as unix CRACK password cracking program). Hash is consistent which is why you can pick up the password string and drop it to another database (same username) and have the password work on the new machine. A non Oracle example would be to perform the following at the unix prompt: echo 'some test string' | md5 With the hash, you could create several variations and test against the known or 'captured' hash. Again, brute force method. Jon Baker Database Architect [EMAIL PROTECTED] www.netsec.net -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:18 AM To: Multiple recipients of list ORACLE-L Sameer, The obvious answer you can't decrypt the password. Else a number of people would think harder about buying Oracle. It's a one-way hash -- you can't get the original value back. It is possible to temporarily reset a user's password to something else, become the user with your own password and reset the password back to the original value, without knowing what the original password was. e.g. suppose a user's encrypted password string is 'ABCDEFGHIJKLMNOP', read this string from DBA_USERS, store it someplace (a variable, a table ;), execute ALTER USER username identified by mypassword, login as the user CONNECT username/mypassword, do your SQLs as that user, reset the user's password ALTER USER username identified by values 'ABCDEFGHIJKLMNOP' Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Ghadge,Sameer [EMAIL PROTECTED] 22/02/2002 11:38 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: password question Hi, Oracle stores password in encrypted format, is it possible (suppose i have access to dba_users table) to retrieve and descrypt the password. thx Sameer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ghadge,Sameer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San
