DBA ARABIC

ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﻭﺍﺤﺩﺓ ﻴﺴﺘﻁﻴﻊ ﺍﻟﻤﺴﺘﺨﺩﻤﻭﻥ ﻤﻨﺢ ﺼﻼﺤﻴﺎﺕ ﻋﻠﻰ ﺍﻟﻜﺎﺌﻨﺎﺕ ﺍﻟﺘﻲ ﻴﻤﻠﻜﻭﻨﻬﺎ ﻟﻐﻴﺭﻫﻡ ﻤﻥ ﺍﻟﻤﺴﺘﺨﺩﻤﻴﻥ، ﺃﻤﺎ ﺇﺫﺍ ﻜﺎﻥ ﻟﺩﻴﻙ ﺃﻜﺜﺭ ﻤﻥ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﻓﻤﻥ ﺍﻟﻤﺘﻌﺫﺭ ﺘﻌﺎﻤل ﺍﻟﻤﺴﺘﺨﺩﻤﻴﻥ

ﺒﻴﻥ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﻭﺃﺨﺭﻯ ﻤﺎ ﻟﻡ ﻴﻜﻥ ﻟﺩﻴﻨﺎ Database Link ﻓﻬﻲ ﺍﻟﺭﺍﺒﻁ ﺒﻴﻥ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﻭﺃﺨﺭﻯ، ﻭﻗﺩ ﺘﺤﺘﺎﺝ ﺫﻟﻙ ﻜﺜﻴﺭﺍﹰ ﺃﺜﻨﺎﺀ ﻋﻤﻠﻙ ﻓﻘﺩ ﻴﻜﻭﻥ ﻟﺩﻴﻙ ﺃﻜﺜﺭ ﻤﻥ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﺘﺤﺘﺎﺝ ﻟﺭﺒﻁﻬﺎ ﻤﻊ ﺒﻌﺽ.

ﻭﻟﻨﻔﺘﺭﺽ ﺃﻥ ﻟﺩﻴﻨﺎ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﺘﺴﻤﻰ OBAY واخري تسمي ORCL.

SELECT NAME FROM V$DATABASE;

ﻨﺤﺘﺎﺝ ﺍﻵﻥ ﺭﺒﻁ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻤﻊ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL، ﻭﻟﻨﻔﺘﺭﺽ ﺃﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻴﺤﺘﺎﺝ ﻟﻌﻤل ﺍﺴﺘﻌﻼﻡ ﻋﻠﻰ ﺍﻟﺠﺩﻭل EMPLOYEE ﺍﻟﻤﻤﻠﻭﻙ

ﻟﻠﻤﺴﺘﺨﺩﻡ VBS ﺍﻟﻤﻭﺠﻭﺩ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL، ﺇﺫﺍﹰ ﻨﺤﺘﺎﺝ ﻟﻌﻤل Link Database ﺒﻴﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻭﺒﻴﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ VBS ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL.

ﻟﻜﻥ ﻗﺒل ﺇﻨﺸﺎﺀ ﺍلLink Database ﻻﺒﺩ ﻤﻥ ﺍﻹﺸﺎﺭﺓ ﺇﻟﻰ ﺃﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ ﺍﻟﺫﻱ ﻴﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ ﺍلLink Database ﻻﺒﺩ ﺃﻥ ﻴﻜﻭﻥ ﻟﺩﻴﻪ ﺍﻟﺼﻼﺤﻴﺔ CREATE DATABASE LINK.

ﻭﺍﻵﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﺴﻴﻘﻭﻡ ﺒﻌﻤل Database Link ﺒﻴﻨﻪ ﻭﺒﻴﻥ المستخدم VBS ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL.

ﺃﻭﻻﹰ: ﻴﺘﺼل ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﻭﻴﺘﺄﻜﺩ ﺃﻥ ﻟﺩﻴﻪ ﺍﻟﺼﻼﺤﻴﺔ CREATE DATABASE LINK.

SELECT * FROM USER_SYS_PRIVS WHERE PRIVILEGE='CREATE DATABASE LINK';

ﻓﻲ ﺍﻟﺨﻁﻭﺓ ﺍﻟﺴﺎﺒﻘﺔ ﺘﺄﻜﺩﻨﺎ ﺃﻭﻻﹰ ﺃﻨﻨﺎ ﻨﻌﻤل ﻋﻠﻰ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻭﺘﺄﻜﺩﻨﺎ ﺜﺎﻨﻴﺎﹰ ﺃﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﻴﻤﻠﻙ ﺍﻟﺼﻼﺤﻴﺔ CREATE DATABASE LINK.

