Re: Inserts are taking time !
Thanks Justin, Now its high time and I have to take some harsh steps to resolve this bottleneck. This might even go to an extent of changing the app code. But I have to do it, there is no way out. Thank you all for the support. I will get back to you with my reading and implementation in a day or two (even if this is successful or not). bfn Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 12:28 PM At 09:08 AM 9/5/2002, you wrote: Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. Yes, yes, a thousand times yes. I'm willing to wager that 90% of your time is spent waiting for the commits in this scenario. But if this auto-commit is the issue than why first 10K records are inserted quickly. Well, it's only slower by a factor of 6 when you go from 0 rows to 350,000 rows, which seems reasonable. When you have 0 rows in the database, you probably have the table, indexes, etc. completely cached in memory. When the table grows larger, however, more and more stuff will be coming from the disk. Additionally, the commits are likely to be a bit more complicated the more rows you have, because the number of blocks that need to be touched in the indexes will go up. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Can you do batch inserts, so that you're not committing 10,000 times? Modify the application to batch 1000 statements at a time, and I'll be you get much happier very quickly. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to.
cache settings NT
Hi all I have the following question for you : what are the preferred values for the several cache settings on the fysical disk units (NT W2K) e.g. 100 % read cache - 0 % write cache 50% read cache - 50% write cache is there a difference between settings for an OLTP or DSS solution. etc anyone with an explanation vr. gr. g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Datafile size on NT or 2000
The 4GB problem on NT was ONLY where it would autoextend over a 4gb boundary (eg 4,8,12,16...). You can manually size the datafile whatever size you wish - ie you can cross the boundary manually. However, one place I recently contracted at were determined to cold backup their 120GB data warehouse system and it was decided to zip the datafiles as part of the backup process. Zip only handles files up to 4GB in size so they standardised on 4000MB datafiles (zipped down they reduced to 80mb - yep huge allocation, little data - it takes all sorts!) - guess who had to rebuild the data warehouse to these specs! ;-( So, on deciding your datafile size, be aware of various issues - mostly regarding the management of these files. I would agree with my previous client that 4gb (or near) is probably the biggest I'd go on windows systems unless there's a *specific* and good reason to go higher. Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 06 September 2002 00:10 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- There used to be a bug that caused problems with files on NT 4g or more in size. I've standardsized on 3999 meg as a result of this. -- Tony JohnsonEmail : [EMAIL PROTECTED] Manager Database Administration Voice : ( 480 ) 682 - 0849 Trimble Navigation Cell : ( 602 ) 363 - 7328 7408 W. Detroit Street #100 Fax: ( 480 ) 961 - 8801 Chandler, AZ 85226 -- Murphy's Data Constant:Data will be damaged in direct proportion to its value -Original Message- Sent: Thursday, September 05, 2002 2:29 PM To: Multiple recipients of list ORACLE-L What is an ideal datafile size for a large database 10G or greater Oracle 8.1.6 We have been using slightly under 2G and looking for an ideal size and possible reasons to use such a size Thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tony Johnson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information, attachments and opinions contained in this message are those of its author only and do not necessarily represent those of The Woolwich and or any other members of the Barclays Group and are intended solely for the use of the individual or entity to whom they are addressed. The sender may not be authorised to give financial advice, and nothing in this message should be construed as offering such advice. The message may contain privileged and confidential information and you may not copy, distribute or take any action in reliance on it. If you have received this email in error please notify the Information Security Manager at [EMAIL PROTECTED] Replies to this email may be monitored for operational or business reasons. Woolwich plc. Registered in England Number : 3295699. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bishop Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing:
RE: Inserts are taking time !
Removing the auto-commit sounds good, on condition, of course, that you can afford to lose up to 999 recs in the event of a system crash. You probably may want to write them to a temp storage table first Chris -Original Message- Sent: 06 September 2002 09:28 To: Multiple recipients of list ORACLE-L Thanks Justin, Now its high time and I have to take some harsh steps to resolve this bottleneck. This might even go to an extent of changing the app code. But I have to do it, there is no way out. Thank you all for the support. I will get back to you with my reading and implementation in a day or two (even if this is successful or not). bfn Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 12:28 PM At 09:08 AM 9/5/2002, you wrote: Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. Yes, yes, a thousand times yes. I'm willing to wager that 90% of your time is spent waiting for the commits in this scenario. But if this auto-commit is the issue than why first 10K records are inserted quickly. Well, it's only slower by a factor of 6 when you go from 0 rows to 350,000 rows, which seems reasonable. When you have 0 rows in the database, you probably have the table, indexes, etc. completely cached in memory. When the table grows larger, however, more and more stuff will be coming from the disk. Additionally, the commits are likely to be a bit more complicated the more rows you have, because the number of blocks that need to be touched in the indexes will go up. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Can you do batch inserts, so that you're not committing 10,000 times? Modify the application to batch 1000 statements at a time, and I'll be you get much happier very quickly. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records.
Re: Restoring RMAN backups to different host ......
Babu,
You need to copy your backup files to HOST_B. If your file systems are different
thant HOST_A, create symbolic links for the directories where rman expects to find the
backups.
Jay Hostetter
Oracle DBA
D. E. Communications
Ephrata, PA USA
[EMAIL PROTECTED] 09/05/02 08:13PM
Dear List,
Iam following the procedure to restore database from RMAN backup from HOST_A
to HOST_B with a recovery catalog.
1. I copied the init.ora file to HOST_B
2. on HOST_B, I made an entry in oratab for 'rtest' database and switch to
'rtest' database using . oraenv
3. issued: rman target / catalog rman/rman@Connect String
4. startup nomount;
5. run {
allocate channel ch1 type 'sbt_tape';
restore controlfile;
}
It generates the following error. list backup of controlfile is showing up
the backup entries.
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03002: failure during compilation of command
RMAN-03013: command type: restore
RMAN-03007: retryable error occurred during execution of command: IRESTORE
RMAN-07004: unhandled exception during command execution on channel ch1
RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve
sequential file, handle=nre1u1kk_1_1, parms=
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: sbtrestore: Backup file not found.
RMAN-10031: ORA-19624 occurred during call to
DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
Could someone help me in resolving the issue.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Must Read for Every Developer and DBA
Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Function-Based Index not working
On 2002.09.05 22:18 Rachel Carmichael wrote: I love automagic things :) so I can leave the table alone right now there are all of 7 rows in it Rachel Given the size of the the table, may be you should try partitioning it? -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
APPS password security
Is there a way to prevent some of my enterprising developers from echoing the apps password to a log file from within a concurrent host program? For example: echo INPUT: $1 tells them way too much information. APPS 11.0.3 (soon to be 11.5.7 if all goes well). _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Martin Brown INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Must Read for Every Developer and DBA
I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: connect to the databases without using tnsnames.ora?
Cc I believe the Java thin client can connect without using the tnsnames.ora file. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 8:23 PM To: Multiple recipients of list ORACLE-L Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ 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: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
* Sr. Oracle DBA needed in Des Moines, Iowa
Excellent opportunity with a company in Des Moines, Iowa that needs a Sr. Oracle DBA to join it's IT staff. This company is prepared to assist in relocation in the form of some kind of sign on bonus. They STRONGLY prefer someone with ties to the Midwest. 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. *Description: This company is building a new Oracle infrastructure.. it's partially built. They have an Internet application and are transitioning a lot of old technology to ORACLE. They have one DBA who has limited ORACLE experience but has other database experience. They are currently they are utilizing Oracle v8.17 and considering a move to Oracle 9i. *Requirements: -BSCS or related degree. -5-10 years Oracle DBA experience. -Someone who knows the latest and greatest features - truly an expert. -Must really understands performance and tuning and can guide projects and how they are implementing databases and queries. -Must have solid Data Architecture knowledge - able to model databases for any type of system- whether it be an operational database, a reporting database or a decision support database. -Must be customer focused. -Must have leadership qualities...someone to take charge and make things happen. -Must be a U.S. citizen or perm. resident. Base Salary is up to 80-95K..depending on experience. or 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/Des Moines/DBA/Kim 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: connect to the databases without using tnsnames.ora?
As an FYI, some 3rd-party products still mistakenly require a TNSNAMES.ORA file. Quest's QCO (at least 2.0, haven't D/L'd 2.1 yet and Quest Support tells me it will be fixed in 3.0 next Q1) is one of them. Also, there is at least one circumstance that requires a TNSNAMES.ORA on a server. I can't remember for the life of me what it is, but something with the Intelligent Agent sticks in my head. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, September 05, 2002 10:28 PM To: Multiple recipients of list ORACLE-L -Original Message- From: CC Harvest [mailto:[EMAIL PROTECTED]] Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? To add to what other posters have said: If the DBA was indeed using Oracle Names, you would see the configuration parameters for the names server in the sqlnet.ora file, which would be in the same directory where you would expect to find the tnsnames.ora file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ctrl c on PCs
In sql*plus, if do a ctrl-c from within sqlplus I am returned to the dos prompt. However, from OpenVMS, I am kept in sql*plus. We have some scripts that do a pause in them so can terminate if a problem. However, if hit ctrl-c during the pause sql*plus ends commiting the updates. Shouldn't sql*plus just return to a prompt or is this normal on windows. Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 781-4204
RE: foreign key indexes and parent-table locking
I agree that that's the best way to see what actually happens, and I will do that but I like to understand the theory, too . . . -bill -Original Message- Sent: Thursday, September 05, 2002 5:40 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Importance: High Bill, Rather than try to understand that explanation, you may find it more educational to create a pair of tables with a parent/child relationship via foreign key. Put some data in the tables, then do updates and deletes both with and without FK indexes. Examine dba_locks while doing so and observe the lock modes. This will be much easier to understand than the 'documentation' Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:foreign key indexes and parent-table locking Hi, I'm trying to understand the whole issue of foreign key indexes and locking. Found a note on metalink (11828.1) that seems to explain it, but either it's not clear or I'm missing something. Why then, does an index on the foreign key mean that the shared lock on the parent table is not required? When a row in the child table is inserted, deleted or has its foreign key updated, the corresponding index entry/entries is/are also locked. When an application attempts to delete or update the primary key of a parent row, it reads the FIRST corresponding entry in the child's foreign key index (uncommitted or otherwise) and, if locked, waits for that lock to be released. So far so good . . . this next piece, too, seems to make sense: If the modified child row is NOT the first occurrence of the foreign key in the index then the parent modification must be prevented anyway, regardless of the outcome of uncommitted transactions on other child rows with this key. But now here's the part that leaves me hanging . . . Hence the error can be flagged immediately and so the transaction is not forced to wait. This mechanism ensures the minimum reads and wait times to maintain data consistency. Can anyone help by either translating this last part or rephrasing it? Or explaining the issue differnetly? Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle to publish pricing guide on Sept. 3
H...maybe I need a new calendar, but I'm still getting 404s on the links from that URL. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Murray, Margaret [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 4:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle to publish pricing guide on Sept. 3 Not there yet - promises, promises: Oracle Software Investment Guide -- Available September 6, 2002 Visit http://www.oracle.com/corporate/pricing on Friday, September 6th to see the Oracle Software Investment Guide. Shall we start a pool of what the likely date will be? Maybe a delay of another week and it's Friday Sept 13th -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: off topic: OCP exam registration
The OPP is open to Oracle Partners and their employees only. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, September 05, 2002 6:21 PM To: Multiple recipients of list ORACLE-L Subject:RE: off topic: OCP exam registration Sunil Who can use this code? Employees /partner of Oracle Partner program or anybody? Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 05 Sep 2002 13:38:43 -0800 Try promotion code OPP to get 30% off. Sunil Nookala Dell Computer corp. Austin, TX -Original Message- Sent: Thursday, September 05, 2002 3:05 PM To: Multiple recipients of list ORACLE-L Hi ALL! I just spoke with Prometric and they told me if I give them my OTN number they will give me discount for OCP exam. I'm the member of OTN , but I don't have any number. Someone know where I can get this number? Thanks. Greg. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Faktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Function-Based Index not working
There has been some good stuff on the Usenet list lately about the debating the usefulness of CACHE as opposed to KEEP buffer pool. Ethan Post perotdba (AIM), epost1 (Yahoo) -Original Message- Sent: Thursday, September 05, 2002 7:53 PM To: Multiple recipients of list ORACLE-L Given the fact that the table is so small and frequently accessed, it will get cached 'automagically'. No need to do anything. Anjo. On Thursday 05 September 2002 23:43, you wrote: Rachel, With a table that small I would consider caching the table to eliminate the io. I do not know if you can cache an IOT but then it should be even faster. Ron ROR -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle on Win platforms
Title: RE: Oracle on Win platforms A co-worker, neither a dba nor a developer, was able to successfully install 8.1.7 Personal Edition on 98 with no problems at all. I wonder if the same would work on ME, since 98SE and ME are like twin brothers from what I can tell. -Original Message- From: Robson, Peter [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 7:44 AM To: Multiple recipients of list ORACLE-L Subject: Oracle on Win platforms Just a quickie, please, folks - I have used Win NT4 for years, but have now got Win98SE on a second PC. (Don't mention XP...) Which versions of Oracle will load to 98? Single user only, stand-alone machine (no networking). On attempting to load 7.3.4 an 'unsuported' message pops up, which wasn't exactly confidence inspiring... thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments. http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: connect to the databases without using tnsnames.ora?
Rich - Can you explain what you mean by mistakenly require? Is it because you are using Oracle Names? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, September 06, 2002 9:38 AM To: Multiple recipients of list ORACLE-L As an FYI, some 3rd-party products still mistakenly require a TNSNAMES.ORA file. Quest's QCO (at least 2.0, haven't D/L'd 2.1 yet and Quest Support tells me it will be fixed in 3.0 next Q1) is one of them. Also, there is at least one circumstance that requires a TNSNAMES.ORA on a server. I can't remember for the life of me what it is, but something with the Intelligent Agent sticks in my head. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, September 05, 2002 10:28 PM To: Multiple recipients of list ORACLE-L -Original Message- From: CC Harvest [mailto:[EMAIL PROTECTED]] Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? To add to what other posters have said: If the DBA was indeed using Oracle Names, you would see the configuration parameters for the names server in the sqlnet.ora file, which would be in the same directory where you would expect to find the tnsnames.ora file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: connect to the databases without using tnsnames.ora?
Are you talking about the JDBC Oracle Thin Driver? This is because you configure it separately. It still has a tnsnames.ora type config somewhere hidden in it's own config file. Just that it does not use the regular tnsnames.ora. ltiu DENNIS WILLIAMS wrote: Cc I believe the Java thin client can connect without using the tnsnames.ora file. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 8:23 PM To: Multiple recipients of list ORACLE-L Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ 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: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle 9iAS Rel.1 upgrade to 9iAS Rel.2
Hi Has anyone upgraded from 9iAS 1.0.2.2.2 to the new Release 2? Is there a migration path or should you start from scratch with Rel.2 as it is significantly different (what with the infrastructure stuff and all). I have heard that migrating any Portal stuff is nigh on impossible. Any other comments. Thanks, Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Restoring RMAN backups to different host ......
Babu
I agree with Jay, you need to create symbolic links that mimic the
locations RMAN hopes to find the backup files.
I was unable to get RMAN to restore the control file. The problem I
encountered was that RMAN required the target database to be mounted first,
but without a control file I couldn't mount the database. Someone the list
reported that this was possible, but I finally just said that it was easier
to create a backup control file outside RMAN and use that.
The RMAN information needed to restore is probably still available in the
control file. I found that to simplify the type of recovery you are
attempting.
RMAN will need the archive logs that were created during the time it was
performing the backup in order to get the data files back to the same SCN.
This task took me awhile, so be patient.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, September 05, 2002 7:13 PM
To: Multiple recipients of list ORACLE-L
Dear List,
Iam following the procedure to restore database from RMAN backup from HOST_A
to HOST_B with a recovery catalog.
1. I copied the init.ora file to HOST_B
2. on HOST_B, I made an entry in oratab for 'rtest' database and switch to
'rtest' database using . oraenv
3. issued: rman target / catalog rman/rman@Connect String
4. startup nomount;
5. run {
allocate channel ch1 type 'sbt_tape';
restore controlfile;
}
It generates the following error. list backup of controlfile is showing up
the backup entries.
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03002: failure during compilation of command
RMAN-03013: command type: restore
RMAN-03007: retryable error occurred during execution of command: IRESTORE
RMAN-07004: unhandled exception during command execution on channel ch1
RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve
sequential file, handle=nre1u1kk_1_1, parms=
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: sbtrestore: Backup file not found.
RMAN-10031: ORA-19624 occurred during call to
DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
Could someone help me in resolving the issue.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Restoring RMAN backups to different host ......
About restoring controlfiles, if I remember this correctly as I did just
this a few months ago:
You have to startup database nomount.
Then restore controlfile.
Then alter database mount.
Then restore database.
ltiu
DENNIS WILLIAMS wrote:
Babu
I agree with Jay, you need to create symbolic links that mimic the
locations RMAN hopes to find the backup files.
I was unable to get RMAN to restore the control file. The problem I
encountered was that RMAN required the target database to be mounted first,
but without a control file I couldn't mount the database. Someone the list
reported that this was possible, but I finally just said that it was easier
to create a backup control file outside RMAN and use that.
The RMAN information needed to restore is probably still available in the
control file. I found that to simplify the type of recovery you are
attempting.
RMAN will need the archive logs that were created during the time it was
performing the backup in order to get the data files back to the same SCN.
This task took me awhile, so be patient.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, September 05, 2002 7:13 PM
To: Multiple recipients of list ORACLE-L
Dear List,
Iam following the procedure to restore database from RMAN backup from HOST_A
to HOST_B with a recovery catalog.
1. I copied the init.ora file to HOST_B
2. on HOST_B, I made an entry in oratab for 'rtest' database and switch to
'rtest' database using . oraenv
3. issued: rman target / catalog rman/rman@Connect String
4. startup nomount;
5. run {
allocate channel ch1 type 'sbt_tape';
restore controlfile;
}
It generates the following error. list backup of controlfile is showing up
the backup entries.
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03002: failure during compilation of command
RMAN-03013: command type: restore
RMAN-03007: retryable error occurred during execution of command: IRESTORE
RMAN-07004: unhandled exception during command execution on channel ch1
RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve
sequential file, handle=nre1u1kk_1_1, parms=
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: sbtrestore: Backup file not found.
RMAN-10031: ORA-19624 occurred during call to
DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
Could someone help me in resolving the issue.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: ltiu
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Must Read for Every Developer and DBA
Vikas I had a developer recently report that his program was actually a little faster by not using bind variables. Once I stopped screaming I calmly explained the following. The issue isn't whether bind variables are faster or not, but rather what SQL statements that don't use bind variables do to the shared pool. I will simplify some of the details following. When Oracle receives a SQL statement, it first scans the SQL buffer to see if it has encountered this statement before. If it finds the SQL statement in the buffer, then it proceeds to execute it. If it doesn't find the SQL statement, then it must parse it and find a place in the buffer to keep it in hopes it will encounter it again. The real problem with SQL statements that don't use bind variables is that the SQL buffer becomes filled with statements that will never be used again. So Oracle has to expend a lot of effort searching, parsing, aging out the oldest statements, etc. All for nothing because you aren't using bind variables and those statements will never be used again. How you use bind variables varies a lot depending on which language you are using. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, September 06, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing
Re: ctrl c on PCs
try ctrl-y on VMS, it interrupts the session. [EMAIL PROTECTED] 09/06/02 09:58AM In sql*plus, if do a ctrl-c from within sqlplus I am returned to the dos prompt. However, from OpenVMS, I am kept in sql*plus. We have some scripts that do a pause in them so can terminate if a problem. However, if hit ctrl-c during the pause sql*plus ends commiting the updates. Shouldn't sql*plus just return to a prompt or is this normal on windows. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Dev tools for web-based apps
Title: Dev tools for web-based apps What development tools (Oracle Forms, Java/JDeveloper) are your shops using to deploy new applications to the web? Or what Oracle tool would you recommend to Developers? My client is wanting to re-design an application (currently written in C) using Oracle Forms (partly due to a shorter learning curve with Forms.) The application will contain a great deal of complex business rules and consistency checks. I might add that product in production date is late 2005. I have concerns about Forms' performance issues in running a huge applet and mainly Forms' life expectancy. It appears to me that Oracle's focus and future is with Java and JDeveloper. Am I off base here? Thanks in advance! Val Valerie H. Webber Management Systems Designers, Inc Database Administrator [EMAIL PROTECTED] 704-566-5321
RE: Inserts are taking time !
Hi Marul, A few things to check before you alter the application. 1. Have you checked the growth of extents in dba_extents for the concerned table and indexes as you insert rows in the table? I would suggest to create the table and indexes with large initial and next extent sizes. 2. You can also use nologging option when you create the table and indexes. You can experiment with append and/or parallel hint in your insert. 3. I would also monitor the rollback segment that your transaction is using, if the table is being read at the same time if delete/inserts are going on, then it is likely that your RBS may be extending too much. You might want to revisit the sizing of the extents for RBS. Cheers Viral From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Inserts are taking time ! Date: Fri, 06 Sep 2002 04:28:19 -0800 Removing the auto-commit sounds good, on condition, of course, that you can afford to lose up to 999 recs in the event of a system crash. You probably may want to write them to a temp storage table first Chris -Original Message- Sent: 06 September 2002 09:28 To: Multiple recipients of list ORACLE-L Thanks Justin, Now its high time and I have to take some harsh steps to resolve this bottleneck. This might even go to an extent of changing the app code. But I have to do it, there is no way out. Thank you all for the support. I will get back to you with my reading and implementation in a day or two (even if this is successful or not). bfn Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 12:28 PM At 09:08 AM 9/5/2002, you wrote: Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. Yes, yes, a thousand times yes. I'm willing to wager that 90% of your time is spent waiting for the commits in this scenario. But if this auto-commit is the issue than why first 10K records are inserted quickly. Well, it's only slower by a factor of 6 when you go from 0 rows to 350,000 rows, which seems reasonable. When you have 0 rows in the database, you probably have the table, indexes, etc. completely cached in memory. When the table grows larger, however, more and more stuff will be coming from the disk. Additionally, the commits are likely to be a bit more complicated the more rows you have, because the number of blocks that need to be touched in the indexes will go up. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Can you do batch inserts, so that you're not committing 10,000 times? Modify the application to batch 1000 statements at a time, and I'll be you get much happier very quickly. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the
RE: connect to the databases without using tnsnames.ora?
I think the best way is to go to SQLNET.ORA and look for the ordering of the NAME.DIRECTORY_PATH. Remove one by one and you know what is being used. Mostly i think it will be Name Server. A query: Is HOST NAMING used widely? Naveen -Original Message- Sent: Friday, September 06, 2002 7:58 PM To: Multiple recipients of list ORACLE-L Cc I believe the Java thin client can connect without using the tnsnames.ora file. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 8:23 PM To: Multiple recipients of list ORACLE-L Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ 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: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
materialized view of a remote partition
8.1.7.4 create snapshot TEMP as select * from [EMAIL PROTECTED] partition (020904) ERROR at line 1: ORA-14100: partition extended table name cannot refer to a remote object why not? === 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: APPS password security
When we found that some of our developers were printing the value of $1 in custom programs, we gave strict instructions not to do so. We check every custom program before it goes in (it should be checked before it goes in to the TEST environment) to see that it is not printing $1. The answer to your question : Manually checking the code ! Hemant At 05:48 AM 06-09-02 -0800, you wrote: Is there a way to prevent some of my enterprising developers from echoing the apps password to a log file from within a concurrent host program? For example: echo INPUT: $1 tells them way too much information. APPS 11.0.3 (soon to be 11.5.7 if all goes well). _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Martin Brown INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Now using Eudora Email. Try it ! My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
bind variables
Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network
RE: Restoring RMAN backups to different host ......solved
Dear List,
Thanks for all those who replied.
One of my collegue helped me in resolving the issue yesterday. Iam using
Veritas Netbackup software. If I set the UNIX env variable
NB_ORA_CLIENT=host_A and also in RMAN script parms, then it is restoring the
backups from host_A.
run {
allocate channel ch1 type 'sbt_tape';
restore controlfile parms=ENV=(NB_ORA_CLIENT=host_A; export
NB_ORA_CLIENT);
}
I also set CLIENT_NAME=host_A in /usr/openv/netbackup/bp.conf file. As per
Netbackup manual, this should work, but not sure why it is not working
unless I set NB_ORA_CLIENT.
Thanks,
-- Babu
-Original Message-
Sent: Thursday, September 05, 2002 7:18 PM
To: Multiple recipients of list ORACLE-L
Janardhana Babu Donga,
hi, which backup software are u using? as far as i know, you must
setup something in the second node so that MML software can THINK it is the
node that is backed up, so it can restore back.
Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
=== 2002-09-05 16:13:00 ,you wrote£º===
Dear List,
Iam following the procedure to restore database from RMAN backup from
HOST_A
to HOST_B with a recovery catalog.
1. I copied the init.ora file to HOST_B
2. on HOST_B, I made an entry in oratab for 'rtest' database and switch to
'rtest' database using . oraenv
3. issued: rman target / catalog rman/rman@Connect String
4. startup nomount;
5. run {
allocate channel ch1 type 'sbt_tape';
restore controlfile;
}
It generates the following error. list backup of controlfile is showing
up
the backup entries.
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03002: failure during compilation of command
RMAN-03013: command type: restore
RMAN-03007: retryable error occurred during execution of command: IRESTORE
RMAN-07004: unhandled exception during command execution on channel ch1
RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve
sequential file, handle=nre1u1kk_1_1, parms=
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: sbtrestore: Backup file not found.
RMAN-10031: ORA-19624 occurred during call to
DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
Could someone help me in resolving the issue.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
= = = = = = = = = = = = = = = = = = = =
zhu chao
[EMAIL PROTECTED]
2002-09-06
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: zhu chao
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: ctrl c on PCs
Jeffrey In the first case you are executing SQL*Plus directly on your PC. In the OpenVMS case, I assume that you are using some terminal emulation program to reach OpenVMS and starting SQL*Plus on that system. You may want to verify whether your terminal emulation program is sending the control-C. I regularly use a terminal emulation program that doesn't send the control-C along, and this is irritating. To kill something I just start another window and kill the other session from there. There may be a setting somewhere you can change if this is the situation. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, September 06, 2002 8:58 AM To: Multiple recipients of list ORACLE-L In sql*plus, if do a ctrl-c from within sqlplus I am returned to the dos prompt. However, from OpenVMS, I am kept in sql*plus. We have some scripts that do a pause in them so can terminate if a problem. However, if hit ctrl-c during the pause sql*plus ends commiting the updates. Shouldn't sql*plus just return to a prompt or is this normal on windows. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Function-Based Index not working
you know, I was thinking about that. I can't decide between hash partitioning or list partitioning though :) --- Mladen Gogala [EMAIL PROTECTED] wrote: On 2002.09.05 22:18 Rachel Carmichael wrote: I love automagic things :) so I can leave the table alone right now there are all of 7 rows in it Rachel Given the size of the the table, may be you should try partitioning it? -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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).
Oracle DBAs Need Jobs
Our company just gave us notice that they are moving our jobs off shore. My group is made up of 5 very experienced DBAs. Any help finding new jobs would be greatly appreciated. I have included my resume'. Thanks guys and gals. Linda Miller-Coker Home: (281) 252-0023 CAREER OBJECTIVE To be associated with a progressive, dynamic corporation that offers long term growth and career advancement using my technical skills and experience. EXPERIENCE March 1999 JPMorganChase to Present Senior Data Architect Responsible for data modeling, analyzing, designing, and implementing database applications and data warehouses using Oracle's database and tools including Discover. Implemented large data warehouses taking advantage of partitioning, material views and other 8I/9I features. Responsible for the installing, monitoring and supporting Oracle products on NT and UNIX. Design and implemented databases using Internet technologies Websphere/JSP, Vignette and Story Server. Configured and Maintain Websphere server running on NT. Migrated databases from Access, SQLServer, SyBase and 7.3 databases to Oracle 8I and 9I. Dec. 1990 Texaco Group, Inc. to March 1999 Senior Database Administrator Responsible for the installation and support of Oracle products on NT and UNIX. Responsible for the selection and implementation of Platinum's data manipulation and monitoring products. Application Development Developed a Geoscience application using Designer 2000 and Oracle's Developer 2000. Data Analyst Developed data strategies for Texaco departments, Caltex Pacfic Indonesia and Star Enterprise. Designed a SAP data warehouse. Instructor for Information Analysis I and II. Texaco's internal data modeling and database design courses. Beta Test Oracle Products such as Designer 2000, Oracle's Web Server, Developer 2000 and Web Forms. Chairperson for the Texaco/Star Enterprise Oracle User Group. Jan. 1990 toDeloitte Touche Dec. 1990 Database Consultant/Marketing Support Responsible for Oracle business development as well as functioning as a database consultant. Developed business plan to introduce company to prospective Oracle clients. Created advertisements for regional publications. Also responsible for preparing software proposals and making technical presentations. Dec. 1987 toAnadarko Petroleum Jan. 1990 Application Analyst Responsible for data modeling, analyzing, designing, and implementing database applications using Oracle's database and tools. Aug. 1983 toUnisys Corporation Dec. 1987 System Specialist/Consultant Functioned as a business solution consultant to customer and sales personnel by answering their varied questions about software products and programs, counseling them technically on product installations, modifications to software proposals and making technical presentations to customers. Dec. 1981 toSouthwest Electric Company Aug. 1983 Programmer Analyst EDUCATION May 1988University of Houston Masters of Business Administration Dec. 1981 University of New Mexico Bachelor of Business Administration Concentration: Business Computer Systems Minor in Accounting RECENT TECHNICAL TRAINING Oracle's Enterprise Manager Oracle9I New Functions and Features Vignette Content Management Server Using JSP Oracle's Develop Applications with Java -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle on Win platforms
The best way to know is to actually do it. ltiu Richard Huntley wrote: A co-worker, neither a dba nor a developer, was able to successfully install 8.1.7 Personal Edition on 98 with no problems at all. I wonder if the same would work on ME, since 98SE and ME are like twin brothers from what I can tell. -Original Message- From: Robson, Peter [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 04, 2002 7:44 AM To: Multiple recipients of list ORACLE-L Subject: Oracle on Win platforms Just a quickie, please, folks - I have used Win NT4 for years, but have now got Win98SE on a second PC. (Don't mention XP...) Which versions of Oracle will load to 98? Single user only, stand-alone machine (no networking). On attempting to load 7.3.4 an 'unsuported' message pops up, which wasn't exactly confidence inspiring... thanks, peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Function-Based Index not working
Probably composite partitioning! -Original Message- Sent: Friday, September 06, 2002 1:04 PM To: Multiple recipients of list ORACLE-L you know, I was thinking about that. I can't decide between hash partitioning or list partitioning though :) --- Mladen Gogala [EMAIL PROTECTED] wrote: On 2002.09.05 22:18 Rachel Carmichael wrote: I love automagic things :) so I can leave the table alone right now there are all of 7 rows in it Rachel Given the size of the the table, may be you should try partitioning it? -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ANSI Isolation Levels
Hi Ian, The purpose of the command set transaction read only is to implement the repeatable read isolation level. Did you misspeak? As I understand it, the purpose of the set transaction command is to set the isolation level of which there are 4 specified in the ANSI spec and Oracle does not support the repeatable read isolation level as of 8i. (As I understand it there are ambiguities in the ANSI spec and efforts are underway to provide clarification with the anticipated result that there will be even more granularity in isolations levels.) I thought MySQL, at least the earlier versions, had no concept of a transaction You're right about the earlier versions but with current versions MySQL now has a table type of InnoDB (as well as table types of ISAM and Berkeley DB) and this supports transactions, referential integrity and row level locking. Without this option MySQL transaction support is limited to full table locks with no concept of rollbacks. With the InnoDB option MySQL is now ACID compliant. Supposedly the Berkeley DB option supports transactions but it's not a workable solution. If I block a query from even accessing an object which has gained, changed, or lost data until that data is committed, have I implemented the read committed isolation level. Hmmm... by blocking access to an object altogether there is no reading at all including read committed. Right? Steve Orr -Original Message- Sent: Thursday, September 05, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Importance: High The purpose of the command set transaction read only is to implement the repeatable read isolation level. I just checked the 9i documentation ... Oracle provides these transaction isolation levels: Read committed This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data. Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that executes a given query twice can experience both nonrepeatable read and phantoms. Serializable Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms. Read-only Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements. Set the Isolation Level Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction: SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL READ ONLY; --- I thought MySQL, at least the earlier versions, had no concept of a transaction and queries read uncommitted data from other sessions. Does one have a MySQL administrator? Is it true for Sybase, SQLServer, DB2 that writers never block readers and vice versa? For the purists this does not include latching of buffers. Do all these products have their own versions of undo segments? If I block a query from even accessing an object which has gained, changed, or lost data until that data is committed, have I implemented the read committed isolation level. Ian MacGregor Stnford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 12:12 PM To: Multiple recipients of list ORACLE-L Intro: There are 4 defined ANSI isolation levels: 1) read uncommitted; 2) read committed; 3) repeatable read; 4) serializable. By default Oracle implements the read committed (2) isolation level. Oracle can implement the serializable isolation level but not the repeatable read isolation level. Questions: I'm looking for a summary document of how the various database engines implement ANSI SQL transaction management. For performance reasons, is the read committed isolation level the most commonly implemented default by the various database vendors? (From what I gather it is also the default for Sybase, SQLServer and PostgreSQL.) Is the read committed isolation level the most practical? Has anyone ever administered a database or application with a different isolation level and why? Is there any summary document of transaction features for all the database vendors? Theoretically and
RE: connect to the databases without using tnsnames.ora?
What do you mean by mistakenly require a TNSNAMES.ORA file ? The TNSNAMES.ORA file is required unless you are using either of a) Oracle Names b) Thin Java client Hemant At 06:38 AM 06-09-02 -0800, you wrote: As an FYI, some 3rd-party products still mistakenly require a TNSNAMES.ORA file. Quest's QCO (at least 2.0, haven't D/L'd 2.1 yet and Quest Support tells me it will be fixed in 3.0 next Q1) is one of them. Also, there is at least one circumstance that requires a TNSNAMES.ORA on a server. I can't remember for the life of me what it is, but something with the Intelligent Agent sticks in my head. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, September 05, 2002 10:28 PM To: Multiple recipients of list ORACLE-L -Original Message- From: CC Harvest [mailto:[EMAIL PROTECTED]] Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? To add to what other posters have said: If the DBA was indeed using Oracle Names, you would see the configuration parameters for the names server in the sqlnet.ora file, which would be in the same directory where you would expect to find the tnsnames.ora file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Now using Eudora Email. Try it ! My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle User identified EXTERNALLY
Hi All, Here is the situation . I have a Unix user called oracle on a Sun Solaris box. I created the user called ops$oracle by using the following syntax. CREATE USER OPS$ORACLE PROFILE DEFAULT IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK; GRANT CONNECT TO OPS$ORACLE; Now I login to Unix box as oracle(Unix user), set up the Oracle environment(ORACLE_SID, ORACLE_HOME etc). Then try to login to sqlplus but It prompts for password or it says invalid username/password. I am under impression that I should be able to login to Oracle without specifying the Oracle password as described below. But I am not able to do so as shown below. Could you please tell me what am I missing and how can I log into oracle through the Unix user without specifying the password as it should be identified externally. Thanks, Ashoke Unix sqlplus / SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 6 10:40:59 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: I also tried Unix sqlplus ops\$oracle SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 6 10:44:27 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter password: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: connection output on clone instance
The Production has nothing to do with your database/instance name. It is the Production Release of the Oracle RDBMS. Your database/instance name could be anything. You cannot change the label provided by Oracle when you connect to the database [unless you try hacking the source code and recompiling it !]. Hemant At 11:29 AM 05-09-02 -0800, you wrote: can anyone guide me in changing the connect output: Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production With the Partitioning option JServer Release 8.1.7.2.0 - Production Where Production will be replaced with TEST Hemant K Chitale Now using Eudora Email. Try it ! My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ctrl c on PCs
Be careful about setting AutoCommit. What if you really want to abort a transaction in certain cases ? Hemant At 06:43 AM 06-09-02 -0800, you wrote: Set autocommit to on and try. that will commit before exiting sqllplus Shiva -Original Message- From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]] Sent: Friday, September 06, 2002 8:58 AM To: Multiple recipients of list ORACLE-L Subject: ctrl c on PCs In sql*plus, if do a ctrl-c from within sqlplus I am returned to the dos prompt. However, from OpenVMS, I am kept in sql*plus. We have some scripts that do a pause in them so can terminate if a problem. However, if hit ctrl-c during the pause sql*plus ends commiting the updates. Shouldn't sql*plus just return to a prompt or is this normal on windows. Jeffrey Beckstrom Database Administrator Greater Cleveland Regional Transit Authority 1240 W. 6th Street Cleveland, Ohio 44113 (216) 781-4204 Hemant K Chitale Now using Eudora Email. Try it ! My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: connect to the databases without using tnsnames.ora?
JDBC Oracle thin driver DOES NOT require any config file. The connection url needs to be of the form jdbc:oracle:thin:@host:port:SID Naveen -Original Message- Sent: Friday, September 06, 2002 9:33 PM To: Multiple recipients of list ORACLE-L Are you talking about the JDBC Oracle Thin Driver? This is because you configure it separately. It still has a tnsnames.ora type config somewhere hidden in it's own config file. Just that it does not use the regular tnsnames.ora. ltiu DENNIS WILLIAMS wrote: Cc I believe the Java thin client can connect without using the tnsnames.ora file. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 8:23 PM To: Multiple recipients of list ORACLE-L Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ 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: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Inserts are taking time !
This appears to be a classic example of a design that cripples performance and once in production 'nothing' can be done to solve the problem. By nothing, I mean that possible options are not 'possible' due to external (business, application) reasons. Here are some areas to examine: 1. Are all the indexes actually in use? Are there some that are redundant (identical leading columns)? Are there 'performance' indexes that are rarely used? The fewer the indexes, the fewer i/os for each insert, the faster it will be. 2. Is there an off-time where the indexes can be rebuilt for better performance? Perhaps even nightly? 3. Can you use any sort of partitioning? Can the application use partition elimination? 4. How immediate is the need to see the data? If there can be a delay, consider inserting into a transient table and then performing a bulk load during off hours. 5. Are you using the background processes efficiently? With a single dbwr, you may be getting bogged down in writing all these blocks. If logwr is slow, check for i/o contention. 6. How much space management is occurring? Are you constantly throwing extents for the table/indexes? Here are some areas that can be given low priority: (knowing full well this is an invitation to heated disagreements with other listers) 1. RBS - Adding rbs space will not help the situation, unless you are seeing rbs related errors. The real problem is the time that may be required to create a read-consistent view of the data. 2. Commit time - The time for a 'commit' to occur is more a function of the number of changes (i.e. table data + each index entry) than the amount of data currently in the table/index. Good luck. I hope this provides some areas to examine. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Must Read for Every Developer and DBA
I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. What will happen if i don't use bind variables and use CURSOR_SHARING = FORCE Will the use of histograms be enabled or disabled in such a case? Any disadvantages of using cursor_sharing=force? Naveen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Must Read for Every Developer and DBA
Another issue is the ability of the CBO to use bind variables. Until recently (9i, I think), the CBO would use a single execution plan for a statement with bind variables, even if the data values of the bind variables actually indicated that the plan was not optimal. For example, a query that retrieves employee addresses by city (bind variable) would use the same execution plan for the city of New York, NY as for the city of Twin Lakes, Colorado. In the first case, a fts may be in order, where an index lookup would be more appropriate for the second. The execution plan is determined by which statement was executed first. Dan -Original Message- Sent: Friday, September 06, 2002 9:18 AM To: Multiple recipients of list ORACLE-L Vikas I had a developer recently report that his program was actually a little faster by not using bind variables. Once I stopped screaming I calmly explained the following. The issue isn't whether bind variables are faster or not, but rather what SQL statements that don't use bind variables do to the shared pool. I will simplify some of the details following. When Oracle receives a SQL statement, it first scans the SQL buffer to see if it has encountered this statement before. If it finds the SQL statement in the buffer, then it proceeds to execute it. If it doesn't find the SQL statement, then it must parse it and find a place in the buffer to keep it in hopes it will encounter it again. The real problem with SQL statements that don't use bind variables is that the SQL buffer becomes filled with statements that will never be used again. So Oracle has to expend a lot of effort searching, parsing, aging out the oldest statements, etc. All for nothing because you aren't using bind variables and those statements will never be used again. How you use bind variables varies a lot depending on which language you are using. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, September 06, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10
RE: materialized view of a remote partition
Hi Ray, Please let me know if you received any answer. Thanks, Ashoke -Original Message- Sent: Friday, September 06, 2002 10:23 AM To: Multiple recipients of list ORACLE-L 8.1.7.4 create snapshot TEMP as select * from [EMAIL PROTECTED] partition (020904) ERROR at line 1: ORA-14100: partition extended table name cannot refer to a remote object why not? === 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle User identified EXTERNALLY
Did you set the init.ora parameter for external authentication? Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Friday, September 06, 2002 12:54 PM To: Multiple recipients of list ORACLE-L Subject:Oracle User identified EXTERNALLY Hi All, Here is the situation . I have a Unix user called oracle on a Sun Solaris box. I created the user called ops$oracle by using the following syntax. CREATE USER OPS$ORACLE PROFILE DEFAULT IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK; GRANT CONNECT TO OPS$ORACLE; Now I login to Unix box as oracle(Unix user), set up the Oracle environment(ORACLE_SID, ORACLE_HOME etc). Then try to login to sqlplus but It prompts for password or it says invalid username/password. I am under impression that I should be able to login to Oracle without specifying the Oracle password as described below. But I am not able to do so as shown below. Could you please tell me what am I missing and how can I log into oracle through the Unix user without specifying the password as it should be identified externally. Thanks, Ashoke Unix sqlplus / SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 6 10:40:59 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: I also tried Unix sqlplus ops\$oracle SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 6 10:44:27 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter password: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 DBAs Need Jobs
Hello, This is very unfortunate. If you don't mind, where are your jobs moving to? Which country? Have you guys considered doing consulting/education/training work since you guys have quite a bit of experience? ltiu [EMAIL PROTECTED] wrote: Our company just gave us notice that they are moving our jobs off shore. My group is made up of 5 very experienced DBAs. Any help finding new jobs would be greatly appreciated. I have included my resume'. Thanks guys and gals. Linda Miller-Coker Home: (281) 252-0023 CAREER OBJECTIVE To be associated with a progressive, dynamic corporation that offers long term growth and career advancement using my technical skills and experience. EXPERIENCE March 1999 JPMorganChase to Present Senior Data Architect Responsible for data modeling, analyzing, designing, and implementing database applications and data warehouses using Oracle's database and tools including Discover. Implemented large data warehouses taking advantage of partitioning, material views and other 8I/9I features. Responsible for the installing, monitoring and supporting Oracle products on NT and UNIX. Design and implemented databases using Internet technologies Websphere/JSP, Vignette and Story Server. Configured and Maintain Websphere server running on NT. Migrated databases from Access, SQLServer, SyBase and 7.3 databases to Oracle 8I and 9I. Dec. 1990 Texaco Group, Inc. to March 1999 Senior Database Administrator Responsible for the installation and support of Oracle products on NT and UNIX. Responsible for the selection and implementation of Platinum's data manipulation and monitoring products. Application Development Developed a Geoscience application using Designer 2000 and Oracle's Developer 2000. Data Analyst Developed data strategies for Texaco departments, Caltex Pacfic Indonesia and Star Enterprise. Designed a SAP data warehouse. Instructor for Information Analysis I and II. Texaco's internal data modeling and database design courses. Beta Test Oracle Products such as Designer 2000, Oracle's Web Server, Developer 2000 and Web Forms. Chairperson for the Texaco/Star Enterprise Oracle User Group. Jan. 1990 toDeloitte Touche Dec. 1990 Database Consultant/Marketing Support Responsible for Oracle business development as well as functioning as a database consultant. Developed business plan to introduce company to prospective Oracle clients. Created advertisements for regional publications. Also responsible for preparing software proposals and making technical presentations. Dec. 1987 toAnadarko Petroleum Jan. 1990 Application Analyst Responsible for data modeling, analyzing, designing, and implementing database applications using Oracle's database and tools. Aug. 1983 toUnisys Corporation Dec. 1987 System Specialist/Consultant Functioned as a business solution consultant to customer and sales personnel by answering their varied questions about software products and programs, counseling them technically on product installations, modifications to software proposals and making technical presentations to customers. Dec. 1981 toSouthwest Electric Company Aug. 1983 Programmer Analyst EDUCATION May 1988University of Houston Masters of Business Administration Dec. 1981 University of New Mexico Bachelor of Business Administration Concentration: Business Computer Systems Minor in Accounting RECENT TECHNICAL TRAINING Oracle's Enterprise Manager Oracle9I New Functions and Features Vignette Content Management Server Using JSP Oracle's Develop Applications with Java -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
Re: Dev tools for web-based apps
Hello, Oracle's Developer Suite is available for free to developers as well as for educational use. It's good enough. You may want to look at Forte for java from Sun for your Java needs. It integrates well with Oracle using JDBC Thin Driver. ltiu Webber Valerie H wrote: What development tools (Oracle Forms, Java/JDeveloper) are your shops using to deploy new applications to the web? Or what Oracle tool would you recommend to Developers? My client is wanting to re-design an application (currently written in C) using Oracle Forms (partly due to a shorter learning curve with Forms.) The application will contain a great deal of complex business rules and consistency checks. I might add that product in production date is late 2005. I have concerns about Forms' performance issues in running a huge applet and mainly Forms' life expectancy. It appears to me that Oracle's focus and future is with Java and JDeveloper. Am I off base here? Thanks in advance! Val *Valerie H. Webber* Management Systems Designers, Inc Database Administrator [EMAIL PROTECTED] 704-566-5321 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: off topic: OCP exam registration
Stephen P. Karniotis Thanks for replyThat's why I am using OTN20 not being a part of OPP. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 06 Sep 2002 07:43:32 -0800 The OPP is open to Oracle Partners and their employees only. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, September 05, 2002 6:21 PM To: Multiple recipients of list ORACLE-L Subject:RE: off topic: OCP exam registration Sunil Who can use this code? Employees /partner of Oracle Partner program or anybody? Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 05 Sep 2002 13:38:43 -0800 Try promotion code OPP to get 30% off. Sunil Nookala Dell Computer corp. Austin, TX -Original Message- Sent: Thursday, September 05, 2002 3:05 PM To: Multiple recipients of list ORACLE-L Hi ALL! I just spoke with Prometric and they told me if I give them my OTN number they will give me discount for OCP exam. I'm the member of OTN , but I don't have any number. Someone know where I can get this number? Thanks. Greg. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Faktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include
Re:bind variables
John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from 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: BMC Obacktrack
With obacktrack, you can edit the script it creates to eliminate the uncessary steps. Some of those steps are not actually restoring the file, but verifying it is in the backup pool, etc. Are you doing a point in time recovery, or a single file recovery? I have not seen it attempt to recreate or restore datafiles that were not specified unless they were not there in the 1st place when the recovery process was started... (obacktrack checks to make sure all datafiles are there) --- Gurelei [EMAIL PROTECTED] wrote: Hi all, I'm testing BMC Backtrack v 3.30 on Dynix 4.5.2 and experiencing some strange behavior of the tool. I have deleted a datafile and use the tool to restore it from the backup. When I let the tool to do a restore, everything runs great and fast. When however I have the tool generate a script and run that script manually, it attempts to restore ALL the datafiles (even though it was generated to only restore one). Has anyone experienced this before and is there something I can do about it? thanks Gene __ 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: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Michael Barger INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: cache settings NT
I'll take a shot at this. Don't have any write caching. Oracle may think that it has safely committed the transactions and they are written to disk when they are in fact only in cache. If the machine were to shut down ungracefully, i.e., someone kicks out the plug, then your database would not be in a consistent state. Granted, the odds are small, but is it worth the risk? If the database is archived and you don't mind the increased chance of having to do a recovery then I would say 0% read - 100% write. Oracle is doing it's own read caching in the SGA and it's unlikely that the disk cache will correctly anticipate Oracle's read requirements. Therefore, caching on the read side is only likely to increase IO. Write caching does not lead to any increased IO but it does have some small risk of producing an inconsistent database. GKor @rdw.nl To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: cache settings NT 09/06/2002 04:18 AM Please respond to ORACLE-L Hi all I have the following question for you : what are the preferred values for the several cache settings on the fysical disk units (NT W2K) e.g. 100 % read cache - 0 % write cache 50% read cache - 50% write cache is there a difference between settings for an OLTP or DSS solution. etc anyone with an explanation vr. gr. g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: connect to the databases without using tnsnames.ora?
Title: RE: connect to the databases without using tnsnames.ora? No it does not. try the following from the command line (with no tnsnames.ora file) (substitute valid values for machine, port, sid, etc) sqlplus username@(description=(address=(protocol=tcp)(host=machine1)(port=1521))(connect_data=(sid=sid1))) it will work. A similar syntax can be used in defining database links, by passing all need for tnsnames.ora file Matt Adams - GE Appliances - [EMAIL PROTECTED] Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED]] Sent: Friday, September 06, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: connect to the databases without using tnsnames.ora? What do you mean by mistakenly require a TNSNAMES.ORA file ? The TNSNAMES.ORA file is required unless you are using either of a) Oracle Names b) Thin Java client Hemant At 06:38 AM 06-09-02 -0800, you wrote: As an FYI, some 3rd-party products still mistakenly require a TNSNAMES.ORA file. Quest's QCO (at least 2.0, haven't D/L'd 2.1 yet and Quest Support tells me it will be fixed in 3.0 next Q1) is one of them. Also, there is at least one circumstance that requires a TNSNAMES.ORA on a server. I can't remember for the life of me what it is, but something with the Intelligent Agent sticks in my head. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, September 05, 2002 10:28 PM To: Multiple recipients of list ORACLE-L -Original Message- From: CC Harvest [mailto:[EMAIL PROTECTED]] Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? To add to what other posters have said: If the DBA was indeed using Oracle Names, you would see the configuration parameters for the names server in the sqlnet.ora file, which would be in the same directory where you would expect to find the tnsnames.ora file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Now using Eudora Email. Try it ! My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Same SQL statement, Same Oracle, Different OS == Different Expla
Hello everybody, One of our developers is encountering a situation where Oracle 9.0.x explain plan chooses one index when on UNIX, and chooses a different index when running on Windows NT. I'd appreciate any insights or similar experiences. The following are the facts: 1. The explain plan is run against the same SELECT statement on both platforms 2. It has been confirmed that there are no statistics on either of the databases 3. Both databases are using RBO (not CBO) 4. The UNIX database has about 100 times as many rows (in this table) as the NT database 5. The SELECT statement that gives different explain plans on different platforms is: SELECT FN.*, FN.ROWID FROM UNITFUND FN WHERE FN.FU_CODE = :cFuCode AND FN.MKEY = :cMkey AND FN.CLNT = :sKey AND FN.PLANNO = :sKey AND FN.DATE_FROM = :dDate AND FN.SOURCE = :cSource AND FN.TSTATUS = 'O' ORDER BY FN.DATE_FROM, FN.TSECOND; 6. Between the following 2 indexes, Oracle 9.0x chooses (2) on Unix and (1) on Windows NT. 1) clnt, mkey, planno, fu_code, date_from, source, tracode, tsecond... 2) date_from, clnt, planno, mkey, fu_code Any insights from anybody out there? Thanks. Sam Bootsma, OCP Technical Support Analyst -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Bootsma INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: connect to the databases without using tnsnames.ora?
Actually, I am talking about my experince with Forte for Java. When establishing a connection to a database using a GUI. It will ask you for the username, password and SID as well as server name or IP address, and then it connects. I am completely oblivious as to where it keeps the parameters I have given it to I'm assuming it stores it somewhere. Not strictly a tnsnames.ora file but a config file somewhere that's specific to Forte. I also have the same experience with JDeveloper. What I meant is that JDBC itself does not use tnsnames.ora but something like it. Thanks for clarifying. ltiu Naveen Nahata wrote: JDBC Oracle thin driver DOES NOT require any config file. The connection url needs to be of the form jdbc:oracle:thin:@host:port:SID Naveen -Original Message- Sent: Friday, September 06, 2002 9:33 PM To: Multiple recipients of list ORACLE-L Are you talking about the JDBC Oracle Thin Driver? This is because you configure it separately. It still has a tnsnames.ora type config somewhere hidden in it's own config file. Just that it does not use the regular tnsnames.ora. ltiu DENNIS WILLIAMS wrote: Cc I believe the Java thin client can connect without using the tnsnames.ora file. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 8:23 PM To: Multiple recipients of list ORACLE-L Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ 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: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 DBAs Need Jobs
India. I have done both consulting and training in the past. ltiu [EMAIL PROTECTED]@fatcity.com on 09/06/2002 12:43:36 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hello, This is very unfortunate. If you don't mind, where are your jobs moving to? Which country? Have you guys considered doing consulting/education/training work since you guys have quite a bit of experience? ltiu [EMAIL PROTECTED] wrote: Our company just gave us notice that they are moving our jobs off shore. My group is made up of 5 very experienced DBAs. Any help finding new jobs would be greatly appreciated. I have included my resume'. Thanks guys and gals. Linda Miller-Coker Home: (281) 252-0023 CAREER OBJECTIVE To be associated with a progressive, dynamic corporation that offers long term growth and career advancement using my technical skills and experience. EXPERIENCE March 1999 JPMorganChase to Present Senior Data Architect Responsible for data modeling, analyzing, designing, and implementing database applications and data warehouses using Oracle's database and tools including Discover. Implemented large data warehouses taking advantage of partitioning, material views and other 8I/9I features. Responsible for the installing, monitoring and supporting Oracle products on NT and UNIX. Design and implemented databases using Internet technologies Websphere/JSP, Vignette and Story Server. Configured and Maintain Websphere server running on NT. Migrated databases from Access, SQLServer, SyBase and 7.3 databases to Oracle 8I and 9I. Dec. 1990 Texaco Group, Inc. to March 1999 Senior Database Administrator Responsible for the installation and support of Oracle products on NT and UNIX. Responsible for the selection and implementation of Platinum's data manipulation and monitoring products. Application Development Developed a Geoscience application using Designer 2000 and Oracle's Developer 2000. Data Analyst Developed data strategies for Texaco departments, Caltex Pacfic Indonesia and Star Enterprise. Designed a SAP data warehouse. Instructor for Information Analysis I and II. Texaco's internal data modeling and database design courses. Beta Test Oracle Products such as Designer 2000, Oracle's Web Server, Developer 2000 and Web Forms. Chairperson for the Texaco/Star Enterprise Oracle User Group. Jan. 1990 toDeloitte Touche Dec. 1990 Database Consultant/Marketing Support Responsible for Oracle business development as well as functioning as a database consultant. Developed business plan to introduce company to prospective Oracle clients. Created advertisements for regional publications. Also responsible for preparing software proposals and making technical presentations. Dec. 1987 toAnadarko Petroleum Jan. 1990 Application Analyst Responsible for data modeling, analyzing, designing, and implementing database applications using Oracle's database and tools. Aug. 1983 toUnisys Corporation Dec. 1987 System Specialist/Consultant Functioned as a business solution consultant to customer and sales personnel by answering their varied questions about software products and programs, counseling them technically on product installations, modifications to software proposals and making technical presentations to customers. Dec. 1981 toSouthwest Electric Company Aug. 1983 Programmer Analyst EDUCATION May 1988University of Houston Masters of Business Administration Dec. 1981 University of New Mexico Bachelor of Business Administration Concentration: Business Computer Systems Minor in Accounting RECENT TECHNICAL TRAINING Oracle's Enterprise Manager Oracle9I New Functions and Features Vignette Content Management Server Using JSP Oracle's Develop Applications with Java -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
Re:bind variables
O'Reilly and PL/SQl Programming almost ALWAYS means the author is Steven Feuerstein --- [EMAIL PROTECTED] wrote: John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
Re: connect to the databases without using tnsnames.ora?
This is interesting!! I'll give it a try. Thanks. ltiu Adams, Matthew (GEA, MABG, 088130) wrote: No it does not. try the following from the command line (with no tnsnames.ora file) (substitute valid values for machine, port, sid, etc) sqlplus username@(description=(address=(protocol=tcp)(host=machine1)(port=1521))(connect_data=(sid=sid1))) it will work. A similar syntax can be used in defining database links, by passing all need for tnsnames.ora file Matt Adams - GE Appliances - [EMAIL PROTECTED] Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED]] Sent: Friday, September 06, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: connect to the databases without using tnsnames.ora? What do you mean by mistakenly require a TNSNAMES.ORA file ? The TNSNAMES.ORA file is required unless you are using either of a) Oracle Names b) Thin Java client Hemant At 06:38 AM 06-09-02 -0800, you wrote: As an FYI, some 3rd-party products still mistakenly require a TNSNAMES.ORA file. Quest's QCO (at least 2.0, haven't D/L'd 2.1 yet and Quest Support tells me it will be fixed in 3.0 next Q1) is one of them. Also, there is at least one circumstance that requires a TNSNAMES.ORA on a server. I can't remember for the life of me what it is, but something with the Intelligent Agent sticks in my head. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, September 05, 2002 10:28 PM To: Multiple recipients of list ORACLE-L -Original Message- From: CC Harvest [mailto:[EMAIL PROTECTED]] Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? To add to what other posters have said: If the DBA was indeed using Oracle Names, you would see the configuration parameters for the names server in the sqlnet.ora file, which would be in the same directory where you would expect to find the tnsnames.ora file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Now using Eudora Email. Try it ! My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: connect to the databases without using tnsnames.ora?
Neither sqlnet.ora or tnsnames.ora are required. You can connect to a database by specifying the full connect string. This will work from sqlplus: connect system/manager@(description=(address=(protocol=tcp)(host=remedydev)(port=1521))(connect_data=(sid=orcl))) It works, but is not terribly convenient. I don't imagine your dba was doing this though. Could it be that Oracle Names servers are being used? They don't require tnsnames.ora files. Jared CC Harvest [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 06:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:connect to the databases without using tnsnames.ora? Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ 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: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: foreign key indexes and parent-table locking
The theory will make much more sense after you see it in action. Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/06/2002 07:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: foreign key indexes and parent-table locking I agree that that's the best way to see what actually happens, and I will do that but I like to understand the theory, too . . . -bill -Original Message- Sent: Thursday, September 05, 2002 5:40 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Importance: High Bill, Rather than try to understand that explanation, you may find it more educational to create a pair of tables with a parent/child relationship via foreign key. Put some data in the tables, then do updates and deletes both with and without FK indexes. Examine dba_locks while doing so and observe the lock modes. This will be much easier to understand than the 'documentation' Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:foreign key indexes and parent-table locking Hi, I'm trying to understand the whole issue of foreign key indexes and locking. Found a note on metalink (11828.1) that seems to explain it, but either it's not clear or I'm missing something. Why then, does an index on the foreign key mean that the shared lock on the parent table is not required? When a row in the child table is inserted, deleted or has its foreign key updated, the corresponding index entry/entries is/are also locked. When an application attempts to delete or update the primary key of a parent row, it reads the FIRST corresponding entry in the child's foreign key index (uncommitted or otherwise) and, if locked, waits for that lock to be released. So far so good . . . this next piece, too, seems to make sense: If the modified child row is NOT the first occurrence of the foreign key in the index then the parent modification must be prevented anyway, regardless of the outcome of uncommitted transactions on other child rows with this key. But now here's the part that leaves me hanging . . . Hence the error can be flagged immediately and so the transaction is not forced to wait. This mechanism ensures the minimum reads and wait times to maintain data consistency. Can anyone help by either translating this last part or rephrasing it? Or explaining the issue differnetly? Thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Same SQL statement, Same Oracle, Different OS == Different E
Title: RE: Same SQL statement, Same Oracle, Different OS == Different Expla (see answer below) -Original Message- From: Sam Bootsma [mailto:[EMAIL PROTECTED]] One of our developers is encountering a situation where Oracle 9.0.x explain plan chooses one index when on UNIX, and chooses a different index when running on Windows NT. I'd appreciate any insights or similar experiences. The following are the facts: 1. The explain plan is run against the same SELECT statement on both platforms 2. It has been confirmed that there are no statistics on either of the databases 3. Both databases are using RBO (not CBO) 4. The UNIX database has about 100 times as many rows (in this table) as the NT database 5. The SELECT statement that gives different explain plans on different platforms is: SELECT FN.*, FN.ROWID FROM UNITFUND FN WHERE FN.FU_CODE = :cFuCode AND FN.MKEY = :cMkey AND FN.CLNT = :sKey AND FN.PLANNO = :sKey AND FN.DATE_FROM = :dDate AND FN.SOURCE = :cSource AND FN.TSTATUS = 'O' ORDER BY FN.DATE_FROM, FN.TSECOND; 6. Between the following 2 indexes, Oracle 9.0x chooses (2) on Unix and (1) on Windows NT. 1) clnt, mkey, planno, fu_code, date_from, source, tracode, tsecond... 2) date_from, clnt, planno, mkey, fu_code Just a wild guess, but maybe the optimizer is just picking the first index it finds because it thinks both are equally good candidates. Were both indexes created in the same order on both databases? I.e. is Object_id (from dba_objects) for Index A smaller than object_id for Index B on the UNIX database, but the reverse is true on Windows? You say the databases are using RBO. How do you know? Remember that if you use some new features (from the manual: Partitioned tables and indexes Index-organized tables Reverse key indexes Function-based indexes SAMPLE clauses in a SELECT statement Parallel query and parallel DML Star transformations and star joins Extensible optimizer Query rewrite with materialized views Enterprise Manager progress meter Hash joins Bitmap indexes and bitmap join indexes Index skip scans ) the query optimizer will use CBO because new features are not supported by RBO. Finally, this section of the manual may help you guess what's happening: Oracle9i Database Performance Guide and Reference Part Number A87503-02 Chapter 8 Using the Rule-Based Optimizer ... Understanding Access Paths for the RBO
Re: connect to the databases without using tnsnames.ora?
I use host naming extensively here. We do use tnsnames.ora for our apps so that we can take advantage of failover, but for my everyday work, I connect to the databases with host naming. You don't need a tnsnames.ora to do that OR a complex connect string (which is basically a tnsnames.ora entry rolled up into one line). When I connect to one of my servers, it is generally like this: sqlplus scott/tiger@hostname This is possible because the listener for each database is listening on the default port (1521) and the name is resolved through the usual suspects for TCP/IP name resolution, in our case, DNS. The listener then receives the request and connects it to the database with the matching global_dbname. Very simple. You just can't use connection pooling, heterogeneous services or application failover with this method. As for 3rd party products mistakenly requiring a tnsnames.ora file, that is a valid complaint. And it isn't even limited to 3rd party tools. Some of Oracle's own tools only give you the option of connecting to databases that are listed in your tnsnames.ora file, which when using the host naming method is not required! -- Philip Douglass Internet Networking Group Database Administrator SIRS Publishing, Inc. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 12:08 PM I think the best way is to go to SQLNET.ORA and look for the ordering of the NAME.DIRECTORY_PATH. Remove one by one and you know what is being used. Mostly i think it will be Name Server. A query: Is HOST NAMING used widely? Naveen -Original Message- Sent: Friday, September 06, 2002 7:58 PM To: Multiple recipients of list ORACLE-L Cc I believe the Java thin client can connect without using the tnsnames.ora file. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 05, 2002 8:23 PM To: Multiple recipients of list ORACLE-L Does anyone here knows how to setup the connections to the database server without using tnsnames.ora? Our DBA didn't use it. But I don't know how and why? Could anyone tell me the other options? Cc Harvest __ 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: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Philip Douglass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: bind variables
Actually its easy. Any variable declared in PL/SQL and referenced in a non-dynamic SQL statement is a bind variable. In the following example (#1), some_var is an output bind-variable and other_var is a input bind variable. PL/SQL does manipulation on the statement and will send something like the following (#2) to the database #1 DECLARE some_var NUMBER(1); other_var NUMBER(1) BEGIN SELECT 1 INTO some_var FROM my_table WHERE my_column = other_var; END; #2 SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 Kevin -Original Message- Sent: Friday, September 06, 2002 1:59 PM To: Multiple recipients of list ORACLE-L John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and
Re: bind variables
You probably already are. You have to go to a lot of trouble to avoid using bind variables in PL/SQL. (I'm too lazy^H^H^H^H busy to find the doc link right now.) -- Philip - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 11:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want
Re: Oracle User identified EXTERNALLY
Check for os_authent_prefix (I think) in init.ora. Should be set to OPS$. Some people set it to a null string so that they can avoid using the OPS$ prefix. But the advantage to using a prefix is that you can connect locally without a password and remotely with a password. -- Philip - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 12:53 PM Hi All, Here is the situation . I have a Unix user called oracle on a Sun Solaris box. I created the user called ops$oracle by using the following syntax. CREATE USER OPS$ORACLE PROFILE DEFAULT IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK; GRANT CONNECT TO OPS$ORACLE; Now I login to Unix box as oracle(Unix user), set up the Oracle environment(ORACLE_SID, ORACLE_HOME etc). Then try to login to sqlplus but It prompts for password or it says invalid username/password. I am under impression that I should be able to login to Oracle without specifying the Oracle password as described below. But I am not able to do so as shown below. Could you please tell me what am I missing and how can I log into oracle through the Unix user without specifying the password as it should be identified externally. Thanks, Ashoke Unix sqlplus / SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 6 10:40:59 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: I also tried Unix sqlplus ops\$oracle SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 6 10:44:27 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter password: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Philip Douglass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Inserts are taking time !
On Friday 06 September 2002 19:13, you wrote: 2. Commit time - The time for a 'commit' to occur is more a function of the number of changes (i.e. table data + each index entry) than the amount of data currently in the table/index. More data in the index means a greater chance on getting index block splits (especially with small block sizes and large keys), and block splits and adding levels will generate more redo and have an effect on the commit time. Anjo. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: couple of questions ???
Janet, The Show Errors command is only good if you were to reload the package from disk. Maybe if you did an alter package compile. Since you did neither, this command does not help you. Your other problem could be caused by the package being executed by someone while you were trying to compile it. IN this case, the package is locked until the execution is complete. Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 05, 2002 5:58 PM To: Multiple recipients of list ORACLE-L Hi all, I have couple of questions, 817 on NT. 1. There is an invalid SP, my collegue did a Show Errors in SQL Plus, it shows the error; but I did the same thing in my Sql Plus, I got No errors. Is there any settings I need to change? 2. There is a valid SP, when I did a Alter procedure p_Name compile. It just hangs. It was compiling fine, now I added two lines (dbms_output stuff), and it just compiling forever. The code now has 7969 lines. Does that hit the limit? (I really doubt) Or are there any other reasons? Thanks, Janet __ 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: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: BALA,PRAKASH (Non-HP-USA,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How to get rid off certain character from a string ???
Hi all, How to get rid off certain character from a string. For example, I have string 'WC_89_06_03', what's an easy way to get 'WC890603' out, is there a function? Thank you in advance! Janet __ 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: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: couple of questions ???
Janet, The last place where I worked, we had to split a big package into smaller ones since we hit a # of lines limit (undocumented!). Prakash -Original Message- Sent: Thursday, September 05, 2002 5:58 PM To: Multiple recipients of list ORACLE-L Hi all, I have couple of questions, 817 on NT. 1. There is an invalid SP, my collegue did a Show Errors in SQL Plus, it shows the error; but I did the same thing in my Sql Plus, I got No errors. Is there any settings I need to change? 2. There is a valid SP, when I did a Alter procedure p_Name compile. It just hangs. It was compiling fine, now I added two lines (dbms_output stuff), and it just compiling forever. The code now has 7969 lines. Does that hit the limit? (I really doubt) Or are there any other reasons? Thanks, Janet __ 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: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: BALA,PRAKASH (Non-HP-USA,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to get rid off certain character from a string ???
Title: RE: How to get rid off certain character from a string ???
select replace(my_string,my_unwanted_chars) from dual
/
should do the trick ...
select replace('WC_89_06_03','_') from dual
/
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-
From: Janet Linsy [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 06, 2002 4:04 PM
To: Multiple recipients of list ORACLE-L
Subject: How to get rid off certain character from a string ???
Hi all,
How to get rid off certain character from a string.
For example, I have string 'WC_89_06_03', what's an
easy way to get 'WC890603' out, is there a function?
Thank you in advance!
Janet
__
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: Janet Linsy
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
*This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*2
RE: ALTER TABLE MOVE command causes table to grow
No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_IDNOT NULL NUMBER ACCOUNT_IDNOT NULL NUMBER TEMPLATE_ID NOT NULL NUMBER GENERIC_DIFFERENTIATORNOT NULL NUMBER REQUEST_TYPE NOT NULL CHAR(1) ACCOUNT_NONOT NULL CHAR(8) EFFECTIVE_DATENOT NULL DATE EMAIL_ADDRESS NOT NULL VARCHAR2(100) EMAIL_SUBJECT NOT NULL VARCHAR2(100) EMAIL_BODYNOT NULL VARCHAR2(4000) STATUSNOT NULL CHAR(1) STATUS_CHANGE_DATENOT NULL DATE TWEED_SERVER_IDNUMBER TWEED_PACKAGE_PRIORITY NUMBER TWEED_SENDER_ACCOUNT NOT NULL VARCHAR2(50) TWEED_SCHEDULED_DELIVERY_DATE NOT NULL DATE TWEED_CUSTOMER_URLNOT NULL VARCHAR2(255) SENDER_SERVER_ID NUMBER SENDER_INSTANCE_ID NUMBER CREATE_DATE NOT NULL DATE CREATE_USER NOT NULL VARCHAR2(35) UPDATE_DATEDATE UPDATE_USERVARCHAR2(35) -Original Message- Sent: Thursday, September 05, 2002 5:23 PM To: Multiple recipients of list ORACLE-L What the table definition? Are there any LOB's on it? -Original Message- Sent: Thursday, September 05, 2002 2:43 PM To: Multiple recipients of list ORACLE-L pct increase is 0 (uniform sizing) -Original Message- Sent: Thursday, September 05, 2002 2:00 PM To: Multiple recipients of list ORACLE-L you didn't mention the PCT_INCREASE of this segment. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, September 05, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Had an annoying surprise last week. A table had grown unexpectedly large and I scheduled a time over the weekend to move it to its own tablespace from my medium tablespace. The table ended up growing 50%. I had anticipated it might grow somewhat given the PCTFREE of 10% but freeing up that space in the blocks should, at most, have caused it to grow by 10% (assuming that 10% was completely full). Does anyone have ideas as to why it would have grown by so much? Indexes are in a different tablespace and the only other change was from an extent size of 4 meg to one of 25 meg. Both are dictionary managed tablespaces. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
Re: How to get rid off certain character from a string ???
Hello,
I believe you want to get rid of the _.
Here is a simple method . The third argument in the function is null string.
select replace('WC_89_06_03', '_', '') from dual;
Cheers
Viral.
From: Janet Linsy [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: How to get rid off certain character from a string ???
Date: Fri, 06 Sep 2002 12:03:34 -0800
Hi all,
How to get rid off certain character from a string.
For example, I have string 'WC_89_06_03', what's an
easy way to get 'WC890603' out, is there a function?
Thank you in advance!
Janet
__
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: Janet Linsy
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_
Send and receive Hotmail on your mobile device: http://mobile.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viral Desai
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: couple of questions ???
Janet, 1. Did you try to compile the SP in your session before doing the show errors? It will only show errors to you if you did the compile prior to doing show errors. 2. One of the resons this could occur if there are stored objects like other packages, functions and procedure being referred to in your procedure and if they are invalid. If there are a number of invalid objects then this problem may occur..One way to verify this is to remove the dbms lines and try recompiling the package and see if it compiles.. Viral From: BALA,PRAKASH (Non-HP-USA,ex1) [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: couple of questions ??? Date: Fri, 06 Sep 2002 11:58:29 -0800 Janet, The last place where I worked, we had to split a big package into smaller ones since we hit a # of lines limit (undocumented!). Prakash -Original Message- Sent: Thursday, September 05, 2002 5:58 PM To: Multiple recipients of list ORACLE-L Hi all, I have couple of questions, 817 on NT. 1. There is an invalid SP, my collegue did a Show Errors in SQL Plus, it shows the error; but I did the same thing in my Sql Plus, I got No errors. Is there any settings I need to change? 2. There is a valid SP, when I did a Alter procedure p_Name compile. It just hangs. It was compiling fine, now I added two lines (dbms_output stuff), and it just compiling forever. The code now has 7969 lines. Does that hit the limit? (I really doubt) Or are there any other reasons? Thanks, Janet __ 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: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: BALA,PRAKASH (Non-HP-USA,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ 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: Viral Desai INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:How to get rid off certain character from a string ???
Janet,
The REPLACE command will do what you want. As is:
select replace('WC_89_06_03','_') from dual;
Dick Goulet
Reply Separator
Author: Janet Linsy [EMAIL PROTECTED]
Date: 9/6/2002 12:03 PM
Hi all,
How to get rid off certain character from a string.
For example, I have string 'WC_89_06_03', what's an
easy way to get 'WC890603' out, is there a function?
Thank you in advance!
Janet
__
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: Janet Linsy
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Same SQL statement, Same Oracle, Different OS == Different E
Title: RE: Same SQL statement, Same Oracle, Different OS ==> Different Expla I the RBO, the order the indexes were created in is important! I was able to show this to management on a project I was on. How? By doing a difinitive proof (follows) Import the table and data into an empty database. Create index A Create index B EXPLAIN PLAN shows query using index A. Drop table Import the table and data into an empty database Create index B Create index A EXPLAIN PLAN shows query using index B. Drop table Import the table and data into an empty database Create index A Create index B EXPLAIN PLAN shows query using index A. All other things being equal, the RBO will choose the index with the lower object_id! Proof took place in Oracle 8.0.5 on a Sun Solaris box. Kevin -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Friday, September 06, 2002 3:28 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Same SQL statement, Same Oracle, Different OS == Different E (see answer below) -Original Message- From: Sam Bootsma [mailto:[EMAIL PROTECTED]] One of our developers is encountering a situation where Oracle 9.0.x explain plan chooses one index when on UNIX, and chooses a different index when running on Windows NT. I'd appreciate any insights or similar experiences. The following are the facts: 1. The explain plan is run against the same SELECT statement on both platforms 2. It has been confirmed that there are no statistics on either of the databases 3. Both databases are using RBO (not CBO) 4. The UNIX database has about 100 times as many rows (in this table) as the NT database 5. The SELECT statement that gives different explain plans on different platforms is: SELECT FN.*, FN.ROWID FROM UNITFUND FN WHERE FN.FU_CODE = :cFuCode AND FN.MKEY = :cMkey AND FN.CLNT = :sKey AND FN.PLANNO = :sKey AND FN.DATE_FROM = :dDate AND FN.SOURCE = :cSource AND FN.TSTATUS = 'O' ORDER BY FN.DATE_FROM, FN.TSECOND; 6. Between the following 2 indexes, Oracle 9.0x chooses (2) on Unix and (1) on Windows NT. 1) clnt, mkey, planno, fu_code, date_from, source, tracode, tsecond... 2) date_from, clnt, planno, mkey, fu_code Just a wild guess, but maybe the optimizer is just picking the first index it finds because it thinks both are equally good candidates. Were both indexes created in the same order on both databases? I.e. is Object_id (from dba_objects) for Index A smaller than object_id for Index B on the UNIX database, but the reverse is true on Windows? You say the databases are using RBO. How do you know? Remember that if you use some new features (from the manual: Partitioned tables and indexes Index-organized tables Reverse key indexes Function-based indexes SAMPLE clauses in a SELECT statement Parallel query and parallel DML Star transformations and star joins Extensible optimizer Query rewrite with materialized views Enterprise Manager progress meter Hash joins Bitmap indexes and bitmap join indexes Index skip scans ) the query optimizer will use CBO because new features are not supported by RBO. Finally, this section of the manual may help you guess what's happening: Oracle9i Database Performance Guide and Reference Part Number A87503-02 Chapter 8 Using the Rule-Based Optimizer ... Understanding Access Paths for the RBO
RE: bind variables
Kevin, Are you saying then, that by default, any static statement that is executed within PL/SQL will not have be re-parsed eg sp_proc(var in varchar2) as begin select last_name from emp where last_name = var; end; If that's the case, I wont have to change much code. mkb --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Actually its easy. Any variable declared in PL/SQL and referenced in a non-dynamic SQL statement is a bind variable. In the following example (#1), some_var is an output bind-variable and other_var is a input bind variable. PL/SQL does manipulation on the statement and will send something like the following (#2) to the database #1 DECLARE some_var NUMBER(1); other_var NUMBER(1) BEGIN SELECT 1 INTO some_var FROM my_table WHERE my_column = other_var; END; #2 SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 Kevin -Original Message- Sent: Friday, September 06, 2002 1:59 PM To: Multiple recipients of list ORACLE-L John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other
Re: cache settings NT
That's why you use write cache only if it's battery supported. Then it should be safe. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 2:28 PM I'll take a shot at this. Don't have any write caching. Oracle may think that it has safely committed the transactions and they are written to disk when they are in fact only in cache. If the machine were to shut down ungracefully, i.e., someone kicks out the plug, then your database would not be in a consistent state. Granted, the odds are small, but is it worth the risk? If the database is archived and you don't mind the increased chance of having to do a recovery then I would say 0% read - 100% write. Oracle is doing it's own read caching in the SGA and it's unlikely that the disk cache will correctly anticipate Oracle's read requirements. Therefore, caching on the read side is only likely to increase IO. Write caching does not lead to any increased IO but it does have some small risk of producing an inconsistent database. GKor @rdw.nl To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: cache settings NT 09/06/2002 04:18 AM Please respond to ORACLE-L Hi all I have the following question for you : what are the preferred values for the several cache settings on the fysical disk units (NT W2K) e.g. 100 % read cache - 0 % write cache 50% read cache - 50% write cache is there a difference between settings for an OLTP or DSS solution. etc anyone with an explanation vr. gr. g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to get rid off certain character from a string ???
You could try the translate function and leave off the translation for the _. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Friday, September 06, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Subject:How to get rid off certain character from a string ??? Hi all, How to get rid off certain character from a string. For example, I have string 'WC_89_06_03', what's an easy way to get 'WC890603' out, is there a function? Thank you in advance! Janet __ 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: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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:bind variables
John, I'm in exactly the same predicament. I'm also trying to find some examples. I have an older version of Feuerstein book which does talk about using DBMS_SQL package to bind variables. Unfortunately it looks a little messy. I'm now looking at the following link: http://gethelp.devx.com/techtips/oracle_pro/10min/10min1000.asp which seems to provide a couple examples. This is for 8i and above. If I get anything to work, I'll pass along what I have. hth mkb --- Rachel Carmichael [EMAIL PROTECTED] wrote: O'Reilly and PL/SQl Programming almost ALWAYS means the author is Steven Feuerstein --- [EMAIL PROTECTED] wrote: John, You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title. Dick Goulet Reply Separator Author: John Dunn [EMAIL PROTECTED] Date: 9/6/2002 7:38 AM Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual) Can anyone point me at any decent documentation on the subject of using bind variables in PL/SQL? John -Original Message- From: Nicoll, Iain (Calanais) [SMTP:[EMAIL PROTECTED]] Sent: 06 September 2002 15:23 To: Multiple recipients of list ORACLE-L Subject: RE: Must Read for Every Developer and DBA I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1
Re: materialized view of a remote partition
On Fri, Sep 06, 2002 at 07:23:28AM -0800, Ray Stell wrote: 8.1.7.4 create snapshot TEMP as select * from [EMAIL PROTECTED] partition (020904) ERROR at line 1: ORA-14100: partition extended table name cannot refer to a remote object Well, it ain't pretty, but... on the remote db: SQL create snapshot eh_snap as select * from emp partition (e18); Materialized view created. on the local db: SQL create snapshot eh_snap as select * from [EMAIL PROTECTED]; Materialized view created. === 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreate database script
Export can do this. Right? But the output is not a script but a binary file only Import can understand. ltiu Connie Milliken wrote: Does anyone have a script that will write another script to recreate a particular database quickly with all the info specific to that particular database? Seems to me that I have seen this somewhere before, but I am not sure where. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Must Read for Every Developer and DBA
This is true in 8i. But in 9i, this has changed per Gaja. Even if bind variables are used, it will use histograms if histograms are present. Prakash -Original Message- Sent: Friday, September 06, 2002 10:23 AM To: Multiple recipients of list ORACLE-L I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a
RE: Must Read for Every Developer and DBA
Histograms are useless if the optimizer does not know the exact value in the bind variable during a one pass execution plan (static execution plans). But if the execution path could be delayed to a later phase (bind stage) then probably the execution plan could be altered based on the value in the bind variable. I heard that this will be implemented in some future Oracle release (may be in 9i). Waleed -Original Message- Sent: Friday, September 06, 2002 6:08 PM To: Multiple recipients of list ORACLE-L This is true in 8i. But in 9i, this has changed per Gaja. Even if bind variables are used, it will use histograms if histograms are present. Prakash -Original Message- Sent: Friday, September 06, 2002 10:23 AM To: Multiple recipients of list ORACLE-L I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
RE: ANSI Isolation Levels
I have not read the ANSI specifications, however the gist of repeatable reads is that the query will return the same data each time it is issued by a transaction. Set transaction read only does provide this. Your attention is invited to ... http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a88856/c21cnsis.htm#3374 There certainly could be nuances which prevent set transaction read only from meeting the repeatable read specification. There is also the question of setting it globally vs. per transaction. There is no way to set repeatable read globally in Oracle. I thought Oracle planned to or had removed the ability to set serializable globally as well. Does the read committed specification written to say that committed blocks are always readable or that dirty blocks are never read? What about a more sophisticated model. An update only locks the changed rows. A read blocks any further changes and would succeed if it didn't visit the changed rows. I don't really want to go on building a hypothetical system. I thought at one time databases such as Sybase did have readers blocking writers and vice versa, but I don't know the circumstances. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, September 06, 2002 9:49 AM To: Multiple recipients of list ORACLE-L Hi Ian, The purpose of the command set transaction read only is to implement the repeatable read isolation level. Did you misspeak? As I understand it, the purpose of the set transaction command is to set the isolation level of which there are 4 specified in the ANSI spec and Oracle does not support the repeatable read isolation level as of 8i. (As I understand it there are ambiguities in the ANSI spec and efforts are underway to provide clarification with the anticipated result that there will be even more granularity in isolations levels.) I thought MySQL, at least the earlier versions, had no concept of a transaction You're right about the earlier versions but with current versions MySQL now has a table type of InnoDB (as well as table types of ISAM and Berkeley DB) and this supports transactions, referential integrity and row level locking. Without this option MySQL transaction support is limited to full table locks with no concept of rollbacks. With the InnoDB option MySQL is now ACID compliant. Supposedly the Berkeley DB option supports transactions but it's not a workable solution. If I block a query from even accessing an object which has gained, changed, or lost data until that data is committed, have I implemented the read committed isolation level. Hmmm... by blocking access to an object altogether there is no reading at all including read committed. Right? Steve Orr -Original Message- Sent: Thursday, September 05, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Importance: High The purpose of the command set transaction read only is to implement the repeatable read isolation level. I just checked the 9i documentation ... Oracle provides these transaction isolation levels: Read committed This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data. Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that executes a given query twice can experience both nonrepeatable read and phantoms. Serializable Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms. Read-only Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements. Set the Isolation Level Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction: SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL READ ONLY; --- I thought MySQL, at least the earlier versions, had no concept of a transaction and queries read uncommitted data from other sessions. Does one have a MySQL administrator? Is it true for Sybase, SQLServer, DB2 that writers never block readers and vice
RE: Must Read for Every Developer and DBA
...But only on the first execution of a session. It's a bug. Test (credit Jonathan Lewis): 0. Set up select c1, c2 from t1 where c1=:bind1 where different bind1 values would motivate different execution plans if we used literals. E.g., insert only a few rows where c1=5, and thousands of rows where c1=70. 1. Flush shared pool. Set bind1=5. Execute to get an indexed access. Set bind1=70. Execute and the optimizer will still use the index. 2. Flush shared pool. Set bind1=70. Execute to get a table scan. Set bind1=5. Execute and the optimizer will still use the table scan. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- BALA,PRAKASH (Non-HP-USA,ex1) Sent: Friday, September 06, 2002 5:08 PM To: Multiple recipients of list ORACLE-L This is true in 8i. But in 9i, this has changed per Gaja. Even if bind variables are used, it will use histograms if histograms are present. Prakash -Original Message- Sent: Friday, September 06, 2002 10:23 AM To: Multiple recipients of list ORACLE-L I thought that bind variables were faster but you always have to ensure that if you're accessing by data which may be heavily skewed and histograms would usually help you may not want to use bind variables as they will disable the use of histograms. In saying that it doesn't look as though that would be the case here. Iain Nicoll -Original Message- Sent: Friday, September 06, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Hello Vikas, As You said We should always make use of bind variables as it executes faster as compare to the statements where we do not make use of bind variables. Q1) Can you please take a more specific example as how a statement can be altered to make use of bind variable. Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM 5 to get few samples for you These are as follows UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 ANDUSER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A ' AND PROCESS = 1 AND USER_ID = 'A105722' UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A105722 How can I Introduce bind variables in these statements ? I may be sending a wrong SAMPLE as I feel I should apply your remove constant function and then send few SQL statements Warm Regards, Om In your case -- you are NOT using bind variables. Taking your update statement here: UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675' ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND USER_ID = 'A101675' that SHOULD BE recoded in the application to become : update cnst_queue set process = :b1, user_id = :b2, date_queued = sysdate, where awb_prefix = :b3 and awb_number = :b4 and awb_suffix = :b5 and awb_process = :b6 and user_id = :b7; and bind in those values before you execute this statement. There are ways in which it could be done and vary from language to language and environment to environment but they ALL support it. You MUST do this. In this case,the first time you execute this statement you need to parse this statement (HARD PARSING) and once the execution plan gets into the SHARED POOL (V$libraryCache) the other users can use this to great effect. They would not reparse this statement again and again and but does do the soft parsing of it. So One Parse may lead to MANY executions instead of 1 Parsing - 1 Execution. At least 90% of your database execution time is spent PARSING and OPTIMIZING that update -- 10% is spent actually DOING it. If you use bind variables -- very little time will be spent parsing (you can get that statement to execute in 1/10 of the time). Not only that -- but the concurrency and scalability of your database will go WAY up. This is the root cause of your issues, this must be fixed -- no questions about it. Vikas Khanna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network
Re: connect to the databases without using tnsnames.ora?
On Friday 06 September 2002 12:38, Philip Douglass wrote: As for 3rd party products mistakenly requiring a tnsnames.ora file, that is a valid complaint. And it isn't even limited to 3rd party tools. Some of Oracle's own tools only give you the option of connecting to databases that are listed in your tnsnames.ora file, which when using the host naming method is not required! I discovered that a couple of years ago with Forms 6 on Solaris. Won't work without a tnsnames.ora, which we didn't use there. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OPS Sequences: nocache == order ??
On Wednesday 04 September 2002 09:53, Tim Gorman wrote: Thinking more about it last night... Since Oracle's theoretical limit is 16384 commits per second, I imagine that you could safely make the sequence recycle at (or 16384 or 9) and limit the number of digits contributed by the sequence to 4-5... Really? What have they done in the past to get those astronomical TPS numbers on some of their bencmarks? I'm pretty sure they were in excess of that number. IIRC, they were done on an nCube using OPS and about 400 CPUs. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Dev tools for web-based apps
Val, While Oracle is looking at Java and JDeveloper, keep in mind that Oracle's own ERP package that is extremely huge and complex is mostly written in Forms. It started at F2.0, moved to 2.4 (a special version) and 4.5 (Client-Server) and is now at 6i and is deployed via the web. Oracle, however, has started using Java a lot and I observe that this trend is on the increase, even within Oracle Apps. The bulk is in Forms still. I would consult with someone at Oracle for the long term plans for Forms. In your situation, I would recommend a mix of the two, as I think that works well in Apps. Fyi, you might look at using the AOL (Application Object Layer) for Apps - I believe you can purcahse/use this module on its own and develop on it. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- Sent: Friday, September 06, 2002 9:23 AM To: Multiple recipients of list ORACLE-L What development tools (Oracle Forms, Java/JDeveloper) are your shops using to deploy new applications to the web? Or what Oracle tool would you recommend to Developers? My client is wanting to re-design an application (currently written in C) using Oracle Forms (partly due to a shorter learning curve with Forms.) The application will contain a great deal of complex business rules and consistency checks. I might add that product in production date is late 2005. I have concerns about Forms' performance issues in running a huge applet and mainly Forms' life expectancy. It appears to me that Oracle's focus and future is with Java and JDeveloper. Am I off base here? Thanks in advance! Val Valerie H. Webber Management Systems Designers, Inc Database Administrator [EMAIL PROTECTED] 704-566-5321 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
truss/trace/strace equivalent on Windows
Hi to all, I need a utility to debug Oracle's thread on Windows2000. do you know a utility which can trace windows system calls in thread level ? thanks in advance... -- Danisment Gazi Unal http://www.ubTools.com Web-based Oracle Database Products
Re: Same SQL statement, Same Oracle, Different OS == Different E
Interesting. This might explain a similar problem I had a few years ago. Oracle support did not have a good answer for it. Jared On Friday 06 September 2002 13:43, Toepke, Kevin M wrote: I the RBO, the order the indexes were created in is important! I was able to show this to management on a project I was on. How? By doing a difinitive proof (follows) Import the table and data into an empty database. Create index A Create index B EXPLAIN PLAN shows query using index A. Drop table Import the table and data into an empty database Create index B Create index A EXPLAIN PLAN shows query using index B. Drop table Import the table and data into an empty database Create index A Create index B EXPLAIN PLAN shows query using index A. All other things being equal, the RBO will choose the index with the lower object_id! Proof took place in Oracle 8.0.5 on a Sun Solaris box. Kevin -Original Message- Sent: Friday, September 06, 2002 3:28 PM To: Multiple recipients of list ORACLE-L (see answer below) -Original Message- From: Sam Bootsma [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] One of our developers is encountering a situation where Oracle 9.0.x explain plan chooses one index when on UNIX, and chooses a different index when running on Windows NT. I'd appreciate any insights or similar experiences. The following are the facts: 1. The explain plan is run against the same SELECT statement on both platforms 2. It has been confirmed that there are no statistics on either of the databases 3. Both databases are using RBO (not CBO) 4. The UNIX database has about 100 times as many rows (in this table) as the NT database 5. The SELECT statement that gives different explain plans on different platforms is: SELECT FN.*, FN.ROWID FROM UNITFUND FN WHERE FN.FU_CODE = :cFuCode AND FN.MKEY = :cMkey AND FN.CLNT = :sKey AND FN.PLANNO = :sKey AND FN.DATE_FROM = :dDate AND FN.SOURCE = :cSource AND FN.TSTATUS = 'O' ORDER BY FN.DATE_FROM, FN.TSECOND; 6. Between the following 2 indexes, Oracle 9.0x chooses (2) on Unix and (1) on Windows NT. 1) clnt, mkey, planno, fu_code, date_from, source, tracode, tsecond... 2) date_from, clnt, planno, mkey, fu_code Just a wild guess, but maybe the optimizer is just picking the first index it finds because it thinks both are equally good candidates. Were both indexes created in the same order on both databases? I.e. is Object_id (from dba_objects) for Index A smaller than object_id for Index B on the UNIX database, but the reverse is true on Windows? You say the databases are using RBO. How do you know? Remember that if you use some new features (from the manual: Partitioned tables and indexes Index-organized tables Reverse key indexes Function-based indexes SAMPLE clauses in a SELECT statement Parallel query and parallel DML Star transformations and star joins Extensible optimizer Query rewrite with materialized views Enterprise Manager progress meter Hash joins Bitmap indexes and bitmap join indexes Index skip scans ) the query optimizer will use CBO because new features are not supported by RBO. Finally, this section of the manual may help you guess what's happening: Oracle9i Database Performance Guide and Reference Part Number A87503-02 Chapter 8 Using the Rule-Based Optimizer ... Understanding Access Paths for the RBO Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: truss/trace/strace equivalent on Windows
Hi Danisment Long time no see There is free version of truss for nt called strace (http://razor.bindview.com/tools/desc/strace_readme.html) this migth help you Have fun ;-) Danisment Gazi Unal (ubTools) wrote: Hi to all, I need a utility to debug Oracle's thread on Windows2000. do you know a utility which can trace windows system calls in thread level ? thanks in advance... -- Danisment Gazi Unal http://www.ubTools.com Web-based Oracle Database Products -- /regards Peter Gram Mobil : +45 2527 7107 Fax : +45 4466 8856 Miracle A/S Kratvej 2 2760 Måløv http://miracleas.dk smime.p7s Description: application/pkcs7-signature
RE: OPS Sequences: nocache == order ??
One of our accelerator control system developers, an Oracle neophyte, claims to have achieved 13,000 tps writing to a RAID 5 array. I did set up the database, but most of the credit goes to him for exploring the OCI direct I/O options. I have no verified the rate, but I have no reason whatsoever to doubt him. This is on older four processor sun box. We've now traded in the lone a-1000 ,attached two T3's, and turned on archive logging. I had him retest and he said it was quicker than before . It's still RAID 5. If you are wondering why RAID 5, we have another little 659.9 Terabyte database and thousands of machines in compute farms to process the associated data. That project has first choice, and the rest of us make do with what's left. I too am curious where this theoretical limit of 16384 comes from. Theoretical as it no matter what hardware one chose this limit could not be surpassed? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, September 06, 2002 4:38 PM To: Multiple recipients of list ORACLE-L On Wednesday 04 September 2002 09:53, Tim Gorman wrote: Thinking more about it last night... Since Oracle's theoretical limit is 16384 commits per second, I imagine that you could safely make the sequence recycle at (or 16384 or 9) and limit the number of digits contributed by the sequence to 4-5... Really? What have they done in the past to get those astronomical TPS numbers on some of their bencmarks? I'm pretty sure they were in excess of that number. IIRC, they were done on an nCube using OPS and about 400 CPUs. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle to publish pricing guide on Sept. 3
... and as September 6th dwindles into its waning hours, there is still no pricing guide. Jared On Wednesday 04 September 2002 13:59, [EMAIL PROTECTED] wrote: Just checked this, and it is supposed to be out now on Sept 6th. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/29/2002 01:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Oracle to publish pricing guide on Sept. 3 Oracle has moved the release date from Aug. 28 to Sept. 3. All will be revealed . . . http://www.eweek.com/article2/0,3959,491399,00.asp -Original Message- Sent: Wednesday, August 28, 2002 2:02 PM To: '[EMAIL PROTECTED]' Has anyone seen this pricing guide yet? http://www.infoworld.com/articles/hn/xml/02/08/16/020816hnoraguide.xml?0816 f ram -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 loader loads - what is the name of the counterpart that exports
Sqplus works well for this as long as the file is not too large. Spooling to a file in sqlplus is quite slow. C or, ahem, Perl, are much faster. Jared - OCP and Part Time Perl Evangelist ;) On Thursday 05 September 2002 11:44, [EMAIL PROTECTED] wrote: It's called SQL Plus. Set the heading off, pagesize = 0, linesize = 200, set record delimiter = ',' or '|' and set feedback off; and termout on. This should produce an ASCII file once you supply your own query. RWB ltiu [EMAIL PROTECTED]@fatcity.com on 09/05/2002 01:05:07 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: exports Hello guys, I just blurted out my whole message in the subject line. Here it is again? Sql loader loads - what is the name of the counterpart that exports Oracle data in plain text? Export and Import does not handle plain ascii - these handle their own proprietary binary format, which utility can export Oracle data out from an Oracle database to a plain text file in comma-delimited format? Thanks for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Recreate database script
Connie, There are some scripts in Oracle Annotated Archives (by Kevin Loney and Rachel Carmichael, Oracle Press) that will do what you want. Dennis Connie Milliken wrote: Does anyone have a script that will write another script to recreate a particular database quickly with all the info specific to that particular database? Seems to me that I have seen this somewhere before, but I am not sure where. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Millike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
