O9i: MERGE tables across database links
Hi, I am trying to merge data from two tables (over two servers, using dblink). Approx 40,000 records in each. Server1(source) and Server2(dest) On Server1, db-link dblink1 points to Server2 On Server2, db-link dblink1 points to Server1 Schema names are same on the two servers. PULL Data Now, when i am on destination server (Server2), then MERGE INTO myemp D USING (SELECT * FROM [EMAIL PROTECTED]) S ON (D.emp_id = S.emp_id) WHEN MATCHED THEN UPDATE SET FIRST_NAME = S.FIRST_NAME, MIDDLE_NAME = S.MIDDLE_NAME, LAST_NAME = S.LAST_NAME, LAST_UPDATION_DATE = sysdate WHEN NOT MATCHED THEN INSERT (EMP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, LAST_UPDATION_DATE) VALUES (S.EMP_ID, S.FIRST_NAME, S.MIDDLE_NAME, S.LAST_NAME, sysdate); works fine... but if i am on source server (Server1), then PUSH data MERGE INTO [EMAIL PROTECTED] D USING (SELECT * FROM myemp) S ON (D.emp_id = S.emp_id) WHEN MATCHED THEN UPDATE SET FIRST_NAME = S.FIRST_NAME, MIDDLE_NAME = S.MIDDLE_NAME, LAST_NAME = S.LAST_NAME, LAST_UPDATION_DATE = sysdate WHEN NOT MATCHED THEN INSERT (EMP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, LAST_UPDATION_DATE) VALUES (S.EMP_ID, S.FIRST_NAME, S.MIDDLE_NAME, S.LAST_NAME, sysdate); produces following error - The following error has occurred: ORA-01008: not all variables bound ORA-02063: preceding line from DBLINK1 Is it that for MERGE to work, data is MERGED into local table and we cannot execute MERGE on a remote table through db-link? I have to do loads of validation and pre-processing on my server1 and when all data is updated in myemp, then it is to be copied over to [EMAIL PROTECTED] Replication is not to be used, have to work within the boundaries assigned. Other waye round, i'll have to create a wrapper sql script using sql*plus connect to connect to server2 and then calling MERGE (PULLING data) from there instead of PUSHING the updated data from server1. Is there any other way out? __ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Testing database links
Sorry for the delay in replying; something more urgent popped up. Nope, the x$uganco doesn't show the db_links opened by other sessions. The inst_id field looks like it would be useful only in Parallel Server environments. Any other ideas? I can't believe Oracle doesn't keep track of open database links. There must be some x$ view for this. How to find it out? Thanks regards, Charu. -Original Message- Allan Sent: Monday, February 24, 2003 4:45 PM To: Multiple recipients of list ORACLE-L There is an underlying x$table named x$uganco that contains a column named inst_id which is being filtered in the view_definition for V$DBLINK as found in V$FIXED_VIEW_DEFINITION. Selecting from the x$uganco should do the trick. Mind you there are no rows in there when the links are not active so this may be a real problem for you in terms of capturing all the links. Allan -Original Message- Sent: Monday, February 24, 2003 6:34 AM To: Multiple recipients of list ORACLE-L I think I spoke too soon. The v$dblink view shows the db_links opened by the current session only. I want to be able to find out the db_links opened by all current sessions and the sids for the sessions. This way I can monitor all the application instances that opened the db_link and those that didn't close it. Thanks regards, Charu. -Original Message- Sent: Friday, February 21, 2003 5:19 PM To: '[EMAIL PROTECTED]' Darn!! I had taken a hasty look at 'Oracle 8i reference', before posting the query. Not my day today. Thanks Allan. Regards, Charu -Original Message- Allan Sent: Friday, February 21, 2003 5:04 PM To: Multiple recipients of list ORACLE-L V$dblink -Original Message- Sent: Friday, February 21, 2003 7:04 AM To: Multiple recipients of list ORACLE-L Dear Listers, Oracle 8i HP-UX11. We have a database link with a remote database which is accessed from the application code. In the application code, a call is made to the 'dbms_session.close_database_link' procedure (that is what they claim!!). We want to track the call to the database link and the subsequent closure. We don't have any access to the remote system to check the remote session being created and closed. Is there any way (dynamic performance view etc.) which would show the database link being in use and closed again on the local database itself? Thanks regards, Charu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
RE: Testing database links
Sorry, no more ideas. Allan -Original Message- Sent: Wednesday, February 26, 2003 6:19 AM To: Multiple recipients of list ORACLE-L Sorry for the delay in replying; something more urgent popped up. Nope, the x$uganco doesn't show the db_links opened by other sessions. The inst_id field looks like it would be useful only in Parallel Server environments. Any other ideas? I can't believe Oracle doesn't keep track of open database links. There must be some x$ view for this. How to find it out? Thanks regards, Charu. -Original Message- Allan Sent: Monday, February 24, 2003 4:45 PM To: Multiple recipients of list ORACLE-L There is an underlying x$table named x$uganco that contains a column named inst_id which is being filtered in the view_definition for V$DBLINK as found in V$FIXED_VIEW_DEFINITION. Selecting from the x$uganco should do the trick. Mind you there are no rows in there when the links are not active so this may be a real problem for you in terms of capturing all the links. Allan -Original Message- Sent: Monday, February 24, 2003 6:34 AM To: Multiple recipients of list ORACLE-L I think I spoke too soon. The v$dblink view shows the db_links opened by the current session only. I want to be able to find out the db_links opened by all current sessions and the sids for the sessions. This way I can monitor all the application instances that opened the db_link and those that didn't close it. Thanks regards, Charu. -Original Message- Sent: Friday, February 21, 2003 5:19 PM To: '[EMAIL PROTECTED]' Darn!! I had taken a hasty look at 'Oracle 8i reference', before posting the query. Not my day today. Thanks Allan. Regards, Charu -Original Message- Allan Sent: Friday, February 21, 2003 5:04 PM To: Multiple recipients of list ORACLE-L V$dblink -Original Message- Sent: Friday, February 21, 2003 7:04 AM To: Multiple recipients of list ORACLE-L Dear Listers, Oracle 8i HP-UX11. We have a database link with a remote database which is accessed from the application code. In the application code, a call is made to the 'dbms_session.close_database_link' procedure (that is what they claim!!). We want to track the call to the database link and the subsequent closure. We don't have any access to the remote system to check the remote session being created and closed. Is there any way (dynamic performance view etc.) which would show the database link being in use and closed again on the local database itself? Thanks regards, Charu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET
RE: Testing database links
I think I spoke too soon. The v$dblink view shows the db_links opened by the current session only. I want to be able to find out the db_links opened by all current sessions and the sids for the sessions. This way I can monitor all the application instances that opened the db_link and those that didn't close it. Thanks regards, Charu. -Original Message- Sent: Friday, February 21, 2003 5:19 PM To: '[EMAIL PROTECTED]' Darn!! I had taken a hasty look at 'Oracle 8i reference', before posting the query. Not my day today. Thanks Allan. Regards, Charu -Original Message- Allan Sent: Friday, February 21, 2003 5:04 PM To: Multiple recipients of list ORACLE-L V$dblink -Original Message- Sent: Friday, February 21, 2003 7:04 AM To: Multiple recipients of list ORACLE-L Dear Listers, Oracle 8i HP-UX11. We have a database link with a remote database which is accessed from the application code. In the application code, a call is made to the 'dbms_session.close_database_link' procedure (that is what they claim!!). We want to track the call to the database link and the subsequent closure. We don't have any access to the remote system to check the remote session being created and closed. Is there any way (dynamic performance view etc.) which would show the database link being in use and closed again on the local database itself? Thanks regards, Charu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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: Testing database links
There is an underlying x$table named x$uganco that contains a column named inst_id which is being filtered in the view_definition for V$DBLINK as found in V$FIXED_VIEW_DEFINITION. Selecting from the x$uganco should do the trick. Mind you there are no rows in there when the links are not active so this may be a real problem for you in terms of capturing all the links. Allan -Original Message- Sent: Monday, February 24, 2003 6:34 AM To: Multiple recipients of list ORACLE-L I think I spoke too soon. The v$dblink view shows the db_links opened by the current session only. I want to be able to find out the db_links opened by all current sessions and the sids for the sessions. This way I can monitor all the application instances that opened the db_link and those that didn't close it. Thanks regards, Charu. -Original Message- Sent: Friday, February 21, 2003 5:19 PM To: '[EMAIL PROTECTED]' Darn!! I had taken a hasty look at 'Oracle 8i reference', before posting the query. Not my day today. Thanks Allan. Regards, Charu -Original Message- Allan Sent: Friday, February 21, 2003 5:04 PM To: Multiple recipients of list ORACLE-L V$dblink -Original Message- Sent: Friday, February 21, 2003 7:04 AM To: Multiple recipients of list ORACLE-L Dear Listers, Oracle 8i HP-UX11. We have a database link with a remote database which is accessed from the application code. In the application code, a call is made to the 'dbms_session.close_database_link' procedure (that is what they claim!!). We want to track the call to the database link and the subsequent closure. We don't have any access to the remote system to check the remote session being created and closed. Is there any way (dynamic performance view etc.) which would show the database link being in use and closed again on the local database itself? Thanks regards, Charu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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: Nelson,
Testing database links
Dear Listers, Oracle 8i HP-UX11. We have a database link with a remote database which is accessed from the application code. In the application code, a call is made to the 'dbms_session.close_database_link' procedure (that is what they claim!!). We want to track the call to the database link and the subsequent closure. We don't have any access to the remote system to check the remote session being created and closed. Is there any way (dynamic performance view etc.) which would show the database link being in use and closed again on the local database itself? Thanks regards, Charu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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: Testing database links
Charu, The view V$DBLINK can show you if the link is in use. select open_cursors, in_transaction from v$dblonk where db_link = 'mylink' HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, February 21, 2003 8:03 AM Dear Listers, Oracle 8i HP-UX11. We have a database link with a remote database which is accessed from the application code. In the application code, a call is made to the 'dbms_session.close_database_link' procedure (that is what they claim!!). We want to track the call to the database link and the subsequent closure. We don't have any access to the remote system to check the remote session being created and closed. Is there any way (dynamic performance view etc.) which would show the database link being in use and closed again on the local database itself? Thanks regards, Charu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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).
RE: Testing database links
V$dblink -Original Message- Sent: Friday, February 21, 2003 7:04 AM To: Multiple recipients of list ORACLE-L Dear Listers, Oracle 8i HP-UX11. We have a database link with a remote database which is accessed from the application code. In the application code, a call is made to the 'dbms_session.close_database_link' procedure (that is what they claim!!). We want to track the call to the database link and the subsequent closure. We don't have any access to the remote system to check the remote session being created and closed. Is there any way (dynamic performance view etc.) which would show the database link being in use and closed again on the local database itself? Thanks regards, Charu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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: Testing database links
Darn!! I had taken a hasty look at 'Oracle 8i reference', before posting the query. Not my day today. Thanks Allan. Regards, Charu -Original Message- Allan Sent: Friday, February 21, 2003 5:04 PM To: Multiple recipients of list ORACLE-L V$dblink -Original Message- Sent: Friday, February 21, 2003 7:04 AM To: Multiple recipients of list ORACLE-L Dear Listers, Oracle 8i HP-UX11. We have a database link with a remote database which is accessed from the application code. In the application code, a call is made to the 'dbms_session.close_database_link' procedure (that is what they claim!!). We want to track the call to the database link and the subsequent closure. We don't have any access to the remote system to check the remote session being created and closed. Is there any way (dynamic performance view etc.) which would show the database link being in use and closed again on the local database itself? Thanks regards, Charu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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).
Function use across database links
Title: Function use across database links List, Oracle documentation indicates the Oracle database server breaks the distributed query into a corresponding number of remote queries which it then sends to the remote node for execution. The remote node executes the queries and sends the results back to the local node. Therefore, I ran a statement and explained it. The results show SELECT STATEMENT Cost=8 REMOTE The question I have, is what if the sql is stored in a function that doesn't exist on the remote database. Will the Oracle server use the local function? If so, does it pull the data from the table on the remote server across the network. I checked orafaq.com, but didn't find any hits on this subject, and was unable to locate any other documentation regarding function usage and Database links. TIA MCG
RE: Function use across database links
Title: Function use across database links Melissa, I think the answer to your question is yes. First, if you think about it, it does not matter where the sql statement exists - either in the local or remote database - both would work as long as security is resolved and you have access to the remote table. For practicle purposes, I put the function on the remote server, and create a function on the local server that simply calls the remote one. I do this for two reasons: To protect the local applications from having to include the database link name in any of their applications - in case I need to change it for some reason And more importantly, I've found that it is best to perform the "where" clause on the machine where the data resides - it is just much faster than joining tables from a remote db across a db link. Maybe this functionality has improved with 9i, but with 8i, it is just too slow. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message-From: Godlewski, Melissa [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 17, 2002 11:25 AMTo: Multiple recipients of list ORACLE-LSubject: Function use across database links List, Oracle documentation indicates the Oracle database server breaks the distributed query into a corresponding number of remote queries which it then sends to the remote node for execution. The remote node executes the queries and sends the results back to the local node. Therefore, I ran a statement and explained it. The results show SELECT STATEMENT Cost=8 REMOTE The question I have, is what if the sql is stored in a function that doesn't exist on the remote database. Will the Oracle server use the local function? If so, does it pull the data from the table on the remote server across the network. I checked orafaq.com, but didn't find any hits on this subject, and was unable to locate any other documentation regarding function usage and Database links. TIA MCG
FW: Database Links standards
Title:
-Original Message-From: Henry Poras Sent:
Monday, July 09, 2001 4:52 PMTo:
'[EMAIL PROTECTED]'Subject: RE: Database Links
standards
Here is our stuff. Some of this arose because for a long time
each application team worked as an independent entity.Up to this
point, most of our database links have been private (owned by a particular
schema) and connected via a given user/password determined during the creation
of the link. I would like to move to public database links without embedded
usernames and passwords. This will entail slightly more administration on the
remote database (creating a user) but allow greater security and
flexibility.The difficulties with database links as we have been using
them are· Private database
links are an administrative nightmare (for exports, compiling objects,
.)· The user defined in the
link often was the schema owner of most objects in the remote database. The
security of the remote database was thus dependent on the security of the local
database.· If the remote
database needs to change the user password, there is no good way to know which
applications and database links will be affected.Points 2 and 3 can be
avoided by creating a new user on the remote database, but since all that is
needed when creating the link is a single entry in the local database, this is
often not done. (The phone call can be "can you tell me the username and
password on your database?" "sure")The advantages with the newer method
are· Better communication
between the local and remote databases is
necessary· The remote
database will need to create a user to match the username on the local database.
Thus it can easily control the rights assigned to this user. The remote database
is in control of its own security. The username chosen should reflect the source
application which will help in times of password changes.We no longer have
to worry about private database links.
HTH
Henry-Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Friday,
July 06, 2001 4:51 PMTo: Multiple recipients of list ORACLE-LSubject:
Database Links standardsWe came up with the below standards with
respect to database links (heavilyused in our
environment). The result has been a billion complaints byour
developers, stating that the standards are unnecessarily complex.
I'mcurious as to what others might think, if they *are* indeed too
complex.Also what kind of naming/adminstrative standards that other shops
employ.1. To access remote data across a DB LINK, the
standard implementation consists of four pieces:a) A private database link
owned by the schema owner of the table objects being
accessed. The CONNECT TO and IDENTIFIED BY clauses are
required - Naming Standard: {remote
schema}_{database name}b) A standard PUBLIC SYNONYM created for the remote
table being accessed across the link. - Naming
Standard: {remote schema}_{remote table}. The purpose of
this synonym is both to allow portability and also
to provide documentation of the remote
connection.c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above
(1.b). - Naming Standard: {remote
table}_VWd) A PUBLIC SYNONYM on the VIEW above(1.c.) -
Naming Standard: {remote table}Thanks,Jeff T[EMAIL PROTECTED]
Database Links standards
Title: Database Links standards
We came up with the below standards with respect to database links (heavily
used in our environment). The result has been a billion complaints by
our developers, stating that the standards are unnecessarily complex. I'm
curious as to what others might think, if they *are* indeed too complex.
Also what kind of naming/adminstrative standards that other shops employ.
1. To access remote data across a DB LINK, the
standard implementation consists of four pieces:
a) A private database link owned by the schema owner of the table
objects being accessed. The CONNECT TO and IDENTIFIED BY clauses are
required
- Naming Standard: {remote schema}_{database name}
b) A standard PUBLIC SYNONYM created for the remote table being
accessed across the link.
- Naming Standard: {remote schema}_{remote table}. The purpose of this
synonym is both to allow portability and also to provide documentation
of the remote connection.
c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above (1.b).
- Naming Standard: {remote table}_VW
d) A PUBLIC SYNONYM on the VIEW above(1.c.)
- Naming Standard: {remote table}
Thanks,
Jeff T
[EMAIL PROTECTED]
RE: Database Links standards
Title: Database Links standards
Jeff,
Looks
ok, except that I don't think you need step b) - the PUBLIC SYNONYM for the
remote table.
I
always create a private synonym within my DBA account (which happens to own the
tables in the local database) and then create the VIEW and a PUBLIC SYNONYM on
the view to hide everything from the user. The view will say
:
select
* from table@dblink.
The
developers then get access to the view only - never to the base db-linked
tables.
Your
approaches works for me, though. Tell the developers to use it as
delivered and to like it! :)
hope
this helps.
Tom Mercadante Oracle Certified Professional
-Original Message-From: Thomas Jeff
[mailto:[EMAIL PROTECTED]]Sent: Friday, July 06, 2001 4:51
PMTo: Multiple recipients of list ORACLE-LSubject:
Database Links standards
We came up with the below standards with respect to
database links (heavily used in our
environment). The result has been a billion complaints by
our developers, stating that the standards are unnecessarily
complex. I'm curious as to what others might
think, if they *are* indeed too complex. Also what
kind of naming/adminstrative standards that other shops employ.
1. To access remote data across a DB LINK, the
standard implementation consists of four
pieces:
a) A private database link owned by the schema owner of the
table objects being accessed. The
CONNECT TO and IDENTIFIED BY clauses are
required - Naming
Standard: {remote schema}_{database name}
b) A standard PUBLIC SYNONYM created for the remote table
being accessed across the link.
- Naming Standard: {remote
schema}_{remote table}. The purpose of this synonym is both to allow portability and also
to provide documentation of
the remote connection.
c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above
(1.b). - Naming
Standard: {remote table}_VW
d) A PUBLIC SYNONYM on the VIEW above(1.c.) - Naming Standard: {remote table}
Thanks, Jeff T [EMAIL PROTECTED]
RE: Database Links standards
Title: Database Links standards
this
is fine.
your
problem may be in marketing the technology, not the technology itself.
-Original Message-From: Thomas Jeff
[mailto:[EMAIL PROTECTED]]Sent: Friday, July 06, 2001 4:51
PMTo: Multiple recipients of list ORACLE-LSubject:
Database Links standards
We came up with the below standards with respect to
database links (heavily used in our
environment). The result has been a billion complaints by
our developers, stating that the standards are unnecessarily
complex. I'm curious as to what others might
think, if they *are* indeed too complex. Also what
kind of naming/adminstrative standards that other shops employ.
1. To access remote data across a DB LINK, the
standard implementation consists of four
pieces:
a) A private database link owned by the schema owner of the
table objects being accessed. The
CONNECT TO and IDENTIFIED BY clauses are
required - Naming
Standard: {remote schema}_{database name}
b) A standard PUBLIC SYNONYM created for the remote table
being accessed across the link.
- Naming Standard: {remote
schema}_{remote table}. The purpose of this synonym is both to allow portability and also
to provide documentation of
the remote connection.
c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above
(1.b). - Naming
Standard: {remote table}_VW
d) A PUBLIC SYNONYM on the VIEW above(1.c.) - Naming Standard: {remote table}
Thanks, Jeff T [EMAIL PROTECTED]
Re: Database Links standards
Looks good to me, except for step b.
You don't really need that.
I've used the same setup several
times successfully.
Jared
On Friday 06 July 2001 13:51, Thomas Jeff wrote:
We came up with the below standards with respect to database links
(heavily
used in our environment). The result has been a billion complaints by
our developers, stating that the standards are unnecessarily complex. I'm
curious as to what others might think, if they *are* indeed too complex.
Also what kind of naming/adminstrative standards that other shops employ.
1. To access remote data across a DB LINK, the
standard implementation consists of four pieces:
a) A private database link owned by the schema owner of the table
objects being accessed. The CONNECT TO and IDENTIFIED BY clauses are
required
- Naming Standard: {remote schema}_{database name}
b) A standard PUBLIC SYNONYM created for the remote table being
accessed across the link.
- Naming Standard: {remote schema}_{remote table}. The purpose of this
synonym is both to allow portability and also to provide documentation
of the remote connection.
c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above (1.b).
- Naming Standard: {remote table}_VW
d) A PUBLIC SYNONYM on the VIEW above(1.c.)
- Naming Standard: {remote table}
Thanks,
Jeff T
[EMAIL PROTECTED]
Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
Content-Transfer-Encoding: 7bit
Content-Description:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Database Links
Can I give a suggestion , how about create a unique user which will have all the link db , so much so that we will know where is the link come from , and it seen easily to manage ? Will it be a solution to this ? -Original Message- Sent: Thursday, May 31, 2001 2:46 PM To: Multiple recipients of list ORACLE-L Tracy, Allowing developers to muck around in your production system is not generally a good idea. If you create db links for them, that's what they will be doing. In addition, have you ever managed an environment like that? I have and it's not pretty. How will you administer the privileges? Will it be a public database link ( dangerous ) or lots of private database links (messy )? Will the connection be to their own account on the production system ( that you must create ) or an account that has all the needed privs? Managing this is something of a headache. And when your developers do a cartesian join on your production database, you will be scrambling to determine which session is causing it, and determining if you can safely kill it. etc, etc, etc. :) Jared On Wednesday 30 May 2001 16:09, Tracy Rahmlow wrote: We have several large look-up tables that we use in development as well as in production environments. The data is the same in both environments. I am looking for some comments regarding whether or not we store duplicate data in each environment or should we allow the development users to access the table in production through a database link. Below, I have listed some issues with both of these processes and am looking for further input. Thanks Duplicate table in production and development (either through export/import or snapshots): Cons additional storage is need process needed to keep tables in sync Pros reduced network traffic Access table in production through a database link in development: Cons additional network traffic possibility of poorly tuned adhoc sql executing in a production environment Pros only one copy of table do not need an ongoing process to keep the tables in sync -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raymond Lee Meng Hong 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: Database Links
Tracy, I have a similar deal going on here. If you are on version 8.1.x and the lookup tables are large, you can use Materialized Views, and since they are lookup tables that shouldn't change much, you should only have to refresh them every once in a while. If they are really small tables, however, you can just use database links. What I do is create a view across a database link on top of the lookup tables. I then create a synonym on the view that is the same as the lookup tables' name. The users, then, have no idea the tables don't exist locally. Jim Jim Hawkins Lead SAPR/3 Oracle DBA MEMC Electronic Materials, Inc. St. Louis, MO (636) 474-7832 [EMAIL PROTECTED] (work) [EMAIL PROTECTED] (personal) -Original Message- Rahmlow Sent: Wednesday, May 30, 2001 6:10 PM To: Multiple recipients of list ORACLE-L We have several large look-up tables that we use in development as well as in production environments. The data is the same in both environments. I am looking for some comments regarding whether or not we store duplicate data in each environment or should we allow the development users to access the table in production through a database link. Below, I have listed some issues with both of these processes and am looking for further input. Thanks Duplicate table in production and development (either through export/import or snapshots): Cons additional storage is need process needed to keep tables in sync Pros reduced network traffic Access table in production through a database link in development: Cons additional network traffic possibility of poorly tuned adhoc sql executing in a production environment Pros only one copy of table do not need an ongoing process to keep the tables in sync -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Hawkins Family 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).
Database Links
We have several large look-up tables that we use in development as well as in production environments. The data is the same in both environments. I am looking for some comments regarding whether or not we store duplicate data in each environment or should we allow the development users to access the table in production through a database link. Below, I have listed some issues with both of these processes and am looking for further input. Thanks Duplicate table in production and development (either through export/import or snapshots): Cons additional storage is need process needed to keep tables in sync Pros reduced network traffic Access table in production through a database link in development: Cons additional network traffic possibility of poorly tuned adhoc sql executing in a production environment Pros only one copy of table do not need an ongoing process to keep the tables in sync -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Database Links
We have several large look-up tables that we use in development as well as in production environments. The data is the same Developers shouldn't work against production tables. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Database Links
Tracy, Allowing developers to muck around in your production system is not generally a good idea. If you create db links for them, that's what they will be doing. In addition, have you ever managed an environment like that? I have and it's not pretty. How will you administer the privileges? Will it be a public database link ( dangerous ) or lots of private database links (messy )? Will the connection be to their own account on the production system ( that you must create ) or an account that has all the needed privs? Managing this is something of a headache. And when your developers do a cartesian join on your production database, you will be scrambling to determine which session is causing it, and determining if you can safely kill it. etc, etc, etc. :) Jared On Wednesday 30 May 2001 16:09, Tracy Rahmlow wrote: We have several large look-up tables that we use in development as well as in production environments. The data is the same in both environments. I am looking for some comments regarding whether or not we store duplicate data in each environment or should we allow the development users to access the table in production through a database link. Below, I have listed some issues with both of these processes and am looking for further input. Thanks Duplicate table in production and development (either through export/import or snapshots): Cons additional storage is need process needed to keep tables in sync Pros reduced network traffic Access table in production through a database link in development: Cons additional network traffic possibility of poorly tuned adhoc sql executing in a production environment Pros only one copy of table do not need an ongoing process to keep the tables in sync -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: database links
Hi, Millie, Try this location ftp://oracle-ftp.oracle.com/server/ HTH Vadim Gorbounov Oracle DBA -Original Message- Sent: Wednesday, April 11, 2001 10:26 AM To: Multiple recipients of list ORACLE-L I have set the global_names parameter to false on my mainframe database ORS1 and recycled the data base. When I do a select * from global_name, it show the name of ORS1. When I do the same on the unix database dw1, it show the name of DW1.WORLD. When I do select * from dba_db_link on ORS1, it show other host as dw1.world. How can I change my global_name to be ors1.world? What should the SERVICE_NAME parameter be? We don't use this parameter, I tried serveral different names, but it didn't seem to make any difference. Thanks. Millie -Original Message- Sent: Saturday, April 07, 2001 2:00 AM To: Multiple recipients of list ORACLE-L Millie, Check both instances and verify if global_names parameter is switched on. What is the name of your db link? select * from global_name; Is it the same as global_name? I have a problems creating database links when I have global_names parameter set to TRUE. I normally switch global_names=false unless the application uses global_names. I create my database link using the connect description from my tnsnames.ora file for that instance. It normally works. eg. create database link test connect to jdatest identified by jdatest using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = odbms)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test) ) )' ; This normally works for me. DB links are very fussy when it comes to global names. Also the name that is given to your db link when global names are used. HTH Suhen Suhen We are setting up a test Oracle 8i (8.1.7 with OSDI) on our mainframe (os/390 v2.8), we can connect to the database instance (ORS1) from windows NT client, but when we try to connect to it using database links set up from our unix platform, we get ORA-12154 TNS: Could not resolve service name When we try to link from ors1 I get: ORA-02019 Connection Description For Remote Database Not Found. We created a tar with Oracle and waiting to hear from them. Just wondering if anyone on the list has the same problem? Millie Chan Rutgers, The State University of New Jersey Administrative Computing Services 65 Davidson Road Piscataway, NJ 08855 Tel: (732)445-5433 EMAIL: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chan, Millie 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: Suhen Pather 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: Chan, Millie 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: Vadim Gorbounov 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
RE: database links - had problem, hlp me urgent pls
hi Suhen, i too before tried to create a db like millie, i failed now i applied as u suggested and i succeed also in one case. What happened u know, i have 2 machines development machine(V731) and in my PC i have my own database(V815). First time i created a db link from the development machine to my server. This case i suceeded. But again i tried to create another DB link from my server to development machine. i time i got 'Uable to resolve TNS names' like that for the first time and 2nd time also got the error similar to the first one, 3rd time i got the error as like below "ORA-00602: internal programming exception %SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=, PC=, PS=". then i stoped to create the db link. Now the problem is, am not able to connect to my development m/c through any tool like sql, d2k. All cases i got the following error. What is the proble what i did mistake in that, Could any one clear me please, it's very urgent.. thanks in advance.. SQL*Plus: Release 8.0.6.0.0 - Production on Sun Apr 8 13:35:19 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. ERROR: ORA-00602: internal programming exception %SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=, PC=, PS= ERROR: ORA-00602: internal programming exception %SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=, PC=, PS= Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-00602: internal programming exception %SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=, PC=, PS= Connected to: Oracle7 Server Release 7.3.4.3.0 - Production With the distributed option PL/SQL Release 2.3.4.3.0 - Production SQL Regards, Nirmal. -Original Message- From: Suhen Pather [SMTP:[EMAIL PROTECTED]] Sent: Saturday, April 07, 2001 9:00 AM To: Multiple recipients of list ORACLE-L Subject: RE: database links Millie, Check both instances and verify if global_names parameter is switched on. What is the name of your db link? select * from global_name; Is it the same as global_name? I have a problems creating database links when I have global_names parameter set to TRUE. I normally switch global_names=false unless the application uses global_names. I create my database link using the connect description from my tnsnames.ora file for that instance. It normally works. eg. create database link test connect to jdatest identified by jdatest using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = odbms)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test) ) )' ; This normally works for me. DB links are very fussy when it comes to global names. Also the name that is given to your db link when global names are used. HTH Suhen Suhen We are setting up a test Oracle 8i (8.1.7 with OSDI) on our mainframe (os/390 v2.8), we can connect to the database instance (ORS1) from windows NT client, but when we try to connect to it using database links set up from our unix platform, we get ORA-12154 TNS: Could not resolve service name When we try to link from ors1 I get: ORA-02019 Connection Description For Remote Database Not Found. We created a tar with Oracle and waiting to hear from them. Just wondering if anyone on the list has the same problem? Millie Chan Rutgers, The State University of New Jersey Administrative Computing Services 65 Davidson Road Piscataway, NJ 08855 Tel: (732)445-5433 EMAIL: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chan, Millie 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: Suhen Pather 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
RE: database links
hi millie, you must verify that the host name is resolved in the unix platform(DNS), otherwise put the physical adress in the description of the host. Best Regards, Nabila Mekkaoui DBA -Message d'origine- De : Chan, Millie [mailto:[EMAIL PROTECTED]] Envoy : vendredi 6 avril 2001 19:51 : Multiple recipients of list ORACLE-L Objet : database links We are setting up a test Oracle 8i (8.1.7 with OSDI) on our mainframe (os/390 v2.8), we can connect to the database instance (ORS1) from windows NT client, but when we try to connect to it using database links set up from our unix platform, we get ORA-12154 TNS: Could not resolve service name When we try to link from ors1 I get: ORA-02019 Connection Description For Remote Database Not Found. We created a tar with Oracle and waiting to hear from them. Just wondering if anyone on the list has the same problem? Millie Chan Rutgers, The State University of New Jersey Administrative Computing Services 65 Davidson Road Piscataway, NJ 08855 Tel: (732)445-5433 EMAIL: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chan, Millie 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: INF/MEKKAOUI 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).
database links
We are setting up a test Oracle 8i (8.1.7 with OSDI) on our mainframe (os/390 v2.8), we can connect to the database instance (ORS1) from windows NT client, but when we try to connect to it using database links set up from our unix platform, we get ORA-12154 TNS: Could not resolve service name When we try to link from ors1 I get: ORA-02019 Connection Description For Remote Database Not Found. We created a tar with Oracle and waiting to hear from them. Just wondering if anyone on the list has the same problem? Millie Chan Rutgers, The State University of New Jersey Administrative Computing Services 65 Davidson Road Piscataway, NJ 08855 Tel: (732)445-5433 EMAIL: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chan, Millie 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: database links
Millie, Check both instances and verify if global_names parameter is switched on. What is the name of your db link? select * from global_name; Is it the same as global_name? I have a problems creating database links when I have global_names parameter set to TRUE. I normally switch global_names=false unless the application uses global_names. I create my database link using the connect description from my tnsnames.ora file for that instance. It normally works. eg. create database link test connect to jdatest identified by jdatest using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = odbms)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test) ) )' ; This normally works for me. DB links are very fussy when it comes to global names. Also the name that is given to your db link when global names are used. HTH Suhen Suhen We are setting up a test Oracle 8i (8.1.7 with OSDI) on our mainframe (os/390 v2.8), we can connect to the database instance (ORS1) from windows NT client, but when we try to connect to it using database links set up from our unix platform, we get ORA-12154 TNS: Could not resolve service name When we try to link from ors1 I get: ORA-02019 Connection Description For Remote Database Not Found. We created a tar with Oracle and waiting to hear from them. Just wondering if anyone on the list has the same problem? Millie Chan Rutgers, The State University of New Jersey Administrative Computing Services 65 Davidson Road Piscataway, NJ 08855 Tel: (732)445-5433 EMAIL: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chan, Millie 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: Suhen Pather 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).
