RE: Rollback segment full
3 options 1. if possible , put a commit in your procedure to reduce load on rollback segs 2. ask your dba to increase size of rollback segments. Since rollback segment allocation is random, u may have to drop the smaller rbs segs and create new larger segs 3. increase the maxextents of existing rbs segs HTH ratnesh -Original Message- Sachin (GEP) Sent: Friday, November 01, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Hi all i am trying to execuate one procedure which upadte the table containing 29443 records. After 2 hours going in loop, procedure returns following error: SQL exec test BEGIN test; END; * ERROR at line 1: ORA-01562: failed to extend rollback segment number 2 ORA-01628: max # extents (249) reached for rollback segment R02 ORA-01562: failed to extend rollback segment number 2 ORA-01628: max # extents (249) reached for rollback segment R02 ORA-06512: at SA.TEST, line 14 ORA-06512: at line 1 Any help is appreciated. Regards, Sachin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gharat, Sachin (GEP) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh 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: will the Return Order of rows change with time ?
Thanks a lot to all those people who replied to my query. answering stephane question : the table is 500MB+ , and there is no unique key since it is a child table. Moreover presently i cannot modify the table. The general concensus is that there is no guarantee of return order of rows without using order by clause. Anybody knows why this is so ? Oracle must be having a fixed algorith for data access , probably using the extent map. Assuming 1. no query parallelism no deletes/updates to table 2. no index/optimizer/structure changes the data retrieval path should not change with time for the same query ?? thanks regards ratnesh singh -Original Message- Kumar Singh Sent: Tuesday, October 22, 2002 4:09 PM To: Multiple recipients of list ORACLE-L Hi I have a very large DW table in which there are only inserts and NO updates/deletes.The table grows by around 2-5 % every week due to new inserts. I need to return the rows for each customer in the same order as inserted to table.Due to design/delivery constraints , i cannot modify the table. ques 1 : if i do a 'select * from table' with where clause but no order by clause,will the Order of rows returned be the same whenever this query is executed ? Is this guaranteed by Oracle ? ques 2 : if i export/import this table , then execute the same query, will the Order of rows returned be the same as before the export/import ? ques 3 : if i use the 'move' cmd to rebuild this table , then execute my query,will the Order of rows returned be the same as before the rebuild ? any explanations are most welcome many thanks ratnesh singh - Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107/2106 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh 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: will the Return Order of rows change with time ?
Thanks Richard good explanation .. Your answer has cleared all my doubts . thanks again to all who replied to this query. ratnesh -Original Message- Richard Sent: Wednesday, October 23, 2002 1:14 PM To: Multiple recipients of list ORACLE-L I understand what you are saying. Most people responded that there is no guarantee because that is the true answer. In a sense you are correct - if nothing changes the optimisers approach then in all likelyhood the rows will come back in the same order each time... However you are inserting rows into the table, so there is a good chance that the optimiser may change it's mind at some point in time. Also, most of our comments have been about returning the rows in the same sequence each time... Promising that this sequence will also match the order in which they are inserted becomes even more difficult. Even today the query may be using an index (depending on the where clause) which may result in a traversal of your table that does not match the insertion order. For example, if you had an index on delivery_date and your query was select * where delivery_date sysdate then the result set will most likely appear sorted by delivery_date, since the index is sorted. Delivery_date may have nothing to do with the order of inserting rows into your table however, since different products may have different delivery schedules. Therefore what your are asking for is really quite unpredictable. Indeed, I have seen cases where index hints are used to return rows in a specific order without using an order by clause, and these rows definitely don't match their insertion order. Using a hint to force the order of a result set makes me cringe - although it does perform very fast, and can be combined with where rownum 100 to return the earliest 100 rows without even reading more than 100 rows from the table - a cute trick. hth, Mark. Ratnesh Kumar SinghTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ratnesh.singh@ cc: patni.comSubject: RE: will the Return Order of rows change with time ? Sent by: [EMAIL PROTECTED] m 23/10/2002 16:58 Please respond to ORACLE-L Thanks a lot to all those people who replied to my query. answering stephane question : the table is 500MB+ , and there is no unique key since it is a child table. Moreover presently i cannot modify the table. The general concensus is that there is no guarantee of return order of rows without using order by clause. Anybody knows why this is so ? Oracle must be having a fixed algorith for data access , probably using the extent map. Assuming 1. no query parallelism no deletes/updates to table 2. no index/optimizer/structure changes the data retrieval path should not change with time for the same query ?? thanks regards ratnesh singh -Original Message- Kumar Singh Sent: Tuesday, October 22, 2002 4:09 PM To: Multiple recipients of list ORACLE-L Hi I have a very large DW table in which there are only inserts and NO updates/deletes.The table grows by around 2-5 % every week due to new inserts. I need to return the rows for each customer in the same order as inserted to table.Due to design/delivery constraints , i cannot modify the table. ques 1 : if i do a 'select * from table' with where clause but no order by clause,will the Order of rows returned be the same whenever this query is executed ? Is this guaranteed by Oracle ? ques 2 : if i export/import this table , then execute the same query, will the Order of rows returned be the same as before the export/import ? ques 3 : if i use the 'move' cmd to rebuild this table , then execute my query,will the Order of rows returned be the same as before the rebuild ? any explanations are most welcome many thanks ratnesh singh - Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107/2106 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh 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
will the Return Order of rows change with time ?
Hi I have a very large DW table in which there are only inserts and NO updates/deletes. The table grows by around 2-5 % every week due to new inserts. I need to return the rows for each customer in the same order as inserted to table. Due to design/delivery constraints , i cannot modify the table. ques 1 : if i do a 'select * from table' with where clause but no order by clause, will the Order of rows returned be the same whenever this query is executed ? Is this gauranteed by Oracle ? ques 2 : if i export/import this table , then execute the same query, will the Order of rows returned be the same as before the export/import ? ques 3 : if i use the 'move' cmd to rebuild this table , then execute my query, will the Order of rows returned be the same as before the rebuild ? any explanations are most welcome many thanks ratnesh singh - Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107/2106 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh 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: Idle Connections
how about setting the parameter sqlnet.expire_time=10 in the sqlnet.ora file ? ( the figure 10 is in minutes ) --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -Original Message- Samir Sent: Tuesday, September 10, 2002 9:48 PM To: Multiple recipients of list ORACLE-L Hi All, I am facing problems with a database with some very irresponsible users who just connect to the database from their applications and simply dont logout. Apart from the usual chidings I have been giving them, could any of you please tell me whether any parameter exists which can be put either in the sqlnet.ora file or database parameter file which will timeout and close the idle connections after a particular time interval ?? Thanks, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ratnesh Kumar Singh 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: ANALYZE question
An export has default parameter of STATISTICS=ESTIMATE. If such an exported file is imported , the default import parameter ANALYZE=Y will result in the import utility executing the analyze stmts in dump file. -Original Message- Sean Sent: Wednesday, July 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: locking issues in web based applications
Dennis , thanks for your suggestions. It seems detection is the only viable option to avoid locking. i'll try to get the suggested book from somewhere . ratnesh -Original Message- WILLIAMS Sent: Tuesday, July 23, 2002 9:08 PM To: Multiple recipients of list ORACLE-L Ratnesh I assume you are using the JDBC interface, rather than J2EE. You may want to consider buying the book Java Programming with Oracle JDBC by Donald Bales. He devotes a chapter to this subject. One issue he raises that will limit your options is whether you have other applications besides your Web-based application accessing this database. If not, that gives you additional flexibility. Bales distinguishes between locking and detection. He contends that locking alone does not solve the problem of multiuser data access integrity, and offers several examples to support his contention. He then outlines 3 methods for employing detection. He defines detection as the ability to detect if data you are about to modify has changed since the point when you selected it to be updated. 1. Pessimistic. Use an updatestamp 2. Pessimistic. Compare all the columns in the table or attributes of an object with their original values. 3. Optimistic. Compare only modified columns or attributes in a WHERE clause. I wish I could speak from experience, but I think this author has studied this issue in more detail than I could. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 23, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Hi I'm not very familiar with web based application development. One of our programmers has asked me a question regarding locking issues in web based applications.In a typical scenario , users access the oracle database thro a browser via app-svr/web-svr . Connection pooling is implemented on the web-svr to support large no of users. If a user locks some objects via updates or deletes , then kills his browser , the objects remains locked. The question is how to design the system such that abnormal client browser termination does not lock any objects.One solution is not to lock objects at all , and commit immediately after update if the object timestamp has not changed .But this approach is suitable only for short sweet transactions. Connection timeout is too time-taking for intensive applications to be of any use. There must be other better ways of doing this. I need your suggestions. TIA, ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Oracle Architecture - Abrief Intro
') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: db2
I once used VAJAVA 3.5 with fixpack for a project . and it turned out to be a complete nightmare. my pc had a 512M RAM , and yet every now and then VAJAVA would say insufficient memory and would freeze the entire system , leading to a reboot. My entire team faced the same problem . we spent more time rebooting than coding. But the tool does have good development features esp their shared repository features. -Original Message- Emery {PDBI~Palo Alto} Sent: Thursday, July 25, 2002 2:50 AM To: Multiple recipients of list ORACLE-L Has anyone compared IBMs DB2 offerings against Oracle tools. There appears to be a produce very close to JDeveloper and the tools claim to compete well in the mid tier database market. Emery Gordon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gordon, Emery {PDBI~Palo Alto} INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
locking issues in web based applications
Hi I'm not very familiar with web based application development. One of our programmers has asked me a question regarding locking issues in web based applications.In a typical scenario , users access the oracle database thro a browser via app-svr/web-svr . Connection pooling is implemented on the web-svr to support large no of users. If a user locks some objects via updates or deletes , then kills his browser , the objects remains locked. The question is how to design the system such that abnormal client browser termination does not lock any objects.One solution is not to lock objects at all , and commit immediately after update if the object timestamp has not changed .But this approach is suitable only for short sweet transactions. Connection timeout is too time-taking for intensive applications to be of any use. There must be other better ways of doing this. I need your suggestions. TIA, ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: RE: dblink problem ( ORA-02019)
Hi thanks for your mail. Dick , I checked out , both db's are 8.1.7.2 and have same db_domain as world. i also tried out Suzy Vordos' suggestion : selected name,value,ismodified from v$paramater for both databases , but the result for both is same . So change in init.ora or alter system after db startup does not seem likely. regards Ratnesh -Original Message- Sent: Tuesday, July 16, 2002 6:57 PM To: Ratnesh Kumar Singh; Multiple recipients of list ORACLE-L Folks, Since I recently slammed into this unexpected wall I'll pass along the experience. A couple of weeks ago I upgraded an 8.0.5.2.1 instance to 8.1.7 and subsequently at OTS's request to 8.1.7.4. To make a long story short most things with database links worked OK, but a describe across one did not (ORA-02019). The problem ended up being a small error in Oracle's INIT parameter documentation. Back in 8.0.5.2.1, due to a bug introduced, you needed to have DB_DOMAIN=.WORLD or DB_DOMAIN=.VICR.COM. Note the '.' before the domain. Well in 8.1.7, or sometime before that, they fixed the bug, but forgot about those of us who don't always follow the 'yellow brick road'. So if your having problems with ORA-02019's take a second and check this parameter in your init.ora. I changed mine the problems went away. Dick Goulet - Original Message - From: Ratnesh Kumar Singh To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 10, 2002 6:23 PM Subject: dblink problem ( ORA-02019 ) hi i have 3 databases(A,B,X) on 3 diff boxes. i have created 1 dblink each from A to X from B to X. The syntax for dblink creation is exactly same for both dblinks. I am able to query from dblink A-X as select * from user.table@dblinkAX but when i try to query from dblink B-X as select * from user.table@dblinkBX i get the foll error Error: ORA-02019: connection description for remote database not found When i modify my query by suffixing '.world' as select * from [EMAIL PROTECTED] , the query works fine. I have compared the entries in init,sqlnet,tnsnmames,listener files on both A B databases and they are absolutely similar . i was thinking that the domain or globalnames parameters might be diff on A B , but they are absolutely same . any suggestions are most welcome ... thanks in advance ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sequence number generation
It is not possible to avoid sequence number loss . If a user requests a sequence number, then ultimately rolls back that transaction, that sequence number is lost . However you can catch such lost sequence numbers programatically and reuse then. that would require a bit of extra coding. -Original Message- Sent: Thursday, July 18, 2002 10:43 AM To: Multiple recipients of list ORACLE-L Hi Is it possible to avoid sequence number loss in the sequence number generation. if so how? Thanks in Advance Ayyappan.S Dreams translate into thoughts, and thoughts translate into action: Kalam This communication contains information, which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, printing, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ayyappan S INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dblink problem ( ORA-02019 )
yes i cross checked , the 2 sqlnet.ora files are exactly the same. i am using 8.1.7.2.0 for all 3 db's. -Original Message- Bruce (CALBBAY) Sent: Thursday, July 11, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Check the sqlnet.ora on both boxes - are they the same? There was a bug in 1 version where the domain would get selected from the default_domain present in sqlnet.ora HTH, Bruce Reardon -Original Message- Sent: Thursday, 11 July 2002 16:13 Hi many thanks to all who replied to my query. I was able to solve the problem by changing the dblink create stmt. It seems the user had created the links as CREATE PUBLIC DATABASE LINK abc.WORLD CONNECT TO username IDENTIFIED BY pwd USING 'dbname.world' ; I recreated the dblinks as CREATE PUBLIC DATABASE LINK abc CONNECT TO username IDENTIFIED BY pwd USING 'dbname.world' ; However , Kevin's reply has resulted in me finding another problem. When i did a select * from global_name on db A , i get dbname.world . but when i do the same on db B , i get only dbname ??? i crosschecked all init.ora , sqlnet tnsnames params on both A B ,and i did not find any differences. db_domain for both A B is world, global_names is FALSE. db_name, instance_name service_names are consistent across both A B. According to oradocs , global_name is a view for the init.ora param GLOBAL_NAME. Why is this diff coming and how do i resolve it. thanks again ratnesh -Original Message- Sent: Wednesday, July 10, 2002 10:23 PM I had this problem in the past at a site where we were switching from .world to named domains. I can't remember exactly where I found it but I remember that the method Oracle used to determining the dblink domain was funky. On both databases, check select * from global_name and compare the result to select * from dba_db_links. It seems to me that the domain extension on the global_name was used as the default extension when creating links. Caused me no end of heartburn -- nothing I couldn't work around, but more work arounds than I was happy with. Hope my memory is sufficient since I don't have the problem at my current site. Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Wednesday, July 10, 2002 5:53 AM hi i have 3 databases(A,B,X) on 3 diff boxes. i have created 1 dblink each from A to X from B to X. The syntax for dblink creation is exactly same for both dblinks. I am able to query from dblink A-X as select * from user.table@dblinkAX but when i try to query from dblink B-X as select * from user.table@dblinkBX i get the foll error Error: ORA-02019: connection description for remote database not found When i modify my query by suffixing '.world' as select * from [EMAIL PROTECTED] , the query works fine. I have compared the entries in init,sqlnet,tnsnmames,listener files on both A B databases and they are absolutely similar . i was thinking that the domain or globalnames parameters might be diff on A B , but they are absolutely same . any suggestions are most welcome ... thanks in advance ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
dblink problem ( ORA-02019 )
hi i have 3 databases(A,B,X) on 3 diff boxes. i have created 1 dblink each from A to X from B to X. The syntax for dblink creation is exactly same for both dblinks. I am able to query from dblink A-X as select * from user.table@dblinkAX but when i try to query from dblink B-X as select * from user.table@dblinkBX i get the foll error Error: ORA-02019: connection description for remote database not found When i modify my query by suffixing '.world' as select * from [EMAIL PROTECTED] , the query works fine. I have compared the entries in init,sqlnet,tnsnmames,listener files on both A B databases and they are absolutely similar . i was thinking that the domain or globalnames parameters might be diff on A B , but they are absolutely same . any suggestions are most welcome ... thanks in advance ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dblink problem ( ORA-02019 )
Hi many thanks to all who replied to my query. I was able to solve the problem by changing the dblink create stmt. It seems the user had created the links as CREATE PUBLIC DATABASE LINK abc.WORLD CONNECT TO username IDENTIFIED BY pwd USING 'dbname.world' ; I recreated the dblinks as CREATE PUBLIC DATABASE LINK abc CONNECT TO username IDENTIFIED BY pwd USING 'dbname.world' ; However , Kevin's reply has resulted in me finding another problem. When i did a select * from global_name on db A , i get dbname.world . but when i do the same on db B , i get only dbname ??? i crosschecked all init.ora , sqlnet tnsnames params on both A B ,and i did not find any differences. db_domain for both A B is world, global_names is FALSE. db_name, instance_name service_names are consistent across both A B. According to oradocs , global_name is a view for the init.ora param GLOBAL_NAME. Why is this diff coming and how do i resolve it. thanks again ratnesh -Original Message- Sent: Wednesday, July 10, 2002 10:23 PM To: Multiple recipients of list ORACLE-L I had this problem in the past at a site where we were switching from .world to named domains. I can't remember exactly where I found it but I remember that the method Oracle used to determining the dblink domain was funky. On both databases, check select * from global_name and compare the result to select * from dba_db_links. It seems to me that the domain extension on the global_name was used as the default extension when creating links. Caused me no end of heartburn -- nothing I couldn't work around, but more work arounds than I was happy with. Hope my memory is sufficient since I don't have the problem at my current site. Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Wednesday, July 10, 2002 5:53 AM To: Multiple recipients of list ORACLE-L hi i have 3 databases(A,B,X) on 3 diff boxes. i have created 1 dblink each from A to X from B to X. The syntax for dblink creation is exactly same for both dblinks. I am able to query from dblink A-X as select * from user.table@dblinkAX but when i try to query from dblink B-X as select * from user.table@dblinkBX i get the foll error Error: ORA-02019: connection description for remote database not found When i modify my query by suffixing '.world' as select * from [EMAIL PROTECTED] , the query works fine. I have compared the entries in init,sqlnet,tnsnmames,listener files on both A B databases and they are absolutely similar . i was thinking that the domain or globalnames parameters might be diff on A B , but they are absolutely same . any suggestions are most welcome ... thanks in advance ratnesh --- Ratnesh Kumar Singh Sr. Software Engineer Patni Computer Systems Ltd TTC Mahape , Navi Mumbai Work : (91 22) 7611090/110/128/350 Ext. 2107 Home : (91 22) 8662162 http://www.patni.com World-Wide Partnerships. World-Class Solutions. --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Optimizer and function based index
Oracle function based indexes do not work if OR clause is used .This is mentioned in the oracle docs. I think if u use union clause it may work out . try and see... --- Seefelt, Beth [EMAIL PROTECTED] wrote: Hi everybody, I'm hoping someone can help me understand this. I have this query - SELECT ORDERID FROM WWW SHOPCART WHERE UPPER(MEMBERNAME) ='19891' ; I added an index on the column UPPER(MEMBERNAME) and it works great. I have another query - SELECT ORDERID FROM WWW SHOPCART WHERE UPPER(MEMBERNAME) ='19891' OR UPPER(MEMBERNAME)='198915'; and this query will not use the index at all. I can't understand why the optimizer won't choose to use the function based index when there is an OR clause. I've tried the same query with just MEMBERNAME= and using an index on MEMBERNAME, and the optimizer will use the index if I OR my criteria together. Any ideas? Thanks, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).