ﺜﺎﻨﻴﺎﹰ: ﻴﻘﻭﻡ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﺒﺎﺨﺘﺒﺎﺭ ﺍلOracle Net Service aliases ﺍﻟﻤﻭﺠﻭﺩﺓ ﻓﻲ ﻤﻠﻑ ﺍلtnsnames.ora ﻭﺫﻟﻙ ﻟﻠﺘﺄﻜﺩ ﻤﻥ ﺴﻼﻤﺔ ﺍﻻﺘﺼﺎل ﺒﻘﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻷﺨﺭﻯ ﻭﻫﻰ ﻫﻨﺎ ORCL.

TNSPING ORCL

ﺜﺎﻟﺜًﺎ: ﻴﻘﻭﻡ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﺒﺈﻨﺸﺎﺀ ﺍلDatabase Link.

CREATE DATABASE LINK TESTVBS CONNECT TO VBS IDENTIFIED BY VBS USING 'ORCL';

ﻟﻘﺩ ﻗﺎﻡ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﺒﺈﻨﺸﺎﺀ Database Link ﺘﺴﻤﻰ TESTVBS ﻴﺴﺘﻁﻴﻊ ﻤﻥ ﺨﻼﻟﻬﺎ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﺍﻻﺘﺼﺎل ﺒﺎﻟﻤﺴﺘﺨﺩﻡ VBS ﺍﻟﻤﻭﺠﻭﺩ ﻓﻲ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﺃﺨﺭﻯ ﺘﺴﻤﻰ ORCL.

ﺭﺍﺒﻌﺎﹰ: ﺍﻵﻥ ﻴﻤﻜﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﺍﻻﺴﺘﻌﻼﻡ ﻋﻥ ﺍﻟﺠﺩﻭل EMPLOYEE ﺍﻟﻤﻤﻠﻭﻙ ﻟﻠﻤﺴﺘﺨﺩﻡ VBS اﻟﻤﻭﺠﻭﺩ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL.

SELECT * FROM EMPLOYEE@TESTVBS;

ﻓﻲ ﺒﻌﺩ ﺍﻷﺤﻴﺎﻥ ﻴﻜﻭﻥ ﺍﺴﻡ ﺍلDatabase Link ﻤﺼﺤﻭﺒﺎﹰ ﺒﺎﺴﻡ ﺍﻟﺠﺩﻭل ﻁﻭﻴل ﻓﻤﻥ ﺍﻷﻓﻀل ﺇﻨﺸﺎﺀ ﻤﺭﺍﺩﻑ synonym ﻟﺘﺴﻬﻴل ﻭﺍﺨﺘﺼﺎﺭ ﻜﺘﺎﺒﺔ ﺍﻟﻌﺒﺎﺭﺍﺕ ﺨﺼﻭﺼﺎﹰ ﺘﻠﻙ ﺍﻟﺘﻲ ﻴﺘﻜﺭﺭ ﻜﺘﺎﺒﺘﻬﺎ.

CREATE SYNONYM EMP FOR EMPLOYEE@TESTVBS;

ﻟﻜﻥ ﻴﺠﺏ ﻤﺭﺍﻋﺎﺓ ﺃﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ ﻴﺤﺘﺎﺝ ﻟﻠﺼﻼﺤﻴﺔ CREATE SYNONYM ﻹﻨﺸﺎﺀ ﺍﻟﻤﺭﺍﺩﻑ.

ﺍﻵﻥ ﻴﻤﻜﻥ ﻜﺘﺎﺒﺔ ﻋﺒﺎﺭﺓ ﺍﻻﺴﺘﻌﻼﻡ ﺒﺎﻟﺸﻜل ﺍﻻﺘﻰ:

SELECT * FROM EMP;

ﺘﺠﺩﺭ ﺍﻹﺸﺎﺭﺓ ﻫﻨﺎ ﺒﺄﻥ ﻨﻘﻭل ﺍﻨﻙ ﺘﺴﺘﻁﻴﻊ ﻤﻥ ﺨﻼل ﺍلDatabase Link ﺇﺠﺭﺍﺀ ﻋﻤﻠﻴﺎﺕ ﺍلDML ﻭﻫﻰ

(DELETE & UPDATE & INSERT & SELECT) ﻟﻜﻥ ﻤﻥ ﺍﻟﻤﺘﻌﺫﺭ ﺇﺠﺭﺍﺀ ﻋﻤﻠﻴﺎﺕ ﺍلDDL ﻤﺜل (CREATE & ALTER & DROP).

