Tag Info

Hot answers tagged

16

These views are helpful for working with DB links in Oracle: DBA_DB_LINKS - All DB links defined in the database ALL_DB_LINKS - All DB links the curernt user has access to USER_DB_LINKS - All DB links owned by current user


10

You want to use a Database Link (DBLink). http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htm http://psoug.org/reference/db_link.html -- create tnsnames entry for conn_link conn_link = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = perrito2)(PORT = 1521)) ) (CONNECT_DATA = ...


5

You'll have to setup SQL Profiler to monitor for queries which use the linked server. There's no DMV that you can easily monitor for usage.


4

it looks like a permission issue with the authentication mode you're using against your mysql server. You can test the linked servers using the stored procedure "sp_testlinkedserver" - details here. See if you can take data from your linked server using simple select statement: "select top 10 * from server.database.schema.table". The MS error is for the ...


3

Your syntax for using the database link is incorrect, you've got the link and table identifiers in the wrong order. It should be: select ... from table_name@db.link.name Having too many . in a table name results in an ORA-00933 error, like you're getting.


2

You might be able to look at the dmv sys.dm_exec_sql_text to look at where the linked server name was last used in a sql statement. set transaction isolation level read uncommitted SELECT (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ...


2

There are some restrictions with database links. You can't execute DDL remotely for example. Queries run against remote tables are not processed in exactly the same way as regular tables: The local Oracle Database server breaks the distributed query into a corresponding number of remote queries, which it then sends to the remote nodes for execution. ...


2

The driving_site hint, as suggested by Alex Poole would be an interesting solution to consider/investigate. I'm not sure how/if it works for remote inserts though. If the hint doesn't work, you can use a local temporary table. Insert the data locally into your temporary table. Then INSERT INTO tab@remote FROM temp should perform well.


2

If you want to block all connections from whatever machine B is running on to A whether they are not they are using a database link, you can use the TCP.EXCLUDED_NODES parameter in your sqlnet.ora file. If that is not what you want, you could potentially write a login trigger that tries to deduce whether the connection is via a database link presumably ...


2

Oracle can connect to MySql with an ODBC connector. This is described in some detail here. Also see the official Oracle documentation here. This will work for Oracle 10 and 11g to MySQL 4.1, 5.0, 5.1 or 6.0. There are limitations both in performance and what you can do. My understanding is no distributed transactions no insert as select ..... treat ...


1

I think the only way to do that is to write a UDF in Java. Oracle itself won't be able to connect to a MySQL server.


1

A distributed query cannot be optimized in the same way that local tables can, however, distributed queries are optimized and caches, therefore the vendor is incorrect. You can see this by running something like the following. You will see the execution count rise with each run. SELECT /* 23409268-09389 */ * FROM schema_name.table_name@db_link WHERE ...


1

You could make use of FEDERATED Tables. More on them: HERE. Basically, what you do, is to create a federated table on your local DB, based on the remote table (example in the link). Then you can join them, as they're both local. Everything is pretty straightforward, and explained in the link above.


1

DB links are probably not going to work as a solution. Oracle7 is crazy old. The question's facts are little limited. What types of objects (e.g., tables, stored procs, triggers, etc.) are involved? How much data? That said: As it relates to table data, my solution would be to spool the data to text and access the text via external tables you setup in the ...


1

You can reference a remote table in a procedure or a package. Here's an example with a loopback link: SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 l dual.dummy@loopback%TYPE; 3 BEGIN 4 SELECT * INTO l FROM dual@loopback; 5 dbms_output.put_line(l); 6 END; 7 / X PL/SQL procedure successfully completed I think there are ...


1

Step by step: There is no way to test a dblink other than select a dummy over it A network timeout affects only the running transaction that is currently the dblink. There is no recorded state about the dblink, there is no status like ACTIVE or BROKEN. DBLink creation simply instruct the DB about remote host, port, tns, user and password. Hence drop and ...


1

Since you're using a standard Oracle-to-Oracle database link, Oracle automatically does two-phase commits. If you commit (or rollback) on one system, any changes made as part of the same transaction on the other system will committed (or rolled back) as well. If there is an open transaction on the remote system that has row-level locks, that strongly ...


1

This can be done by conneting to an ODBC data source through a dg4odbc listener. There is a reasonable explanation available here: http://hs2n.wordpress.com/2012/04/03/oracle-create-database-link-to-mysql-database/


1

First off, you should be aware that PostgreSQL 8.3 will soon be going EOL. You should probably use a more current version. In fact, if you did use a more current version (9.1 or later), you could use Foreign Data Wrappers (FDW). Someone has even posted source code for a TDS FDW, which should work for older SQL Server. If you're somehow stuck with ...


1

If you have the option of moving the data into another database I would begin looking at using a star schema. Here is a website that might help you begin research http://www.ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx Star schemas are built specificly for the fast retrieval of large amounts of data and ...


1

SELECT DB_LINK, USERNAME, HOST FROM ALL_DB_LINKS you can try that, perhaps it can help you solve your problem


1

I've just stumbled on this post while encountering the same issue, this link shows how to link up properly. I'm using 5.2 connector and it walks you through each setting you need. http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008


1

No, in Oracle 11.2.0.2 proxy connections cannot use passed authentication in a public database link. Switch to a private database link with specified authentication.



Only top voted, non community-wiki answers of a minimum length are eligible