Oracle DBLink question - linking Oracle tables.

Think DBLINK like a path, you are giving oracle a path, from where oracle can get the data if it find dblink in the statement, when you use DBLINK, you are using local database for only viewing it, every thing is happening on remote database, if the local user have permission then the user can issue dml statement on romote database objects, if you drop dblink then you'll get only the error of connection description for remote database not found, nothing would happen to your remote database.

I don't understand re linking tables.

You can create simple snapshots for your remote database objects using dblink. The same thing if you drop dblink it wont refresh, but your production or remote database snapshot log tables would be getting bigger and bigger.  

That is exactly what I am looking for, but with some questions about how it works.  I will use "linking from" as a label for  the database that will be
accessing the data in the database with the tables I will label "linking to".

1.  Do you have to create the tables in the "linking from " database that match the tables in the "linking to" database?

2.  Is this only a map with data remaining in the "linking to" database?

3.  If sql is executed in the "linking from" on these mapped tables, is the sql pushed down to the "linking to" database for execution.  In other words, will
     the SGA impact be on the "linking to" database.

4.  If the "linking to" database is brought down, will the "linking from" database also need to be brought down or will the mapped tables simply not be accessible?

5.  Is there a means of including the mapped tables in backups on the "linking from" database?

6.  Will Oracle data locking be properly administered to
    transactions initiated by the "linking from" database?

Sorry for the number of questions, but I am attempting to nail down if this is a viable solution as soon as possible.

Thanks

Bill

1.  Do you have to create the tables in the "linking from " database that match the tables in the "linking to" database?

No, you don't have to create any tables in your "linking from" database. Just use the "@linking_to" in your sql statements in the "linking from" database, and it'll work.

2.  Is this only a map with data remaining in the "linking to" database?

The link basically allows you to open a connection to the "linking to" database and include its objects in your queries for "linking from." It maps to "linking to".

3.  If sql is executed in the "linking from" on these mapped tables, is the sql pushed down to the "linking to" database for execution.  In other words, will the SGA impact be on the "linking to" database.

Yes, if you write a query in "linking from" that references one table from "linking to" then basically the "linking from" database will do a select from the "linking to" database and it will affect "linking to" like any other query.

4.  If the "linking to" database is brought down, will the "linking from" database also need to be brought down or will the mapped tables simply not be accessible?

The mapped tables will just be inaccessible. You don't need to bring your "linking from" database down.

5.  Is there a means of including the mapped tables in backups on the "linking from" database?

When you do a full database export it will export your database links. You don't actually create linked tables...just the link. You reference the tables in the "linking to" database in your queries that you run in the "linking from" database.

6.  Will Oracle data locking be properly administered to transactions initiated by the "linking from" database?

The query from the "linking from" database to the "linking to" database will be treated by the "linking to" database just like a normal query, with the Oracle locking mechanisms functioning just like they would if you did a direct query to the database.

Trojanguy1,

Excellent feedback.  I have one further clarifying question.  I plan on using this scenario in relation to an SAP environment which requires defining tables in SAP first.  What I am looking for is to take the table defined in SAP with the same format as table in the "linked to" database and then to link the tables.  A second scenario would be to link to the "linked to" table which would create a virtual table and then go into SAP and build the table.  But either way, I have to have the SAP table point to the linked table.

I hope that makes sense.

Bill

Bill,

Honestly, I'm not too clear on what you're trying to do. I'm not incredibly versed when it comes to SAP, which could be part of the problem. Are you saying that you need to have SAP create a table in SAP based on a table in the "linked from" database, which is referencing a table in the "linked to" database?

Trojanguy1,

In SAP, tables are built through SAP.  One type of SAP table is called a "transparent" table.  When a transparent table is defined in SAP, an exact match is also created at the database level in this case Oracle.

My thought was to allow the Oracle table to be built by SAP but to then link it to a table with the same layout in the "linked to" database.  

A second scenario would be if the linking process acted similar to an MS Access link that created a virtual image of the table in the "linked from" database.  If that was possible, I would create the table in SAP, afterwards drop the Oracle physical table that would be created and then build a link of the same name to the "linked to" table.  

It sounds complicated, but that is what I'm thinking.  I'm currently researching the "how to" for SAP, but still need to nail down how Oracle works.

Thanks for the feedback