Orcale Tutorial Content
Getting Started - البدء مع إدارة قواعد البيانات
Oracle Architectural Components - مكونات قاعدة البيانات أوركل
Installing Oracle Database - تثبيت الأوركل
Creating and Configuring Database - إنشاء قاعدة البيانات
Controlling The Database -التحكم في قاعدة البيانات
Administrating Users - إدارة المستخدمين
Database Security & Monitoring - تأمين ومراقبة قاعدة البيانات
Oracle Net Services & Database Link & Metrlized Viewِ
Undo Management & Flashback Technology
Managing Shared Servers
Using Globalization Support
Logical Backup & Recovery
Physical Backup & Recovery
Recovery Manager (RMAN)
Managing Resources
Automating Tasks with the Scheduler
Dealing with Locking
Diagnostic Sources
Dealing With Database Corruption
Managing & Monitoring Memory
Performance Tunning
Oracle Net Services & Database Link & Metrlized Viewِ
Materialized Views
ﻋﻨﺩ ﻤﻨﺎﻗﺸﺘﻨﺎ ﻟلDatabase Link ﻋﺭﻓﻨﺎ ﻜﻴﻑ ﻴﻤﻜﻥ ﺭﺒﻁ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﺒﺄﺨﺭﻯ ﻭﻜﻴﻑ ﻴﻤﻜﻥ ﺍﻟﻭﺼﻭل ﻟﻠﻜﺎﺌﻨﺎﺕ ﻓﻲ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﺃﺨﺭﻯ،
ﻭﻟﻜﻥ ﻗﺩ ﺘﺤﺘﺎﺝ ﻓﻲ ﺒﻌﺽ ﺍﻷﺤﻴﺎﻥ ﻟﻨﻘل ﻭﺘﺤﺩﻴﺙ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﻤﻭﺠﻭﺩﺓ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺇﻟﻰ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﺃﺨﺭﻯ.
ﻜﺄﻥ ﺘﻜﻭﻥ ﻤﺜﻼﹰ ﻤﺩﻴﺭﺍﹰ ﻟﻤﺠﻤﻭﻋﺔ ﺼﻴﺩﻟﻴﺎﺕ؛ ﻜل ﺼﻴﺩﻟﻴﺔ ﺘﺤﺘﻭﻯ ﻋﻠﻰ ﻗﺎﻋﺩﺓ ﺒﻴﺎﻨﺎﺕ ﻭﻟﻜﻥ ﻋﻠﻰ ﺭﺃﺱ ﻜل ﺴﺎﻋﺔ ﺘﺤﺘﺎﺝ ﻟﺠﻠﺏ ﺠﻤﻴﻊ ﺤﺴﺎﺒﺎﺕ ﺍﻟﺼﻴﺩﻟﻴﺎﺕ ﻤﻥ ﻗﻭﺍﻋﺩ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﻤﻭﺯﻋﺔ
ﺇﻟﻰ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﺭﺌﻴﺴﻴﺔ، ﻫﺫﻩ ﻫﻲ ﻤﻬﻤﺔ ﺍلViews Materialized؛ ﻭﻟﻜﻥ ﻟلViews Materialized ﻤﻬﺎﻡ ﺃﺨﺭﻯ ﻟﻴﺴﺕ ﻫﻨﺎ ﻤﺠﺎل ﺘﻔﺼﻴﻠﻬﺎ.
ﺴﻨﺴﺘﺨﺩﻡ ﻫﻨﺎ ﻨﻔﺱ ﺍﻟﻤﺜﺎل ﺍﻟﺫﻱ ﺍﺴﺘﺨﺩﻤﻨﺎﻩ ﻓﻲ ﺍلLink Database، ﻭﻟﻨﻔﺘﺭﺽ ﻫﻨﺎ ﺃﻥ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻫﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﺭﺌﻴﺴﻴﺔ ﺍﻟﺘﻲ ﺴﺘﺴﺘﻘﺒل ﺍﻟﺒﻴﺎﻨﺎﺕ،
ﻭﻟﻨﻔﺘﺭﺽ ﺃﻨﻨﺎ ﺴﻨﺴﺘﻘﺒل ﺍﻟﺒﻴﺎﻨﺎﺕ ﻓﻲ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST،
ﺃﻤﺎ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻷﺨﺭﻯ ORCL ﻭﺍﻟﺘﻲ ﺘﺤﺘﻭﻯ ﻋﻠﻰ ﺍﻟﺠﺩﻭل ﺍﻟﺭﺌﻴﺴﻲ ﻭﻟﻨﻔﺘﺭﺽ ﺃﻨﻪ EMPLOYEE ﺍﻟﻤﻤﻠﻭﻙ ﻟﻠﻤﺴﺘﺨﺩﻡ VBS ﺍﻟﺫﻱ ﻨﺤﺘﺎﺝ ﺇﻟﻰ ﻨﻘل ﻭﺘﺤﺩﻴﺙ ﺒﻴﺎﻨﺎﺘﻪ ﻜل ﺜﺎﻨﻴﺔ ﺇﻟﻰ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﺭﺌﻴﺴﻴﺔ OBAY.
ﺒﺎﻟﻁﺒﻊ ﻨﺤﺘﺎﺝ ﻗﺒل ﻜل ﺸﻲ ﻟﻌﻤل Link Database ﺒﻴﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ TEST ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻭﺒﻴﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ VBS ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL، ﻭﺫﻟﻙ ﻟﻌﻤﻠﻴﺔ ﻨﻘل ﻭﺘﺤﺩﻴﺙ ﺒﻴﺎﻨﺎﺕ ﺍﻟﺠﺩﻭل
EMPLOYEE ﻤﻥ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL ﺇﻟﻰ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY.
ﺃﻴﻀﺎﹰ ﻋﻤﻠﻴﺔ ﺘﺤﺩﻴﺙ ﺍﻟﺒﻴﺎﻨﺎﺕ (Refresh) ﺒﻴﻥ ﺍﻟﺠﺩﻭل ﺍﻟﺭﺌﻴﺴﻲ ﻭﺍلMaterialized Viewsتنقسم ﺇﻟﻰ ﺜﻼﺙ ﺃﻨﻭﺍﻉ:
1- FAST REFRESH: ﻓﻲ ﻫﺫﺍ ﺍﻟﻨﻭﻉ ﻤﻥ ﺍﻟﺘﺤﺩﻴﺙ ﻴﺘﻡ ﻓﻘﻁ ﻨﻘل ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﺘﻲ ﺘﻐﻴﺭﺕ ﺒﻌﺩ ﺁﺨﺭ ﺘﺤﺩﻴﺙ ﻓﻬﻭ ﻻ ﻴﺤﺘﺎﺝ ﻟﻨﻘل ﺠﻤﻴﻊ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻤﻥ ﺍﻟﺠﺩﻭل ﻭﺇﻨﻤﺎ ﻓﻘﻁ ﻤﺎ ﺘﻡ ﺘﻐﻴﺭﻩ ﺒﻌﺩ ﺁﺨﺭ ﺘﺤﺩﻴﺙ،
ﻫﺫﺍ ﺍﻟﻨﻭﻉ ﻓﻲ ﺍﻟﻐﺎﻟﺏ ﻴﺨﺘﺼﺭ ﺍﻟﺯﻤﻥ.
2- COMPLETE REFRESH: ﺃﻤﺎ ﻓﻲ ﻫﺫﺍ ﺍﻟﻨﻭﻉ ﻤﻥ ﺍﻟﺘﺤﺩﻴﺙ ﻴﻘﻭﻡ ﺒﻨﻘل ﺠﻤﻴﻊ ﺒﻴﺎﻨﺎﺕ ﺍﻟﺠﺩﻭل ﻤﻥ
ﺍﻟﻤﺼﺩﺭ ﺇﻟﻰ ﺍلMaterialized Views ﻓﻴﻘﻭﻡ ﺒﻌﻤل ﺇﻋﺎﺩﺓ ﻜﺘﺎﺒﺔ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﻘﺩﻴﻤﺔ ﻭﺇﻀﺎﻓﺔ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﺠﺩﻴﺩﺓ.
ﻏﺎﻟﺒﺎﹰ ﻫﺫﺍ ﺍﻟﻨﻭﻉ ﻴﺘﻁﻠﺏ ﺯﻤﻥ ﺃﻜﺜﺭ ﻤﻥ ﺍﻟﻨﻭﻉ FAST.
3- FORCE REFRESH: ﻫﺫﺍ ﺍﻟﻨﻭﻉ ﻴﺒﺩﺃ ﺃﻭﻻﹰ ﺒﺘﻁﺒﻴﻕ ﺍﻟﻨﻭﻉ FAST ﺇﺫﺍ ﻓﺸﻠﺕ ﺍﻟﻌﻤﻠﻴﺔ ﻜﺄﻥ ﻟﻡ ﻴﺠﺩ ﻤﺜﻼﹰ ﺍلLogs Views Materialized ﻓﻲ ﺠﺎﻨﺏ ﺍﻟﻤﺼﺩﺭ،
ﻓﻔﻲ ﻫﺫﻩ ﺍﻟﺤﺎﻟﺔ ﻴﻁﺒﻕ ﺍﻟﻨﻭﻉ COMPLETE.
ﺇﺫﺍ ﻟﻡ ﻴﺤﺩﺩ ﻨﻭﻉ ﺍﻟﺘﺤﺩﻴﺙ ﺃﺜﻨﺎﺀ ﺇﻨﺸﺎﺀ ﺍلMaterialized Views ﻓﺎﻥ الوضع الإفتراضي هو (FORCE).
-:Materialized Views Logs
ﻜﻤﺎ ﺴﺒﻕ ﻭﺍﻥ ﺫﻜﺭﻨﺎ ﺃﻥ ﻨﻭﻉ ﺍﻟﺘﺤﺩﻴﺙ FAST ﻴﻘﻭﻡ ﻓﻘﻁ ﺒﻨﻘل ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﺘﻲ ﺘﻡ ﺘﻐﻴﺭﻫﺎ ﻤﻨﺫ ﺁﺨﺭ ﺘﺤﺩﻴﺙ ﻤﻥ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﺇﻟﻰ ﺍلViews Materialized.
ﻭﻟﻜﻥ ﺃﻴﻥ ﻴﺘﻡ ﺘﺨﺯﻴﻥ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﺘﻲ ﺘﻐﻴﺭﺕ ﻓﻲ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﻗﺒل ﻨﻘﻠﻬﺎ ﺇﻟﻰ ﺍلViews Mataerialized ؟ ﺍﻟﺠﻭﺍﺏ ﻴﺘﻡ ﺘﺨﺯﻴﻨﻬﺎ ﻓﻲ ﺍلLogs View Materialized،
ﻭﻫﻭ ﻋﺒﺎﺭﺓ ﻋﻥ ﺠﺩﻭل ﻴﺘﻡ ﺇﻨﺸﺎﺅﻩ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺒل ﻭﻓﻰ ﺍﻟﻤﺴﺘﺨﺩﻡ ﺍﻟﺫﻱ ﻴﻤﻠﻙ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﻭﺫﻟﻙ ﻋﻥ ﻁﺭﻴﻕ ﺍﻷﻤﺭ ﺍﻟﺘﺎﻟﻲ :
CREATE MATERIALIZED VIEW LOG ON EMPLOYEE; |
ﺒﺤﻴﺙ EMPLOYEE ﻫﻭ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ. ﻓﻠﺤﻅﺔ ﻜﺘﺎﺒﺔ ﺍﻷﻤﺭ ﺃﻋﻼﻩ؛ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺘﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ ﺠﺩﻭل ﺒﺎﻟﺼﻴﻐﺔ
<MLOG$_<TABLE_NAME.
:Primary Key Materialized Views
ﻭﻟﻜﻥ ﺤﺘﻰ ﺘﺴﺘﻁﻴﻊ ﺘﻨﻅﻴﻡ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ، ﺩﻭﻥ ﺍﻟﺘﺄﺜﻴﺭ ﻋﻠﻰ ﺍلFAST REFRESH ﺒﺤﻴﺙ ﻴﺘﻡ ﺤﻔﻅ ﺍﻟﺘﻐﻴﺭﺍﺕ ﺒﻨﺎﺀً ﻋﻠﻰ ﺍلPrimary Key، ﻓﻨﺴﺘﻁﻴﻊ ﻟﺤﻅﺔ ﺇﻨﺸﺎﺀ ﺍلViews Materialized
ﺘﺤﺩﻴﺩ ﺍﻟﺨﻴﺎﺭ WITH PRIMARY KEY ﻭﻫﻭ الوضع الإفتراضي (DEFAULT) ﻓﻲ ﺤﺎﻟﺔ ﻋﺩﻡ ﺘﺤﺩﻴﺩ ﺨﻴﺎﺭ ﺁﺨﺭ، ﻭﺍﻟﺨﻴﺎﺭ ﺍﻵﺨﺭ ﻫﻭ ROWID.
ﻟﺤﻅﺔ ﺇﻨﺸﺎﺀ ﺍلMaterialized Views ﺒﺎﻟﺨﻴﺎﺭ Primary Key ﻴﺠﺏ ﺃﻥ ﻴﻜﻭﻥ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﻴﺤﺘﻭﻯ ﻋﻠﻰ primary Key Constraint ، ﻜﺫﻟﻙ ﻋﻨﺩ ﺘﺤﺩﻴﺩ ﺍﻟﺨﻴﺎﺭ Fast Refresh ﻋﻨﺩ ﺇﻨﺸﺎﺀ
ﺍل Materialized Views ﻴﺠﺏ ﺃﻥ ﻨﻜﻭﻥ ﻗﻤﻨﺎ ﺒﺈﻨﺸﺎﺀ ﺍلMaterialized View Logs ﻓﻲ ﺍﻟﻤﺴﺘﺨﺩﻡ
ﺍﻟﺫﻱ ﻴﺤﻭﻯ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﻭﺇﻻ ﻅﻬﺭﺕ ﻟﻨﺎ ﺭﺴﺎﻟﺔ ﺨﻁﺄ. ﻗﺩ ﻴﻜﻭﻥ ﻓﻲ ﺍﻟﺸﺭﺡ ﺍﻟﺴﺎﺒﻕ ﻨﻭﻉ ﻤﻥ ﺍﻟﺘﻌﻘﻴﺩ ﻟﻜﻥ ﻻ ﻋﻠﻴﻙ ﺭﻜﺯ ﻤﻌﻲ ﻓﻲ ﺍﻟﺴﻴﻨﺎﺭﻴﻭ ﺍﻟﺫﻱ ﺴﻨﻨﻔﺫﻩ ﻤﻌﺎﹰ ﺨﻁﻭﺓ ﺒﺨﻁﻭﺓ ﺍﺒﺘﺩﺍﺀً
ﻤﻥ ﺇﻨﺸﺎﺀ ﺍﻟﻤﺴﺘﺨﺩﻤﻴﻥ ﻭﺤﺘﻰ ﻋﻤل ﺍلMaterialized Views ﻭﻫﺫﺍ ﻫﻭ ﺒﻴﺕ ﺍﻟﻘﺼﻴﺩ.
ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻷﻭﻟﻰ ﺘﺴﻤﻰ OBAY، ﺴﻨﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ ﻤﺴﺘﺨﺩﻡ ﻓﻴﻬﺎ ﻴﺴﻤﻰ MAIN؛ ﻭﺴﻨﻘﻭﻡ
ﺒﺈﻨﺸﺎﺀ Materialized Views ﻓﻲ ﻫﺫﺍ ﺍﻟﻤﺴﺘﺨﺩﻡ ﻟﺠﻠﺏ ﺒﻴﺎﻨﺎﺕ ﻤﻭﺠﻭﺩﺓ ﻓﻲ ﺍﻟﺠﺩﻭل EMPLOYEE ﺍﻟﻤﻤﻠﻭﻙ ﻟﻠﻤﺴﺘﺨﺩﻡ SUB ﺍﻟﻤﻭﺠﻭﺩ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL.
ﻭﺍﻟﻴﻙ ﺍﻟﺨﻁﻭﺍﺕ :-
1- ﺴﻨﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ ﺍﻟﻤﺴﺘﺨﺩﻡ MAIN ﻓﻰ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻭﺴﻨﻤﻨﺤﻪ ﺍﻟﺼﻼﺤﻴﺎﺕ ﺍﻟﻜﺎﻓﻴﺔ.
CREATE USER MAIN IDETIFIED BY MAIN; GRANT CONNECT,RESOURCE,CREATE DATABASE LINK,CREATE MATERIALIZED VIEW TO MAIN; |
2- ﻓﻲ ﺍﻟﺠﺎﻨﺏ ﺍﻷﺨﺭ ﻨﺘﺄﻜﺩ ﻤﻥ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﻭﻫﻭ ﻫﻨﺎ EMPLOYEE ﻭﺍﻟﺘﺄﻜﺩ ﻜﺫﻟﻙ ﺃﻨﻪ ﻴﺤﺘﻭﻯ ﻋﻠﻰ PRIMARY KEY CONSTRAINT ﺤﺘﻰ ﻨﺴﺘﻁﻴﻊ ﺇﻨﺸﺎﺀ MATERIALIZED VIEWS ﻓﻰ
ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻷﺨﺭﻯ ﺒﺎﺴﺘﺨﺩﺍﻡ ﺍﻟﺨﻴﺎﺭ WITH PRIMARY KEY.
SELECT * FROM EMPLOYEE; SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM USER CONSTRAINTS;
|
3- ﺍﻵﻥ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻨﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ DATABASE LINK ﺒﻴﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ MAIN ﻭﺒﻴﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ SUB ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL.
CREATE DATABASE LINK MAINSUB CONNECT TO SUB IDENTIFIED BY SUB USING 'ORCL'; |
4- ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ORCL ﻭﻓﻰ ﺍﻟﻤﺴﺘﺨﺩﻡ SUB ﺍﻟﺫﻱ ﻴﺤﻭﻯ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ EMPLOYEE ﻨﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ MATERIALIZED VIEW LOG.
CREATE MATERIALIZED VIEW LOG ON EMPLOYEE; |
ﻗﻤﻨﺎ ﺒﺈﻨﺸﺎﺀ MATERIALIZED VIEW LOG ﻟﻠﺠﺩﻭل EMPLOYEE ﻭﻴﻤﻜﻥ ﺍﻟﺘﺤﻘﻕ ﻤﻥ ﺇﻨﺸﺎﺀ ﺍلMATERIALIZED VIEW LOG ﻟﻠﺠﺩﻭل EMPLOYEE ﺒﺎﻻﺴﺘﻌﻼﻡ ﺍﻟﺘﺎﻟﻲ
SELECT * FROM TAB WHERE TNAME LIKE '%EMPLOYEE'; |
5- ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ OBAY ﻨﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ ﺍلMATERIALIZED VIEW.
CREATE MATERIALIZED VIEW EMPLOYEE_MV REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/(24*60*60) WITH PRIMARY KEY AS SELECT * FROM EMPLOYEE@MAINSUB; |
ﻗﻤﻨﺎ ﺒﺈﻨﺸﺎﺀ ﺍلMATERIALIZED VIEW ﻤﺴﺘﺨﺩﻤﻴﻥ ﺍﻟﺨﻴﺎﺭ FAST REFRESH ﻭﺫﻟﻙ ﻷﻨﻨﺎ ﻗﻤﻨﺎ ﺒﺈﻨﺸﺎﺀ ﺍلMATERIALIZED VIEW LOG ﻓﻲ ﺍﻟﺠﺎﻨﺏ ﺍﻵﺨﺭ ﻭﺇﻻ ﻅﻬﺭﺕ ﺭﺴﺎﻟﺔ ﺨﻁﺄ،
ﻭﻜﺫﻟﻙ ﺍﺴﺘﺨﺩﻤﻨﺎ ﺍﻟﺨﻴﺎﺭ WITH PRIMARY KEY ﻭﺫﻟﻙ ﻷﻥ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﻴﺤﺘﻭﻯ ﻋﻠﻰ PRIMARY KEY CONSTRAINT ، ﻋﻠﻰ ﺃﻱ ﺤﺎل ﻫﻨﺎﻙ ﺨﻴﺎﺭ ﺁﺨﺭ ﻫﻭ WITH ROWID.
6- ﺍﻵﻥ ﻴﻤﻜﻥ ﺍﻻﺴﺘﻌﻼﻡ ﻋﻥ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﻭﺍلMATERIALIZED VIEW ﺴﺘﻼﺤﻅ ﺃﻨﻪ ﻜل ﺜﺎﻨﻴﺔ ﻜﻤﺎ ﺤﺩﺩﻨﺎ ﺫﻟﻙ
(SYSDATE + 1/(24*60*60
ﺘﻜﻭﻥ ﺍﻟﻨﺘﻴﺠﺔ ﻤﺘﻁﺎﺒﻘﺔ ﺒﻴﻥ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ EMPLOYEE وبين الMATERIALIZED VIEW وهو EMPLOYEE_MV.
7- ﺍﻵﻥ ﻟﻭ ﻗﻤﻨﺎ ﺒﺈﻀﺎﻓﺔ ﺤﻘل ﺠﺩﻴﺩ ﻓﻲ ﺍﻟﺠﺩﻭل ﺍﻟﻤﺼﺩﺭ ﺜﻡ ﺒﻌﺩ ﺫﻟﻙ ﺃﻋﺩﻨﺎ ﻋﻤﻠﻴﺎﺕ ﺍﻻﺴﺘﻌﻼﻡ سيظهر الحقل الجديد في الMATERIALIZED VIEW.
ﻟﻭ ﻗﻤﻨﺎ ﺒﻌﻤل ﺍﺴﺘﻌﻼﻡ ﻟﻠﺠﺩﻭل MLOG$_EMPLOYEE ﻗﺒل ﻋﻤﻠﻴﺔ ﺍلREFRESH ﺴﻨﺠﺩ ﻤﻌﻠﻭﻤﺎﺕ ﻋﻥ ﺍﻟﺤﻘﻭل ﺍﻟﺘﻲ ﺃﻀﻴﻔﺕ ﺒﻌﺩ ﺁﺨﺭ ﻋﻤﻠﻴﺔ REFRESH ﻭﻟﻜﻥ ﺒﻌﺩ ﻋﻤﻠﻴﺔ ﺍلREFRESH ﺘﺨﺘﻔﻲ
ﺍﻟﻤﻌﻠﻭﻤﺎﺕ ﺍﻟﺴﺎﺒﻘﺔ ﻋﻥ ﺍﻟﺠﺩﻭل ﻓﻲ ﺍﻨﺘﻅﺎﺭ ﺍﻟﺠﺩﻴﺩ.
ﻴﺴﺘﻁﻴﻊ ﻤﺩﻴﺭ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻋﻤل ﺍﻻﺴﺘﻌﻼﻡ ﻋﻥ ﺍلMATERIALIZED VIEWS ﺍﻟﺘﻲ ﺘﻌﻤل ﻓﻲ
ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺒﻭﺍﺴﻁﺔ ﺍﻟﺠﺩﻭل DBA_MVIEWS.
ﻨﺴﺘﻁﻴﻊ ﺃﻥ ﻨﺤﺼل ﻋﻠﻰ ﻤﻌﻠﻭﻤﺎﺕ ﺃﺨﺭﻯ ﻤﻥ ﻨﻔﺱ ﺍﻟﺠﺩﻭل.
ﻭﻨﺴﺘﻁﻴﻊ ﺃﻥ ﻨﺤﺼل ﻋﻠﻰ ﻤﻌﻠﻭﻤﺎﺕ ﻋﻥ ﺍلJOBS ﺒﻭﺍﺴﻁﺔ ﺍﻟﺠﺩﻭل DBA_JOBS.
ﺒﺎﻟﻁﺒﻊ ﺍﻟﻤﺴﺘﺨﺩﻡ MAIN ﻴﺴﺘﻁﻴﻊ ﺤﺫﻑ ﺍلMATERIALIZED VIEW.
DROP MATERIALIZED VIEW EMPLOYEE_MV; |
---------------------------