DBA ARABIC

ﻫﺫﺍ ﺍﻟﻨﻭﻉ ﻤﺘﺎﺡ ﻤﻨﺫ ﺍﻹﺼﺩﺍﺭ Oracle9i ﺒﺤﻴﺙ ﻨﺴﺘﻁﻴﻊ ﻤﻥ ﺨﻼﻟﻪ ﻋﻤل ﺍﺴﺘﻌﻼﻡ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻟﻴﺱ ﻟﻠﺒﻴﺎﻨﺎﺕ ﺍﻟﻤﻭﺠﻭﺩﺓ ﺤﺎﻟﻴﺎﹰ ﻭﺇﻨﻤﺎ ﻟﺒﻴﺎﻨﺎﺕ ﻤﻭﺠﻭﺩﺓ ﻓﻲ ﻟﺤﻅﺔ ﺯﻤﻨﻴﺔ ﻓﻲ ﺍﻟﻤﺎﻀﻲ،

ﻭﻴﻌﺘﻤﺩ ﻫﺫﺍ ﺍﻟﻨﻭﻉ ﺃﺴﺎﺴﺎﹰ ﻋﻠﻰ ﺍلUndo Tablespace، ﻓﺒﻤﺠﺭﺩ ﻋﻤل ﺍﻻﺴﺘﻌﻼﻡ ﻴﺘﻡ ﺍﻟﺒﺤﺙ ﻋﻥ ﺍﻟﻤﻌﻠﻭﻤﺎﺕ ﻓﻲ ﺍلUndo Segments، ﻫﺫﻩ ﺍﻟﻤﻌﻠﻭﻤﺎﺕ ﺍﻟﺘﻲ ﺴﺘﻌﺭﺽ ﻟﻠﻤﺴﺘﺨﺩﻡ ﺘﻅل ﻤﺅﻗﺘﺔ

ﻭﻤﺘﺎﺤﺔ ﻓﻘﻁ ﻟلSession ﺍﻟﺤﺎﻟﻴﺔ، ﺒﺎﻟﻁﺒﻊ ﻓﻘﺩ ﺘﻔﺸل ﻋﻤﻠﻴﺔ ﺍلFlashback Query ﺇﺫﺍ ﻟﻡ ﺘﺠﺩ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﻤﻁﻠﻭﺒﺔ ﻓﻲ ﺍلUndo Tablespace ﻤﺜﻼﹰ ﺒﺴﺒﺏ ﻁﻭل ﺍﻟﻤﺩﺓ ﻭﺇﻋﺎﺩﺓ ﻜﺘﺎﺒﺔ ﺒﻴﺎﻨﺎﺕ ﺠﺩﻴﺩﺓ

ﻓﻲ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﻤﻁﻠﻭﺒﺔ ﻟﺫﺍ ﻋﻠﻰ ﻤﺩﻴﺭ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺘﺤﺩﻴﺩ ﻓﺘﺭﺓ ﺍﺤﺘﻔﺎﻅ Retention ﻤﻨﺎﺴﺒﺔ .

ﻭﻟﻨﺘﺼﻭﺭ ﺍﻵﻥ ﺍﻟﺴﻴﻨﺎﺭﻴﻭ ﺍﻟﺘﺎﻟﻲ ﺤﺘﻰ ﻨﺴﺘﻭﻋﺏ ﻋﻤﻠﻴﺔ ﺍلFlashback Query ﺒﺼﻭﺭﺓ ﺃﻭﻀﺢ:

ﻟﺩﻴﻨﺎ ﺍﻵﻥ ﺠﺩﻭل ﻴﺴﻤﻰ Employee ﻴﺤﺘﻭﻯ ﻋﻠﻰ ﻤﺠﻤﻭﻋﺔ ﻤﻥ ﺍﻟﺤﻘﻭل.

SELECT * FROM EMPLOYEE;

ﺍﻟﺠﺩﻭل EMPLOYEE ﻴﺤﺘﻭﻯ ﻋﻠﻰ 8 ﺤﻘﻭل.

ﻟﻨﻔﺘﺭﺽ ﺃﻥ ﺍﻟﻤﺴﺘﺨﺩﻡ ﻗﺎﻡ ﺒﺤﺫﻑ ﺒﻌﺽ ﺍﻟﺤﻘﻭل .

DELETE EMPLOYEE WHERE EMP_NO IN (1,5,7);

ﺍﻵﻥ ﻟﻭ ﻗﻤﻨﺎ ﺒﺎﺴﺘﻌﺭﺍﺽ ﺍﻟﺠﺩﻭل سنجد 5 حقول فقط .

