oracle scnÑо¿
ÉîÈëÁ˽âOracle SCN (1)
--------------------------------------------------------------------------------
£Û˵Ã÷£Ý±¾À´ÔÚÑо¿Backup and Recovery£¬¿ÉÊÇÔÚ×öʵÑéµÄʱºòÓöµ½Ò»Ð©ÎÊÌâ²»ÊǺÜÀí½â£¬½ø¶ø×·¸ùÇóÔ´£¬Ïë¸ãÇå³þ£¬ÓÚÊDZØÐë¸ãÇå³þSCNºÍRecoveryÖ®¼äµÄ¹Øϵ¡£ÓÚÊǺõת¶øÑо¿SCN£¬ÕâÁ½Ìì¿´Á˺ܶàÎÄÕ£¬°üÀ¨EygleÍøÕ¾ÉϵÄÎÄÕ£¬itpubÉÏbitiµÈ´óʦÒÔÇ°·¢¹ý»òÕß²ÎÓëÌÖÂÛ¹ýµÄÌû×Ó£¬·¢ÏÖ˼·ÓÐÄ£ºýµ½ÇåÎú£¬ÔÙÓÉÇåÎúµ½Ä£ºý£¬Ô½×ßÔ½ÉÖÁ½ñ»¹Ã»ÓÐÍêÈ«¸ãÇå³þ¡£¾ö¶¨ÔÝʱµ½´ËΪֹ£¬µÈÒÔºó¸÷·½ÃæµÄ֪ʶ»ýÀÛ¶àÁË£¬ÔÙת¹ýÀ´Ñо¿Ò»Ï¡£±¾Ïë×Ô¼ºÐ´¸ö×ܽáÐÔµÄÎÄÕ£¬¿ÉÊÇ·¢ÏÖÁËһƪÎÄÕÂÒѾдµÄºÜºÃÁË£¬ÎÒ¾ÍÀÁ¶èµÄÒÔÒÝ´ýÀÍ£¬copy¹ýÀ´ÁË£¬ÔÚ´Ëллǰ±²ÃǵŤ×÷£¬´ó¼ÒÐÁ¿àÁË¡«
SCNµÄ¸ÅÄî
SCNÊÇ˳ÐòµÝÔöµÄÒ»¸öÊý×Ö£¬ÔÚOracleÖÐÓÃÀ´±êʶÊý¾Ý¿âµÄÿһ´Î¸Ä¶¯£¬¼°ÆäÏȺó˳Ðò¡£SCNµÄ×î´óÖµÊÇ0xffff.ffffffff¡£
SCNµÄ¹ÜÀí·½Ê½
Oracle¶ÔSCNµÄ¹ÜÀí£¬·ÖΪµ¥½ÚµãºÍRACÁ½ÖÖ·½Ê½¡£
µ¥½ÚµãµÄinstanceÖÐ
µ¥½ÚµãµÄinstanceÖУ¬SCNÖµ´æÔÚSGAÇø£¬ÓÉsystem commit number latch±£»¤¡£Èκνø³ÌÒªµÃµ½µ±Ç°µÄSCNÖµ£¬¶¼ÒªÏȵõ½Õâ¸ölatch¡£
RAC/OPS»·¾³ÖÐ
Oracleͨ¹ýÅŶӻúÖÆ(Enqueue)ʵÏÖSCNÔÚ¸÷²¢ÐнڵãÖ®¼äµÄ˳ÐòÔö³¤¡£¾ßÌåÓÐÁ½ÖÖ·½·¨£º
LamportËã·¨£ºÓÖ³ÆÃæ°ü·¿Ëã·¨£¬ÏÈÀ´ÏÈ·þÎñËã·¨¡£¸úºÜ¶àÒøÐвÉÓõÄÅŶӻúÖÆÒ»Ñù¡£¿Í»§µ½ÁËÒøÐУ¬ÏÈÁìÈ¡Ò»¸ö·þÎñºÅ¡£Ò»µ©Ä³¸ö´°¿Ú³öÏÖ¿ÕÏУ¬ÓµÓÐ×îС·þÎñºÅµÄ¿Í»§¾Í¿ÉÒÔÈ¥¿ÕÏд°¿Ú°ìÀíÒµÎñ¡£
Commit¹ã²¥Ëã·¨£ºÒ»ÓÐcommitÍê³É£¬×îеÄSCN¾Í¹ã²¥µ½ËùÓнڵãÖС£
ÉÏÊöÁ½ÖÖËã·¨¿ÉÒÔͨ¹ýµ÷Õû³õʼ»¯²ÎÊýmax_commit_propagation_delayÀ´Çл»¡£ÔÚ¶àÊýϵͳ(³ýÁËCompaq Tur64 Unix)ÖУ¬¸Ã²ÎÊýµÄĬÈÏÖµ¶¼ÊÇ700ÀåÃë(centisecond)£¬²ÉÓÃLamportËã·¨¡£Èç¹û¸ÃֵСÓÚ100ÀåÃ룬Oracle¾Í²ÉÓù㲥Ëã·¨£¬²¢ÇҼǼÔÚalert.logÎļþÖС£
¼¸ÖÖÖØÒªµÄSCN
Commit SCN
µ±Óû§Ìá½»commitÃüÁîºó£¬ÏµÍ³½«µ±Ç°scn¸³¸ø¸Ãtransaction¡£ÕâЩÐÅÏ¢¶¼·´Ó³ÔÚredo bufferÖУ¬²¢ÂíÉϸüе½redo logÎļþÀï¡£
Offline SCN
³ýÁËSystem tablespaceÒÔÍâµÄÈκαí¿Õ¼ä£¬µ±ÎÒÃÇÖ´ÐÐSQL>alter tablespace…offline normalÃüÁîʱ£¬¾Í»á´¥·¢Ò»¸öcheckpoint£¬½«ÄÚ´æÖеÄdirty bufferдÈë´ÅÅÌÎļþÖС£CheckpointÍê³Éºó£¬Êý¾ÝÎļþÍ·»á¸üÐÂcheckpoint scnºÍoffline normal scnÖµ¡£ÆäÖÐÊý¾Ý¿âÎļþÍ·µÄcheckpoint scnÖµ¿Éͨ¹ý²éѯÁÐx$kccfe.fecpsµÃµ½¡£
Èç¹ûÖ´ÐÐSQL>alter table
Ïà¹ØÎĵµ£º
ÔÚ$ORACLE_HOME/sqlplus/admin/login.sql (»òÕßÊÇglogin.sql)ÖÐÌí¼Ó£º
set termout off
COLUMN prompter new_value m_prompt
select host_name || ':' || instance_name ||' >' prompter
from sys.v_$insta ......
rlwrapʹÓ÷½·¨
¹Ø¼ü×Ö: rlwrapʹÓ÷½·¨
ÔÚWindows²Ù×÷ϵͳÉÏ,µ±ÔÚDOSÃüÁî´°¿ÚÖÐÔËÐÐSQL*PlusµÄʱºò,¿ÉÒÔʹÓÃÏòÉÏ,ÏòϼüÀ´Ìø»Ø֮ǰÒѾִÐйýµÄSQLÓï¾ä.Äã¿ÉÒÔ¸ù¾ÝÐèÒªÐÞ¸ÄËûÃÇ,È»ºó°´Enter¼üÖØÐÂÌá½»Ö´ÐÐ.
È»¶ø,µ±ÔÚLinux ShellÖÐÔËÐÐSQL*PlusµÄʱºò,²¢²»Ìṩä¯ÀÀÀúÊ·ÃüÁîÐеŦÄÜ.
ΪÁËÔÚLinuxÖдﵽͬÑùµÄÄ¿µÄ,Ä ......
1. select * from emp;
2. select empno, ename, job from emp;
3. select empno ±àºÅ, ename ÐÕÃû, job ¹¤×÷ from emp;
4. select job from emp;
5. select distinct job from emp;
6. select distinct empno, job from emp;
˵Ã÷:ÒòΪ¹ÍÔ±±àºÅ²»Öظ´, ËùÒÔ´Ëʱ֤Ã÷ËùÓеÄÁÐûÓÐÖظ´,ËùÒÔ²»ÄÜÏû ......
k ˵(14:52):
ÎʸöÎÊÌâ ²éѯÊý¾Ý¿â±íµÄʱºò ¸Ã±í»¹ÄÜдÊý¾ÝÂð ÊÇ°´ÕÕÅŶӵÄ˳Ðò »¹ÊÇ°´ÕÕ²Ù×÷µÄʱ¼ä˳Ðò(²»ÖªµÀoracleÈçºÎ´¦ÀíÕâ¸öÎÊÌ⣩ ¼ÙÉèÒ»¸öÇé¿ö£ºÎÒÏÖÔÚÔÚ²éѯ¼Æ·Ñ±í£¨Õâ¸öʱ¼ä¿ÉÄÜÒª»¨10·ÖÖÓ£© 10·ÖÖÓÓкܶà¼Æ·ÑÊý¾ÝÒªÈë¼Æ·Ñ±í£¨ÄÇÕâ¸öдÊý¾ÝÊÇÒªµÈÎÒ²éѯÍêÁËÖ®ºó²ÅÄÜдÂ𣩠дÊý¾Ý¿â¿Ï¶¨º ......