Re: Cheap table lock?
Um, no. Select for update will lock whatever rows it selects. If it happens to select all the rows in a table, then all the rows in the table will be locked. But, you could select for update where primary_key=1 and select (and lock) just one row. As with other DML locks, the lock will be released when you commit or rollback. See the concepts manual for more details. Hope that helps, -Mark On Fri, 2002-09-27 at 20:33, tony ynot wrote: Is Select ... For Update; a cheap table lock? It seems whenever code uses this statement all the records in the cursor are locked until the cursor is closed. __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak 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: Cheap table lock?
Mark J. Bobak wrote: Um, no. Select for update will lock whatever rows it selects. If it happens to select all the rows in a table, then all the rows in the table will be locked. But, you could select for update where primary_key=1 and select (and lock) just one row. As with other DML locks, the lock will be released when you commit or rollback. See the concepts manual for more details. Hope that helps, -Mark On Fri, 2002-09-27 at 20:33, tony ynot wrote: Is Select ... For Update; a cheap table lock? It seems whenever code uses this statement all the records in the cursor are locked until the cursor is closed. IMHO SELECT .. FOR UPDATE is something which, in most cases where I see it used, is perfectly dispensable. It's a relic of Oracle 5 when there was no real row lock (only exclusive table locks and 'intent' row locks) and the difficulty was to hold them for as short a period as possible - hence SELECT ... FOR UPDATE to get the rowid, then a very fast UPDATE using the rowid and commit. Since Oracle6, unless you intend to 'reserve' a row, run a 3 hours process and be certain to find the same value when you're done, you can directly execute the update, it will spare you a select. -- 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 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).
Determine process of index build - HOW
Hi there How can I see how far my index build has is. I got a table with 43 mil records. It is taking forever as expected but would like to see how far it is, Any idea. Oracle 8.1.6.3 (32Bit) on Solaris 2.6 on Sun E10K George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! *** This message contains information intended solely for the addressee, which is confidential or private in nature and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message or any file attached to this message. Any such unauthorised use is prohibited and may be unlawful. If you have received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the original message from your machine. Furthermore, the information contained in this message, and any attachments thereto, is for information purposes only and may contain the personal views and opinions of the author, which are not necessarily the views and opinions of Dimension Data (South Africa) (Proprietary) Limited or its subsidiaries and associated companies (Dimension Data). Dimension Data therefore does not accept liability for any claims, loss or damages of whatsoever nature, arising as a result of the reliance on such information by anyone. Whilst all reasonable steps are taken to ensure the accuracy and integrity of information transmitted electronically and to preserve the confidentiality thereof, Dimension Data accepts no liability or responsibility whatsoever if information or data is, for whatsoever reason, incorrect, corrupted or does not reach its intended destination. * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Leonard (ZA) 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).
Does anyone know the HACMP for oracle 8i in an AIX box?
RE: Setting Cursor Sharing = Force in 8.1.7.3
Nat, We are currently using CS=F on 8.1.7.2 and we'll be patching to 8.1.7.4 soon. You need to be made aware of some severe problems with this in 8i. We've run across BUG 2225065 listed on MetaLink. It says that you can actually get incorrect results in certain queries when using CS=F. Luckily one of our developers noticed the very subtle problem and we were able to use ALTER SESSION SET CURSOR_SHARING=EXACT for his particular program. The catch is finding all the affected queries! Also, we get sporadic ORA-600s that can be attributed to CS=F, but it hasn't been a showstopper yet. For more CS=F issues, you will want to check Metalink doc 120607.1. It has pointers to the docs that have all the fixes for all the 8.1.7.x releases. There are a few important fixes in .3 and .4. Offhand, I don't remember if the BUG I mentioned is fixed in either release. OTOH, all this mess is well worth it for us. Since we had no idea how important bind variables are when we first started with Oracle, more than 95% of our code (not a very good hit-ratio!) is bind free. And to go back and change all that code -- possibly messing something up in the process -- without any perceived benefit to the user, well, it just ain't gonna happen. CS=F has been a life-saver. HTH! GL! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Nat [mailto:[EMAIL PROTECTED]] Sent: Friday, September 27, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Subject: Setting Cursor Sharing = Force in 8.1.7.3 We are looking into setting up cursor_sharing parameter to FORCE. Has anyone seen any bad effects of setting Cursor_sharing=FORCE. Are there any real bad effects of setting it..? I was thinking of going back to my developers and make them use bind variables in their code. If I set the above parameter, they may continue to develop their code the way it is now. Let me know what you all think about it.. Thanks in advance, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Remember me? Oracle DBA veteran considering getting certifi
Title: RE: Remember me? Oracle DBA veteran considering getting certified? Well, Given the IT market I felt that it was worth getting certified even though I haven't had any problems and been working with Oracle as DBA for over 8 years. However, I decided that I didn't want to spend a lot of money or time to do it. I have 2 small children, work, - yadayadayada(sp?). I got the self-test for the first test, studied using that and read Mike Ault's Exam cram book from front to back (excellent resource, concise, straightforward, good examples - just a couple of errors in whole book). Total test time was about 30 hours. Took the exam this morning in 60 minutes (120 alloted), got 49 out of 57 questions correct and passed. I really want to thank Mike Ault for the excellent concise Cram book and intend to continue on this same path for the other exams. Unfortunately, Mike didn't write all of them - however, I am hoping they are all of the same level of quality. I haven't taken a course in Oracle (any) for about 5 year and SQL/PLSQL in about 10-12. Total hours to prepare : 30 hours Resources: Exam Cram by Mike Ault and self-test exam Any additional costs - none Didn't want to study on clients time so ended up studying mostly between the hours of 2:00 a.m. and 8:00 a.m. in the morning. Hope the others go well and can get this done before Oracle changes the criteria.
RE: Determine process of index build - HOW
Title: RE: Determine process of index build - HOW George, Doing some big index rebuilding myself on this fine weekend. If you know about how big the index will be and the tablespace, you can see how large the temporary segment is at the moment. That can give you a rough idea of how far along things are. It usually takes quite a while for the first extent then things get moving faster. select segment_name, segment_type, round(bytes/1024/1024,2) Mb , initial_extent, next_extent, extents , Owner, max_extents from dba_segments Where segment_type = 'TEMPORARY' and tablespace_name= 'HISTORYC'; Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: George Leonard (ZA) [SMTP:[EMAIL PROTECTED]] Hi there How can I see how far my index build has is. I got a table with 43 mil records. It is taking forever as expected but would like to see how far it is, Any idea. Oracle 8.1.6.3 (32Bit) on Solaris 2.6 on Sun E10K George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za
RE: Remember me? Oracle DBA veteran considering getting certifi
Which version you are talking about? 8i or 9i upgrade certification Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Sat, 28 Sep 2002 08:53:19 -0800 Well, Given the IT market I felt that it was worth getting certified even though I haven't had any problems and been working with Oracle as DBA for over 8 years. However, I decided that I didn't want to spend a lot of money or time to do it. I have 2 small children, work, - yadayadayada(sp?). I got the self-test for the first test, studied using that and read Mike Ault's Exam cram book from front to back (excellent resource, concise, straightforward, good examples - just a couple of errors in whole book). Total test time was about 30 hours. Took the exam this morning in 60 minutes (120 alloted), got 49 out of 57 questions correct and passed. I really want to thank Mike Ault for the excellent concise Cram book and intend to continue on this same path for the other exams. Unfortunately, Mike didn't write all of them - however, I am hoping they are all of the same level of quality. I haven't taken a course in Oracle (any) for about 5 year and SQL/PLSQL in about 10-12. Total hours to prepare : 30 hours Resources: Exam Cram by Mike Ault and self-test exam Any additional costs - none Didn't want to study on clients time so ended up studying mostly between the hours of 2:00 a.m. and 8:00 a.m. in the morning. Hope the others go well and can get this done before Oracle changes the criteria. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Remember me? Oracle DBA veteran considering getting certifi
I think he meant Oracle 9i Exam 1 of 4 - Intro to SQL. On Saturday 28 September 2002 14:28, Mohammad Rafiq wrote: Which version you are talking about? 8i or 9i upgrade certification Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Sat, 28 Sep 2002 08:53:19 -0800 Well, Given the IT market I felt that it was worth getting certified even though I haven't had any problems and been working with Oracle as DBA for over 8 years. However, I decided that I didn't want to spend a lot of money or time to do it. I have 2 small children, work, - yadayadayada(sp?). I got the self-test for the first test, studied using that and read Mike Ault's Exam cram book from front to back (excellent resource, concise, straightforward, good examples - just a couple of errors in whole book). Total test time was about 30 hours. Took the exam this morning in 60 minutes (120 alloted), got 49 out of 57 questions correct and passed. I really want to thank Mike Ault for the excellent concise Cram book and intend to continue on this same path for the other exams. Unfortunately, Mike didn't write all of them - however, I am hoping they are all of the same level of quality. I haven't taken a course in Oracle (any) for about 5 year and SQL/PLSQL in about 10-12. Total hours to prepare : 30 hours Resources: Exam Cram by Mike Ault and self-test exam Any additional costs - none Didn't want to study on clients time so ended up studying mostly between the hours of 2:00 a.m. and 8:00 a.m. in the morning. Hope the others go well and can get this done before Oracle changes the criteria. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu 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: [Q] imp/exp error on 9iR2?
The password may not be a problem here. In 9i, sys must connect 'as sysdba' or 'as sysoper'. So, use 'sys as sysdba' when prompted for username (without the single quotes). If you want to use a parfile, then specify userid='sys/password as sysdba'. Those single quotes are needed and notice where 'as sysdba' goes :) HTH, - Kirti -Original Message- Sent: Friday, September 27, 2002 7:43 PM To: Multiple recipients of list ORACLE-L It would appear you don't know the pw of sys, or maybe the capslock is on ;) If you can sqlplus /nolog can connect and alter the sys pw. Then you will know the sys pw and can use it on imp cmd. $ sqlplus /nolog SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 27 19:36:42 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. SQL connect / as sysdba Connected. SQL alter user sys identified by change_on_install User altered. SQL On Fri, Sep 27, 2002 at 02:51:37PM -0800, dist cash wrote: I tried to do full database import use sys ID and have error come out. for sqlplus I can use sqlplus /nolog to avoid it, but on import/export how to avoid it? RSS::/home/app/oracle/admin/db92/export[173]% imp Import: Release 9.2.0.1.0 - Production on Fri Sep 27 17:36:13 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Username: sys Password: change_on_install IMP-00058: ORACLE error 28009 encountered ORA-28009: connection to sys should be as sysdba or sysoperUsername: Password: IMP-00058: ORACLE error 1017 encountered ORA-01017: invalid username/password; logon deniedUsername: Thanks. _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dist cash 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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: Deshpande, Kirti 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).