ﻤﺎﺫﺍ ﻟﻭ ﻗﻤﻨﺎ ﺍﻵﻥ ﺒﻌﻤل ﺍﺴﺘﻌﻼﻡ ﻋﻠﻰ ﺍﻟﺠﺩﻭل ﻭﻟﻜﻥ ﻓﻲ ﻓﺘﺭﺓ ﺯﻤﻨﻴﺔ ﻓﻲ ﺍﻟﻤﺎﻀﻲ ﻭﻟﻨﻔﺘﺭﺽ ﺃﻨﻬﺎ ﻗﺒل 20 دقيقة .

ﺒﺎﻟﻁﺒﻊ ﺴﻴﺒﺤﺙ ﻋﻥ ﻫﺫﻩ ﺍﻟﻤﻌﻠﻭﻤﺎﺕ ﻓﻲ ﺍلUndo segments ﻭﻴﻘﻭﻡ ﺒﻌﺭﻀﻬﺎ، ﺃﻤﺎ ﺇﺫﺍ ﻟﻡ ﻴﺠﺩﻫﺎ ﻓﺴﺘﻅﻬﺭ ﺭﺴﺎﻟﺔ ﺨﻁﺄ .

SELECT * FROM EMPLOYEE AS OF TIMESTAMP

TO_TIMESTAMP ('23-06-08:03-33-38','DD-MM- YY:HH٤٢-MI-SS');

ستكون ﻨﺘﻴﺠﺔ ﺍﻻﺴﺘﻌﻼﻡ، ﻋﺭﺽ 8 ﺤﻘﻭل، ﻟﻜﻥ ﻤﺎ ﻫﻭ ﻤﺘﺎﺡ ﺤﺎﻟﻴﺎﹰ ﻫﻭ 5 ﺤﻘﻭل ﻓﻘﻁ. ﻫﻜﺫﺍ ﻗﻤﻨﺎ ﺒﻌﺭﺽ ﻤﻌﻠﻭﻤﺎﺕ ﻓﻲ ﺍﻟﻤﺎﻀﻲ، ﻟﻜﻥ ﻤﺎﺫﺍ ﻟﻭ ﺃﺭﺩﻨﺎ ﺃﻥ ﻨﺄﺘﻲ ﺒﻬﺫﻩ ﺍﻟﻤﻌﻠﻭﻤﺎﺕ ﻓﻲ ﺍﻟﺤﺎﻀﺭ

ﺃﻱ ﺘﻅل ﻫﺫﻩ ﺍﻟﻤﻌﻠﻭﻤﺎﺕ ﻤﻭﺠﻭﺩﺓ ﻓﻲ ﺍﻟﺠﺩﻭل، ﻴﻤﻜﻥ ﺃﻥ ﻨﻨﺸﺊ ﺠﺩﻭل ﺠﺩﻴﺩ ﻤﻥ ﺍﻟﻤﻌﻠﻭﻤﺎﺕ ﺍﻟﻤﺴﺘﻌﺭﻀﺔ ﻭﻤﻥ ﺜﻡ ﻨﻘﻭﻡ ﺒﺤﺫﻑ ﺍﻟﺠﺩﻭل Employee ﻭﺇﻋﺎﺩﺓ ﺘﺴﻤﻴﺔ ﺍﻟﺠﺩﻭل ﺍﻟﺠﺩﻴﺩ ﺇﻟﻰ Employee1 .

CREATE TABLE EMPLOYEE1 AS (SELECT * FROM EMPLOYEE AS OF TIMESTAMP

TO_TIMESTAMP ('23-06-08:03-33-38','DD-MM- YY:HH٤٢-MI-SS'));

ﺍﻵﻥ ﻨﻘﻭﻡ ﺒﺤﺫﻑ ﺍﻟﺠﺩﻭل Employee .

DROP TABLE EMPLOYEE;

ﺜﻡ ﻨﻘﻭﻡ ﺒﺈﻋﺎﺩﺓ ﺘﺴﻤﻴﺔ ﻟﻠﺠﺩﻭل EMPLOYEE1 ﺇﻟﻰ EMPLOYEE .

RENAME EMPLOYEE1 TO EMPLOYEE;

ﺍﻵﻥ ﺍﻟﺠﺩﻭل EMPLOYEE ﻴﺤﺘﻭﻯ ﻋﻠﻰ ﺍﻟﻤﻌﻠﻭﻤﺎﺕ ﺍﻟﺘﻲ ﻜﺎﻨﺕ ﺒﻪ ﻤﻨﺫ 20 ﺩﻗﻴﻘﺔ.

