Paging problem in SunOS
Hi,I am experiencing a massive performance problem due tohigh page-in operation In Sun OS 5.6/Oracle 8.1.7.The RAM is 512 M and SGA is around is 50M and no otherprocess is running on that m/c. Any suggestions. Thanks in advance. PradyutDo You Yahoo!? Yahoo! Health - your guide to health and wellness
Licences for testing
We're planning to set-up some test servers here and I'm wondering what the practice is out there regarding licences for same. This is only for short term testing and to try out various DR scenarios etc. I'd guess there's an Oracle view which would be if you install on server you pay a licence, (correct?). Is there some workaround for this?. All creative ideas welcome :) - 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: Oracle IFS
Hi All, Oracle iFS is, of course, much slower in standard operations, such as file copying, saving and retrieving, but... It has really great features for searching as well as more protocols to access files (smb, nfs, http, webdav, and more). Another nice feature is customized smart file system, where you can program it's behaviors such as presenting the same data in different formats as needed (dxf as bmp or jpg when user don't have dxf viewer, for example). It may be integrated well with Oracle Workflow. iFS implements out of the box file versioning and chek-in, check-out. All backup problems go to database backup. However, it is not so flexible as regular file backups - it's questionable to restore only one file from the past and so on. Samba is free. iFS is included free of charge in Oracle Server (both EE and SE). Samba works on *NIX. iFS works on both Windows NT and UNIX/LINUX. In other words, need a regular file system - go with Samba. ;) Regards. Alexandre Gorbatchev Oracle DBA/Developer, OCP Avermann Maschinenfabrik GmbH Co. KG [EMAIL PROTECTED] +49 (0) 540 / 550 5177 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 6:01 PM Samba on Linux has been a life saver. Users can map a network drive, that's nfs mounted on UNIX that's accessible from the web. David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Jack van Zanen To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: L Subject: Oracle IFS Sent by: [EMAIL PROTECTED] om 05/02/2002 03:48 AM Please respond to ORACLE-L Hi All, We are looking for possible solutions to share files between Windows Unix. And also store millions of little files that are now a backup nightmare. Does anybody have any experience with IFS (performance, possibilities, pitfalls ) TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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).
RE: Licences for testing
Would the personal edition work for you? I don't think there would be a licensing issue with this. Dave -Original Message- Sent: Friday, May 03, 2002 7:09 AM To: Multiple recipients of list ORACLE-L We're planning to set-up some test servers here and I'm wondering what the practice is out there regarding licences for same. This is only for short term testing and to try out various DR scenarios etc. I'd guess there's an Oracle view which would be if you install on server you pay a licence, (correct?). Is there some workaround for this?. All creative ideas welcome :) - 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: 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: Licences for testing
As far as I am aware : NO. It has played a major role in me not being able to obtain some more test boxes at my current job. I believe the licensing is a little better, but it is still there. Plus, you also need the OS (Sun Solaris) so you get hit there too. Hannah -Original Message- From: [EMAIL PROTECTED]@SUNGARD On Behalf Of O'Neill, Sean [EMAIL PROTECTED] Sent: Friday, May 03, 2002 8:09 AM To: Multiple recipients of list ORACLE-L Subject: Licences for testing We're planning to set-up some test servers here and I'm wondering what the practice is out there regarding licences for same. This is only for short term testing and to try out various DR scenarios etc. I'd guess there's an Oracle view which would be if you install on server you pay a licence, (correct?). Is there some workaround for this?. All creative ideas welcome :) -- 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: Paging problem in SunOS
I think your SGA is too small. It should be around 200-250MB totally. -Original Message-From: Pradyut Mitra [mailto:[EMAIL PROTECTED]]Sent: Friday, May 03, 2002 8:38 AMTo: Multiple recipients of list ORACLE-LSubject: Paging problem in SunOS Hi,I am experiencing a massive performance problem due tohigh page-in operation In Sun OS 5.6/Oracle 8.1.7.The RAM is 512 M and SGA is around is 50M and no otherprocess is running on that m/c. Any suggestions. Thanks in advance. Pradyut Do You Yahoo!?Yahoo! Health - your guide to health and wellness *** This electronic mail transmission contains confidential and/or privileged information intended only for the person(s) named. Any use, distribution, copying or disclosure by another person is strictly prohibited. ***
RE: shared pool memory issue on OPS (non-MTS)
Jonathon, Currently we do not pin anything in the shared pool. Still nothing from Oracle on this yet. Thank you, Paul Sherman DBAElcom, Inc. voice - 781-501-4143 (direct #) fax- 781-278-8341 (secure) email - [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 02, 2002 7:43 PM To: Multiple recipients of list ORACLE-L That certainly sounds like the solution to the immediate problem. (Mind you, those initial numbers look like generated values, not manual settings). However, it doesn't answer the question of why one of the machines has the problem. Do you think it;s possible that lots of material gets loaded and KEEP'ed in this instance before the other instance starts up, making this instance the resource master for a very large dictionary cache ? Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 02 May 2002 22:44 |Check out the following two values: | |Resource Current MaxInitial Limit |Name Utilization UtilizationAllocation Value |---- ---- |lm_ress252143 256732 177599 UNLIMITED |lm_locks 278106 288642 189208 UNLIMITED | |Notice that Current/Max Utilization is much higher than Initial |Allocation? | |This means that (messages to alert.log or not), the DLM is overflowing its |allocated space in the SGA and invading the Shared Pool. Increase your |LM_RESS and LM_LOCKS parameter values... | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: Sherman, Paul R. 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: Paging problem in SunOS
Hi Pradyut, Check the distribution of your DB on your disks: -Do U have multiple /raided Disk sub-system ? -If so, what is your lay out in as tablespace datafile(s) location, index datafile location, redo logs file location etc If possible post the above for an analysis of the same. Thanking you all, --- CSW -Original Message-From: Pradyut Mitra [mailto:[EMAIL PROTECTED]]Sent: Friday, May 03, 2002 3:38 PMTo: Multiple recipients of list ORACLE-LSubject: Paging problem in SunOS Hi,I am experiencing a massive performance problem due tohigh page-in operation In Sun OS 5.6/Oracle 8.1.7.The RAM is 512 M and SGA is around is 50M and no otherprocess is running on that m/c. Any suggestions. Thanks in advance. Pradyut Do You Yahoo!?Yahoo! Health - your guide to health and wellness
Re: Licences for testing
You could talk to your sales rep about short-term licensing, or you could just do it and hope they don't discover it. I've found that temporary servers have a tendency to become permanent though. |+--- || | || | || Sean.ONeill@o| || rganon.ie| || | || 05/03/2002 | || 08:08 AM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Licences for testing| | We're planning to set-up some test servers here and I'm wondering what the practice is out there regarding licences for same. This is only for short term testing and to try out various DR scenarios etc. I'd guess there's an Oracle view which would be if you install on server you pay a licence, (correct?). Is there some workaround for this?. All creative ideas welcome :) - 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: 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: Licences for testing
Last time I checked Oracle on Linux is free if it's used for testing only. If you don't mind using Linux (we use it extensively) it could be a viable option. --Walt Weaver Bozeman, Montana -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 7:43 AM To: Multiple recipients of list ORACLE-L You could talk to your sales rep about short-term licensing, or you could just do it and hope they don't discover it. I've found that temporary servers have a tendency to become permanent though. |+--- || | || | || Sean.ONeill@o| || rganon.ie| || | || 05/03/2002 | || 08:08 AM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Licences for testing| | We're planning to set-up some test servers here and I'm wondering what the practice is out there regarding licences for same. This is only for short term testing and to try out various DR scenarios etc. I'd guess there's an Oracle view which would be if you install on server you pay a licence, (correct?). Is there some workaround for this?. All creative ideas welcome :) - 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: 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: Weaver, Walt 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: Disaster Recovery Plan
Rick - I went to http://www.google.com and entered Disaster Recovery Plan, and found what appeared to be several high-quality links. You might start with Disaster Recovery Made Easy. Once you get beyond these, you will probably need a book on the subject. I imagine there are a number of good ones, given the current political climate. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 02, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Hi All, We are in the process of developing a discovery plan. Does anyone have any links,example disaster recovery plans,etc that I can research for ideas. Thanks Rick -- 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: 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).
Oracle - Access
Hallo, anyone who has a good hint on this: I have an appplication which goes against Oracle and when I am inthe application and run VBA code which connects to linked tables everything works fine. But when I have an icon on the desktop and runs that icon as shortcut then I get error messagelike table not exist, but it really exists and it is the same code running both times. Thanks in advance Roland -- 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: Database Normalization-Outdated?
Ok. Since we are telling old stories around the campfire and at the risk of extending this thread even more, here are my 2bit stories :) Absolutely positively you must have RI on any tranactional system critial to your bussiness. If someone recommends otherwise, politely disagree then run if they go that way anyway. I have consulted at a number of Telcos and the biggest problem is data integrity. Combine this with no RI at a database level and you are looking at an even worse disaster... On the otherside of the coin the name/value pair (NVP) approach to data modeling is extremely flexible. I have designed several systems with varying degrees of success using this data modeling approach. It closes models OO within a relational database. You create an object relational model with object definitions and instances of those objects. You can implement something like this with very few objects (object definition, attribute definition, object instance, attribute instance). Like I said it is very flexible and you can model anything in a very dynamic manner without the need to spend alot of time recoding (if you layer a meta-data driven GUI on top of this). The problem as Tim indicated is that it is almost impossible to denormalize data out of this into something meaningful without joining the same tables to themselves and performing union operators all over the place. One approach to mitigate this problem is to use nested tables for the attributes. I did some experimentation with this approach that looked promising, but the project got killed before we got much further. Essentially, you can flatten the attributes associated with an object from a nested table using a view. You could dynamically regenerate the views (smells like Remedy) based on the definitions in the definition hierarchy to get a data model that is meaningful to real people (instead of us tech heads). The last problem that needs to be tackled with this symplistic data model is how to you capture referential integrity in the object definitions and implement in the object instances? If you omit this part you have come full circle to the beginning of this e-mail (no data integrity). It is essential that you model relationships between object definitions then implement some means of enforcement in the object instances. To implement this we added an additional table to the two previoiusly defined called association. It modeled associations between object definitions. We also had a counterpart in the instance tree. We then implemented triggers in the database to enforce these relationships. Other issues that I can recall off the top of my head are: - You need someway of constraining attribute values. We did this by adding characterestics to the attribute definitions such as data type, length, mandatory, primary key, etc... and enforced in attribute instances via triggers. - You need someway to access data via something other than the primary key. This was an issue we did not tackle and is something that still haunts the implementation today. In summary, there are at lease two systems that I designed like this that are still in operation (don't know about the third) today. They work well for what they were designed (complete flexibility), but are very difficult to get data out of. In that regard I would call them failures. Bill P.S.: In Oracle there exists a set of tables that does essentially the same thing. It is called the data dictionary ;-} --- Tim Gorman [EMAIL PROTECTED] wrote: I would be *extremely* interested in knowing the author's name. Especially if it's a he and his initials are DK... Back in 1992-93, I was working for Oracle and was asked to assist a company who had done exactly what you suggested in this email thread -- data-pair combinations and metadata mixed with data. Probably makes a great research project for a course, but totally irresponsible in real life... The database designer had created an order entry system with perhaps 150-170 logical entities, but all logical entities were encapsulated into a single physical table, named DATA. This table had 35 indexes, 240 columns, measured about 200m rows. Pretty huge stuff for v7.0.15... For logging/audit-trail purposes, he actually did break out some data from DATA into subset tables (so the database actually had about 6-7 tables), but of course they were all still organized the same way. The application worked, for entering data *ONLY*. It did *NOT* work at all for extracting data. It was totally impossible to write a report and the people in this company made the fatal mistake of trusting the database designer when he said that he would work something out. He never did. Month by month, the finance department extrapolated financial data from the last-known accurate financial reports, from the system replaced by this disaster. Since these folks ran in production on this beast for almost a year, you can imagine how
DB Size
Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: DB Size
you really might want to read the manuals -- specifically the reference manual on dba_data_files select sum(bytes) from dba_data_files will tell you the total size (in bytes) of all the datafiles in your database. There will also be space used by the control files, redo log files, archived log files and binaries etc. |+--- || | || | || [EMAIL PROTECTED]| || o.ug | || | || 05/03/2002 | || 10:53 AM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: DB Size | | Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: Remove an SID from sun solaris
You might try using DBCA to delete the old database/SID that is interfering with your current creation attempt. That should do the trick. If not, you will have to track down the occurrences of the SID in Oracle files like tnsnames.ora, listener.ora, oratab, etc. and remove them. HTH, David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. -Original Message- Sent: Thursday, May 02, 2002 3:03 PM To: Multiple recipients of list ORACLE-L Hi List, During the creation a new database, I just create an script but still oracle create an SID for me I want to use this SID again so How can I remove this SID and reuse it again. BTW still my ORA-03113: end-of-file on communication channel didn't solved. Thanks allot Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: David Wagoner 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: Create a new database
Hi all, In second run almost every thing goes OK except the PL/SQL part but I could open the database, but still looking for a link to download the patch2 for oracle 8.1.7 sun solaris. Tanks all for helping with this issue. BTW: Nobody answer how can I remove the SID name from oracle. -Original Message- Sent: Thursday, May 02, 2002 7:53 PM To: Multiple recipients of list ORACLE-L Hamid, Generally, finding an alert*.log file in the default $ORACLE_HOME/rdbms/log directory, even when BACKGROUND_DUMP_DEST has been set, indicates that someone issued the SHUTDOWN ABORT command when the instance wasn't already running. Alternatively, issuing the STARTUP FORCE command (which first performs a SHUTDOWN ABORT then a STARTUP) when the instance isn't already running could get the same effect. In other words, I think it's a red herring -- you probably shouldn't worry about it. Set your BACKGROUND_DUMP_DEST, USER_DUMP_DEST, CORE_DUMP_DEST, and AUDIT_FILE_DEST parameters to $ORACLE_BASE/admin/$ORACLE_SID/bdump, .../udump, .../cdump, and .../adump respectively and start over. If the ORA-03113 occurs again, look for core or .trc files in those directories and log a TAR with Oracle Support... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 3:37 PM Tim, This is me again, this is all in $ORACLE_HOME/rdbms/log Thu May 2 10:15:06 2002 Shutting down instance (abort) That's it which I found as a log. Thanks for your HELP -Original Message- Sent: Thursday, May 02, 2002 1:54 PM To: Multiple recipients of list ORACLE-L ORA-03113 (and ORA-03114) are error messages issued by the client process, not generated on the server (like most ORA- messages). Both essentially are complaints by the client process (i.e. SQL*Plus) that there is no longer a connection to the database server process. The usual way this occurs is if the database server process has core-dumped or aborted from experiencing a bus error, segmentation fault, segmentation violation, etc. Something dramatic and instantly fatal, kind of like a brain aneurism for computer processes... You should be able to find a large, cryptic ASCII-text trace file (i.e. *.trc) in whatever directory the parameter USER_DUMP_DEST is pointing to. The alert_ora-sid.log file (located in whatever directory the BACKGROUND_DUMP_DEST parameter is pointing to) should also have something logged in it. Unless the process was killed with the KILL or -9 signal, Oracle executables generally try to produce a core file before it dies. This trace file would represent a good starting point for a search of MetaLink or for logging a TAR... If you haven't set these two _DEST parameters yet, then these files can probably be found in $ORACLE_HOME/rdbms/log or $ORACLE_HOME/dbs or some such... And the advice to upgrade to 8.1.7.2 still holds... :-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 1:48 PM I don't know why you are getting the ORA-3113 I was merely trying to offer some advice based on limited information. ORA-3113 is a 'catch all' error; any number of things can cause that error. Are there other errors associated with it? Please include the list in your replies so as not to limit responses to one individual. And the advice to move to 8.1.7.2 still holds. Jared Hamid Alavi [EMAIL PROTECTED] 05/02/2002 11:40 AM To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] cc: Subject:RE: Create a new database yes I am 0n 8.1.7.0, so you mean 8.1.7.0 can not create a new database. But during the installation 8.1.7.0 I create a database, How come now I can not create another one?? -Original Message- Sent: Thursday, May 02, 2002 11:22 AM To: [EMAIL PROTECTED] Cc: Hamid Alavi Which version exactly? If on version 8.1.7.0, you should upgrade to 8.1.7.2 before creating any databases. 8.1.7.3 is available, but rife with bugs. Jared Hamid Alavi [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/02/2002 11:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Create a new database Hi List, When I try to create a new database (8.1.7) under sun solaris I got the following error: ORA-03113: end-of-file on communication channel Any Idea? Any help realy appreciated. This is the contenet of log file which created: Connected. ORA-03113: end-of-file on communication channel CREATE DATABASE CMSREPT * ORA-03114: not connected to ORACLE Disconnected. Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The
Re: One way replication in multimaster environment
Looks like snapshots make the most sense. --- Softhome - Fico [EMAIL PROTECTED] wrote: hi experts, In my replicated environment, i have one site (example: site X) that consolidate data from other sites (example : site Y and Z). I'm using multimaster to push transaction from site Y and Z to site X. How can i set - off the replication in site X , cause i dont want site X to push the changes to other sites or to disable row-level replication. any idea ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Softhome - Fico 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). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett 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).
Syntax For Dropping a default value?
Hi, Been searching for syntax to drop a default value (we decided NOT to use the default value) for a colmun. I looked in the docs and even tried to remove the default value via DBA Studio. No go. Can you not modify a column to REMOVE a default value specification? Thanks, Hannah (Its NOT in the docs.) -- 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 - Access
Can you show us your code, call out the line that returns the error and give the exact error # message? Also, consider moving this to an msaccess list. One good one can be found at http://peach.ease.lsoft.com/archives/access-l.html Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, May 03, 2002 7:49 AM To: Multiple recipients of list ORACLE-L Hallo, anyone who has a good hint on this: I have an appplication which goes against Oracle and when I am inthe application and run VBA code which connects to linked tables everything works fine. But when I have an icon on the desktop and runs that icon as shortcut then I get error messagelike table not exist, but it really exists and it is the same code running both times. Thanks in advance Roland -- 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: DB Size
Try this: spool files.txt set pagesize 60 set linesize 80 col name format a55 heading Control Files select name from sys.v_$controlfile / col name format a22 heading Dump / ARCH Files col value format a55 heading Location select name, value from sys.v_$parameter where name like '%archive_dest%' or name like '%dump_dest%' / col group# format 99 heading Group col status format a8 heading Status col member format a55 heading Redo Logs col mb format heading MB select a.group#,b.status,b.archived,a.member,round(b.bytes/1024000) mb from sys.v_$logfile a, sys.v_$log b where a.group# = b.group# / set pagesize 60 set linesize 80 col statusformat a3 heading Sta col Idformat 99 heading ID col Mbyte format 99 heading MBYTE col name format a55 heading Database Data Files break on report compute sum of Mbyte on report select F.file_id Id, F.file_name name, F.bytes/(1024*1024) Mbyte, decode(F.status,'AVAILABLE','OK',F.status) status from sys.dba_data_files F order by Id / spool off -Original Message- Sent: Friday, May 03, 2002 9:53 AM To: Multiple recipients of list ORACLE-L Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: 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: DB Size
A good question for a friday afternoon before a bank-holiday weekend... Query the data dictionary to get the names and locations of the data files: select file_name, tablespace_name, bytes from dba_data_files; That will give you the size of all the files for data, indexes, rollback segments, the temporary area, the system tablespace, etc. There are other bits it won't tell you about (Config files, online redologs, archived logs) but they're another story. This doesn't tell you how much of that space actually has data in it if that's what you meant, just how much space things are taking on the disks. What all these numbers actually mean is a much longer and more difficult question, best left 'till next week. Simon Anderson Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale INET: [EMAIL PROTECTED] -- 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 - Access
Try right-clicking on the icon on your desktop. Check under properties and see where the program is starting (it's under the shortcut tab). Maybe you're starting in the wrong directory. HTH Roland.Skoldb lom To: Multiple recipients of list ORACLE-L @ica.se [EMAIL PROTECTED] Sent by: rootcc: Subject: Oracle - Access 05/03/2002 10:48 AM Please respond to ORACLE-L Hallo, anyone who has a good hint on this: I have an appplication which goes against Oracle and when I am inthe application and run VBA code which connects to linked tables everything works fine. But when I have an icon on the desktop and runs that icon as shortcut then I get error messagelike table not exist, but it really exists and it is the same code running both times. Thanks in advance Roland -- 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: Thomas Day 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: Syntax For Dropping a default value?
try this: alter table table_name modify column_name default null; -Original Message- Sent: Friday, May 03, 2002 11:13 AM To: Multiple recipients of list ORACLE-L Hi, Been searching for syntax to drop a default value (we decided NOT to use the default value) for a colmun. I looked in the docs and even tried to remove the default value via DBA Studio. No go. Can you not modify a column to REMOVE a default value specification? Thanks, Hannah (Its NOT in the docs.) -- 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: Magaliff, Bill 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: DB Size
select sum(bytes) from dba_extents; This is a quick and easy though not totally accurate. Not every row in every block will be filled. Simon Waibale waibals To: Multiple recipients of list ORACLE-L @mtn.co.ug [EMAIL PROTECTED] Sent by: rootcc: Subject: DB Size 05/03/2002 10:53 AM Please respond to ORACLE-L Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: Thomas Day 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: shared pool memory issue on OPS (non-MTS)
What do the v$resource_limit numbers look like on the other node ? Is there any other way that the bad node could have become the master for all the dictionary cache information ? Does one node start up a few minutes before the other ? Is there anything that makes one node the preferred not for user access ? Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 03 May 2002 14:40 |Jonathon, | |Currently we do not pin anything in the shared pool. Still nothing from |Oracle on this yet. | |Thank you, | |Paul Sherman |DBAElcom, Inc. |voice - 781-501-4143 (direct #) |fax- 781-278-8341 (secure) |email - [EMAIL PROTECTED] | | |-Original Message- |Sent: Thursday, May 02, 2002 7:43 PM |To: Multiple recipients of list ORACLE-L | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: ERD generation tool - Active SCM
Yechiel, Yes, I have been there, done that, over and over... But then, there is a Toyota Corolla solution and maybe a Ferrari Testarosa solution. If we can control Dom Phoc without tieing his hands behind the back, wouldn't that would be the best: white paper: http://www.iraje.com/docs/ActiveSecureDesigner.htm Keith Date: Thu, 02 May 2002 11:48:38 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Reply-to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Well Keith Our solution to the Doom Phoc (and their siblings) is: Do not grant they rights to do any DDL either in test nor in prod. The dab stuff does all the DDL work. Sure it is an added chore, but after tracking down, a few times, tables that were dropped inadvertently by users (their tool did it by itself) we now use the following policy: Every application has two user id's: Owner, with password known only to the DBA group. User with rights for select, insert, update, delete ONLY. It works. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 7:54 PM Lisa, There is only so much you can control via a model, since it remains a process away from the DB, and cannot be enforced via privileges, etc. So, we are always in the hands of Dom Phoc (and their siblings), who can do stuff even in the production database with SQLPLus/TOAD/... Under this schenario, do you sleep well at night? So, we said lets work with our Dom Phoc's. On production databases, we will STRIP them off of the Oracle database passwords. No password, no change. ENFORCED! Now, I can sleep well at night. How? Not via models. Via a solution involving the following, and it seems to be working for us well: ActiveDesigner/ActiveChangeManager/ActiveCompare/A+ White Paper: http://www.iraje.com/docs/ActiveSecureDesigner.htm Take charge of the Dom Phocs in your org! Keith To: '[EMAIL PROTECTED]' [EMAIL PROTECTED], '[EMAIL PROTECTED]' [EMAIL PROTECTED] Date: Wed, 1 May 2002 16:06:00 -0500 Well, for one thing, if your developer, Dom Phoc, starts changing crap in your database (as has happened to me in the past) a compare to the dev model would be great because my development changes would be in the model, not in the test or production databases. In that specific case I had to TRUST him (what? trust him after what he just did?) to change everything back, or restore from a backup, which would have been very time consuming. I was one large ball of raging hormones that day and I took it all out on him. We don't work on the same projects anymore. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Keith Peterson [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: ERD generation tool - Active Comparisons Am I speaking to the wind For Compares, why would you compare the MODEL with the DATABASE...like going from US to London via Tokyo... ... and you get to pay more, like... you pay not for distance, but for time in the air... If a tool takes longer to do something, makes more mistakes, is bumpy and complex... you get to pay more. For compares, someone tell me what beats ActiveCompare: http://www.iraje.com/compare-diff.htm http://www.iraje.com/ActiveCompare_viewlet.html ...and I will switch my tool. Keith __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Keith Peterson 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: DB Size
SELECT SUM(BYTES) FROM ( SELECT BYTES FROM sys.DBA_DATA_FILES UNION ALL SELECT BYTES FROM sys.DBA_TEMP_FILES ) ; it's in bytes not kB or MB JP On Fri 3. May 2002 16:53, you wrote: Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- 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).
In an Analyze Necessary?
Hello Folks, A datawarehouse. There are only a few selected SQL's run against this database. And all this SQL's are tuned to optimum during design with hints embedded to take the least execution time. The SQL's currently execute in times, much better than what the expected response time was by the users. Would it still be required to analyze the tables? What other benefits would one reap from an analyze? Assume that there are no other SQL's running against the database other than this select few, and the data in the underlying tables has been accounted for a period of 2 years during design. Any new SQL's which would need to be added would go thru the drawing board. Absolutely Nothing adhoc. Regards Raj -- 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: Syntax For Dropping a default value?
I do not know how to remove it but you could ALTER TABLE table_name MODIFY(column DEFAULT NULL); Rick johanna.doran@s ungard.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Syntax For Dropping a default value? m 05/03/2002 11:13 AM Please respond to ORACLE-L Hi, Been searching for syntax to drop a default value (we decided NOT to use the default value) for a colmun. I looked in the docs and even tried to remove the default value via DBA Studio. No go. Can you not modify a column to REMOVE a default value specification? Thanks, Hannah (Its NOT in the docs.) -- 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 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: One way replication in multimaster environment
Not, if you want real-time row-level replication. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 11:33 AM Looks like snapshots make the most sense. --- Softhome - Fico [EMAIL PROTECTED] wrote: hi experts, In my replicated environment, i have one site (example: site X) that consolidate data from other sites (example : site Y and Z). I'm using multimaster to push transaction from site Y and Z to site X. How can i set - off the replication in site X , cause i dont want site X to push the changes to other sites or to disable row-level replication. any idea ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Softhome - Fico 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). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett 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: Igor Neyman 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: DB Size
I like to use this SQL*Plus script: -- Begin script -- /** * File: spc.sql * Type: SQL*Plus script * Author: Tim Gorman (Evergreen Database Technologies, Inc.) * Date: 10-Oct-97 * * Description: * SQL*Plus script to display database space usage. * * Modifications: * TGorman 11mar02 added support for AUTOEXTENSIBLE data files */ col tablespace format a25 col owner format a20 col type format a19 col sort1 noprint col mb format 999,990.00 clear breaks clear compute break on report on tablespace on owner on type set echo off feedback off timing off pagesize 66 verify off trimspool on col instance new_value V_INSTANCE noprint select instance from v$thread; spool spc_V_INSTANCE select tablespace_name tablespace, owner, 'a' sort1, segment_type type, sum(bytes)/1048576 mb from dba_segments group by tablespace_name, owner, segment_type union all select tablespace, username owner, 'b' sort1, segtype type, sum(blocks)/128 mb from v$sort_usage group by tablespace, username, segtype union all select tablespace_name tablespace, '' owner, 'c' sort1, '---total---' type, sum(bytes)/1048576 mb from dba_segments group by tablespace_name union all select tablespace, '' owner, 'd' sort1, '---total---' type, sum(blocks)/128 mb from v$sort_usage group by tablespace union all select tablespace_name tablespace, '' owner, 'e' sort1, '-allocated-' type, sum(bytes)/1048576 mb from dba_data_files group by tablespace_name union all select tablespace_name tablespace, '' owner, 'f' sort1, '-allocated-' type, sum(bytes)/1048576 mb from dba_temp_files group by tablespace_name union all select tablespace_name tablespace, '' owner, 'g' sort1, 'allocatable' type, sum(decode(autoextensible,'YES',maxbytes,bytes))/1048576 mb from dba_data_files group by tablespace_name union all select tablespace_name tablespace, '' owner, 'h' sort1, 'allocatable' type, sum(decode(autoextensible,'YES',maxbytes,bytes))/1048576 mb from dba_temp_files group by tablespace_name union all select tablespace_name tablespace, '' owner, 'i' sort1, '' type, to_number('') mb from dba_tablespaces union all select tablespace, owner, sort1, type, sum(mb) from (select '' tablespace, 'Total' owner, 'a' sort1, 'Used' type, sum(bytes)/1048576 mb from dba_segments union all select '' tablespace, 'Total' owner, 'a' sort1, 'Used' type, sum(blocks)/128 mb from v$sort_usage) group by tablespace, owner, sort1, type union all select tablespace, owner, sort1, type, sum(mb) from (select '' tablespace, 'Total' owner, 'b' sort1, 'Allocated' type, sum(bytes)/1048576 mb from dba_data_files union all select '' tablespace, 'Total' owner, 'b' sort1, 'Allocated' type, sum(bytes)/1048576 mb from dba_temp_files) group by tablespace, owner, sort1, type union all select tablespace, owner, sort1, type, sum(mb) from (select '' tablespace, 'Total' owner, 'c' sort1, 'Allocatable' type, sum(decode(autoextensible,'YES',maxbytes,bytes))/1048576 mb from dba_data_files union all select '' tablespace, 'Total' owner, 'c' sort1, 'Allocatable' type, sum(decode(autoextensible,'YES',maxbytes,bytes))/1048576 mb from dba_temp_files) group by tablespace, owner, sort1, type order by 1, 2, 3, 4; spool off -- End script -- If you want a version with all the formatting intact, you can download it from www.EvDBT.com/library.htm... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 8:53 AM Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
SMJ, NL or HJ
Hello Gurus, A SQL tuning question. Given three large tables with the same millions of rows, and all three are referenced in a query, without any filter, as under: Select .. from largetableA a, largeTableB b, largeTableC c where a.empnum = b.empnum and a.empnum = c.empnum; What would be the prefered way of joining these tables, Merge Join, Nested Loops or Hash Joins? Thanks Raj -- 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: pl/sql is INTERPRETED?
I used to work with Jared. He just has 'The Gift'. For those of us who are mere mortals it takes a little longer :-) --- Alex [EMAIL PROTECTED] wrote: You must be pretty smart then. I wonder why rates for java are not $6/hr seeing that it only takes a week to learn. You could probably say any language is easy to learn; it is just ifs, elses, and loops. On Thu, 2 May 2002 [EMAIL PROTECTED] wrote: It ain't that tough. We're not talking about taking a programming class without any experience, I've done a bit of it before. Learning all the API's, etc.: that would take some time. The language? It isn't that difficult, though I would be hard put to write any at the moment. The job I was going to use Java on was at Enron, and we all know what happened to that. It's been a year since I took the class, and I *much* prefer Perl. It can run circles around Java for most stuff. Jared Alex [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/02/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pl/sql is INTERPRETED? It took you a week to learn it? Then you obviously do not know it. Syntax is one thing design is another. I would love to know what you learned in that week. On Thu, 2 May 2002, Jared Still wrote: Hold on Lisa! Java is not complex. It's a very simple language actually. It took me a week to learn it, though I'm not using it now: I much prefer Perl. Getting a handle on all of the libraries and API's is another story, but Java as a language is pretty simple. Jared On Tuesday 30 April 2002 11:14, Koivu, Lisa wrote: You have a point Chris, but pl/sql is nowhere near as complex as an OO language like java or C++, IMHO. I agree with Tom that pl/sql can be learned fairly easily in comparison to the many other choices out there. However, it takes a bit of database savvy to do it correctly. (Not much tho) I was amazed in my database class in college that the same people failing the simple entity-relationship modeling portion of the class that had aced the Op Systems and networking classes we took. I nearly failed both classes, they were so complex. I was the teacher's pet in the db class because I asked him questions that made him think, and he sometimes couldn't answer. (And I had to wear a skirt - night student, straight from work.) What's easy for who is dependent on the person's strengths. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Grabowy, Chris [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 1:14 PM To:Multiple recipients of list ORACLE-L Subject: RE: pl/sql is INTERPRETED? IMHO, I don't believe that you can properly learn PL/SQL in a very short period of time, or for that matter, any other language. I attended Steve Feuerstein's presentation at MAOP-AOTC conference, and he tore into many real-life examples of PL/SQL. Supposedly, these were written by developers that knew what they were doing. Granted, if a smart developer sits down and reads Feuerstein's Learning PL/SQL and Best Practices books, then perhaps they will be good. But who the hell has free time? There is no free time on any project or effort that I know of!! I'm struggling with trying to improve my Oracle DBA skills, plus some developers skills so I can speak their language when they blow out OPEN_CURSORS or something. My head is swimming in the stupid technical alphabet soup, XML, XDK, XSQL, XSLT, XPath, SOAP, ASP, ADO, EJB, BC4J, JDBC, SQLJ, PSP, JVM, JSP, J2EE, EAD, RMI, CORBA, IIOP...and don't ask me what all those mean, because I can't keep them straight. But I do keep hearing that XML is going to put me out of a job, so I guess I should learn that...whatever that is. Isn't XML an add-on, or extension, or something to DML??? Now where the heck did I hide that bottle... -Original Message- Sent: Tuesday, April 30, 2002 12:15 PM To: Multiple recipients of list ORACLE-L Lisa, You are right about the debate between PL/SQL Java (or anything else outside of the db). In my mind, the deciding factor (and something that is *never* mentioned) is what programming langauage the organization is satisfied with/settled upon. === message truncated === = Pete Barnett
RE: Paging problem in SunOS
Your SGA is pretty small by usual standards - which is not, in itself, a cause for alarm, and, in any case, you shouldn't experience the SGA being swapped in and out. In practice, it means that Oracle leaves lots of memory to other processes. I would look for the culprit on the client side - you may have a process boldly attempting to load a mega table in memory or something of the kind. Can't you check with 'top' or ps with the right -o options which processes are guzzling the most memory? Could be a good place to start. - Original Message - From: Pradyut Mitra [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 03 May 2002 04:38:27 Hi,I am experiencing a massive performance problem due tohigh page-in operation In Sun OS 5.6/Oracle 8.1.7.The RAM is 512 M and SGA is around is 50M and no otherprocess is running on that m/c. Any suggestions. Thanks in advance. Pradyut - Do You Yahoo!? Yahoo! Health - your guide to health and wellness Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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 - Access
Looks like Access doesn't connect to Oracle db. Try to create connection to Oracle in Autorun procedure in MS Access. JP On Fri 3. May 2002 16:48, you wrote: Hallo, anyone who has a good hint on this: I have an appplication which goes against Oracle and when I am inthe application and run VBA code which connects to linked tables everything works fine. But when I have an icon on the desktop and runs that icon as shortcut then I get error messagelike table not exist, but it really exists and it is the same code running both times. Thanks in advance Roland -- 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).
RE: Create a new database
Hi Jared, I was off the listserv for about 2 months and probably missed the discussion on this. Do you have a pointer to where I can get information on what was messed up in the 8.1.7.3 patch? I was intending to schedule the 8.1.7.3 patch installations for some of my databases in the next few days so this was a very timely post! Thanks, Jay -Original Message- Sent: Thursday, May 02, 2002 3:24 PM To: Multiple recipients of list ORACLE-L Which version exactly? If on version 8.1.7.0, you should upgrade to 8.1.7.2 before creating any databases. 8.1.7.3 is available, but rife with bugs. Jared Hamid Alavi [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/02/2002 11:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Create a new database Hi List, When I try to create a new database (8.1.7) under sun solaris I got the following error: ORA-03113: end-of-file on communication channel Any Idea? Any help realy appreciated. This is the contenet of log file which created: Connected. ORA-03113: end-of-file on communication channel CREATE DATABASE CMSREPT * ORA-03114: not connected to ORACLE Disconnected. Thanks Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Miller, Jay 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: In an Analyze Necessary?
Don't fix, if it's not broken:-) Do something else with your spare time instead :-) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 11:48 AM Hello Folks, A datawarehouse. There are only a few selected SQL's run against this database. And all this SQL's are tuned to optimum during design with hints embedded to take the least execution time. The SQL's currently execute in times, much better than what the expected response time was by the users. Would it still be required to analyze the tables? What other benefits would one reap from an analyze? Assume that there are no other SQL's running against the database other than this select few, and the data in the underlying tables has been accounted for a period of 2 years during design. Any new SQL's which would need to be added would go thru the drawing board. Absolutely Nothing adhoc. Regards Raj -- 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: Igor Neyman 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: Syntax For Dropping a default value? - Resolved for now.
Yep, ALTER TABLE table_name MODIFY(column DEFAULT NULL); Works so I guess its ok. Was hoping to completely remove Thanks All. -- 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: DB Size
Physical size (disk) or logical size (bytes of actual data)? Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Simon Waibale [SMTP:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 9:53 AM To: Multiple recipients of list ORACLE-L Subject: DB Size Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: YAPP / Oraperf / STATSPACK - Optimal SQL linesize and pagesize
Cherie, I have been trying to figure out your upload problems. And I noticed that your report of statspack looks like it is from 8.1.6, but there are some small differences. Can you tell me the source of this statspack, where did you get it ? Did you modify any of it. I am contemplating a fix, there is a simple one and a more difficult one. Anjo. [EMAIL PROTECTED] wrote: When I try to use YAPP to analyze my STATSPACK report, I get an error message which states that my SQL is wrapping or too long. It says that I should set my pagesize and linesize correctly. The report looks o.k. to the naked eye. I looked all over their website but I don't see anywhere that it tells me what the ideal pagesize and linesize should be. Can anyone else recommend a good pagesize and linesize? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- 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).
How to simulate Block Corruption?
Hello Gurus, I am in the process of testing recovery options from block corruptions. So, my setup requires a db which is having block corruption problem. How to simulate block corruption in db? Any help? TIA, Sandeep. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sandeep Kurliye 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: One way replication in multimaster environment
Very interesting question. Couple years ago, I had very similar problem: I had one central database, which had to consolidate data from multiple source databases (having identical schemas) in real time with as little delay as possible after transaction occurs on the source database, and at the same time source databases should not be getting data from their piers (or from central database). I looked at the advanced multimaster replication (offered by Oracle) and didn't find a way to use it as a solution to my problem. I ended up designing my own replication process and writing code (triggers and queue for replicated data on the source databases and stored procedures and replicating job on the central database) to support it. Of course, this solution means, that I have to modify replication code (triggers and stored procedures) every time, when there are changes to the database schema (but this does not happen very often), and my solution does not replicate DDL. So, when new release of our product comes out, it includes necessary code to modify source and central database schemas and replication code appropriately. So far, so good: this solution works reliably on multiple installations. Forgot to mention also, that it accounts also for the time intervals, when network between source and destination databases is down, or database on any side of replication is down: replicated data queue on the source databases takes care of these problems. Also, conflict resolution is taken care of by assigning source_id (which is part of PK on each of replicated table) to every replicated record. Also, process of setting up my replication is very simple (it's automated with the scripts, I wrote), so our field engineers are doing it on customer sites without having any knowledge about databases. So, I'd love to see, if someone has a solution, which utilizes replication provided by Oracle, to this pretty common (in my mind, anyway) problem. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 12:18 AM hi experts, In my replicated environment, i have one site (example: site X) that consolidate data from other sites (example : site Y and Z). I'm using multimaster to push transaction from site Y and Z to site X. How can i set - off the replication in site X , cause i dont want site X to push the changes to other sites or to disable row-level replication. any idea ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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: SMJ, NL or HJ
Depends. The number of rows matching a given FK may vary widely. Collect stats, and let the CBO decide, it should not have it too wrong in such a case. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 03 May 2002 08:13:27 Hello Gurus, A SQL tuning question. Given three large tables with the same millions of rows, and all three are referenced in a query, without any filter, as under: Select .. from largetableA a, largeTableB b, largeTableC c where a.empnum = b.empnum and a.empnum = c.empnum; What would be the prefered way of joining these tables, Merge Join, Nested Loops or Hash Joins? Thanks Raj -- 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). --- - Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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 simulate Block Corruption?
Hi If you are on a unix platform use dd to write to a datafile, this is what oracle uses in there Backup Recovery Class. On NT find a freeware hex editor and edit the datafile ! I know that there are multible event in Oracle to write corrupt block's, but forget them and use brut force ! Sandeep Kurliye wrote: Hello Gurus, I am in the process of testing recovery options from block corruptions. So, my setup requires a db which is having block corruption problem. How to simulate block corruption in db? Any help? TIA, Sandeep. -- /regards Peter Gram Mobil : +45 2527 7107 Fax : +45 4466 8856 Miracle A/S Kratvej 2 2760 Måløv 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).
Re: SMJ, NL or HJ
Consider analyzing the EMPNUM column for each table as well, to provide the CBO with possibly crucial data distribution information... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 11:18 AM Depends. The number of rows matching a given FK may vary widely. Collect stats, and let the CBO decide, it should not have it too wrong in such a case. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 03 May 2002 08:13:27 Hello Gurus, A SQL tuning question. Given three large tables with the same millions of rows, and all three are referenced in a query, without any filter, as under: Select .. from largetableA a, largeTableB b, largeTableC c where a.empnum = b.empnum and a.empnum = c.empnum; What would be the prefered way of joining these tables, Merge Join, Nested Loops or Hash Joins? Thanks Raj -- 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). --- - Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Tim Gorman 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 simulate Block Corruption?
-- Sandeep Kurliye [EMAIL PROTECTED] Hello Gurus, I am in the process of testing recovery options from block corruptions. So, my setup requires a db which is having block corruption problem. How to simulate block corruption in db? dd if=/vmunix of=$yourdbf bs=8k count=5; you now have 40KBytes of corruption. If your dd has offset capability then use it to simulate corruption at various points in the file. to simulate corruption in expanding the file use: dd if=/dev/urandom bs=8k count=$howevermany $yourdbf; -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: In an Analyze Necessary?
Words of Wisdom ;-) But, me thinks, I will go ahead and disable that once a week analyze cron job. The CPU can be better utilized for the other night batch jobs. Thanks Raj Igor Neyman ineyman@perceTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ptron.comcc: Sent by: Subject: Re: In an Analyze Necessary? [EMAIL PROTECTED] om May 03, 2002 12:53 PM Please respond to ORACLE-L Don't fix, if it's not broken:-) Do something else with your spare time instead :-) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 11:48 AM Hello Folks, A datawarehouse. There are only a few selected SQL's run against this database. And all this SQL's are tuned to optimum during design with hints embedded to take the least execution time. The SQL's currently execute in times, much better than what the expected response time was by the users. Would it still be required to analyze the tables? What other benefits would one reap from an analyze? Assume that there are no other SQL's running against the database other than this select few, and the data in the underlying tables has been accounted for a period of 2 years during design. Any new SQL's which would need to be added would go thru the drawing board. Absolutely Nothing adhoc. Regards Raj -- 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: One way replication in multimaster environment
Would you be able to send sample of your scripts. Thanks. -Original Message- Sent: Friday, May 03, 2002 1:29 PM To: Multiple recipients of list ORACLE-L Very interesting question. Couple years ago, I had very similar problem: I had one central database, which had to consolidate data from multiple source databases (having identical schemas) in real time with as little delay as possible after transaction occurs on the source database, and at the same time source databases should not be getting data from their piers (or from central database). I looked at the advanced multimaster replication (offered by Oracle) and didn't find a way to use it as a solution to my problem. I ended up designing my own replication process and writing code (triggers and queue for replicated data on the source databases and stored procedures and replicating job on the central database) to support it. Of course, this solution means, that I have to modify replication code (triggers and stored procedures) every time, when there are changes to the database schema (but this does not happen very often), and my solution does not replicate DDL. So, when new release of our product comes out, it includes necessary code to modify source and central database schemas and replication code appropriately. So far, so good: this solution works reliably on multiple installations. Forgot to mention also, that it accounts also for the time intervals, when network between source and destination databases is down, or database on any side of replication is down: replicated data queue on the source databases takes care of these problems. Also, conflict resolution is taken care of by assigning source_id (which is part of PK on each of replicated table) to every replicated record. Also, process of setting up my replication is very simple (it's automated with the scripts, I wrote), so our field engineers are doing it on customer sites without having any knowledge about databases. So, I'd love to see, if someone has a solution, which utilizes replication provided by Oracle, to this pretty common (in my mind, anyway) problem. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 12:18 AM hi experts, In my replicated environment, i have one site (example: site X) that consolidate data from other sites (example : site Y and Z). I'm using multimaster to push transaction from site Y and Z to site X. How can i set - off the replication in site X , cause i dont want site X to push the changes to other sites or to disable row-level replication. any idea ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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: James A 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: SMJ, NL or HJ
The CBO, presently does quite a good job. It chooses a sort merge join on the tables. Given my understanding of the data distribution in the tables, I agree its the best execution plan. But this kills my temporary tablespace, ORA-1652. To accomodate this query, I altered the sort area for the session to a high value, and then, I took a hit on my temporary tablespace utilization, not withstanding the rather small values for the extent sizes. And changing it would require me go thru a lot of bureaucracy, change management controls, approvals, the works. So, I was actually looking for a way to get around using sort merge joins, and not compromise on performance. I would tend to use hash joins, when a join happens between a smaller row source, and a large one. But that, I know, is not the case here. I would like to drive this query via a full table access, since I expect the query to return me about 90% of the rows from each table. So, a nested loop is also not feasible. Left without an option, I guess. Headed now to put in a change management request :( Raj Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sfaroult@oriolcc: ecorp.com Subject: RE: SMJ, NL or HJ Sent by: [EMAIL PROTECTED] m May 03, 2002 01:18 PM Please respond to ORACLE-L Depends. The number of rows matching a given FK may vary widely. Collect stats, and let the CBO decide, it should not have it too wrong in such a case. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 03 May 2002 08:13:27 Hello Gurus, A SQL tuning question. Given three large tables with the same millions of rows, and all three are referenced in a query, without any filter, as under: Select .. from largetableA a, largeTableB b, largeTableC c where a.empnum = b.empnum anda.empnum = c.empnum; What would be the prefered way of joining these tables, Merge Join, Nested Loops or Hash Joins? Thanks Raj -- 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). --- - Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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
Re: One way replication in multimaster environment
One way to handle this problem is to not schedule your jobs to push from Y and Z to X. If transactions occur on Y and Z, they will become deferred transactions, waiting to be pushed to X, but will never push. Then, periodically you can delete the transactions from Y and Z (bound for X). I suppose you could setup a cron to do the purge automatically... Hope that helps, Alan Softhome - FicoTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ficomail@soft cc: home.netSubject: One way replication in multimaster environment Sent by: [EMAIL PROTECTED] om 05/02/02 10:18 PM Please respond to ORACLE-L hi experts, In my replicated environment, i have one site (example: site X) that consolidate data from other sites (example : site Y and Z). I'm using multimaster to push transaction from site Y and Z to site X. How can i set - off the replication in site X , cause i dont want site X to push the changes to other sites or to disable row-level replication. any idea ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Softhome - Fico 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: How to simulate Block Corruption?
I remember reading a reply from X$ Gopal to a similar question, talking about the possible use of BBED, which is an Oracle support tool. Raj Sandeep Kurliye Sandeep@almoayyedinTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tl.com.bh cc: Sent by:Subject: How to simulate Block Corruption? [EMAIL PROTECTED] May 03, 2002 01:18 PM Please respond to ORACLE-L Hello Gurus, I am in the process of testing recovery options from block corruptions. So, my setup requires a db which is having block corruption problem. How to simulate block corruption in db? Any help? TIA, Sandeep. -- 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).
9i Release 2 features/fixes
Does anyone know of or have a list of new features/fixes for Release 2 of 9i? I can't find anything on oracle.com except XML XML XML. Just wondering if OiD gets any better, I guess. :) TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Licences for testing
I would not think you would need a license for evaluating the software. However, once you cross the line into development or production licensing is mandatory. Unless things have changed, the Oracle LINUX client is free, but the LINUX server requires licensing as defined above. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- From: O'Neill, Sean [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 5:09 AM To: Multiple recipients of list ORACLE-L We're planning to set-up some test servers here and I'm wondering what the practice is out there regarding licences for same. This is only for short term testing and to try out various DR scenarios etc. I'd guess there's an Oracle view which would be if you install on server you pay a licence, (correct?). Is there some workaround for this?. All creative ideas welcome :) - 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: MacGregor, Ian A. 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).
Desginer 6i causing me grief
I am upgrading from designer 2.1.2 to designer 6i 4.2. I have installed Designer on my win2k workstation and craeted a new instance on a Solaris box running SUn OS 8. My version of Oracle is 64-bit 8.1.7.3. I closely follow the instruction in the install guide for creating a new repository. (The plan is to create a new repository on the Sun box then migrate the old 2.1.2 repository from the DG box.) However while running the Install Wizard in the Repository Admin tool the process freeze. No error message just a dead process. Logging into the Unix box and bringing up SQL*PLUS is also impossible. It just hangs there waiting. I do not have any errors in my alert logs and the only shutdown abort seems to clear the problem. I have increased my rollbacks segments to 100M. I have increased my SHARED_POOL_SIZE to 64M. I have increased SYSTEM tablespace to 250M (though I can not see how running out of system tablespace would not at least raise an error.) I have not found any help on Metalink or from Oracle Support as of yet, so in a act of sheer desparation I have turned here in the forlorn hope that someone else has encountered this problem. Questions: What event would cause the database to hang without producing an error message? (If I had a place to start looking I could research the problem myself.) Are there any hidden gotchas in the INIT file regarding Designer 6i? Steve, a co-worker of mine, seems to recall similar problems when he was installing designer 2.1.2 and traced them to the INIT file. However I have known Steve for most of my life and have come to the conclusion he is a throughly disreputable individual. :) Thanks Alec -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alec Macdonell 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: SMJ, NL or HJ
Since table B and C are using the same column to join to table A, then it should be possible to ensure that Oracle hashes tables B and C at the same time, then scans table A passing rows through each hash in turn. (The order can be permuted as necessary). If you can set the hash area size to something large enough you can start getting your results through without any I/O above a single table scan of A B and C. Remember that the total memory usage in this case will be 2 x hash_area_size though - one for table B, one for table C. The path would be: hash table C hash table B table A Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 03 May 2002 18:43 | |The CBO, presently does quite a good job. It chooses a sort merge join on |the tables. Given my understanding of the data distribution in the tables, |I agree its the best execution plan. But this kills my temporary |tablespace, ORA-1652. To accomodate this query, I altered the sort area for |the session to a high value, and then, I took a hit on my temporary |tablespace utilization, not withstanding the rather small values for the |extent sizes. And changing it would require me go thru a lot of |bureaucracy, change management controls, approvals, the works. | |So, I was actually looking for a way to get around using sort merge joins, |and not compromise on performance. I would tend to use hash joins, when a |join happens between a smaller row source, and a large one. But that, I |know, is not the case here. I would like to drive this query via a full |table access, since I expect the query to return me about 90% of the rows |from each table. So, a nested loop is also not feasible. | |Left without an option, I guess. Headed now to put in a change management |request :( | |Raj | | | | |Select .. |from largetableA a, largeTableB b, largeTableC c |where a.empnum = b.empnum |anda.empnum = c.empnum; | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: 9i Release 2 features/fixes
Jesse, Check the Joe site www.oracle-dba.com -Original Message- Sent: Friday, 03 May, 2002 2:38 PM To: Multiple recipients of list ORACLE-L Does anyone know of or have a list of new features/fixes for Release 2 of 9i? I can't find anything on oracle.com except XML XML XML. Just wondering if OiD gets any better, I guess. :) TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Ramon E. Estevez 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: 9i Release 2 features/fixes
Yup, I've been there. And while the info's good, it's about the new features of 9i, and not of Release 2. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Ramon E. Estevez [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Subject: RE: 9i Release 2 features/fixes Jesse, Check the Joe site www.oracle-dba.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: 9i Release 2 features/fixes
Bingo! I probably just overlooked that link on their site because it isn't named very well. I should have guessed. Thx! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Toepke, Kevin M [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 3:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: 9i Release 2 features/fixes http://www.oracle.com/features/9i/index.html?t1db_unbreakable.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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 - Access
I guess this is what has replaced Friday humor and recipes. Anything tangentially related to Oracle. David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Roland.Skoldbl [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Oracle - Access om 05/03/2002 09:48 AM Please respond to ORACLE-L Hallo, anyone who has a good hint on this: I have an appplication which goes against Oracle and when I am inthe application and run VBA code which connects to linked tables everything works fine. But when I have an icon on the desktop and runs that icon as shortcut then I get error messagelike table not exist, but it really exists and it is the same code running both times. Thanks in advance Roland -- 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 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).
ORA-1025??
Hi friends, When Iam exporting thru pipes and mknod, Iam getting error:- PL/SQL Release 2.3.2.0.0 - Production Export done in US7ASCII character set Note: indexes on tables will not be exported About to export specified tables via Direct Path ... . . exporting table TTFGLD410100 EXP-8: ORACLE error 1025 encountered ORA-01025: UPI parameter out of range . . exporting table TTFGLD106100 EXP-8: ORACLE error 1025 encountered ORA-01025: UPI parameter out of range Export terminated successfully with warnings. My script is looks like.. PATH=$PATH:$ORACLE_HOME/bin LOG=/data10 RBSCRIPTS=/rb/scripts/backup # Create a named pipe PIPE=/tmp/dsptab_pipe if [ -a ${PIPE} ] then rm ${PIPE} fi mknod ${PIPE} p # dd in from the named pipe and out to the tape device dd if=${PIPE} of=/dev/rmt1 cd ${RBSCRIPTS} exp file=${PIPE} parfile=dsptab ${LOG}/dsptab.log 21 TIA Raghu. _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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).
Filesystems vs raw devices on Linux (?)
There was a mention at the RAID roundtable at IOUG-A Live! 2002 on some results published in this whitepaper: http://www.quest.com/whitepapers/Linux_Maximus_Part2.pdf The general consensus was that the test was most likely flawed, but with no specifics given. I am curious as to what was the problem. I heard from someone just as I was leaving that session and heading for the airport, that there had been a recent discussion of this paper in this forum. I evidently missed it when I was out for a few days as my ISP changed. If I remember correctly, Stephane Faroult was mentioned as having shed some light on it. (Wish I had known this earlier - I spend quite a bit of time with him there!) Does anyone have any pointers, light to shed, or other information. Even just the date(s) and/or subject line of the thread would be nice - so I could find it in the oracle-l archives at www.orafaq.com . Thanks! Don Granaman [OraSaurus] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman 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: ORA-1025??
Raghu Kota wrote: Hi friends, When Iam exporting thru pipes and mknod, Iam getting error:- PL/SQL Release 2.3.2.0.0 - Production Export done in US7ASCII character set Note: indexes on tables will not be exported About to export specified tables via Direct Path ... . . exporting table TTFGLD410100 EXP-8: ORACLE error 1025 encountered ORA-01025: UPI parameter out of range . . exporting table TTFGLD106100 EXP-8: ORACLE error 1025 encountered ORA-01025: UPI parameter out of range Export terminated successfully with warnings. My script is looks like.. PATH=$PATH:$ORACLE_HOME/bin LOG=/data10 RBSCRIPTS=/rb/scripts/backup # Create a named pipe PIPE=/tmp/dsptab_pipe if [ -a ${PIPE} ] then rm ${PIPE} fi mknod ${PIPE} p # dd in from the named pipe and out to the tape device dd if=${PIPE} of=/dev/rmt1 cd ${RBSCRIPTS} exp file=${PIPE} parfile=dsptab ${LOG}/dsptab.log 21 TIA Raghu. // *Action: This usually indicates an error in a tool built on top // of the oracle dbms. Report the error to your customer // support representative. I remember having encountered 'out of range' problems when trying to fetch more than 32K very small rows at a time ... It may be a foolish suggestion, but I'd try with SMALLER values for parameters such as BUFFER, etc. No idea what your default may be, but try to make it small enough so as not be able to hold more than 30,000 rows. Otherwise, the obvious workaround is to try WITHOUT direct=Y. Slower but usually safer. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Filesystems vs raw devices on Linux (?)
If I remember correctly, Stephane Faroult was mentioned as having shed some light on it. Don, You probably don't remember correctly. I hardly know what RAID stands for. I always try to do everything in memory :-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Filesystems vs raw devices on Linux (?)
The basic consensus of the discussion here was not the test itself but the analysis was somewhat flakey. The performance was generally reviewed purely with the stopwatch, so seeing where the bottlenecks were (on raw or any of the filesystems tested) was not really assessed or reported. But having said that, it did have some merit for out of the box testing - ie if you just bang Oracle on a box and pray, then the benchmark probably applies hth connor --- Don Granaman [EMAIL PROTECTED] wrote: There was a mention at the RAID roundtable at IOUG-A Live! 2002 on some results published in this whitepaper: http://www.quest.com/whitepapers/Linux_Maximus_Part2.pdf The general consensus was that the test was most likely flawed, but with no specifics given. I am curious as to what was the problem. I heard from someone just as I was leaving that session and heading for the airport, that there had been a recent discussion of this paper in this forum. I evidently missed it when I was out for a few days as my ISP changed. If I remember correctly, Stephane Faroult was mentioned as having shed some light on it. (Wish I had known this earlier - I spend quite a bit of time with him there!) Does anyone have any pointers, light to shed, or other information. Even just the date(s) and/or subject line of the thread would be nice - so I could find it in the oracle-l archives at www.orafaq.com . Thanks! Don Granaman [OraSaurus] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
Datawarehousing help
Okay, my background is OLTP, but we are looking at a data warehousing project here any and all help appreciated! Specifically: 1) does anyone have any experience with a product called SAS Datawarehousing Administrator (or SAS)? 2) how do I go about doing rough estimates of sizing needs, assuming I will get rough numbers of information being collected, growth rates, length of history to keep, etc. help? Rachel -- 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: SMJ, NL or HJ
Thanks Jonathan, for your inputs. I am trying to understand hash joins. New to it. As I understand this, and looking at the plan that you have mentioned : hash table C hash table B table A 1. Tables B and C will be hashed in parallel, and Hash table for A will be created. 2. Rows from B will be probed against the hash table in A. 3. Rows from step 2 will be probed against the hash table in C. Is my understanding right? Also, will my usage of hash joins reduce the TEMP tablespace utilization. I know for sure that none of these tables will fit in the hash area size. So, part of it will definitely be written to TEMP. But will this utilization be less than that of a merge sort join? You say the memory usage will be twice the hash area size? From the little search that I have done on this parameter, I find no reference in the Oracle Docs. saying it will be so. Anyways, if you say it, it must be so ; -) Will look up at ixora from home. Have a nice Weekend, e'one. Raj Something I found out during my research: HASH_AREA_SIZE is done away with in Oracle 9i, or retained for backward compatibility. Jonathan Lewis [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] mon.co.uk cc: Sent by: Subject: Re: SMJ, NL or HJ [EMAIL PROTECTED] May 03, 2002 04:13 PM Please respond to ORACLE-L Since table B and C are using the same column to join to table A, then it should be possible to ensure that Oracle hashes tables B and C at the same time, then scans table A passing rows through each hash in turn. (The order can be permuted as necessary). If you can set the hash area size to something large enough you can start getting your results through without any I/O above a single table scan of A B and C. Remember that the total memory usage in this case will be 2 x hash_area_size though - one for table B, one for table C. The path would be: hash table C hash table B table A Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 03 May 2002 18:43 | |The CBO, presently does quite a good job. It chooses a sort merge join on |the tables. Given my understanding of the data distribution in the tables, |I agree its the best execution plan. But this kills my temporary |tablespace, ORA-1652. To accomodate this query, I altered the sort area for |the session to a high value, and then, I took a hit on my temporary |tablespace utilization, not withstanding the rather small values for the |extent sizes. And changing it would require me go thru a lot of |bureaucracy, change management controls, approvals, the works. | |So, I was actually looking for a way to get around using sort merge joins, |and not compromise on performance. I would tend to use hash joins, when a |join happens between a smaller row source, and a large one. But that, I |know, is not the case here. I would like to drive this query via a full |table access, since I expect the query to return me about 90% of the rows |from each table. So, a nested loop is also not feasible. | |Left without an option, I guess. Headed now to put in a change management
Re: 9i Release 2 features/fixes
You can sure that Oid will be better - because lets face it - it couldn't have got any worse :-) --- Jesse, Rich [EMAIL PROTECTED] wrote: Does anyone know of or have a list of new features/fixes for Release 2 of 9i? I can't find anything on oracle.com except XML XML XML. Just wondering if OiD gets any better, I guess. :) TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: In an Analyze Necessary?
Rajesh, You seem to be one of those lucky DBAs who don't have performance issues :) Just keep in mind that when statistics are NOT present and the CBO is forced by one of the many reasons, some defaults will be assumed. These defaults are absolutely invalid and incorrect. So, even if you analyze once, these stats will remain and provide the CBO with a picture which is more or less correct (unless your data is really skewed). Rather than completely switch off stats collection, why don't you look at what's the difference between that stats between one weekend's ANALYZE and the next? If the changes are not significant, then you may choose to perform this only once a quarter or so One of the myths with Stats collection is 'Analyze everyday!', but the truth is 'it all depends'. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** But, me thinks, I will go ahead and disable that once a week analyze cron job. The CPU can be better utilized for the other night batch jobs. A datawarehouse. There are only a few selected SQL's run against this database. And all this SQL's are tuned to optimum during design with hints embedded to take the least execution time. The SQL's currently execute in times, much better than what the expected response time was by the users. Would it still be required to analyze the tables? What other benefits would one reap from an analyze? Assume that there are no other SQL's running against the database other than this select few, and the data in the underlying tables has been accounted for a period of 2 years during design. Any new SQL's which would need to be added would go thru the drawing board. Absolutely Nothing adhoc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
IN() question
Slap me if this is a dumb question. Here's my pay methods SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*) 2 FROM LEISURE_PLAN_MASTER_TEMP 3 WHERE MEMBERSHIP_CLASS = 'D' 4 GROUP BY PAY_METHOD; ASCII(PAY_METHOD) P COUNT(*) - - -- 67 C 42955 80 P 34373 11786 I expected this statement to return the 11,786 records that have null values. However, it doesn't: SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND pay_METHOD NOT IN ('C','P'); COUNT(*) -- 0 But when I do this, I get the answer I expect. SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND PAY_METHOD IS NULL; COUNT(*) -- 11786 This isn't exactly correct. There may be other values in this field, and if they show up I need to include them, not just records where this field is null. What am I missing? Is it because the value is NULL that Oracle excludes it from the IN() statement, because of the classic definition of NULL (can't be defined, therefore can't be sure it's not a C or a P)? This is easy enough to fix, I'll change my data load to populate the null values with my own code. But still? Have I got the WHY correct? Thanks for any light someone can shed on this stupid question. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: Licences for testing
Download the 30 day trial from OTN -Original Message- Sean Sent: Friday, May 03, 2002 5:09 AM To: Multiple recipients of list ORACLE-L We're planning to set-up some test servers here and I'm wondering what the practice is out there regarding licences for same. This is only for short term testing and to try out various DR scenarios etc. I'd guess there's an Oracle view which would be if you install on server you pay a licence, (correct?). Is there some workaround for this?. All creative ideas welcome :) - 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: Kimberly Smith 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: Licences for testing
Personal Oracle is indeed a product that needs to be licensed. -Original Message- Dave Sent: Friday, May 03, 2002 6:33 AM To: Multiple recipients of list ORACLE-L Would the personal edition work for you? I don't think there would be a licensing issue with this. Dave -Original Message- Sent: Friday, May 03, 2002 7:09 AM To: Multiple recipients of list ORACLE-L We're planning to set-up some test servers here and I'm wondering what the practice is out there regarding licences for same. This is only for short term testing and to try out various DR scenarios etc. I'd guess there's an Oracle view which would be if you install on server you pay a licence, (correct?). Is there some workaround for this?. All creative ideas welcome :) - 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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: IN() question
In any equivalence operation (which includes non-equivalence, too), NULL never returns TRUE -- it just returns NULL which non-TRUE has the same result as FALSE. Only IS and IS NOT operators can be used to evaluate NULLs... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 5:43 PM Slap me if this is a dumb question. Here's my pay methods SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*) 2 FROM LEISURE_PLAN_MASTER_TEMP 3 WHERE MEMBERSHIP_CLASS = 'D' 4 GROUP BY PAY_METHOD; ASCII(PAY_METHOD) P COUNT(*) - - -- 67 C 42955 80 P 34373 11786 I expected this statement to return the 11,786 records that have null values. However, it doesn't: SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND pay_METHOD NOT IN ('C','P'); COUNT(*) -- 0 But when I do this, I get the answer I expect. SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND PAY_METHOD IS NULL; COUNT(*) -- 11786 This isn't exactly correct. There may be other values in this field, and if they show up I need to include them, not just records where this field is null. What am I missing? Is it because the value is NULL that Oracle excludes it from the IN() statement, because of the classic definition of NULL (can't be defined, therefore can't be sure it's not a C or a P)? This is easy enough to fix, I'll change my data load to populate the null values with my own code. But still? Have I got the WHY correct? Thanks for any light someone can shed on this stupid question. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: Tim Gorman 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: Paging problem in SunOS
He is having paging problems and you are recommending that he increase his SGA? Could you explain how you feel that will solve his problem please? Thx -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Paul LiSent: Friday, May 03, 2002 6:48 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Paging problem in SunOS I think your SGA is too small. It should be around 200-250MB totally. -Original Message-From: Pradyut Mitra [mailto:[EMAIL PROTECTED]]Sent: Friday, May 03, 2002 8:38 AMTo: Multiple recipients of list ORACLE-LSubject: Paging problem in SunOS Hi,I am experiencing a massive performance problem due tohigh page-in operation In Sun OS 5.6/Oracle 8.1.7.The RAM is 512 M and SGA is around is 50M and no otherprocess is running on that m/c. Any suggestions. Thanks in advance. Pradyut Do You Yahoo!?Yahoo! Health - your guide to health and wellness *** This electronic mail transmission contains confidential and/or privileged information intended only for the person(s) named. Any use, distribution, copying or disclosure by another person is strictly prohibited. ***
RE: Datawarehousing help
Rachel - I always find it helpful to understand something if I know the origins. I worked with SAS several years ago. At that time it was a statistical analysis package. A scientist or engineer could load a set of test data into it and perform various arithmetic and statistical analyses. Today most of that can be done with Oracle or MS Excel. My point is that I would expect it to be heavily biased toward mathematical capabilities. Like Data Mining, which is all statistics. Learn what that term means. To learn Data Warehousing, I would encourage you to just do some Googling and find good tutorials. An excellent newslist is dwlist. Instructions: For help with list commands, send a message to mailto:[EMAIL PROTECTED] with the word help in the body of the message. The magazine http://www.intelligententerprise.com/ has some excellent information. I would search for Ralph Kimball. He is one of the leading figures in the DW arena. Look for some of his earliest columns on the magazine site. He also answers questions on dwlist from time to time. The main change you need yourself is to forget normalization. DBAs that can't get past that point don't last long in the DW field. In the early days the DW people would patiently explain the reasons to a DBA, but today there are enough DBAs that have made the leap that a hard-headed normalization bigot just isn't tolerated. It is much easier to just ask for a replacement DBA. The reason normalization isn't adhered to in DW is that users will be creating their own queries and they can't understand 10-table joins with outer joins, etc. A DW is usually loaded and then queried. Our DW is loaded each weekend and then queried all week. So a DW is deliberately denormalized and contains redundant data for ease of use. OLTP databases have no concept of time. A DW is all about time. To reconstruct what the situation is at various points of time, the DW has loads of historical data. For example, marketing people need to be able to reconstruct the amount of business they did with a customer over a period of time last year and compare it with the same period this year. So between denormalization and tons of detailed historical data, DWs are normally BIG! Fortunately they are usually read-only. For Oracle, you want Enterprise Edition with the partitioning option. And study Oracle Materialized Views. In schema, a DW is usually a central fact table and 4-6 dimension tables. Less than 4 dimensions and you don't need a DW. More than 6 and marketing people can't understand the model. Normally the fact table is much larger than the others, but not always. One of Wal-Mart's dimension tables is each person in the U.S. Just size each of those tables, and you've got your size. Growth is easy to predict. Ralph Kimball warns that often people will get the grain wrong. They will size it for data summarized at the weekly level, then after it is built they will realize that isn't going to cut it and need a daily level. You must start almost from scratch and get 7 times the disk capacity. That is the fun side of being a DW DBA. Your cynical instincts will still serve you well, just get them away from normalization and worry about getting the grain right. Okay, I've rambled along here too long. Hope that gets you off on the right foot. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Okay, my background is OLTP, but we are looking at a data warehousing project here any and all help appreciated! Specifically: 1) does anyone have any experience with a product called SAS Datawarehousing Administrator (or SAS)? 2) how do I go about doing rough estimates of sizing needs, assuming I will get rough numbers of information being collected, growth rates, length of history to keep, etc. help? Rachel -- 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: 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
Re: IN() question
Yes, your why is correct, NULL is neither equal nor not equal to any value. I usually nvl to return a space for null values when I have to consider them, then a not equal will work. At 03:43 PM 5/3/02 -0800, you wrote: Slap me if this is a dumb question. Here's my pay methods SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*) 2 FROM LEISURE_PLAN_MASTER_TEMP 3 WHERE MEMBERSHIP_CLASS = 'D' 4 GROUP BY PAY_METHOD; ASCII(PAY_METHOD) P COUNT(*) - - -- 67 C 42955 80 P 34373 11786 I expected this statement to return the 11,786 records that have null values. However, it doesn't: SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND pay_METHOD NOT IN ('C','P'); COUNT(*) -- 0 But when I do this, I get the answer I expect. SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND PAY_METHOD IS NULL; COUNT(*) -- 11786 This isn't exactly correct. There may be other values in this field, and if they show up I need to include them, not just records where this field is null. What am I missing? Is it because the value is NULL that Oracle excludes it from the IN() statement, because of the classic definition of NULL (can't be defined, therefore can't be sure it's not a C or a P)? This is easy enough to fix, I'll change my data load to populate the null values with my own code. But still? Have I got the WHY correct? Thanks for any light someone can shed on this stupid question. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: Regina Harter 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: IN() question
Thanks Tim. I thought so. Lisa -Original Message- From: Tim Gorman [SMTP:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 8:33 PM To: Multiple recipients of list ORACLE-L Subject: Re: IN() question In any equivalence operation (which includes non-equivalence, too), NULL never returns TRUE -- it just returns NULL which non-TRUE has the same result as FALSE. Only IS and IS NOT operators can be used to evaluate NULLs... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 5:43 PM Slap me if this is a dumb question. Here's my pay methods SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*) 2 FROM LEISURE_PLAN_MASTER_TEMP 3 WHERE MEMBERSHIP_CLASS = 'D' 4 GROUP BY PAY_METHOD; ASCII(PAY_METHOD) P COUNT(*) - - -- 67 C 42955 80 P 34373 11786 I expected this statement to return the 11,786 records that have null values. However, it doesn't: SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND pay_METHOD NOT IN ('C','P'); COUNT(*) -- 0 But when I do this, I get the answer I expect. SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND PAY_METHOD IS NULL; COUNT(*) -- 11786 This isn't exactly correct. There may be other values in this field, and if they show up I need to include them, not just records where this field is null. What am I missing? Is it because the value is NULL that Oracle excludes it from the IN() statement, because of the classic definition of NULL (can't be defined, therefore can't be sure it's not a C or a P)? This is easy enough to fix, I'll change my data load to populate the null values with my own code. But still? Have I got the WHY correct? Thanks for any light someone can shed on this stupid question. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: Tim Gorman 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: Koivu, Lisa 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: IN() question
Hi Regina, thanks for your reply. Lisa -Original Message- From: Regina Harter [SMTP:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 8:58 PM To: Multiple recipients of list ORACLE-L Subject: Re: IN() question Yes, your why is correct, NULL is neither equal nor not equal to any value. I usually nvl to return a space for null values when I have to consider them, then a not equal will work. At 03:43 PM 5/3/02 -0800, you wrote: Slap me if this is a dumb question. Here's my pay methods SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*) 2 FROM LEISURE_PLAN_MASTER_TEMP 3 WHERE MEMBERSHIP_CLASS = 'D' 4 GROUP BY PAY_METHOD; ASCII(PAY_METHOD) P COUNT(*) - - -- 67 C 42955 80 P 34373 11786 I expected this statement to return the 11,786 records that have null values. However, it doesn't: SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND pay_METHOD NOT IN ('C','P'); COUNT(*) -- 0 But when I do this, I get the answer I expect. SQL SELECT COUNT(*) 2 from leisure_plan_master_temp 3 where membership_class = 'D' 4 AND PAY_METHOD IS NULL; COUNT(*) -- 11786 This isn't exactly correct. There may be other values in this field, and if they show up I need to include them, not just records where this field is null. What am I missing? Is it because the value is NULL that Oracle excludes it from the IN() statement, because of the classic definition of NULL (can't be defined, therefore can't be sure it's not a C or a P)? This is easy enough to fix, I'll change my data load to populate the null values with my own code. But still? Have I got the WHY correct? Thanks for any light someone can shed on this stupid question. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: Regina Harter 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: Koivu, Lisa 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).
test - text file attachment / Re: ORACLE-L Digest -- Volume 2002, Number 123
what is factotum? ORACLE-L Digest -- Volume 2002, Number 123 -- From: Mercadante, Thomas F [EMAIL PROTECTED] Date: Thu, 2 May 2002 08:09:14 -0400 Subject: RE: ITS THE PHB, STPID (was: anti-virus spam) what - you changing careers - trying out for Denis Miller's show? TEST TEXT FILE ATTACHMENT FOR ORACLE-L ANTI-VIRUS SPAMMERS
RE: Datawarehousing help
Excellent dude. -Original Message- WILLIAMS Sent: Friday, May 03, 2002 5:48 PM To: Multiple recipients of list ORACLE-L Rachel - I always find it helpful to understand something if I know the origins. I worked with SAS several years ago. At that time it was a statistical analysis package. A scientist or engineer could load a set of test data into it and perform various arithmetic and statistical analyses. Today most of that can be done with Oracle or MS Excel. My point is that I would expect it to be heavily biased toward mathematical capabilities. Like Data Mining, which is all statistics. Learn what that term means. To learn Data Warehousing, I would encourage you to just do some Googling and find good tutorials. An excellent newslist is dwlist. Instructions: For help with list commands, send a message to mailto:[EMAIL PROTECTED] with the word help in the body of the message. The magazine http://www.intelligententerprise.com/ has some excellent information. I would search for Ralph Kimball. He is one of the leading figures in the DW arena. Look for some of his earliest columns on the magazine site. He also answers questions on dwlist from time to time. The main change you need yourself is to forget normalization. DBAs that can't get past that point don't last long in the DW field. In the early days the DW people would patiently explain the reasons to a DBA, but today there are enough DBAs that have made the leap that a hard-headed normalization bigot just isn't tolerated. It is much easier to just ask for a replacement DBA. The reason normalization isn't adhered to in DW is that users will be creating their own queries and they can't understand 10-table joins with outer joins, etc. A DW is usually loaded and then queried. Our DW is loaded each weekend and then queried all week. So a DW is deliberately denormalized and contains redundant data for ease of use. OLTP databases have no concept of time. A DW is all about time. To reconstruct what the situation is at various points of time, the DW has loads of historical data. For example, marketing people need to be able to reconstruct the amount of business they did with a customer over a period of time last year and compare it with the same period this year. So between denormalization and tons of detailed historical data, DWs are normally BIG! Fortunately they are usually read-only. For Oracle, you want Enterprise Edition with the partitioning option. And study Oracle Materialized Views. In schema, a DW is usually a central fact table and 4-6 dimension tables. Less than 4 dimensions and you don't need a DW. More than 6 and marketing people can't understand the model. Normally the fact table is much larger than the others, but not always. One of Wal-Mart's dimension tables is each person in the U.S. Just size each of those tables, and you've got your size. Growth is easy to predict. Ralph Kimball warns that often people will get the grain wrong. They will size it for data summarized at the weekly level, then after it is built they will realize that isn't going to cut it and need a daily level. You must start almost from scratch and get 7 times the disk capacity. That is the fun side of being a DW DBA. Your cynical instincts will still serve you well, just get them away from normalization and worry about getting the grain right. Okay, I've rambled along here too long. Hope that gets you off on the right foot. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Okay, my background is OLTP, but we are looking at a data warehousing project here any and all help appreciated! Specifically: 1) does anyone have any experience with a product called SAS Datawarehousing Administrator (or SAS)? 2) how do I go about doing rough estimates of sizing needs, assuming I will get rough numbers of information being collected, growth rates, length of history to keep, etc. help? Rachel -- 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
(Fwd) iDS 9.0.2 / Forms 9i Available
--- Forwarded message follows --- Date sent: Fri, 03 May 2002 10:15:26 -0800 O D T U G - D E V 2 K - L @ f a t c i t y . c o m d u n c a n . m i l l s @ o r a c l e . c o m Organization: Fat City Network Services, San Diego, California For everyone who's been waiting for 9i wait no longer - (except for the download time of course...) http://technet.oracle.com/software/products/ids/content.html I'm an vacation for a week so hold off on all the complaints for at least that long - just kidding you'll love it! Regards Duncan Mills Senior Principal Product Manager Oracle Application Development Tools ... --- End of forwarded message --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce 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).
Spec'ing hardware
I'm about to start a new job and one of my first tasks will be to specify hardware for a new 9i database. I'd appreciate any procedures, tips, etc. for deciding the hardware configuration (CPU, memory) of a new server that will host the database. It'll mostly likely be a Sun machine. Thanks in advance! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.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: Spec'ing hardware
Fewer, faster CPU's, more memory. The SA's have been really happy with the Sun servers they have now, The Sun Fire series. They prefer them over the Enterprise version. -Original Message- Baumgartel Sent: Friday, May 03, 2002 7:33 PM To: Multiple recipients of list ORACLE-L I'm about to start a new job and one of my first tasks will be to specify hardware for a new 9i database. I'd appreciate any procedures, tips, etc. for deciding the hardware configuration (CPU, memory) of a new server that will host the database. It'll mostly likely be a Sun machine. Thanks in advance! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.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: Kimberly Smith 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).