الانتقال إلى المحتوى

How can i query from 2 database instance


OracleMan

Recommended Posts

Database Links Steps



Global Naming
Oracle enforces the requirement that the database.domain portion of the database link name must match the complete global name of the remote database by setting GLOBAL_NAMES to TRUE in the initialization parameter file initSID.ora.
Example: Local DB is 'SOL3' (Oracle 8.1.6), remote DB is 'SOL1' (Oracle 7.3.4)
# Parameter file initSOL3.ora for Database SOL3
#
### Global Naming
### -------------
# Enforce that a dblink has same name as the DB it connects to

global_names = TRUE
# Parameter file initSOL1.ora for Database SOL1
#
### Global database name is db_name.db_domain
### -----------------------------------------

db_name = SOL1
db_domain = world
Our database link points from the local database SOL3 to the remote database SOL1. Therefore we need the global database name for SOL1. Ask the remote database administrator for these information or connect to SOL1 and execute the following query on SOL1:
SQL> select GLOBAL_NAME from GLOBAL_NAME;

GLOBAL_NAME
-----------
SOL1.WORLD
We found the database link name 'SOL1.WORLD' for our local database SOL3. Now connect to the local database database SOL3 as a user, who has the privilege to create a database link and create the following named database link to SOL1.
$ sqlplus jones/lion@SOL3
SQL> CREATE DATABASE LINK sol1.world
CONNECT TO scott IDENTIFIED BY tiger using 'SOL1';
With this DB-Link, you (jones/lion) can connect to the remote database SOL1 as user scott/tiger. This user must exist on the remote database SOL1. Test the database link as user jones/lion from SOL3.
SQL> SELECT * FROM [email protected];
You may ask, what's this strange 'SOL1' in ..... using 'SOL1' means ?. Well this is the so called connect_string (or net_connect_string in Oracle8i). This string has nothing common with the DB-Link name, but very often the same name is used. The connect string must be defined in the Net8 configuration file TNSNAMES.ORA, if you don't use Oracle Names.
#
# TNSNAMES.ORA for SOL3 ###############################
#
SOL1.world = (DESCRIPTION = (ADDRESS = (COMMUNITY = tcp.world)
(PROTOCOL = TCP) (Host = saturn) (Port = 1521))
(CONNECT_DATA = (SID = SOL1) (GLOBAL_NAME = SOL1.world)
(SERVER = DEDICATED)))
Well, now you understand our short story at the beginning of this article. If the DBA on SOL1 changes scott's password to snake, we have the disaster with our missing sales data ... poor management.
External references
Oracle allows three kinds of external references to DB-links, which are resolved as follows:
• Named Link: The username specified in the link is used. You specify the username and password used to connect to the remote database (this database link is sometimes called fixed user database link).
CREATE DATABASE LINK sol1.world
CONNECT TO scott IDENTIFIED BY tiger USING 'sol1';
• Anonymous Link: The session username is used. If you omit the CONNECT TO clause, the database link uses the username and password of each user who is connected to the database (this database link is sometimes called connected user database link).
CREATE DATABASE LINK sol1.world USING 'sol1';
• Privileged Link: The username of the invoker is used. The current user must be a 'global' user with a valid account on the remote database for the link to succeed. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.

When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure SCOTT.show_emp (created by SCOTT), and user JONES calls procedure SCOTT.show_emp, the current user is SCOTT.

However, if the stored object is an invoker-rights function, procedure, or package (new in Oracle8i), the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure SCOTT.show_emp (an invoker-rights procedure created by SCOTT), and user JONES calls procedure SCOTT.show_emp, then CURRENT_USER is JONES and the procedure executes with JONES's privileges. For more information on invoker-rights functions click here
CREATE DATABASE LINK sol1.world
CONNECT TO CURRENT_USER USING 'sol1';
Besides these often used DB-Links, you can create a database link as PUBLIC. Be very careful with PUBLIC database links, they may open a door for everybody to a remote database. We suggest, NOT TO USE public database links without Authentication.
Shared PUBLIC DB-Link with Authentication
A shared PUPLIC DB-Link with Authentication uses a single network connection to create a PUBLIC database link that can be shared between multiple users with more security. This DB-Link is available only with the multi-threaded server configuration.
Example
SQL> CREATE SHARED PUBLIC DATABASE LINK sol1.world
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY jones IDENTIFIED BY lion
USING 'sol1';
The AUTHENTICATED clause specifies the username and password (JONES/LION) on the target instance (SOL1). This clause authenticates the user to the remote server and is required for security. The specified username and password (JONES/LION) must be a valid username and password on the remote instance (SOL1). The username and password are used only for authentication. No other operations are performed on behalf of this user.


I Hope THis Would Help

رابط هذا التعليق
شارك

  • بعد 2 أسابيع...
  • بعد 3 أسابيع...

شكرا جزيلا للاخ F_WAKEEL وبارك الله فيك كنت محتاج مثل ذلك أأمل ان استوعبها واطبقها
ولى سؤال هل من توضيح لكيفية انشاء INSTANCE على الكمبيوتر الخاص بى دون (LOCAL MACHINE)
دون علم القائمين على ادارة قاعدة البيانات على SERVER للسرية وطبعا اعتقد امكانية عمل اتحاد بين جدولين باستخدام الطريقه السابق شرحها
ولكم جزيل الشكر والامتنان

رابط هذا التعليق
شارك

انضم إلى المناقشة

يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.

زائر
أضف رد على هذا الموضوع...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   تمت استعادة المحتوى السابق الخاص بك.   مسح المحرر

×   You cannot paste images directly. Upload or insert images from URL.

جاري التحميل
×
×
  • أضف...

برجاء الإنتباه

بإستخدامك للموقع فأنت تتعهد بالموافقة على هذه البنود: سياسة الخصوصية