ÀûÓÃ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
Ïà¹ØÎĵµ£º
C#ÖвÙ×÷OracleʱµÄSQLÓï¾ä²ÎÊýµÄÓ÷¨
OracleTransaction myTrans ;
conn.Open();
myTrans =conn.BeginTransaction(IsolationLevel.ReadCommitted) ......
Êý¾ÝÀàÐÍʹÓÃ
1.ÉùÃ÷£º
a.¸ñʽ£ºVariable_name [constant] databyte [not null] [:=default expression]
b.±äÁ¿Óë³£Á¿ÉùÃ÷»ù±¾Ò»Ö£¬Ê¹ÓÃconstantÉùÃ÷µÄΪ³£Á¿£¬²»Ê¹ÓÃΪ±äÁ¿£¬²¢ÇÒ¸³ÖµÖ»ÄÜÔÚDECLAREÇøÓò¸³Öµ£»
c.ʹÓÃ%TYPEºÍ%ROWTYPEÉùÃ÷¿ÉÒÔʹ±äÁ¿µÄÀàÐÍÓë±íÖÐ×Ö¶ÎÀàÐÍ»òÕû¸ö¼Ç¼ÀàÐͱ ......
Òì³£
Ô¤¶¨ÒåÒì³£
oracleΪ³£¼û´íÎóÔ¤¶¨Òå
ÔÚDBMS_STANDARD³ÌÐò°üÖÐÌṩÁËÕâЩ¶¨Òå
²»ÐèÒªÏÔʾÉùÃ÷
declare
sex student.stu_sex%type;
begin
select stu_sex into sex from student;
dbms_output.put_line('sex:'||sex);
exception
&nbs ......
SQLÖеĵ¥¼Ç¼º¯Êý
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO &nbs ......
OracleÈÏ֤ר¼Ò——OCP£¬ÊÇÓÉOracle¹«Ë¾ÊÚȨ¹ú¼Ê¿¼ÊÔÈÏÖ¤ÖÐÐĶԿ¼Éú½øÐеÄ×ʸñÈÏÖ¤¡£¿¼Éú°´¿¼ÊÔ±ê×¼ÒªÇó²Î¼Ó¼¸Ãſγ̵Ŀ¼ÊÔ(Ò»°ãΪ3—5ÃÅ)£¬ÔÚͨ¹ýÈ«²¿¿¼ÊԺ󣬱ã¿É»ñµÃOCPµÄר¼ÒÈÏÖ¤¡£
ĿǰOCPÈÏÖ¤¿¼ÊÔ·ÖΪ£º
Database AdministratorDatabase OperatoDatabase DeveloperJava DeveloperApplication Consul ......