Re: can't create database
I had the same error message on NT with Oracle 9.2 recently. The first problem was that the init_sid.ora file was created a directory different from where the db create scripts were looking for it. The second problem was that one of the init parameters was for Enterprise Edition and I was installing Standard Edition. Both problems resulted in the message of 'not connected to oracle'. Come to think of it, I don't think I've ever had a version of the DB Creation wizard work without getting some error. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/11/2003 12:44 PM, Stahlke, Mark [EMAIL PROTECTED] wrote: RE: LMT monitoring Is this on Linux? If yes, then it sounds like you need to install the glibc stubs patch. If no, then I don't know what the problem might be. -Original Message- From: Milen Pankov [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Subject: can't create database can't create a database with oracle 8.1.7. the installation went fine, but when i start dbassist on the 2% of the database creation it tels me: not connected to oracle. any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sequence as column default
You could also do it as part of your insert statement if you didn't want to use a trigger. INSERT INTO employees VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30); Same thing goes with currval also. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/10/2003 3:49 PM, DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Has anyone defined a sequence as the default value for a column? The manual is a little ambiguous (in my mind anyway): In the 8.1.7 manual: Restriction: A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified. In 9i this was altered to read: Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified. SQL alter table test add 2 (col3 number default addressID.NextVal); (col3 number default addressID.NextVal) * ERROR at line 2: ORA-00984: column not allowed here The alternative is to use an insert trigger, but it seems this would be more efficient. Since we are planning to use this a LOT, I thought I should try for a definate answer. Thanks for your patience. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sequence as column default
Hi Dennis, I'm afraid I can't help you with J2EE. I've only done a little experimenting with java code to produce simple command line programs. However, since you are working with J2EE, you probably want to put as much code on the backend anyway. Much simpler to maintain that way. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/10/2003 4:36 PM, DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Alan - You are correct. However, apparently that isn't easy to do with J2EE / EJB, hense the trigger. Don't ask me why. When will they come out with a book titled: J2EE for the DBA Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 10, 2003 3:16 PM To: Multiple recipients of list ORACLE-L You could also do it as part of your insert statement if you didn't want to use a trigger. INSERT INTO employees VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30); Same thing goes with currval also. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/10/2003 3:49 PM, DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Has anyone defined a sequence as the default value for a column? The manual is a little ambiguous (in my mind anyway): In the 8.1.7 manual: Restriction: A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified. In 9i this was altered to read: Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified. SQL alter table test add 2 (col3 number default addressID.NextVal); (col3 number default addressID.NextVal) * ERROR at line 2: ORA-00984: column not allowed here The alternative is to use an insert trigger, but it seems this would be more efficient. Since we are planning to use this a LOT, I thought I should try for a definate answer. Thanks for your patience. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Performance Tuning Exam
If you are taking the 9i certification, there are only 4 exams that you have to take. Unfortunately, unless you took at least one exam last year and got grand-fathered, you will have to enroll at Oracle U. for one of the four courses covering the certification exams. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 2/27/2003 3:19 PM, Nguyen, David M [EMAIL PROTECTED] wrote: RE: Oracle Performance Tuning Exam There are totally five exams we have to pass to get certified, I'd like to know which exam should I take first and what next in order? Thanks, David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle Performance Tuning Exam BTW, That is why I didn't spend more than a few hours preparing for that exam. I already sensed that it would be a waste of time in the long-run. -Original Message- From: Mogens Nørgaard [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle Performance Tuning Exam Good posting. Thank you. This week Morten Egan from Miracle A/S (who's on this list as well, I think) is teaching the Tuning Class for Oracle Denmark, and he's had a few comments as well about the materials. Morten, would you care to comment (in your usually nice and easy manner?) If was, after all, you who came with the unlearn quote below. Best regards, Mogens DENNIS WILLIAMS wrote: Mogens - I posted this note back in October. -Original Message- Sent: Saturday, October 05, 2002 4:08 PM To: '[EMAIL PROTECTED]' List I spent last week at an official Oracle Education Oracle9i Performance Tuning Class, and here is some of the non-technical stuff I learned. - Oracle is teaching the wait interface more and more. In fact, they are updating the curriculum next month to emphasize the wait interface even more (lucky me). - Just how the wait interface is emphasized may depend quite a bit on the instructor, despite what the materials say. My observation is that our opinions are based on what we have experienced and our interpretations of those experiences. So we will probably still have some instructors that will still feel that the wait interface is a passing fad and if you really want to straighten out a database, you need to get in there and improve the BHR (Buffer Hit Ratio). - My instructor was John Hibbard. He is excellent, and I would highly recommend him. He went well beyond the class materials to providing papers he has researched and presented himself, as well as other sources, including papers from Cary Milsap and Jonathan Gennick who participate on this list. When you get through his class, you really feel you have been taken to a whole new level of Oracle knowledge. He is also heavily involved in selecting and preparing the official Oracle training materials for the courses he teaches. Besides Performance Tuning, he teaches several other Oracle classes. Most of the people in my class happened to be more experienced with Oracle, and John did a good job of answering advanced questions with some depth, but not leaving the newbies in the dust. - A funny observation on buffer hit ratio vs. wait interface. The last day of class is an opportunity to take a really screwed-up database and apply a little of what you have learned. The first scenario is titled Buffer Cache. So you run the workload assignment and STATSPACK and look at the BHR and say wow, that is bad, increase the buffer pool, and rerun the workload and STATSPACK. The BHR hasn't changed much, so the tendency is to dumbly bump the buffer pool even more and go again. Then you look down at the top 5 waits section just below on the first page of the STATSPACK report and see that the big wait item isScattered Read. Then you go dope slap and realize this schema is missing some critical indexes and table scanning it's little heart out. I just found it ironic that some people have reported that some of the Oracle instructors emphasize the BHR too much when the first Workshop Scenario has a great example of why focusing on BHR can't solve many problems. But again, we have experience vs. interpretation of experience. A real died-in-the wool BHR fanatic would probably claim that BHR had solved the problem because the first indication that something was wrong was spotting the bad BHR, which led to other investigations. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 25, 2003 10:24 PM To: Multiple recipients of list ORACLE-L Yeah, if you've taken the performance exam, you must now unlearn what you have learnt, to quote from Starwars. I've considered creating a one- or two-day class that would put people into the right track of thinking after having studied and passed that exam. The other
Re: SQL question
Why not just have Connection B trap the Unique Constrait Error and branch to some different code? What would Connection B have done if it had found the record where id=1? -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 2/24/2003 2:49 PM, Rick Stephenson [EMAIL PROTECTED] wrote: OS: Solaris 2.8 Database: Oracle 9.2.0.2 Situation in chronological order Connection A: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection B: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection A: insert into table A(id) values = 1; Result: 1 row inserted Connection B: insert into table A(id) values = 1; Result: Unique constraint violated -- This is the problem. How do I avoid this happening? Question: How can I force connection B to wait for connection A to insert the new row before it does the select? If I were updating the row, I could use the for update clause to force the wait. Is there a clean way to do that for an insert? Thanks for your help, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Borderline OT - Unix for Oracle at home
For $500 you can build your own Intel/AMD machine with 1GB of RAM that will blow the doors off the Sun Ultras. You may need to spend a little more if you need some hard drives and a cheap video card. Install Linux, Oracle and enjoy. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 1/30/2003 10:14 AM, Fink, Dan [EMAIL PROTECTED] wrote: I am looking to add a unix box to my collection of wintel machines at home. It will be used solely for running/testing Oracle, so I don't need bells whistles. My thoughts are either Linux/intel or Sun Ultra workstation. While it would be convenient to be able to network it into a DSL configuration, it is not essential. My original thought was a Linux desktop, but I can also get Ultra 5 or 10 workstations on ebay for less than $500. Anyone having experience good/bad/ugly for this type of task? All tips, challenges, things to consider are greatly appreciated. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Take Care of your DBAs
Beer Pong is a lot of fun too. ;^) -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 1/30/2003 10:09 AM, Gogala, Mladen [EMAIL PROTECTED] wrote: We would not be able to feed our families on our physical abilities but I definitely would try beating Lisa in the game of Ping Pong. -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 29, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: Take Care of your DBAs Lisa - Some of us became DBAs because we realized we would never be able to feed our families on our physical abilities. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, January 29, 2003 3:55 PM To: Multiple recipients of list ORACLE-L I used to play Ping Pong with the sysadmins and the app architect... aahh, the glory dotcom days when I could bring my dog to work :) Most of the dba's I have met are not into physical activity and exercise. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, January 29, 2003 2:49 PM To: Multiple recipients of list ORACLE-L shooting hoops? Just out of curiosity, how many people on the list have a group of DBAs at their company that they shoot hoops with? Some good points, some odd ones. I'll echo Patrice's sigh (as someone who enjoys both parts of the job). Jay -Original Message- Sent: Wednesday, January 29, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Here's that development DBA alias again. sigh. Pat. -Original Message- Sent: Wednesday, January 29, 2003 7:54 AM To: Multiple recipients of list ORACLE-L Thanks for this article. I will forward to damagement. Dave -Original Message- Sent: Tuesday, January 28, 2003 3:24 PM To: Multiple recipients of list ORACLE-L http://careerlink.devx.com/articles/hc0199/hc0199.asp http://careerlink.devx.com/articles/hc0199/hc0199.asp http://careerlink.devx.com/articles/hc0199/hc0199.asp http://careerlink.devx.com/articles/hc0199/hc0199.asp Interesting article I stumbled across. Best quote: Stay Out of your DBA's Face!WELL PUT! Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
Re: Need help and documentation on moving database from Unix to W2K
Why do they want to move to W2k? Was it running too quickly for them under Unix? ;^) You can use export/import to move the database. Hopefully the database isn't very big. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 1/29/2003 12:59 PM, Baswannappa, Shiva [EMAIL PROTECTED] wrote: Hi Gurus I am not a DBA, but I have request from client to move their oracle 8.1.6 database from a Unix server to W2K. Can somebody lead me to documentation in moving entire DB or any other resource that will help me accomplish the task? Thanks a ton in advance Regards Shiva Baswannappa Senior Developer Digital Consulting and Software Services Phone: 281.243.2658 Fax: 281.243.2504 Web: http://www.dcss.com http://www.dcss.com/ If the reader of this e-mail is not an intended recipient, you have received this e-mail in error and any review, dissemination, distribution or copying is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by return e-mail and permanently delete the copy you received. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Are Oracle courses required for Oracle Certification now?
Hi Lyndon, Well I don't know about your setup, but I can connect just fine with the connect string you listed below on Win2K, Oracle 9.2. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 12/4/2002 2:09 PM, Lyndon Tiu [EMAIL PROTECTED] wrote: This is unfortunate or maybe just it's just an urban legend? Hands on training, may it be $$$ or self taught at home is necessary. But $$$ is definitely not a requirement. I use Oracle at work and do more Oracle at home for my certs. I am able to log-in to Oracle on Linux and Solaris. Oracle WinNT/2K is another story since you cannot really use: sqlplus /nolog connect / as sysdba startup as you would on Unix. Maybe this is what ticked the instructor off - a bunch of Unix Oracle guys can't log into Oracle on NT? -- Lyndon Tiu Quoting DENNIS WILLIAMS [EMAIL PROTECTED]: My instructor in a recent Oracle Education class said that there were a couple of smart alecs that caused quite a stir within Oracle. After receiving their OCP, they couldn't log into a database, and claimed it was because they had never actually used Oracle. The instructor indicated that the new requirement (9i I believe) would require you to take at least one class. I asked do you mean everyone that takes a class from you will be logging on. He just grinned. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, December 04, 2002 11:25 AM To: Multiple recipients of list ORACLE-L Hey people! A friend of mine was just asking me about getting Oracle certified (I completed the Oracle 8i cerfication exams last year), he told me that to his knowledge Oracle requires that you've done Oracle courses before you can be certified now. I knew nothing about this, but can't believe Oracle would so blatantly make you take their over-priced courses. But maybe I'm just naive. Cheers, Kieran Murray CardBASE Technologies Limited® BIM House Crofton Road Dun Laoghaire Co Dublin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kieran Murray INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lyndon Tiu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle you have just received a card from .
If the below link is what I think it is, its a sleasy form of spam (as if there is nice form of spam). I received something similar from a friend a few weeks ago. The web site wants you to download some software so that you can read a greeting card from your friend. If you read the End User Agreement, it says that they have the right to send an email greeting to everyone in your Outlook Address Book if you use their software. I didn't bother to install beyond that point. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 11/25/2002 1:51 PM, [EMAIL PROTECTED] wrote: What is this, flippin' Virii Day or what? Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From:Jay Earle (DBA) [SMTP:[EMAIL PROTECTED]] Sent:Monday, November 25, 2002 12:10 PM To: Multiple recipients of list ORACLE-L Subject: Oracle you have just received a card from . http://www.hkg3.com/f.gif Oracle, recently sent you a postcard. Retrieve your postcard by using this URL. http://www.FriendGreetings.com/pickup.aspx?code=Oracle http://www.hkg3.com/pickup.html?code=Oracleid=2511021 id=2511021 Comment- Oracle, View the card just created. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle is a time machine!!
I get the same date for Oct 5 - 15 (10/15/02).
Good thing I don't deal with dates that far back. ;^)
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 11/22/2002 12:55 PM, Freeman, Robert [EMAIL PROTECTED] wrote:
Don't believe me?? Try this:
create table test(the_date date);
insert into test values (to_date('10-05-1582','mm-dd-') );
select to_char(the_date, 'mm/dd/') from test;
What do you get? :-))
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!
Londo Mollari: Ah, arrogance and stupidity all in the same package.
How
efficient of you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Freeman, Robert
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Davey
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Oracle is a time machine!!
Very tricky Robert. ;^)
Spoiler Alert below!!!
After some time to think about this, I did a quick search on Google and realized that
this is when the calendar changed from Julian to Gregorian.
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 11/22/2002 12:55 PM, Freeman, Robert [EMAIL PROTECTED] wrote:
Don't believe me?? Try this:
create table test(the_date date);
insert into test values (to_date('10-05-1582','mm-dd-') );
select to_char(the_date, 'mm/dd/') from test;
What do you get? :-))
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!
Londo Mollari: Ah, arrogance and stupidity all in the same package.
How
efficient of you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Freeman, Robert
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Davey
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: replace lines feeds in a string
Hi,
Why not just use the replace function?
select replace('Line1'||chr(10)||'Line2',chr(10),' ') from dual
select replace(my_string,chr(13)||chr(10),' ') from my_table;
HTH,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 11/18/2002 7:18 AM, John Dunn [EMAIL PROTECTED] wrote:
I want to replace any carriage returns and lines feeds 'OD0A' and
'0A' in
string, with a space. Can this be done with TRANSLATE, if so how
do I code
this?
John Dunn
Sefas Innovation Ltd
0117 9154267
www.sefas.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Dunn
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Davey
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Oracle DBA with SAP Needed
Well with the ridiculous salary they are offering, they could at least train you in the use of SAP. This is a Fortune 500 company after all. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 11/6/2002 10:56 AM, Paulo Gomes [EMAIL PROTECTED] wrote: unfortunatly i don't work with SAP or i would be interessed regards Paulo -Original Message- Sent: quarta-feira, 6 de Novembro de 2002 14:49 To: Multiple recipients of list ORACLE-L If you are an Oracle DBA With SAP experience looking for a stable company where you can work within a great team environment, this company in Toledo, Ohio is the place for you. This Fortune 500 employer has experienced steady growth over the hundred years it has been in business and is looking for a top notch candidate. This company is located in a very reasonable cost of living area and offers a varied choice of neighborhood communities. If you are looking for a place to grow within your career in a smaller city atmosphere this is the opportunity to check out. Relocation Assistance is provided. PLEASE DO NOT send your resume for this position UNLESS you have the skills outlined below for this position. DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes connot be considered. If you are employed by a consulting company you must have a long term project history. This is a full time staff position so no sub-contractors or third parties please. NO H-1B candidates please. *Requirements: -MUST be a team player. -3+ years Oracle DBA experience. -SAP experience -Must have experience with:Installation, Backup and recovery, Implementation, Conversion, Performance tuning, Troubleshooting, Development, Database Design, Monitoring, and Support. -MUST have excellent communications skills -Major plusses are: SQL Backtrack, DB Artisan, Powerbuilder, Shell scripting and experience with Sybase and/or SQL Server. Base Salary is 55K-to maybe high 60s Firm. The employer itself offers a comprehensive medical plan, dental insurance, life insurance, sick leave and disability plans, a retirement plan, vacation days, a 401K Plan, and much more. For immediate consideration, please email your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Phone: 1-800-549-8502. Please Use Job Code: one/Toledo/DBA-SAP/Jenni I pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the position described above- if it is not a match for your skills. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paulo Gomes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sql question : use of SUBSTR/INSTR functions
Hi Johan,
Try this:
SELECT SUBSTR('127.0.0.1',1,INSTR('127.0.0.1','.')-1)
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.')+1,INSTR('127.0.0.1','.',1,2)-(INSTR('127.0.0.1','.')+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,2)+1,INSTR('127.0.0.1','.',1,3)-(INSTR('127.0.0.1','.',1,2)+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,3)+1,LENGTH('127.0.0.1')-INSTR('127.0.0.1','.',1,3)+1)
FROM DUAL
There may be a more elegant solution, but this was the quickest I could come up with.
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 10/16/2002 4:32 PM, Johan Muller [EMAIL PROTECTED] wrote:
Help!
Anybody have a quick and dirty to parse the 4 octets of a typical
IP address
into 4 separate values. I will insert these into a table where database
checks may verify that the data is in fact a number and also part
of a
valid ip range (the second thru fourth octets cannot be higher than
255. The
source data is very dirty and often fat-fingered, hence the painful
solution):
e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val
4).
I have used various flavors of substr/instr to unravel this, but
the varying
length of the octets (up to 3 bytes) defeats my rudimentary sql coding
skills. I probably have to attack the IP with decode, and any input
will be
very welcome.
Running V 8.1.6.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johan Muller
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Davey
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Yes you can move within the same tablespace. I had to do this when one of the other developers created a table with the default pctfree. Updates were causing rows to chain, so I issued the alter table move command with a new pctfree. Rebuilt the indexes and analyzed the table again and so far everything is working great. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 9/26/2002 12:33 PM, Hemant K Chitale [EMAIL PROTECTED] wrote: Well, yes, that is one option. Can I issue an ALTER TABLE table MOVE to the same tablespace, I wonder. Thanks Hemant At 08:23 AM 25-09-02 -0800, you wrote: Hemant, If I understand your question correctly, trying using the alter table move command and specify new values for pctfree and pctused. This should affect existing blocks. Make sure to rebuild any indicies. HTH, -- Alan Davey [EMAIL PROTECTED] On 9/25/2002 11:38 AM, Hemant K Chitale [EMAIL PROTECTED] wrote: Let me clarify my original question. I do not expect the FreeList for a table to get updated instantaneously after I change the PCTFREE/PCTUSED. What I meant by is the effect ... immediate is that do the new values come into play immediately -- even for existing blocks. Suppose I have a table where PCTFREE was high (40) and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], there would have been about 60% [100-40] usage in the block -- these could mean a large number of rows. Now, I want to reduce the number of rows in a block -- the particular table is a hot table where some blocks become very hot spots [extremely high rate of updates to existing rows, updates which do not increase the size of existing rows]. My cache buffer chains latch contention is high. I further introduce the possibility of deletes [e.g. a purge job running daily]. Because PCTUSED is high, and not very many rows in a block get deleted at each purge, the block is unlikely to come into the FreeList early. It would be a number of days before enough rows are deleted from the block. Therefore, to reduce the contention for the hot blocks, I decide to have only 1 row in each block. Normally, with a *NEW* table, PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. But if I have a large number of blocks in a few extents created when PCTFREE was 40 and PCTUSED 50 or 60. When would these existing blocks start behaving as if they were created with PCTFREE 99 and PCTUSED 1 ? How about new blocks ? Would new [empty] blocks in existing extents immediately behave such that they allow only one row per block ? Or would only new blocks in new extents take the PCTFREE 99 and PCTUSED 1 attributes ? Hemant At 03:53 PM 24-09-02 -0800, you wrote: I replied too soon earlier, I think. Yes, what you state is correct. Jraed [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Is the effect of modifying PCTFREE/PCTUSED immediate ? Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Hemant, If I understand your question correctly, trying using the alter table move command and specify new values for pctfree and pctused. This should affect existing blocks. Make sure to rebuild any indicies. HTH, -- Alan Davey [EMAIL PROTECTED] On 9/25/2002 11:38 AM, Hemant K Chitale [EMAIL PROTECTED] wrote: Let me clarify my original question. I do not expect the FreeList for a table to get updated instantaneously after I change the PCTFREE/PCTUSED. What I meant by is the effect ... immediate is that do the new values come into play immediately -- even for existing blocks. Suppose I have a table where PCTFREE was high (40) and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], there would have been about 60% [100-40] usage in the block -- these could mean a large number of rows. Now, I want to reduce the number of rows in a block -- the particular table is a hot table where some blocks become very hot spots [extremely high rate of updates to existing rows, updates which do not increase the size of existing rows]. My cache buffer chains latch contention is high. I further introduce the possibility of deletes [e.g. a purge job running daily]. Because PCTUSED is high, and not very many rows in a block get deleted at each purge, the block is unlikely to come into the FreeList early. It would be a number of days before enough rows are deleted from the block. Therefore, to reduce the contention for the hot blocks, I decide to have only 1 row in each block. Normally, with a *NEW* table, PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. But if I have a large number of blocks in a few extents created when PCTFREE was 40 and PCTUSED 50 or 60. When would these existing blocks start behaving as if they were created with PCTFREE 99 and PCTUSED 1 ? How about new blocks ? Would new [empty] blocks in existing extents immediately behave such that they allow only one row per block ? Or would only new blocks in new extents take the PCTFREE 99 and PCTUSED 1 attributes ? Hemant At 03:53 PM 24-09-02 -0800, you wrote: I replied too soon earlier, I think. Yes, what you state is correct. Jraed [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Is the effect of modifying PCTFREE/PCTUSED immediate ? Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Stupid Bind Variable question
In any given SQL statement, do I need (or should I :^) ) create a bind variable for every literal value even if it is one of the following cases: 1) substring in a substr function 2) date/time format mask used in to_char or to_date function 3) comparison value in a decode/case statement (not in the where clause) I've seen it mentioned to use bind variables when comparing a column to a literal in the where clause, but nothing specific about the above scenarios. Basically what I am asking is, do all literals need to be made bind variables? Thanks. -- Alan Davey [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Moving data between tablespaces[Scanned]
Karthik,
You need to rebuild your indexes after moving your table as they are invalidated with
the move.
HTH,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 9/17/2002 10:03 AM, Karthikeyan S [EMAIL PROTECTED] wrote:
Thomas / Amar,
I moved the tables from SYSTEM to the DATA tablespace. But now I
am getting the following error.
ORA-01502: index 'ETAIL_TEST_NEW.AGENT_PK' or partition of such
index is in unusable state
Is it because of moving the table to a different tablespace or is
it something else?
TIA
regards,
Karthik
-Original Message-
Sent: Tuesday, September 17, 2002 6:13 PM
To: Multiple recipients of list ORACLE-L
Karthik,
Look at the ALTER TABLE {table_name} MOVE {tablespace}; command.
It will do exactly what you want.
You can also ALTER INDEX {index_name} REBUILD {tablespace} to move
indexes.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, September 17, 2002 4:58 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Some of my tables are accidentally created in the SYSTEM tablespace.
Is there any way to move the records and the table to some other
tablespace?
regards,
Karthik
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Karthikeyan S
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Davey
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Table Locks
Thanks Rachel. I spent the train ride reading the chapters on Instance Tuning and Dynamic Performance Views hoping to find something, but no such luck. I learned a lot of other useful things though, so it wasn't a waste of time. Jacques, v$locked_object shows the table, but I already knew which table was locked. I was hoping to find the offending SQL statement. Have a great weekend everyone. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 8/29/2002 10:43 PM, Rachel Carmichael [EMAIL PROTECTED] wrote: I'm not sure it's possible to find the locking SQL and SID once the session issues other SQL statements. I spent a lot of time a few years back attempting to find it, without success. I got the people at both Platinum Technology and Savant (yes, I'm showing my age here) to try to find it as well, figuring their technical people were better at this sort of thing than I am... no luck. I don't think Oracle stores the statement and who issued it, just the rollback info necessary and the fact that there is a lock. --- Alan Davey [EMAIL PROTECTED] wrote: Hi All, I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Table Locks
Thanks Raj.
I'll give these a try.
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 8/30/2002 10:08 AM, Jamadagni, Rajendra [EMAIL PROTECTED] wrote:
I created following two views for developer's use and so far there
have been
no complaints ..
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKED_OBJECTS
(OBJECT_NAME, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, SQL_ACTIONS,
LOCK_MODE) AS
SELECT DO.object_name, lo.SESSION_ID, lo.oracle_username, lo.OS_USER_NAME,
DECODE(locked_mode,
1, 'SELECT',
2, 'SELECT FOR UPDATE / LOCK ROW SHARE',
3, 'INSERT/UPDATE/DELETE/LOCK ROW EXCLUSIVE',
4, 'CREATE INDEX/LOCK SHARE',
5, 'LOCK SHARE ROW EXCLUSIVE',
6, 'ALTER TABLE/DROP TABLE/DROP INDEX/TRUNCATE TABLE/LOCK
EXCLUSIVE') sql_actions,
DECODE(locked_mode, 1, 'NULL', 2, 'SS - SUB SHARE', 3, 'SX
- SUB
EXCLUSIVE',
4, 'S - SHARE', 5, 'SSX - SHARE/SUB EXCLUSIVE', 6,
'X -
EXCLUSIVE') Lock_mode
FROM sys.V_$LOCKED_OBJECT lo, DB$OBJECTS DO
WHERE DO.object_id = lo.object_id;
CREATE PUBLIC SYNONYM DB$LOCKED_OBJECTS FOR SYSTEM.DB$LOCKED_OBJECTS;
GRANT SELECT ON SYSTEM.DB$LOCKED_OBJECTS TO PUBLIC;
and
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKS
(OBJ_OWNER, OBJ_NAME, OBJ_TYPE, OBJ_ROWID, DB_USER,
SID, LOCK_TYPE, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
AS
SELECT owner obj_owner,
object_name obj_name,
object_type obj_type,
dbms_rowid.rowid_create(1, row_wait_obj#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) obj_rowid,
a.username db_user, a.sid sid, a.TYPE lock_type,
a.row_wait_file#, a.row_wait_block#, a.row_wait_row#
FROM DB$OBJECTS,
(SELECT a.username, a.sid, a.row_wait_obj#, a.ROW_WAIT_FILE#,
a.ROW_WAIT_BLOCK#, a.ROW_WAIT_ROW#, b.TYPE
FROM sys.V_$SESSION a, sys.V_$LOCK b
WHERE a.username IS NOT NULL
AND a.row_wait_obj# -1
AND a.sid = b.sid
AND b.TYPE IN ('TX','TM')
) a
WHERE object_id = a.row_wait_obj#;
CREATE PUBLIC SYNONYM DB$LOCKS FOR SYSTEM.DB$LOCKS;
GRANT SELECT ON SYSTEM.DB$LOCKS TO PUBLIC;
DB$OBJECTs is a snapshot of DBA_OBJECTS, it is too slow to select
from
DBA_OBJECTS, so I created a snapshot that is refreshed on a daily
basis, it
works fine for me.
Hope this helps some. As others have mentioned, currently locked
rows are
very difficult to find, what you can find though is the rowid for
which a
lock is requested.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of
ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
Sent: Friday, August 30, 2002 9:48 AM
To: Multiple recipients of list ORACLE-L
Thanks Rachel.
I spent the train ride reading the chapters on Instance Tuning and
Dynamic
Performance Views hoping to find something, but no such luck. I
learned a
lot of other useful things though, so it wasn't a waste of time.
Jacques, v$locked_object shows the table, but I already knew which
table was
locked. I was hoping to find the offending SQL statement.
Have a great weekend everyone.
Regards,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 8/29/2002 10:43 PM, Rachel Carmichael [EMAIL PROTECTED]
wrote:
I'm not sure it's possible to find the locking SQL and SID once
the
session issues other SQL statements.
I spent a lot of time a few years back attempting to find it, without
success. I got the people at both Platinum Technology and Savant
(yes,
I'm showing my age here) to try to find it as well, figuring their
technical people were better at this sort of thing than I am...
no
luck.
I don't think Oracle stores the statement and who issued it, just
the
rollback info necessary and the fact that there is a lock.
--- Alan Davey [EMAIL PROTECTED] wrote:
Hi All,
I've noticed some locks on various tables and I'm trying to figure
out which DML statements are causing the locks. In this example,
the
lock isn't being released because the developer forgot to include
a
commit/rollback.
If I look at v$session which is causing the lock and query v$sqlarea
with the values in sql_address and prev_sql_addr, I only see
select
statements that were issued after the DML (in this case a delete).
I
can query
v$sqlarea with the locked table name and find the delete statement,
but how do I link this back to the sid that issued it? Also,
what
if
there had been multiple DML statements by this user, how would
I know
which was the first/last one executed?
I'm RTFMing, but so far no luck. Any help would be greatly
appreciated.
Regards,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
Table Locks
Hi All, I've noticed some locks on various tables and I'm trying to figure out which DML statements are causing the locks. In this example, the lock isn't being released because the developer forgot to include a commit/rollback. If I look at v$session which is causing the lock and query v$sqlarea with the values in sql_address and prev_sql_addr, I only see select statements that were issued after the DML (in this case a delete). I can query v$sqlarea with the locked table name and find the delete statement, but how do I link this back to the sid that issued it? Also, what if there had been multiple DML statements by this user, how would I know which was the first/last one executed? I'm RTFMing, but so far no luck. Any help would be greatly appreciated. Regards, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Free ware databases: which are worth the money?
Hi, I've been hearing some good things about FrontBase: http://www.frontbase.com However, I haven't had time to experiment with it myself. It is runs on Windoze, *nix and Mac OS X. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 8/2/2002 8:58 AM, Daniel Wisser [EMAIL PROTECTED] wrote: hi! i can strongly recommend PostgreSQL. i have worked with it modelling data for a java application and perl cgis using it. it supports transactions, functions in pure sql, a procedural language similar to PL/SQL etc. etc. and is very robust. the only thing i miss is handling tablespaces as on oracle. i have sofar only used it on debian, but it should also be fine on other linuxes and some elitist bigots work with it on sun. there is also good literature and a very good o'reilly book on it. on the web htpp://www.postgresql.org regards Mark Teehan wrote: Hi, we are investigating some freeware databases for deployment on systems that dont justify the cost of an oracle license, on linux. What databases out these can cope with a OLTP load, all transaction based, with some reporting? Uncomplicated databases, with mid size volumes of transactions (say low millions) and some reporting queries? I guess reliability is the primary concern, if something can be built as solidly as an oracle instance, with whatever OS protection this would need, then its a starting point for making a non oracle freeware enterprise database. Anyone have any suggestions on what I should download first? Thanks! Mark Teehan Singapore ERG Group -- The contents of this email and any attachments are confidential and may only be read by the intended recipient. - ERG Group -- The contents of this email and any attachments are confidential and may only be read by the intended recipient. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Teehan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Daniel Wisser, Mag. Papyrus Quality Assurance DB Team ISIS Information Systems Alter Wienerweg 12 A-2344 Ma. Enzersdorf, Austria Phone: +43-2236-27551-149 Fax: +43-2236-21081 E-mail: [EMAIL PROTECTED] Hotline: +43-2236-27551-111 Visit the ISIS Website: http://www.isis-papyrus.com --- This e-mail is only intended for the recipient and not legally binding. Unauthorised use, publication, reproduction or disclosure of the content of this e-mail is not permitted. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daniel Wisser INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rant
So no one responded with, We use raid xx. We don't have to worry about backup/recovery. ;^) -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 7/22/02, mkb [EMAIL PROTECTED] wrote: Ok, I need to vent a little. Last week, I was asked to do some tech interviews over the phones for a mid level DBA position. Someone with about 2-3 years experience. I don't consider myself a real smart DBA, nor do I think that I ask particularly tough questions. The questions that I ask potential candidates are soley based on what is on the resume. So I figure if someone has, say, hot backups or SQL tuning on their resumes, I'd expect them to be able to hold a fairly intelligent conversation about these topics. No such luck! What really frustrated me, and what I really want to get out of my system, is that nobody that I talked to, had a real good concept of hot backups. Forget about recovery. I asked each and every candidate who claimed to have done hot backups, just give me a high level overview of how you do a hot backup. Don't care about syntax, just give me the mechanics. The answers I got were completely off base, baffling and frustrating. Some of these folks claimed to have 5 years experience!!! 'Well, we use scripts to do these, so I'm not sure how these are done...' (But it says on your resume you've done this???) 'Oh, I take the tablespace offline, and copy the datafile to tape...' (Unless I'm mistaken, that's not how a hot backup is done, right?) 'Well, I use the export utility, and as the backup starts, it is written to the dump file.' (Huh? What?) 'During this time, everything is written to the redo logs and not to the tablespace...' (You've been reading one of those books, haven't you?) I also asked them how they'd put a tablespace in backup mode. Simple enough, right? Not one of them got it right. Not even close. Didn't have clue as to what I was talking about. Fair enough, you don't know. Well how about a simple recovery scenario. I asked every candidate how they would do an online recover of a datafile while the database was still in use. No ideas. Not even close. I dunno, perhaps I'm spoilt by being a member of this list? Perhaps I expect every candidate to be as knowledgeable as you guys? Perhaps I'm asking too much? Rant over. Thanks for listening. mkb __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Weird Windoze 'AT' Behavior
Dave, I've found AT to be flakey at best. I haven't had your specific problem, but I have had problems where AT would only occasionally run when it was supposed to. I don't remember if it was a specific NT patch or a version of IE explorer, but one of these will install a Scheduler utility, which I've found to be pretty reliable. If you open My Computer (or explorer) you should see a folder called Scheduled Tasks. Double-click and then use the wizard to set up a call to your batch file. HTH, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 7/17/02, Farnsworth, Dave [EMAIL PROTECTED] wrote: I have Oracle 8.1.7 running on NT. I do cold backups nightly and have a batch file that is called by the NT 'AT' scheduler. I recently changed some lines of commands in the batch file and since then when the batch file is executed by 'AT' only the lines that I did not edit are executed. If I execute the batch file from the command prompt it works fine. I deleted the job from 'AT' and then entered it back in but still getting this odd behavior of only executing the commands that I did not edit. Our SA's know nothing about 'AT' so they are of no help. Has anyone else seen this odd behavior in the 'AT' function in Windoze? I know you find it hard to believe that something can be weird in Windoze. ;o) And yes, I am soon planning on learning RMAN and do hot backups. I have the 8i Backup and Recovery Handbook for my reading pleasure. I see the app that is being used going to a 24X7 schedule. Now it is only used during the day. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Alter table move command
Hi All, I want to use the alter table move command (under 9i) to change the storage parameters for a couple of tables. The tables will remain in the current tablespace. Will I need to rebuild any indicies on that table, or will the rowid's be updated automatically. I've looked in the FM, but I don't see any caveats about indicies when using the move option. So, am I safe in assuming that I don't need to worry about them? Thanks, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Alter table move command (THANKS)
Thanks to all who replied. Fortunately, the tables are relatively small, so the rebuild of the indicies shouldn't take very long. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 7/9/02, Jack Silvey [EMAIL PROTECTED] wrote: Alan, The alter table move command will invalidate all existing indicies and you will have to rebuild them. This is because the alter table move is implemented as a CTAS in the background, and all of the rowids will change. table move tips: 1) use parallelism - however, parallel processes will write to their own segments, and will trim the unused space off the end for all but one of the segments during the final merge of all the segments into the new index segment. This can give you odd sized segments, throwing off uniform space allocation (if you use that). index rebuilding tips: 1) If your index is partitioned, you can rebuild the partitions at the same time, and rebuild all of your indexes at the same time. However, you cannot update/insert/delete the table while this is going forward, unless you use the online option, which has limitations. 2) Use parallel (degree x) to rebuild, since it will spawn off more processes and take less time. see space considerations above. 3) Use nologging, unless you want to store your indexes in the redo logs (otherwise, you can always just recreate, much easier) 4) use the compute statstics clause to gather stats during the build 5) consider using initrans 4 or better, otherwise, you risk running into deadlocks during parallel updates (doesn't take up that much more space - 23 bytes or so). Same for pctfree - leave 1 pct, otherwise the ITL list can't grow and you might get into trouble. 6) some indexes can be built online, which allows updates to go forward during the rebuild. hth, jack --- Alan Davey [EMAIL PROTECTED] wrote: Hi All, I want to use the alter table move command (under 9i) to change the storage parameters for a couple of tables. The tables will remain in the current tablespace. Will I need to rebuild any indicies on that table, or will the rowid's be updated automatically. I've looked in the FM, but I don't see any caveats about indicies when using the move option. So, am I safe in assuming that I don't need to worry about them? Thanks, -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: So, What is a 'Production DBA'?
Beware of developers that carry screwdrivers. Its a hardware problem, not software. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 5/30/02, Thomas Day [EMAIL PROTECTED] wrote: I guess it's that old Russian proverb To a hammer, all the world looks like a nail. Developers have experience as hammers and everything revolves around the code. As an ex-developer, now DBA, I know that sometimes you need a screwdriver (or a Harvey Wall Banger). Jay Wade fish_dbaTo: Multiple recipients of list ORACLE-L @hotmail.com[EMAIL PROTECTED] Sent by: rootcc: Subject: Re: So, What is a 'Production DBA'? 05/30/2002 11:08 AM Please respond to ORACLE-L I feel that it is hard to draw the lines between Application and Production DBA's. For example where would you place the DBA that maintains SAP? Without the application knowledge he/she/it wouldn't get very far. Also I have been wondering something and this thread seems a good place to ask. Is there a historical feud between DBA's and Developers? Coming from a consulting/software house I find some of the comments funny but can't believe that there is that quantity of bad developers. Most of the DBA's we deal with have come up through the ranks and started as developers. From: Ron Rogers [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: So, What is a 'Production DBA'? Date: Thu, 30 May 2002 04:48:29 -0800 Rachel, I agree with your short list of the areas of responsibilities but I would change the word application to development. An application DBA, from the people I have talked to, is quite busy performing the upgrades and patches that accompany the Oracle Applications. The applications database generally has many, many tables, triggers and constraints and is constantly the target for upgrades and patches from Oracle. It is a time consuming task as the majority of the different applications (financial, HR, Purchase Order, etc) have hooks into each different package and are so intertwined that any small fix in one involves patches for the others. There are only a few user defined tables as each package has their own named tables that are partially shared between packages. There is very little if any work you can do on the application code because it is so intertwined and customized when it is installed. Any upgrades require that the customization be reworked to make it fit into the new version of the application package. It takes a longer time to install than a standard database, on the magnitude of days, and requires a dedicated and investigative mind set to maintain. To the list you created I would add: Help desk call recipient, network support, client support, software and hardware evaluation, whipping post, IT team member (possibly team leader), self driven, office coffee maker, consumer of various liquids. Ron ROR mª¿ªm [EMAIL PROTECTED] 05/29/02 04:50PM that's not a bad definition :) seriously, everyone will have their own definition, mine is: production dba -- responsible for all databases that are considered production. this includes but is not limited to: backups recovery testing contingency testing production performance tuning (should mostly be database tuning as SQL really should be tuned at the development stage, with information passed back from the production DBA) documentation of all procedures space management on production systems, including capacity planning and projection of growth change management monitoring external data loads into production database health checks on production database application dba -- responsible for all databases in which developers have access. responsibilities: SQL tuning (not SQL coding!) database design, in conjunction with the developers any and all changes to the application schema working with the production DBA to ensure
Re: Fav. Urban Legend...Mem vs Disk
On 3/20/02, Mark Leith [EMAIL PROTECTED] wrote: Ahh, but how can you be sure that 42 does not also *cause* all of life's problems? ;) Because, as Homer Simpson said, Ahhh booze. The cause of and the solution to all of life's problems. ;^) -- Alan Davey [EMAIL PROTECTED] On 3/20/02, Mark Leith [EMAIL PROTECTED] wrote: Ahh, but how can you be sure that 42 does not also *cause* all of life's problems? ;) Does this also mean that the preferred number of disks, tablespaces, and extents should also be 42? ; -Original Message- Krishna Vaidyanatha Sent: 20 March 2002 14:03 To: Multiple recipients of list ORACLE-L My dear friend Mogens, I am so glad we share the same kind of bedtime reading in our own parts of the world. I totally agree with you, every cache hit ratio and performance metric within Oracle needs to be 42, for us to be in a sublime and happy state...;-). After all 42 does solve all of life's problems!! Cheers, Gaja --- Mogens Nxrgaard [EMAIL PROTECTED] wrote: I always thought 42 was a good number. Perhaps I was wrong. Bjxrn Engsig wrote: Cary Milsap from hotsos has much data to confirm an approximate 1:100 ratio between LIO time and PIO time. Can we therefore conclude, that the buffer cache hit ratio should be 99%? :-) Rgds, Bjxrn. On Wednesday 20 March 2002 10:48, Connor McDonald wrote: Some rudimentary testing on a laptop here (500Mhz, 512M RAM, typical single disk) a) visiting a single block via 4,000,000 logical IO's got me approx 35000 gets/sec b) repeated full table scans similar system got me approx 350 phys reads/sec After this extensive, thorough and exhaustive exercise, I can definitely say that memory access versus disk access (as it pertains to Oracle) is 100 times faster on this machine in single user mode I think we can generalise this to be the rule for all servers under all conditions :-) Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: I've heard the disk vs. memory arguments before, but never have seen quantifiable data either way... if anyone has any, I'd love to see it. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 18, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Robert - So THAT is the title of your next book. I'm primed to buy it already. I just recalled a legend, maybe. Disk is 10,000 times slower than memory, so memory access times are infinitesimal compared to disk access. Cary Millsap covers this in his Hotsos Clinic. He has run tests that prove ain't so. The point is that you can't just use ratios to tune Oracle, but need to look at wait times. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L So, does the CoO (Church of Oracle) have an infallibility doctrine then??? ... From the Book of Oracle, chapter 5 ... ...and the DBA did look upon his database, and he saw it was good. His tablespace datafiles being distributed tither and fro, spread amongst the platters of his disks. And he did complete that which was called documentation, and then he rested from his labors, and drank Mountain Dew Code Red... :-) Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. stuff deleted = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