ﻓﻲ ﺍﻟﺨﻁﻭﺍﺕ ﺍﻟﺴﺎﺒﻘﺔ ﻗﻤﻨﺎ ﺒﻌﻤل ﺍﺴﺘﻌﻼﻡ ﻓﻲ Session ﻤﻌﻴﻨﺔ ﻋﻥ ﺒﻴﺎﻨﺎﺕ ﻓﻲ ﻟﺤﻅﺔ ﻤﻌﻴﻨﺔ ﻓﻲ ﺍﻟﻤﺎﻀﻲ، ﻭﻟﻜﻥ ﻤﻥ ﺍﻟﻤﻤﻜﻥ ﻜﺫﻟﻙ ﺃﻥ ﻨﻀﻊ ﻜل ﻫﺫﻩ ﺍلٍSessions ﻓﻲ ﻟﺤﻅﺔ ﻤﻌﻴﻨﺔ ﻤﻥ ﺍﻟﻤﺎﻀﻲ،

ﺒﺤﻴﺙ ﺘﻜﻭﻥ ﻨﺘﻴﺠﺔ ﺠﻤﻴﻊ ﺍﻻﺴﺘﻌﻼﻤﺎﺕ ﻟﻘﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻤﻥ ﺨﻼل ﻫﺫﻩ ﺍلٍSession ﻓﻲ ﻨﻘﻁﺔ ﻤﻌﻴﻨﺔ ﻤﻥ ﺍﻟﻤﺎﻀﻲ ﻴﺘﻡ ﺘﺤﺩﻴﺩﻫﺎ ﺇﺜﻨﺎﺀ ﻋﻤل ﺍلFlashback،

ﻭﻟﻜﻥ ﺒﺎﻗﻲ ﺍلٍSessions ﺍﻟﺘﻲ ﺘﻌﻤل ﻋﻠﻰ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺘﺭﻯ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻓﻲ ﺍﻟﻭﻗﺕ ﺍﻟﺤﻘﻴﻘﻲ ﺍﻵﻥ؛ ﻤﺎ ﻋﺩﺍ ﻫﺫﻩ ﺍلSession ﺍﻟﺘﻲ ﺘﺭﻯ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻓﻲ ﻟﺤﻅﺔ ﻤﻌﻴﻨﺔ ﻤﻥ ﺍﻟﻤﺎﻀﻲ،

ﻴﺘﻡ ﺫﻟﻙ ﻤﻥ ﺨﻼل ﺍﻟﺤﺯﻤﺔ DBMS_FLASHBACK .

ﻟﻠﺭﺠﻭﻉ ﺒﺎلSession ﺍﻟﺤﺎﻟﻴﺔ ﻟﻠﻤﺎﻀﻲ ﻴﻭﻤﺎﹰ ﻜﺎﻤﻼﹰ ﻨﻘﻭﻡ ﺒﺘﻨﻔﻴﺫ ﺍﻹﺠﺭﺍﺀ PROCEDURE ENABLE_AT_TIME الموجود في الحزمة DBMS_FLASHBACK PACKAGE .

DECLARE

QUERY_TIME TIMESTAMP;

BEGIN

QUERY_TIME := TO_TIMESTAMP(SYSDATE -1);

SYS.DBMS_FLASHBACK.ENABLE_AT_TIME ( QUERY_TIME);

COMMIT;

END;

ﻫﻜﺫﺍ ﺨﻼل ﻫﺫﻩ ﺍلSession ﺠﻤﻴﻊ ﺍﻻﺴﺘﻌﻼﻤﺎﺕ ﺘﺭﻯ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻜﻤﺎ ﻟﻭ ﻜﻨﺎ ﺒﺎﻷﻤﺱ .

ﻻﺒﺩ ﻤﻥ ﺍﻹﺸﺎﺭﺓ ﺇﻟﻰ ﺃﻨﻪ ﻤﻥ ﺨﻼل ﻫﺫﺍ ﺍﻟﻨﻤﻁ ﻻ ﻴﻤﻜﻥ ﺇﺠﺭﺍﺀ ﻋﻤﻠﻴﺎﺕ ﺍلDML ﻤﺎﻋﺩﺍ SELECT .

ﻴﻤﻜﻥ ﺇﻟﻐﺎﺀ ﻫﺫﺍ ﺍﻟﻨﻤﻁ ﺒﻭﺍﺴﻁﺔ ﺍﻹﺠﺭﺍﺀ DISABLE ﺍﻟﻤﻭﺠﻭﺩ ﻓﻲ ﺍﻟﺤﺯﻤﺔ DBMS_FLASHBACK .

BEGIN

SYS.DBMS-FLASHBACK.DISABLE;

COMMIT;

END;

ﻫﻜﺫﺍ ﻗﻤﻨﺎ ﺒﺈﻟﻐﺎﺀ ﺍﻟﻨﻤﻁ Flashback .

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

DATABASE

SQL

VIDEOS

Orcale Tutorial Content

Undo Management & Flashback Technology

Flashback Query