39

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 current user has access to USER_DB_LINKS - All DB links owned by current user See more in Oracle documentation


6

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

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.


3

I think you are approaching this from the wrong end. A database link includes the user ID and password to connect to the remote server; if the user ID does not exists on the remote server or is not allowed to connect, the problem does not exist. If you don't want certain users to run queries against your data warehouse, don't authorize them there. If you ...


3

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. The ...


3

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

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


2

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


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 ...


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

You can create one table for saving GamesType as with two column GameId(primary Key) and GameName and in user preference table you can have as Userid &GameId where GameId will be the foreign Key for this table , Let say user has played 5 games, so you can make 5 entry in table user_preference_table This can be an efficient way to map Many to Many ...


2

That is not how you connect to another database through database links. Database links require SQL*Net connectivity, not HTTP(S). First you need to enable access, then you can connect using the address of the compute node and the service name for your database. http://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBI/GUID-372D4233-9793-47EC-8DFC-AFD6CF32866C....


2

It is not DATABASE LINK or DATABASE LINKS, it is DB_LINK. You can find the valid values in DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS.


2

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement: create table blah2 ( ctascolumn1 not null, ctascolumn2 null ) as select col1, col2 from blah;


2

Answer provided by John Eisbrener in a comment The effective equivalent is a Database Link. Sadly there's not an identical equivalent to what OPENROWSET does in one Oracle command or statement. You have to declare the db link first before accessing it. If you don't want to edit a tnsnames.ora file, you can create the dblink using an in-line statement, ...


1

Don't get hung-up on "cloud" vs "local" - what you're doing is establishing a TNS network link between two databases. It is nothing more than that. In order to do this, you will need to have the appropriate tns entries configured in your tnsnames.ora (unless you are using LDAP or you pass in the entire connect string). If you don't know how to do this, the ...


1

If the foreign key constraints don't point to any other table inside of originating database (ie, if SQL Server doesn't point to SQL Server, etc.), then I would drop those foreign keys. They aren't useful. If they do point to other tables, then perhaps you do have to copy what data is needed just to satisfy the constraint. (Ugh). Either that or re-consider ...


1

If you don't want a user to create a database link, don't grant them the CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK privileges. My guess is that you've granted the developers a role that you didn't realize had that privilege-- the CONNECT role in at least some versions of Oracle includes CREATE DATABASE LINK. I think that's been removed in more ...


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

Maybe you can create a synonym for the remote package and switch it out when you want to point to a different one? SQL> execute dbms_workload_repository.create_snapshot@test; PL/SQL procedure successfully completed. SQL> create synonym testp for dbms_workload_repository@test; Synonym created. SQL> execute testp.create_snapshot; PL/SQL ...


1

Changing each call of a component of the DBMS_HS_PASSTHROUGH to a dynamic sql statement (EXECUTE IMMEDIATE) is a possibility but I would prefer the following approach. For tables and views of the remote database one creates views on the local database that wraps this object. For packages, functions and procedures one creates packages, functions and ...


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

Foreign keys can't be connected to non-local objects - they have to reference local tables.


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

We learned that in rare circumstances a distributed transaction fails and leaves some kind of "half lock" on one side of the distributed transaction. Because the other side was gone it never resolved. At least one of the common tools that looks up locks didn't see these locks. However, we were able to find them (I think we queried v$lock, I don't remember, ...


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