16 13 / ( ) 16-1 SQL*Net/Net8 SQL*Net/Net8 SQL*Net/Net8 16-1 / S Q L SQL*Net V2 N e t 8 S Q L * N e t N e t ( ) 16.1 S Q L O r a c l e S Q L
16 401 ) ( H R _ L I N K create database link p u b l i c ( H R _ L I N K ) ( h q ) H R _ L I N K O r a c l e H R _ L I N K E M P L O Y E E w h e r e E M P L O Y E E 1 6-2 select*from EMPLOYEE@HR_LINK where office = 'ANNAPOLIS' HR_LINK HQ ( LONG ) 16-2 L O N G f r o m E M P L O Y E E @ H R _ L I N K H R _ L I N K 16. 4 16.2 Two-Phase Commit(2PC, ) O r a c l e 2 P C 16-3 u p d a t e ( ) u p d a t e ( E M P L O Y E E u p d a t e
402 Oracle ( E M P L O Y E E @ H R _ L I N K ) HR_LINK HQ 16-3 2 P C (global coordinator) 2 P C 16. 5 16.3 O r a c l e (database trigger) i n s e r t ( ) O r a c l e ( s n a p s h o t ) w h e r e group by L O N G LONG RAW B F I L E
16 403 ( simple snapshot) (snapshot log) ( c o m p l e x s n a p s h o t ) 16-4 MASTER 1 REMOTE 1 EMPLOYEE EMPLOYEE a) MASTER 1 REMOTE 1 16-4 SQL*Plus c o p y ( ) ( 13. 6 c o p y ) c o p y 16.4 b) t n s n a m e s. o r a 16.4.1
404 Oracle ( F Q O N full qualified object name) F Q O N (global object name) F Q O N 16-5 F Q O N (, ) F Q O N 16-5 F Q O N i n i t. o r a G L O B A L _ N A M E S T R U E ( h q ) t n s n a m e s. o r a F Q O N H Q H L O C t n s n a m e s. o r a T C P / I P t n s n a m e s. o r a H R _ L I N K F Q O N H R H R F Q O N
16 405 connect to current_user (default connection) H R _ L I N K F Q O N 1) t n s n a m e s. o r a 2) t n s n a m e s. o r a 3) connect to connect current_user 4) f r o m H R. E M P L O Y E E H R ( ) (User pseudocolumn) O r a c l e R E M O T E _ T M P H R _ L I N K h q E M P L O Y E E @ H R _ L I N K E n a m e t n s n a m e s. o r a /
406 Oracle create database link s h a r e d H R _ L I N K _ S H A R E D connect to current_user authenticated by C R E ATE SESSION H R _ L I N K _ S H A R E D H R C R E AT E S E S S I O N s h a r e d s h a r e d 16.4.2 D B A _ D B _ L I N K S U S E R _ D B _ L I N K S D B A _ D B _ L I N K S D B A _ D B _ L I N K S Y S. L I N K $ O W N E R D B _ L I N K U S E R N A M E H O S T C R E AT E D ( H R _ L I N K ) S Q L * N e t 4 i n i t. o r a O P E N _ L I N K S S Q L * N e t N e t 8 13 Net 8 Assistant t n s n a m e s. o r a / UNIX rcp( ) /
16 407 alter user O r a c l e 8 O r a c l e F Q O N F Q O N H R H R _ L I N K S C O T T O r a c l e S C O T T H R _ L I N K O r a c l e create database link S C O T T. H R _ L I N K 16.4.3 i n s e r t d e l e t e u p d a t create public database link TRIGGER_LINK using r e m o t e l ; T R I G G E R _ L I N K ( r e m o t e 1 ) c o n n e c t r e m o t e 1 r e m o t e 1 E M P L O Y E E T R I G G E R _ L I N K
408 Oracle n e w E M P L O Y E E D B A _ T R I G G E R S C O P Y _ D ATA D B A _ T R I G G E R S : 16.4.4 (complex snapshot) (simple snapshot)
16 409 group by C R E AT E TA B L E UNLIMITED TA B L E S PA C E H R _ L I N K ( ) ( E M P _ D E P T _ C O U N T ) ( S y s D a t e + 7 ) O r a c l e 19 S Y S create snapshot A ( group by ) H R. E M P L O Y E E O r a c l e S N A P $ _s n a p s h o t n a m e ( ) M V I E W $ _s n a p s h o t n a m e Oracle Enterprise Manger Schema Manager(Oracle ) 1 6-6 E M P _ D E P T _ C O U N T create snapshot
410 Oracle 16-6 O E M drop snapshot alter sanpshot create index S N A P $ _s n a p s h o t n a m e D B _ S A N P S H O T S D B A _ S H A P S H O T S 1. ( ) E M P _ D E P T _ C O U N T E M P L O Y E E E M P L O Y E E E M P L O O Y E E ( E M P L O Y E E ) with rowid with rowid
16 411 O r a c l e R o w I D ( ) 2. E M P L O Y E E D E P T / E M P L O Y E E D E P T N O D E P T ( ) (refresh group) D B M S _ R E F R E S H D B M S _ R E F R E S H M A K E A D D S U B T R A C T C H A N G E D E S T R O Y R E F R E S H U S E R _ R E F R E S H U S E R _ R E F R E S H _ C H I L D R E N D B M S _ R E F R E S H M A K E 4 L O C A L _ E M P L O C A L _ D E P T
412 Oracle l i s t L O C A L _ E M P L O C A L _ D E P T E M P _ G R O U P ( ) l a x T R U E D B M S _ R E F R E S H A D D M A K E A D D l a x l a T R U E : D B M S _ R E F R E S H S U B T R A C T M A K E A D D ( ) S U B T R A C T D B M S _ R E F R E S H C H A N G E n e x t _ d a t e create snapshot start with i n t e r v a l ( ) c r e a t e s n a s h o t n e x t E M P _ G R O U P 3 ( n e x t _ d a t e N U L L )
16 413 E M P _ G R O U P 3 D B M S _ R E F R E S H D E S T R O Y M A K E i m p l i c i t _ d e s t r o y T R U E 3. 5 E M P _ D E P T _ C O U N T group by 4. E M P _ D E P T _ C O U N T refresh complete r e f r e s h L E T E FA S T F O R C E FA S T C O M P L E T E start with S y s D a t e n e x t O r a c l e S N P S N P i n i t. o r a J O B _ Q U E U E _ P R O C E S S E S 0 ( S N P 0 ) 36 S N P S N Pn ( ) i n i t. o r a
414 Oracle J O B _ Q U E U E _ I N T E RVA L 6 0 J O B _ Q U E U E _ P R O C E S S E S J O B _ Q U E U E _ I N T E RVA L D B M S _ S N A P S H O T R E F R E S H e x e c u t e D B M S _ S N A P S H O T R E F R E S H ( ) F f C c? D B M S _ S N A P S H O T R E F R E S H _ A L L R E F R E S H _ A L L D B M S _ R E F R E S H R E F R E S H R E F R E S E M P _ G R O U P 5. AFTER ROW C R E ATE TRIGGER C R E ATE TA B L E O r a c l e 1 9 E M P L O Y E E D ATA _ 2
16 415 p c t f r e e p c t u s e d M L O G $ _t a b l e n a m e R o w I D M L O G $ _t a b l e n a m e R o w I D R o w I alter snapshot log E M P L O Y E E D B A _ S N A P S H O T _ L O G S ( M L O G $ _t a b l e n a m e ) 6 drop snapshot log 16.4.5 ( insert as select ) 25 % insert as s e l e c t 1 6. 4. 7 16.4.6 O r a c l e create table as select ( 7 ) I m p o r t ( o ffline instantiation)
416 Oracle E M P _ D E P T _ C O U N T H R _ L I N K H R _ L I N K E M P _ D E P T _ C O U N T ( ) I m p o r t C O M M I T B U F F E R I m p o r t E x p o r t 10 16.4.7 p c t u s e d D B M S _ S N A P S H O T P U R G E _ L O G P U R G E _ L O G n u m D E L E T E n u m n u m 1 P U R G E _ L O G E M P L O Y E E O r a c l e t r u n c a t e
16 417 E M P L O Y E E E M P L O Y E E M P L O Y E E Row ID / ( R o w I D ) 16.5 16-3 O r a c l e O r a c l e 2 P C ( ) 2 P C in doubt( ) ( global coordiantor ) D B A _ 2 P C _ P E N D I N G D B A _ 2 P C _ P E N D I N G D B A _ 2 P C _ P E N D I N G R E C O ( R e c o v e r e r ) D B A _ 2 P C _ P E N D I N G R E C O D B A _ 2 P C _ P E N D I N G i n i t. o r a D I S T R I B U T E D _ T R A N S A C T I O N S R E C O R E C O
418 Oracle (commit point strength) ( c o m m i t point site) O r a c l e (commit point strength) i n i t. o r a C O M M I T _ P O I N T _ S T R E N G T H 100 20 C O M M I T _ P O I N T _ S T R E N G T H 255 200 8 0 % 160 ( 200 8 0 %) ( 200 ) 16.6 F Q O N ( D N S ) O r a c l e ( d o m a i n ) C O M E D U H Q. M Y C O R P. C O M H Q C O M M Y C O R P F Q O N O r a c l e D N S D N S i n i t. o r a D B _ N A M E D B _ D O M A I N D N S D B _ D O M A I N L O C H Q. M Y C O R P. C O M i n i t. o r a i n i t. o r a G L O B A L _ N A M E S T R U E
16 419 i n i t. o r a G L O B A L _ N A M E S T R U E L O C L O C. H Q. M Y C O R P. C O M O r a c l e D B _ D O M A I N H Q. M Y C O R P. C O M L O C L O C. H Q. M Y C O R P. C O M ( ) ( c o m m u n i t y ) SQL*Net V2 T C P. H Q. M Y C O R P. C O M H Q. M Y C O R P. C O M T C P / I P S Q L * N e t T C P S Q L * N e t C O M T C P / I P T C P. H Q. M Y C O R P 16.7 6 Command Center I / O
420 Oracle ( ) 6 16.8 8 ( SQL*Plus copy ) ( ) u p d a t e SQL*Plus copy O r a c l e (Advanced Replication Option) O r a c l e ( ) Oracle manuals group by connect by 25 %
16 421 16-7 ( 1 6-7 a ) ( 1 6-7 b ) MASTER 1 REMOTE 1 EMPLOYEE RESUMES a) MASTER 1 REMOTE 1 b) 16-7
422 Oracle S N A P $ _t a b l e n a m e 8 S Q L 16.9 O r a c l e ( J O B _ Q U E U E _ P R O C E S S E S J O B _ Q U E U E _ I N T E RVA L ) D B M S _ J O B S U B M I T R E M O V E C H A N G E W H AT N E X T _ D AT E I N T E RVA L B R O K E N R U N S U B M I T R E M O V E R U N S U B M I T j o b O r a c l e S Y S. J O B S E Q m y p r o c ( B R O K E N C H A N G E I N T E RVA L N E X T _ D AT E W H AT ) ( R E M O V E ) ( R U N ) D B M S _ J O B S B R O K E N b r o k e n b r o k e B R O K E N
16 423 b r o k e n FA L S E b r o k e n b r o k e n ( b r o k e n T R U E ) C H A N G E w h a t C H A N G E ( N U L L ) C H A N G E I N T E RVA L N E X T _ D AT E P L / S Q L W H AT P L / S Q L I N T E RVA L N E X T _ D AT E W H AT C H A N G E C H A N G E N U L L R E M O V E R U N R U N b r o k e n ( ) O r a c l e O r a c l e D B M S _ J O B d b m s j o b. s q l O r a c l e / r d b m s / a d m i n