ÀûÓÃoracle¿ìÕÕdblink½â¾öÊý¾Ý¿â±íͬ²½ÎÊÌâ
±¾ÊµÀýÒÑÍêȫͨ¹ý²âÊÔ,µ¥Ïò,ÓÖÏòͬ²½¶¼¿ÉʹÓÃ.
--Ãû´Ê˵Ã÷£ºÔ´——±»Í¬²½µÄÊý¾Ý¿â
Ä¿µÄ——Ҫͬ²½µ½µÄÊý¾Ý¿â
ǰ6²½±ØÐëÖ´ÐÐ,µÚ6ÒÔºóÊÇһЩ¸¨ÖúÐÅÏ¢.
--1¡¢ÔÚÄ¿µÄÊý¾Ý¿âÉÏ£¬´´½¨dblink
drop public database link dblink_orc92_182;
Create public DATABASE LINK dblink_orc92_182 CONNECT TO bst114 IDENTIFIED BY password USING 'orc92_192.168.254.111';
--dblink_orc92_182 ÊÇdblink_name
--bst114 ÊÇ username
--password ÊÇ password
--'orc92_192.168.254.111' ÊÇÔ¶³ÌÊý¾Ý¿âÃû
--2¡¢ÔÚÔ´ºÍÄ¿µÄÊý¾Ý¿âÉÏ´´½¨ÒªÍ¬²½µÄ±í(×îºÃÓÐÖ÷¼üÔ¼Êø,¿ìÕղſÉÒÔ¿ìËÙË¢ÐÂ)
drop table test_user;
create table test_user(id number(10) primary key,name varchar2(12),age number(3));
--3¡¢ÔÚÄ¿µÄÊý¾Ý¿âÉÏ£¬²âÊÔdblink
select * from test_user@dblink_orc92_182; //²éѯµÄÊÇÔ´Êý¾Ý¿âµÄ±í
select * from test_user;
--4¡¢ÔÚÔ´Êý¾Ý¿âÉÏ£¬´´½¨ÒªÍ¬²½±íµÄ¿ìÕÕÈÕÖ¾
Create snapshot log on test_user;
--5¡¢´´½¨¿ìÕÕ£¬ÔÚÄ¿µÄÊý¾Ý¿âÉÏ´´½¨¿ìÕÕ
Create snapshot sn_test_user as select * from test_user@dblink_orc92_182;
--6¡¢ÉèÖÿìÕÕË¢ÐÂʱ¼ä(Ö»ÄÜÑ¡ÔñÒ»ÖÖˢз½Ê½,ÍÆ¼öʹÓÿìËÙË¢ÐÂ,ÕâÑù²Å¿ÉÒÔÓô¥·¢Æ÷Ë«Ïòͬ²½)
¿ìËÙË¢ÐÂ
Alter snapshot sn_test_user refresh fast Start with sysdate next sysdate with primary key;
--oracleÂíÉÏ×Ô¶¯¿ìËÙˢУ¬ÒÔºó²»Í£µÄË¢ÐÂ,Ö»ÄÜÔÚ²âÊÔʱʹÓÃ.ÕæÊµÏîĿҪÕýȷȨºâË¢ÐÂʱ¼ä.
ÍêȫˢÐÂ
Alter snapshot sn_test_user refresh complete Start with sysdate+30/24*60*60 next sysdate+30/24*60*60;
--oracle×Ô¶¯ÔÚ30Ãëºó½øÐеÚÒ»´ÎÍêȫˢУ¬ÒÔºóÿ¸ô30ÃëÍêȫˢÐÂÒ»´Î
--7¡¢ÊÖ¶¯Ë¢Ð¿ìÕÕ,ÔÚûÓÐ×Ô¶¯Ë¢ÐµÄÇé¿öÏÂ,¿ÉÒÔÊÖ¶¯Ë¢Ð¿ìÕÕ.
ÊÖ¶¯Ë¢Ð·½Ê½1
begin
dbms_refresh.refresh('sn_test_user');
end;
ÊÖ¶¯Ë¢Ð·½Ê½2
EXEC DBMS_SNAPSHOT.REFRESH('sn_test_user','F'); //µÚÒ»¸ö²ÎÊýÊÇ¿ìÕÕÃû,µÚ¶þ¸ö²ÎÊý F ÊÇ¿ìËÙˢРC ÊÇÍêȫˢÐÂ.
--8.Ð޸ĻỰʱ¼ä¸ñʽ
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--9.²é¿´¿ìÕÕ×îºóÒ»´ÎË¢ÐÂʱ¼ä
SELECT NAME,LAST_REFRESH from ALL_SNAPSHOT_REFRESH_TIMES;
--10.²é¿´¿ìÕÕÏ´ÎÖ´ÐÐʱ¼ä
select last_date,next_d
Ïà¹ØÎĵµ£º
°´±í·½Ê½µÄµ¼³ö£¬µ¼Èë
exp SYSTEM/SYSTEM@SBJSDB00 TABLES=KOKAN90.MASTER_SEND_LIMIT LOG=C:\A.LOG
IMP SYSTEM/SYSTEM@SBJSDB00 IGNORE=Y FULL=Y LOG=C:\B.LOG
֮ǰÓùýºÜ¶àÓï¾ä¶¼±¨´í£¬µÃµ½µÄ½ÌѵÊÇ ÏÈÖ´ÐÐexp SYSTEM/SYSTEM@SBJSDB00£¬°´Ìáʾһ²½²½ÊäÈë³É¹¦¹ýºó£¬ÔÙ°ÑÌáʾ·Òë³É²ÎÊýºÍ²ÎÊýÖµ
¹ØÓÚ²ÎÊý˵Ã÷Óà exp hel ......
1.ÔÚɾ³ýÒ»¸ö±íÖеÄÈ«²¿Êý¾Ýʱ£¬ÐëʹÓÃTRUNCATE TABLE ±íÃû;ÒòΪÓÃDROP TABLE£¬DELETE * from ±íÃûʱ£¬TABLESPACE±í¿Õ¼ä¸Ã±íµÄÕ¼Óÿռ䲢δÊÍ·Å£¬·´¸´¼¸´ÎDROP£¬DELETE²Ù×÷ºó£¬¸ÃTABLESPACEÉϰÙÕ׵Ŀռä¾Í±»ºÄ¹âÁË¡£
¡¡¡¡
¡¡¡¡2.having ×Ó¾äµÄÓ÷¨
¡¡¡¡
¡¡¡¡having ×Ó¾ä¶Ô group by ×Ó¾äËùÈ·¶¨µÄÐÐ×é½øÐпØÖÆ,having ×Ó ......
oracle Set ÃüÁî½éÉÜ
http://luoke920.javaeye.com/blog/323743
1). ÉèÖõ±Ç°sessionÊÇ·ñ¶ÔÐ޸ĵÄÊý¾Ý½øÐÐ×Ô¶¯Ìá½»
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
2)£®ÔÚÓÃstartÃüÁîÖ´ÐÐÒ»¸ösql½Å±¾Ê±£¬ÊÇ·ñÏÔʾ½Å±¾ÖÐÕýÔÚÖ´ÐеÄSQLÓï¾ä
SQL> SET ECHO {ON|OFF}
3).ÊÇ·ñÏÔʾµ±Ç°sqlÓï¾ä²éѯ»òÐ޸ĵÄÐÐÊý ......
OracleÖ´Ðмƻ®µÄÏà¹Ø¸ÅÄ
rowid£ºÏµÍ³¸øoracleÊý¾ÝµÄÿÐи½¼ÓµÄÒ»¸öαÁУ¬°üº¬Êý¾Ý±íÃû³Æ£¬Êý¾Ý¿âid£¬´æ´¢Êý¾Ý¿âidÒÔ¼°Ò»¸öÁ÷Ë®ºÅµÈÐÅÏ¢£¬rowidÔÚÐеÄÉúÃüÖÜÆÚÄÚΨһ¡£
recursive sql£ºÎªÁËÖ´ÐÐÓû§Óï¾ä£¬ÏµÍ³¸½¼ÓÖ´ÐеĶîÍâ²Ù×÷Óï¾ä£¬Æ©Èç¶ÔÊý¾Ý×ÖµäµÄά»¤µÈ¡£
row source£¨ÐÐÔ´£©£ºoracleÖ´Ðв½Öè¹ý³ÌÖУ¬ÓÉÉÏÒ» ......
OracleË÷Òý·ÖÇøÔÓ̸
<> OracleÊý¾Ý¿âÖУ¬ÓÐÁ½ÖÖÀàÐ͵ķÖÇøË÷Òý£¬È«¾ÖË÷ÒýºÍ±¾µØË÷Òý£¬ÆäÖб¾µØË÷ÒýÓÖ¿ÉÒÔ·ÖΪ±¾µØÇ°×ºË÷ÒýºÍ±¾µØ·Çǰ׺Ë÷Òý¡£ÏÂÃæ¾Í·Ö±ð¿´¿´Ã¿ÖÖÀàÐ͵ÄË÷Òý¸÷×ÔµÄÌØµã¡£
È«¾ÖË÷ÒýÒÔÕû¸ö±íµÄÊý¾ÝΪ¶ÔÏó½¨Á¢Ë÷Òý£¬Ë÷Òý·ÖÇøÖеÄË÷ÒýÌõÄ¿¼È¿ÉÄÜÊÇ»ùÓÚÏàͬµÄ¼üÖµµ«ÊÇÀ´×Ô²»Í¬µÄ·ÖÇø£¬Ò²¿ÉÄÜÊǶà¸ö²»Í¬¼üÖ ......