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

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

How can i query from 2 database instance


4 رد (ردود) على هذا الموضوع

#1 OracleMan

OracleMan

    مشترك

  • الأعضــاء
  • 152 مشاركة

تاريخ المشاركة 06 September 2004 - 08:04 AM

I have two database instance , what i want to do is!

How can i make query from two tables in different instance?


plz anybody can help me ?


"I die even as a camel dies. I die in bed, in shame. May the eyes of cowards never find rest in sleep!"
Last Words of Khalid bin Al-Waleed, Sword of Allah

#2 f_wakeel

f_wakeel

    عضو

  • الأعضــاء
  • 18 مشاركة

تاريخ المشاركة 06 September 2004 - 01:11 PM

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 emp@SOL1.WORLD;
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



#3 الأسيف

الأسيف

    مشرف سابق وعضو مميز

  • فريق الإشراف
  • 203 مشاركة
  • الاسم الأول:أنس
  • اسم العائلة:القحطاني
  • البـلـد: Country Flag
  • الاهتمامات:إدارة و تقنية المعلومات الصحية
  • المنصب الحالي:IT Manager at Palestine Medical Center

تاريخ المشاركة 06 September 2004 - 01:20 PM

very good Mr.f_wakeel

thanks :)



#4 OracleMan

OracleMan

    مشترك

  • الأعضــاء
  • 152 مشاركة

تاريخ المشاركة 14 September 2004 - 10:38 PM

Thank you so much for f_wakeel
"I die even as a camel dies. I die in bed, in shame. May the eyes of cowards never find rest in sleep!"
Last Words of Khalid bin Al-Waleed, Sword of Allah

#5 TALAL

TALAL

    عضو

  • الأعضــاء
  • 29 مشاركة

تاريخ المشاركة 05 October 2004 - 02:46 PM

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