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
Automating Tasks with the Scheduler
Schedules
ﻟﻨﻔﺘﺭﺽ ﺃﻨﻙ ﺘﺭﻴﺩ ﺇﻨﺸﺎﺀ ﻤﺠﻤﻭﻋﺔ ﻤﻥ ﺍلJobs ﺍﻟﺘﻲ ﺘﻨﻔﺫ ﻓﻲ ﻭﻗﺕ ﻭﺍﺤﺩ ﻭﻴﺘﻡ ﺇﻋﺎﺩﺓ ﺘﻨﻔﻴﺫﻫﺎ ﻓﻲ ﻨﻔﺱ ﺍﻟﻭﻗﺕ ﻭﻴﺘﻡ ﺇﻨﻬﺎﺀ ﺘﻨﻔﻴﺫﻫﺎ ﻓﻲ ﻨﻔﺱ ﺍﻟﻭﻗﺕ,
ﻓﻲ ﻤﺜل ﻫﺫﺍ ﺍﻟﺴﻨﺎﺭﻴﻭ ﺒﺩﻻﹰ ﻤﻥ ﺘﺤﺩﻴﺩ ﺯﻤﻥ ﺒﺩﺀ ﺘﻨﻔﻴﺫ ﺠﻤﻴﻊ ﻫﺫﻩ ﺍلJobs ﻭﺯﻤﻥ ﺘﻜﺭﺍﺭ ﺍﻟﺘﻨﻔﻴﺫ ﻭﺯﻤﻥ ﺇﻨﻬﺎﺀ ﺘﻨﻔﻴﺫ ﺠﻤﻴﻊ ﻫﺫﻩ ﺍلJobs ﺒﺩﻻﹰ ﻤﻥ ﺫﻟﻙ ﺍﻷﻓﻀل ﻟﻙ
ﺇﻨﺸﺎﺀ Scheduler ﻋﻥ ﻁﺭﻴﻕ ﺍﻹﺠﺭﺍﺀ CREATE_SCHEDULE ﺍﻟﺫﻱ ﻴﻘﻭﻡ ﺒﻌﻤل ﺠﺩﻭﻟﺔ ﻜﺎﻤﻠﺔ، ﺒﺤﻴﺙ ﻴﺘﻡ ﺘﺤﺩﻴﺩ ﻭﻤﻥ ﺒﺩﺍﻴﺔ ﺍﻟﺘﻨﻔﻴﺫ ﻭﺯﻤﻥ ﺍﻟﺘﻜﺭﺍﺭ ﻭﺯﻤﻥ ﺃﻨﻬﺎﺀ ﺍﻟﺘﻨﻔﻴﺫ.
ﺒﻌﺩ ﺫﻟﻙ ﻴﻤﻜﻥ ﻟﻤﺠﻤﻭﻋﺔ ﻤﻥ ﺍلJobs ﺍﻟﻤﺸﺎﺭﻜﺔ ﻓﻲ ﻫﺫﻩ ﺍﻟﺠﺩﻭﻟﺔ ﻋﻥ ﻁﺭﻴﻕ ﻤﻨﺎﺩﺍﺓ ﻫﺫﻩ ﺍﻟﺠﺩﻭﻟﺔ ﺃﺜﻨﺎﺀ ﺇﻨﺸﺎﺀ ﺍلJob ﻋﻥ ﻁﺭﻴﻕ ﺍﻹﺠﺭﺍﺀ Create_Job.
ﺤﻴﺙ SCHEDULE_NAME ﻫﻲ ﺍﺴﻡ ﺍﻟﺠﺩﻭﻟﺔ،
ﺃﻤﺎ ﺍل(START_DATE & END_DATE) ﻟﺘﺤﺩﻴﺩ ﺯﻤﻥ ﺒﺩﺍﻴﺔ ﻭﻨﻬﺎﻴﺔ ﺍﻟﺘﻨﻔﻴﺫ،
ﺃﻤﺎ ﺍﻟﻤﺘﻐﻴﺭ REPEAT_INTERVAL ﻟﺘﺤﺩﻴﺩ ﺯﻤﻥ ﺘﻜﺭﺍﺭ ﺍﻟﺘﻨﻔﻴﺫ، ﻭﻫﺫﺍ ﺍﻟﻤﺘﻐﻴﺭ ﻴﻤﻜﻥ ﺃﻥ ﻴﻜﻭﻥ ﻋﺩﺩ ﻤﻥ ﺍﻟﻌﻨﺎﺼﺭ ﻤﺜﻼﹰ Frequency ﺃﻱ ﺍﻟﺘﻜﺭﺍﺭ ﺍﻟﺫﻱ ﻴﻤﻜﻥ ﺃﻥ ﻴﺄﺨﺫ ﺃﺤﺩ ﺍﻟﻘﻴﻡ:
YEARLY
MONTHLY
WEEKLY
DAILY
HOURLY
MINUTELY
SECONDLY
كذلك الINTERVAL ﻟﺘﺤﺩﻴﺩ زﻤﻥ ﺍﻟﺘﻜﺭﺍﺭ.
ﻤﺜﻼﹰ ﻟﻭ ﺃﺭﺩﻨﺎ ﺘﻨﻔﻴﺫ ﺒﻌﺽ ﺍﻟﻤﻬﺎﻡ ﻴﻭﻤﻴﺎﹰ ﺍﻟﺴﺎﻋﺔ ﺍﻟﻌﺎﺸﺭﺓ ﻴﻜﻭﻥ ﺸﻜل ﺍﻟﻤﺘﻐﻴﺭ REPEAT_INTERVAL ﻜﺎﻷﺘﻲ:
REPEAT_INTERVAL=>’FREQ=DAILY; INTERVAL=>10
ﻭﻟﻭ ﺃﺭﺩﻨﺎ ﺘﻨﻔﻴﺫ ﺒﻌﺽ ﺍﻟﻤﻬﺎﻡ ﺒﺤﻴﺙ ﻴﺘﻡ ﺘﻨﻔﻴﺫﻫﺎ ﻜل ﻋﺎﻤﻴﻥ ﻴﻜﻭﻥ ﺸﻜل ﺍﻟﻤﺘﻐﻴﺭ ﻜﺎﻷﺘﻲ:
REPEAT_INTERVAL=>’FREQ=YEARLY; INTERVAL=>2
ﻟﻜﻥ ﻫل ﻻﺤﻅت ﺇﻟﻲ ﺍﻷﻥ ﻟﻡ يتم تحديد ﻓﻲ ﺃﻱ ﺸﻬﺭ ﻭﺃﻱ ﺍﺴﺒﻭﻉ ﻭﺃﻱ ﻴﻭﻡ ﺴﻭﻑ ﻴﺘﻡ ﺘﻨﻔﻴﺫ ﻫﺫﻩ ﺍﻟﻤﻬﻤﺎﻡ ﺍﻟﺘﻲ ﺘﻨﻔﺫ ﻜل ﻋﺎﻤﻴﻥ، ﻓﻜل ما هو موضح أن ﻫﺫﻩ ﺍﻟﻤﻬﺎﻡ ﺴﻭﻑ ﻴﺘﻡ ﺘﻨﻔﻴﺫﻫﺎ ﻜل ﻋﺎﻤﻴﻥ
ﻟﻜﻥ ﻗﺩ نحتاج ﺇﻟﻲ ﺘﻔﺼﻴل ﺃﻜﺜﺭ ﻓﻲ ﻤﺜل ﻫﺫﻩ ﺍﻟﺤﺎﻟﺔ ﺴﻨﺴﺘﺨﺩﻡ ﺒﻌﺽ ﺍﻟﻌﻨﺎﺼﺭ:
BYMONTH
BYWEEKNO
BYYEARDAY
BYMONTHDAY
BYHOUR
BYMINUTE
BYSECOND
ﻓﻠﻭ ﺃﺭﺩﻨﺎ ﻤﺜﻼﹰ ﺘﻨﻔﻴﺫ ﺒﻌﺽ ﺍﻟﻤﻬﺎﻡ ﻜل ﺃﺴﺒﻭﻋﻴﻥ ﻋﻠﻲ ﺃﻥ ﻴﺘﻡ ﺫﻟﻙ ﻴﻭﻡ ﺍﻟﺴﺒﺕ ﻓﻲ ﺘﻤﺎﻡ ﺍﻟﺴﺎﻋﺔ ﺍﻟﺘﺎﺴﻌﺔ ﻭﺍﻟﻨﺼﻑ ﺴﻴﻜﻭﻥ ﺸﻜل ﺍﻟﻤﺘﻐﻴﺭ REPEAT_INTERVAL ﻜﺎﻷﺘﻲ:
REPEAT_INTERVAL=>’FREQ=WEEKLY;INTERVAL=2;BYDAY=SAT;BYH OUR=9;BYMINUTE=30’
ﻭﻟﻨﻔﺘﺭﺽ ﺃﻨﻲ ﺃﺭﻴﺩ ﺘﻨﻔﻴﺫ ﺒﻌﺽ ﺍﻟﻤﻬﺎﻡ ﻓﻲ 4 ﺸﻬﻭﺭ ﻤﻌﻴﻨﺔ ﻤﻥ ﻜل ﻋﺎﻡ (Jan & Apr & Jul & & Oct) ﺒﺤﻴﺙ ﻴﺘﻡ ﺘﻨﻔﻴﺫ ﺍﻟﻤﻬﺎﻡ ﻓﻲ ﺍﻟﻴﻭﻡ ﺍل51 ﻤﻥ ﻫﺫﻩ ﺍﻟﺸﻬﻭﺭ،
ﺴﻴﻜﻭﻥ ﺸﻜل ﺍﻟﻤﺘﻐﻴﺭ REPEAT_INTERVAL ﻜﺎﻷﺘﻲ:
REPEAT_INTERVAL=>’FREQ=YEARLY;BYMONTH=JAN,APR,JUL,OCT;MYMONTHDAY=51
ﻟﻨﻔﺘﺭﺽ ﺍﻻﻥ ﺃﻨﻨﺎ ﻨﺭﻴﺩ ﺇﻨﺸﺎﺀ ﺠﺩﻭﻟﺔ ﻟﺘﻨﻔﻴﺫ ﺍﻟﻤﻬﺎﻡ ﺒﺤﻴﺙ ﻴﻜﻭﻥ ﺘﻨﻔﻴﺫ ﺍﻟﻤﻬﺎﻡ ﺒﺸﻜل ﻴﻭﻤﻲ ﻟﻤﺩﺓ ﺸﻬﺭ.
BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( SCHEDULE_NAME=>'DIALY', START_DATE=>SYSDATE, REPEAT_INTERVAL=>'FREG=DAILY;INTERVAL=1', END_DATE=>SYSDATE+30); END; |
ﻴﻤﻜﻥ ﺍﻹﺴﺘﻌﻼﻡ ﻋﻥ ﺍلSchedules ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻋﻥ ﻁﺭﻴﻕ ﺍﻟﺠﺩﻭل DBA_SCHEDULER_SCHEDULES.
ﻴﻤﻜﻨﻨﺎ ﻜﺫﻟﻙ ﺤﺫﻑ ﻫﺫﻩ ﺍﻟﺠﺩﻭﻟﺔ ﻋﻥ ﻁﺭﻴﻕ ﺍﻹﺠﺭﺍﺀ DROP_SCHEDULER.
BEGIN DBMS_SCHEDULER.DROP_SCHEDULE( SCHEDULE_NAME=>'DIALY', FORCE=>TRUE); END; / |
------------------------------------------------