possible to load a string with paragraphs?
Hi List, I have a web application that allows users to type notes with paragraphs. Is it possiable to load the string with paragraphs into Oracle (not save the note as a file)? Later on the application has to display the same format for the note when the user queries that record on the web. Thanks for any inputs. _ Check out the coupons and bargains on MSN Offers! http://shopping.msn.com/softcontent/softcontent.aspx?scmId=1418 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: commit for triggers
John, Thanks for your very detail explanation. From: "John Flack" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: commit for triggers Date: Fri, 23 Jan 2004 09:09:34 -0800 A two-phase commit is simply a way to make sure that commits happen in a distributed transaction the same way that they do in a local transaction. The absolute rule is: "Everything commits or Nothing does." In-between, with some parts committed and some not, is NOT tolerable. So in your transaction, the change to the audit log is NOT committed if any part of the transaction fails. Everything from the beginning of a transaction up to a commit or rollback command is part of the transaction. All DDL commands are transactions unto themselves, so they end the prior transaction (which is committed, if you have autocommit turned on, or rolled back otherwise) and the command following a DDL command starts a new transaction. Triggers execute within the same transaction as the command that triggered them, and may not include a commit or rollback. So any DML in a trigger is only committed if the entire transaction is committed. There is only one exception to this behavior. You can declare a stored procedure as an Autonomous Transaction, which means that you are starting a new transaction that is independant of the current transaction. This means that the new transaction can commit or rollback without affecting or being affected by the current transaction, and can fail without causing the current transaction to fail or succeed, even if the current transaction fails. This is very useful and powerful, but use it with caution, because you are no longer protected by the normal transaction safeguards. -Original Message- Sent: Friday, January 23, 2004 9:15 AM To: Multiple recipients of list ORACLE-L Hi All, I have a before update trigger for a local table. I know Oracle does not commit the inserting audit entry into the audit log table until the user commits the changes on the audited table. Can I assume Oracle issues one commit for both changes? When commit fails, both changes will be rolled back. However, Oracle uses two-phase commit if a trigger updates remote tables in a distributed database. What happens if Oracle commits the change in audit log table and my change subsequently fails? _ Learn how to choose, serve, and enjoy wine at Wine @ MSN. http://wine.msn.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Check out the new MSN 9 Dial-up fast & reliable Internet access with prime features! http://join.msn.com/?pgmarket=en-us&page=dialup/home&ST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
commit for triggers
Hi All, I have a before update trigger for a local table. I know Oracle does not commit the inserting audit entry into the audit log table until the user commits the changes on the audited table. Can I assume Oracle issues one commit for both changes? When commit fails, both changes will be rolled back. However, Oracle uses two-phase commit if a trigger updates remote tables in a distributed database. What happens if Oracle commits the change in audit log table and my change subsequently fails? _ Learn how to choose, serve, and enjoy wine at Wine @ MSN. http://wine.msn.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sql question
Hi List, I have following sql that runs in 1 sec: SELECT b.* FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) However, when I try to count above query as following, it hangs. Does someone have any ideas? SELECT count(b.award_number) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) _ High-speed usersbe more efficient online with the new MSN Premium Internet Software. http://join.msn.com/?pgmarket=en-us&page=byoa/prem&ST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert in batch loading
Janne, Thanks very much for your wonderful detail suggestion. I'll definitely use it to analyze the redo. David From: Jan Korecki <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 13:34:26 -0800 Hi! I suggest you set up a test where you check the redo. For example: - start test SQL> select LOG_MODE from v$database; LOG_MODE ARCHIVELOG SQL> create table append_test as select * from all_objects where 1=2; Table created. SQL> create index append_test_ind on append_test(owner); Index created. SQL> alter table append_test nologging; Table altered. SQL> alter index append_test_ind nologging; Index altered. SQL> analyze table append_test compute statistics for table for all indexes for all indexed columns; Table analyzed. SQL> set serveroutput on size 10 SQL> set autotrace on STATISTICS SQL> insert /*+ append */ into append_test select * from [EMAIL PROTECTED]; 35605 rows created. Statistics -- 1213732 redo size 35605 rows processed SQL> rollback; Rollback complete. SQL> alter index append_test_ind unusable; Index altered. SQL> alter session set skip_unusable_indexes=TRUE; Session altered. SQL> insert /*+ append */ into append_test select * from [EMAIL PROTECTED]; 35605 rows created. Statistics -- 1172 redo size 35605 rows processed end test This works well with a non-unique index. If the index is unique or you have primary key/unique constraint you will run into problem because you cannot use +append with a unusable unique index. You have 2 choices. 1) have the constraints set to deferreble and disble them before load, set the unique index to unusable, load, rebuild index nologging, enable constraints 2) if you dont want to have the constraints deferrable-> drop indexed before load and create them afterwards with nologging. Even if you do 2) you will save a lot of time and have a lot less redo. Regards, Janne! David Boyd wrote: Janne, Thanks for your reply. We have 6 redo log switchings during inserting a table that has 1 million records. Our redo log size (100 MB) dominates the checkpoint frequency. The table has two indexes. We don't set them to unusable during inserting. David From: Jan Korecki <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause "cannot allocate new log, Checkpoint not complete". All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx Hi! If you do incremental commits the batch will run slower or not at all (ora -01555). Have you checked how much redo your insert statement generates? You might have missed something. If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true Rebuild the indexes after the load with nologging. Janne! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ online games and music with a high-speed Internet connection! Prices start at less than $1 a day average. https://broadband.msn.com (Prices may vary by service area.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki INET: [EMAIL PROT
Re: insert in batch loading
What I meant is that checkpoint occurs only when redo log switches. We set log_checkpoint_interval = 21 and our OS block size is 512. If I increase the size of redo log to 500 MB, checkpoint will occur before log switching. Will the transaction wait until checkpoint complete during checkpoint? Thanks for your inputs. David From: Mladen Gogala <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 12:49:28 -0800 What does it mean that redo log size dominates checkpoint frequency? It brings to mind cases of Edmund Blackadder and Baldrick or Ace Rimmer and Lister & Kryten, both very clear cases of domination. I'm sure that J. Lewis, as a Britt, can tell you more about Black Adder and Red Dwarf, despite the fact that he missed those two subjects in his book. Redo logs of 100M are not really large. You, basically, have two choices: a) Increase redo logs to decent size (500M) b) Disable indexes during load. The third choice (out of two) would be to use "_disable_logging" parameter, recently mentioned by Mr. Anjo Kolk. Now, that would be practical, don't you think? On 11/25/2003 11:59:26 AM, David Boyd wrote: > Janne, > > Thanks for your reply. > > We have 6 redo log switchings during inserting a table that has 1 million > records. Our redo log size (100 MB) dominates the checkpoint frequency. > The table has two indexes. We don't set them to unusable during inserting. > > David > > > >From: Jan Korecki <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: Re: insert in batch loading > >Date: Tue, 25 Nov 2003 08:19:26 -0800 > > > >David Boyd wrote: > > > >>Hi All, > >> > >>We have some batch loading jobs that truncate the tables first, then > >>insert into the tables as select from tables through database link. Those > >>jobs run daily. Every time when those jobs run, they cause "cannot > >>allocate new log, Checkpoint not complete". All of tables and their > >>indexes are in nologging mode. We have /*+append*/ hint in the insert > >>statement. We have 5 redo groups with member of 100 MB. Some tables have > >>more than 1 million records. I was wondering if any body knows a method > >>that forces a commit after every 1000 records inserted, which is like > >>delete_commit procedure. > >> > >>David > >> > >>_ > >>Groove on the latest from the hot new rock groups! Get downloads, videos, > >>and more here. http://special.msn.com/entertainment/wiredformusic.armx > > > > > >Hi! > >If you do incremental commits the batch will run slower or not at all (ora > >-01555). > > > >Have you checked how much redo your insert statement generates? You might > >have missed something. > >If you have indexes on the table you will have to set them unusable and > >alter session set skip_unusable_indexes=true > >Rebuild the indexes after the load with nologging. > > > >Janne! > > > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > >-- > >Author: Jan Korecki > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com > >San Diego, California-- Mailing list and web hosting services > >- > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >the message BODY, include a line containing: UNSUB ORACLE-L > >(or the name of mailing list you want to be removed from). You may > >also send the HELP command for other information (like subscribing). > > _ > online games and music with a high-speed Internet connection! Prices start > at less than $1 a day average. https://broadband.msn.com (Prices may vary > by service area.) > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: David Boyd > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED]
Re: insert in batch loading
Janne, Thanks for your reply. We have 6 redo log switchings during inserting a table that has 1 million records. Our redo log size (100 MB) dominates the checkpoint frequency. The table has two indexes. We don't set them to unusable during inserting. David From: Jan Korecki <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause "cannot allocate new log, Checkpoint not complete". All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx Hi! If you do incremental commits the batch will run slower or not at all (ora -01555). Have you checked how much redo your insert statement generates? You might have missed something. If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true Rebuild the indexes after the load with nologging. Janne! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ online games and music with a high-speed Internet connection! Prices start at less than $1 a day average. https://broadband.msn.com (Prices may vary by service area.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert in batch loading
We have 5 groups of redo log. Each group has two members. Each member is 100 MB. David From: Mladen Gogala <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 07:44:33 -0800 How big are yor redo logs? How many o them do you have? On 11/25/2003 10:29:37 AM, David Boyd wrote: > Hi All, > > We have some batch loading jobs that truncate the tables first, then insert > into the tables as select from tables through database link. Those jobs run > daily. Every time when those jobs run, they cause "cannot allocate new log, > Checkpoint not complete". All of tables and their indexes are in nologging > mode. We have /*+append*/ hint in the insert statement. We have 5 redo > groups with member of 100 MB. Some tables have more than 1 million records. > I was wondering if any body knows a method that forces a commit after > every 1000 records inserted, which is like delete_commit procedure. > > David > > _ > Groove on the latest from the hot new rock groups! Get downloads, videos, > and more here. http://special.msn.com/entertainment/wiredformusic.armx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: David Boyd > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Need a shot of Hank Williams or Patsy Cline? The classic country stars are always singing on MSN Radio Plus. Try one month free! http://join.msn.com/?page=offers/premiumradio -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
insert in batch loading
Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause "cannot allocate new log, Checkpoint not complete". All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: database terminated
Thanks all of you who replied. Your help is highly appreciated. I'll open a TAR with Oracle. David From: <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: database terminated Date: Tue, 04 Nov 2003 11:19:33 -0800 there is an ora-600 query screen on metalink. it has limited amounts of information. ora-600 is not documented anywhere else. you need toopen a TAR immediately. ora-600 is not well documented. if you dont have a support plan, you may have a problem. > > From: "David Boyd" <[EMAIL PROTECTED]> > Date: 2003/11/04 Tue PM 02:04:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: database terminated > > Hi List, > > One of our databases was terminated due to error 600. Below is the error > message from alert log file: > > Tue Nov 4 12:00:25 2003 > Errors in file /oracle/admin/adminp0/bdump/adminp0_pmon_24501.trc: > ORA-00600: internal error code, arguments: [106], [], [], [], [], [], [], [] > Tue Nov 4 12:00:32 2003 > Errors in file /oracle/admin/adminp0/bdump/adminp0_pmon_24501.trc: > ORA-00600: internal error code, arguments: [106], [], [], [], [], [], [], [] > Tue Nov 4 12:00:32 2003 > PMON: terminating instance due to error 600 > Instance terminated by PMON, pid = 24501 > > Could any one please let me know what was the reason caused this happened? > Where can I start to look at the potential cause? Thanks in advance. > > We are on Oracle 8.1.7 and OS Sun 5.8. > > David > > _ > Concerned that messages may bounce because your Hotmail account is over > limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: David Boyd > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _____ MSN Shopping upgraded for the holidays! Snappier product search... http://shopping.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
database terminated
Hi List, One of our databases was terminated due to error 600. Below is the error message from alert log file: Tue Nov 4 12:00:25 2003 Errors in file /oracle/admin/adminp0/bdump/adminp0_pmon_24501.trc: ORA-00600: internal error code, arguments: [106], [], [], [], [], [], [], [] Tue Nov 4 12:00:32 2003 Errors in file /oracle/admin/adminp0/bdump/adminp0_pmon_24501.trc: ORA-00600: internal error code, arguments: [106], [], [], [], [], [], [], [] Tue Nov 4 12:00:32 2003 PMON: terminating instance due to error 600 Instance terminated by PMON, pid = 24501 Could any one please let me know what was the reason caused this happened? Where can I start to look at the potential cause? Thanks in advance. We are on Oracle 8.1.7 and OS Sun 5.8. David _ Concerned that messages may bounce because your Hotmail account is over limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Refresh option for Materialized view , want to use it during refresh - for
This is a very good method. I would like to use it to modify some of my > > > data loading procedures. Here are my questions: > > > 1. Do I need to create the table on the step 1 every time when I refresh > >the > > > data If I refresh data once per day? > > > 2. Is "ON PREBUILT TABLE" available on Oracle 8i? When I was trying the > > > method on Oracle 8i, I got missing keyword error on "PREBUILT". > > > > > > Dave > > > > > > > > > > >Siddharth, > > > > > > > >I will offer a slightly out-of-the-box solution. Please read it through > > > >till the end to determine its applicability in your case. > > > > > > > >It seems yours refresh interval is once a day and you don't mind stale > > > >data for a max of 24 hours. You also refresh is complete, not > > > >incremental. So, I would suggest the follwoing approach. > > > > > > > >(1) Create a table first > > > >CREATE TABLE CT_PRODUCTID_VW > > > >TABLESPACE > > > >NOLOGGING > > > >AS > > > >SELECT . > > > > > > > >(2) When you are ready to "refresh", drop the MV > > > >DROP MATERIALIZED VIEW CT_PRODUCTID_VW; > > > > > > > >(3) Create the MV with the PREBUILT TABLE option. > > > >CREATE > > > >MATERIALIZED VIEW CT_PRODUCTID_VW > > > >BUILD IMMEDIATE > > > >REFRESH START WITH SYSDATE > > > >NEXT (SYSDATE + 1) > > > >ON PREBUILT TABLE > > > >AS > > > >SELECT > > > > msi.segment1productid, > > > >... > > > > > > > >Your MV is not accessible between STEP 2 and STEP3, which is really a > > > >dictionary update and takes about a second or so. So the "outage" is > > > >really 1 second, not 1/2 hr. > > > > > > > >A few explanations are in order here. > > > > > > > >(1) Creating an MV on a Prebuilt Table does not consume more space. The > > > >segment that used to be a table simply becomes an MV. > > > >(2) When you drop the MV, the MV is gone, but the table remains > >instact. > > > >(3) The table can be create by any means - export/import, SQL*Loader, > > > >INSERT APPEND, etc. > > > >(4) IT places less strain on the system comapred to the MV refresh > > > >option, simply because the MV refresh truncates the segment and then > > > >builds it. > > > > > > > >I presented a paper to the same effect at IOUG Live 2003. You can > > > >download a modified version of the same from my website > > > >www.proligence.com/downlaods.html, titled "Painless Master Table Alter" > > > >from the Presentations Section. > > > > > > > >HTH. > > > > > > > >Arup Nanda > > > > > > > > > > > > > > > > > > > > > > > >- Original Message - > > > >To: Multiple recipients of list ORACLE-L > > > >Sent: Tuesday, October 21, 2003 3:59 AM > > > >refresh > > > > > > > > > > > >Hi Gurus, > > > > > > > >I have a materialized view, which is based on Oracle Apps tables and on > > > >remote database. The view refresh takes around ½ hour, during this time > > > >period I cannot see any records in the materialized view and therefore > > > >my application faces errors. > > > >The following is the view definition > > > > > > > >CREATE > > > >MATERIALIZED VIEW CT_PRODUCTID_VW > > > >BUILD IMMEDIATE > > > >REFRESH START WITH SYSDATE > > > >NEXT (SYSDATE + 1) > > > >AS > > > >SELECT > > > > msi.segment1productid, > > > > msi.description description, > > > > msi.inventory_item_id inventory_item_id, > > > > mc.segment1 product_family, > > > > mc.segment2 product_type > > > >FROM [EMAIL PROTECTED] mcs, > > > > [EMAIL PROTECTED] mc, > > > > [EMAIL PROTECTED]mic, > > > > [EMAIL PROTECTED] msi > > > >where 1=1 > > > >and mc.structure_id = 50112 > > > >and mc.segment3 != 'SPARE' > > >
Re: Refresh option for Materialized view , want to use it during refresh - for
te
> >www.proligence.com/downlaods.html, titled "Painless Master Table Alter"
> >from the Presentations Section.
> >
> >HTH.
> >
> >Arup Nanda
> >
> >
> >
> >
> >
> >- Original Message -
> >To: Multiple recipients of list ORACLE-L
> >Sent: Tuesday, October 21, 2003 3:59 AM
> >refresh
> >
> >
> >Hi Gurus,
> >
> >I have a materialized view, which is based on Oracle Apps tables and on
> >remote database. The view refresh takes around ½ hour, during this time
> >period I cannot see any records in the materialized view and therefore
> >my application faces errors.
> >The following is the view definition
> >
> >CREATE
> >MATERIALIZED VIEW CT_PRODUCTID_VW
> >BUILD IMMEDIATE
> >REFRESH START WITH SYSDATE
> >NEXT (SYSDATE + 1)
> >AS
> >SELECT
> > msi.segment1productid,
> > msi.description description,
> > msi.inventory_item_id inventory_item_id,
> > mc.segment1 product_family,
> > mc.segment2 product_type
> >FROM [EMAIL PROTECTED] mcs,
> > [EMAIL PROTECTED] mc,
> > [EMAIL PROTECTED]mic,
> > [EMAIL PROTECTED] msi
> >where 1=1
> >and mc.structure_id = 50112
> >and mc.segment3 != 'SPARE'
> >and mc.global_name= 'US'
> >and mc.enabled_flag = 'Y'
> >and mcs.global_name = mc.global_name
> >and mcs.category_set_name = 'PROD GROUP'
> >and mic.category_set_id = mcs.category_set_id
> >and mic.category_id = mc.category_id
> >and mic.global_name = mc.global_name
> >and mic.organization_id = 1
> >and mic.inventory_item_id = msi.inventory_item_id
> >and msi.organization_id = mic.organization_id
> >and msi.global_name = mc.global_name
> >AND msi.auto_created_config_flag = 'N'
> >AND msi.item_type IN ('ATO MODEL','CONFIG SPARE','CONFIG
> >SUB','FEATURE PACK','PRODUCT LIST>$0','PTO MODEL','SPARE')
> >and msi.inventory_item_status_code IN
> >('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
> >
> >Please note that the tables referenced are remote tables and Oracle
Apps
> >tables and not logging on it is possible.
> >Please suggest an appropriate refresh mechanism to see the records even
> >during refresh period.
> >
> >Thanks in advance.
> >
> >With Warm Regards
> >
> >
> >
> >Siddharth Haldankar
> >Zensar Technologies Ltd.
> >Cisco Systems Inc.
> >(Offshore Development Center)
> ># : 091 020 4128394
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >
>
> _
> Cheer a special someone with a fun Halloween eCard from American
Greetings!
> Go to http://www.msn.americangreetings.com/index_msn.pd?source=msne134
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: David Boyd
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Arup Nanda
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_
Add MSN 8 Internet Software to your current Internet access and enjoy
patented spam control and more. Get two months FREE!
http://join.msn.com/?page=dept/byoa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Refresh option for Materialized view , want to use it during refresh - for
Hi Arup,
This is a very good method. I would like to use it to modify some of my
data loading procedures. Here are my questions:
1. Do I need to create the table on the step 1 every time when I refresh the
data If I refresh data once per day?
2. Is "ON PREBUILT TABLE" available on Oracle 8i? When I was trying the
method on Oracle 8i, I got missing keyword error on "PREBUILT".
Dave
Siddharth,
I will offer a slightly out-of-the-box solution. Please read it through
till the end to determine its applicability in your case.
It seems yours refresh interval is once a day and you don't mind stale
data for a max of 24 hours. You also refresh is complete, not
incremental. So, I would suggest the follwoing approach.
(1) Create a table first
CREATE TABLE CT_PRODUCTID_VW
TABLESPACE
NOLOGGING
AS
SELECT .
(2) When you are ready to "refresh", drop the MV
DROP MATERIALIZED VIEW CT_PRODUCTID_VW;
(3) Create the MV with the PREBUILT TABLE option.
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
ON PREBUILT TABLE
AS
SELECT
msi.segment1productid,
...
Your MV is not accessible between STEP 2 and STEP3, which is really a
dictionary update and takes about a second or so. So the "outage" is
really 1 second, not 1/2 hr.
A few explanations are in order here.
(1) Creating an MV on a Prebuilt Table does not consume more space. The
segment that used to be a table simply becomes an MV.
(2) When you drop the MV, the MV is gone, but the table remains instact.
(3) The table can be create by any means - export/import, SQL*Loader,
INSERT APPEND, etc.
(4) IT places less strain on the system comapred to the MV refresh
option, simply because the MV refresh truncates the segment and then
builds it.
I presented a paper to the same effect at IOUG Live 2003. You can
download a modified version of the same from my website
www.proligence.com/downlaods.html, titled "Painless Master Table Alter"
from the Presentations Section.
HTH.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, October 21, 2003 3:59 AM
refresh
Hi Gurus,
I have a materialized view, which is based on Oracle Apps tables and on
remote database. The view refresh takes around ½ hour, during this time
period I cannot see any records in the materialized view and therefore
my application faces errors.
The following is the view definition
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
AS
SELECT
msi.segment1productid,
msi.description description,
msi.inventory_item_id inventory_item_id,
mc.segment1 product_family,
mc.segment2 product_type
FROM [EMAIL PROTECTED] mcs,
[EMAIL PROTECTED] mc,
[EMAIL PROTECTED]mic,
[EMAIL PROTECTED] msi
where 1=1
and mc.structure_id = 50112
and mc.segment3 != 'SPARE'
and mc.global_name= 'US'
and mc.enabled_flag = 'Y'
and mcs.global_name = mc.global_name
and mcs.category_set_name = 'PROD GROUP'
and mic.category_set_id = mcs.category_set_id
and mic.category_id = mc.category_id
and mic.global_name = mc.global_name
and mic.organization_id = 1
and mic.inventory_item_id = msi.inventory_item_id
and msi.organization_id = mic.organization_id
and msi.global_name = mc.global_name
AND msi.auto_created_config_flag = 'N'
AND msi.item_type IN ('ATO MODEL','CONFIG SPARE','CONFIG
SUB','FEATURE PACK','PRODUCT LIST>$0','PTO MODEL','SPARE')
and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Please note that the tables referenced are remote tables and Oracle Apps
tables and not logging on it is possible.
Please suggest an appropriate refresh mechanism to see the records even
during refresh period.
Thanks in advance.
With Warm Regards
Siddharth Haldankar
Zensar Technologies Ltd.
Cisco Systems Inc.
(Offshore Development Center)
# : 091 020 4128394
[EMAIL PROTECTED]
[EMAIL PROTECTED]
_
Cheer a special someone with a fun Halloween eCard from American Greetings!
Go to http://www.msn.americangreetings.com/index_msn.pd?source=msne134
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: ORA-02049: timeout: distributed transaction waiting for lock
Arup, Thanks for your reply. We don't have a metalink account. Could you please send the note to me? My puzzle is that it seems the lock was acquired since all of records were inserted into the table. How did the error come from commit command? Dave From: "Arup Nanda" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: ORA-02049: timeout: distributed transaction waiting for lock Date: Tue, 21 Oct 2003 08:39:32 -0800 David, Take a look at Note 19332.1, which explains the error and what to do next. In short, the essence of the note is: The error comes if the time waited is mor than the value of the distributed_lock_timeout parameter. Even if you do a select from the remote database, it acquires a TX lock and that can wait. Increase the value of the timeout or, just use an exception handler on the commit statement to retry. HTH. Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 11:49 AM > Hi List, > > We have a job that copies data in a table on a remote database to a local > database through a database link. Here are the steps in the job: > > 1. truncate the table of t1 on the local database > 2. insert into t1 select * from [EMAIL PROTECTED] > 3. commit > > There are only 847 records in the table. The job completes in 1 sec > normally. However, last Sunday we got ORA-02049: timeout: distributed > transaction waiting for lock during commit process. As my understanding, > the error comes from a DML statement that requires locks on a remote > database can be blocked if another transaction own locks on the requested > data. I'm pretty sure that there were no any activities on the remote > database since the application was not open. Also I can see from the log > file (see below) that 847 records were inserted into the t1 table on the > local database. The error was generated during the commit process. Does > any one have any comments? Thanks for any input. > > Here is the job log file: > 847 rows created. > > commit > * > ERROR at line 1: > ORA-02049: timeout: distributed transaction waiting for lock > > We are in Oracle 8.1.7.4 and SunOS 5.8. We take the default value for > DISTRIBUTED_LOCK_TIMEOUT . > > Dave > > _ > Get a FREE computer virus scan online from McAfee. > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: David Boyd > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: unique index
Dick, Thanks for your reply. Unfortunately, the loader's log file was overwritten before our developer called me since she tried to rerun the job. Dave From: "Goulet, Dick" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: unique index Date: Tue, 21 Oct 2003 08:44:32 -0800 Dave, If memory is functioning normally: When you use direct=y in Sql*Loader it flags all of your indexes as invalid and then revalidates/rebuilds then when the load is complete. The reason is that loading data is faster when you don't have to parse index entries all the time and an invalid index does not need to be maintained. It would appear from your message that something caused the one index to not validate during the Sql*Loader run. Why might be revealed in the loader's log file. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, October 21, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Hi List, We have a job that appends records to a table using SQL Loader (DIRECT=TRUE). The table has two unique indexes (no constraints). Last Sunday, the job loaded 11839 records into the table successfully, but the one of the unique indexes became unusable for unknown reason. I dropped the unusable index and recreated it. The index became valid. Then the developer reran the job and loaded the same 11839 records into the table (at that time we did not know the first run already loaded the records). Of course, two unique indexes became unusable again. I could not recreate the unique indexes due to the duplicate keys found. Finally, I deleted all of 23678 newly loaded records, recreated the unique indexes, and reloaded the 11839 records. Every thing is fine now. Here are my questions: 1. Why the same data crashed the index at the first time, but not at the end 2. After I recreated the unique index at the first time, those records were already in the table. Why did not the unique index complain for the duplicates when we reloaded the same 11839 records into the table? Dave _ Send and receive larger attachments with Hotmail Extra Storage. http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Surf and talk on the phone at the same time with broadband Internet access. Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
unique index
Hi List, We have a job that appends records to a table using SQL Loader (DIRECT=TRUE). The table has two unique indexes (no constraints). Last Sunday, the job loaded 11839 records into the table successfully, but the one of the unique indexes became unusable for unknown reason. I dropped the unusable index and recreated it. The index became valid. Then the developer reran the job and loaded the same 11839 records into the table (at that time we did not know the first run already loaded the records). Of course, two unique indexes became unusable again. I could not recreate the unique indexes due to the duplicate keys found. Finally, I deleted all of 23678 newly loaded records, recreated the unique indexes, and reloaded the 11839 records. Every thing is fine now. Here are my questions: 1. Why the same data crashed the index at the first time, but not at the end 2. After I recreated the unique index at the first time, those records were already in the table. Why did not the unique index complain for the duplicates when we reloaded the same 11839 records into the table? Dave _ Send and receive larger attachments with Hotmail Extra Storage. http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-02049: timeout: distributed transaction waiting for lock
Hi List, We have a job that copies data in a table on a remote database to a local database through a database link. Here are the steps in the job: 1. truncate the table of t1 on the local database 2. insert into t1 select * from [EMAIL PROTECTED] 3. commit There are only 847 records in the table. The job completes in 1 sec normally. However, last Sunday we got ORA-02049: timeout: distributed transaction waiting for lock during commit process. As my understanding, the error comes from a DML statement that requires locks on a remote database can be blocked if another transaction own locks on the requested data. I'm pretty sure that there were no any activities on the remote database since the application was not open. Also I can see from the log file (see below) that 847 records were inserted into the t1 table on the local database. The error was generated during the commit process. Does any one have any comments? Thanks for any input. Here is the job log file: 847 rows created. commit * ERROR at line 1: ORA-02049: timeout: distributed transaction waiting for lock We are in Oracle 8.1.7.4 and SunOS 5.8. We take the default value for DISTRIBUTED_LOCK_TIMEOUT . Dave _ Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