PUBLIC DATABASE LINK:

ﻤﺎ ﻗﻤﻨﺎ ﺒﻪ ﻓﻲ ﺍﻟﺨﻁﻭﺍﺕ ﺍﻟﺴﺎﺒﻘﺔ ﻫﻭ ﺭﺒﻁ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻤﻊ ﺍﻟﻤﺴﺘﺨﺩﻡ VBS ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL ﻋﻥ ﻁﺭﻴﻕ LINK DATABASE ﺘﺴﻤﻰ TESTVBS.

ﻟﻜﻥ ﻫل ﻴﺴﺘﻁﻴﻊ ﺃﻱ ﻤﺴﺘﺨﺩﻡ ﺁﺨﺭ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﺍﺴﺘﺨﺩﺍﻡ TESTVBS DATABASE LINK؟ ﺍﻟﺠﻭﺍﺏ ﻭﺒﻜل ﺴﻬﻭﻟﺔ ﻻ، ﻭﺫﻟﻙ ﻷﻥ ﺍلLink Database ﺍﻟﺘﻲ ﻗﻤﻨﺎ ﺒﺨﻠﻘﻬﺎ ﻫﻲ ﺨﺎﺼﺔ ﺒﺎﻟﻤﺴﺘﺨﺩﻡ

TEST ﻭﻟﻴﺴﺕ ﻟﻜل ﺍﻟﻤﺴﺘﺨﺩﻤﻴﻥ ﻭﻫﻰ ﺘﺴﻤﻰ PRIVATE DATABASE LINK.

ﻭﻟﻜﻲ ﻴﺴﺘﻁﻴﻊ ﻜل ﺍﻟﻤﺴﺘﺨﺩﻤﻭﻥ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﺍﻻﺘﺼﺎل ﺒﺎﻟﻤﺴﺘﺨﺩﻡ VBS ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL ﻨﺤﺘﺎﺝ ﻹﻨﺸﺎﺀ PUBLIC DATABASE LINK.

ﺒﺎﻟﻁﺒﻊ ﻹﻨﺸﺎﺀ ﻫﺫﺍ ﺍﻟﻨﻭﻉ ﻨﺤﺘﺎﺝ ﻟﻠﺼﻼﺤﻴﺔ CREATE PUBLIC DATABASE LINK.

SQL> CONN / AS SYSDBA

SQL> GRANT CREATE PUBLIC DATABASE LINK TO TEST;

SQL> CONN TEST/TEST

SQL> CREATE PUBLIC DATABASE LINK PUBTESTVBS CONNECT TO VBS IDENTIFIED BY VBS USING 'ORCL';

ﻗﺎﻡ ﻤﺩﻴﺭ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺒﻤﻨﺢ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﺍﻟﺼﻼﺤﻴﺔ CREATE PUBLIC DATABASE LINK ﻭﻤﻥ ﺜﻡ ﻗﺎﻡ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﺒﺈﻨﺸﺎﺀ PUBLIC DATABASE LINK

ﺘﺴﻤﻰ PUBTESTVBS، ﺍﻵﻥ ﻴﺴﺘﻁﻴﻊ ﺠﻤﻴﻊ ﺍﻟﻤﺴﺘﺨﺩﻤﻴﻥ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﺍﺴﺘﺨﺩﻡ PUBTESTVBS DATABASE LINK.

ﻟﻺﺴﺘﻌﻼﻡ ﻋﻥ ﺍلDATABASE LINK ﻓﻰ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ:

DBA_DB_LINKS

USER_DB_LINKS

ALL_DB_LINKS

V$DBLINK

ﺒﺎﻟﻁﺒﻊ ﻴﻤﻜﻥ ﺤﺫﻑ ﺍلDATABASE LINK.

DROP DATABASE LINK TESTVBS;

ﻜﻤﺎ ﻴﻤﻜﻥ ﺤﺫﻑ ﺍلPUBLIC DATABASE LINK.

DROP PUBLIC DATABASE LINK PUBTESTVBS;

ﺒﺎﻟﻁﺒﻊ ﻨﺤﺘﺎﺝ ﻟﻠﺼﻼﺤﻴﺔ ﻟﻠﺤﺫﻑ DROP PUBLIC DATABASE LINK.

-----------------------

DATABASE

SQL

VIDEOS

Orcale Tutorial Content

Oracle Net Services & Database Link & Metrlized Viewِ

Database Link