Oracle AQ ʹÓÃ
Ëæ×Ų»Í¬Ó¦ÓÃÄ£¿é¼äµÄÏûÏ¢½»»¥ºÍͨÐųÉΪһ¸ö¹Ø¼üµÄ¹¦ÄÜ£¬²¢ÇÒ±äµÃÔ½À´Ô½ÖØÒª¡£OracleÒýÈëÁËÒ»ÖÖÇ¿´óµÄ¶ÓÁлúÖÆ£¬Í¨¹ýËü³ÌÐò¼ä¿ÉÒÔʵÏÖÐÅÏ¢µÄ½»»¥£¬oracle°ÑËü³Æ×÷ΪAQ - Advanced Queuing. ʹÓÃOracle AQ£¬ÎÒÃDz»ÐèÒª°²×°¶îÍâµÄÖмä¼þ£¬ËüÊÇOracleÊý¾Ý¿âµÄÒ»¸ö¹¦ÄÜ×é¼þ£¬Ö»ÒªÄã°²×°ÁËOracle Êý¾Ý¿â¾Í¿ÉÒÔʹÓÃAQÁË¡£½ÓÏÂÀ´·ÖÁ½²¿·ÖÀ´½éÉÜAQµÄʹÓã¬Ê¹ÓÃ֮ǰÎÒÃÇÒª´´½¨QUEUE.
ÎÒÃÇ´´½¨Ò»¸ö×Ô¼ºµÄAQµÄ¹ÜÀí½ÇÉ« "my_aq_adm_role" ºÍ¹ÜÀíÓû§"aqadm"£¬ÔÙ°ÑOracle AQ ¹ÜÀí½ÇÉ« "aq_adminstrator_role" ÊÚȨ¸ø"my_aq_adm_role".
CREATE ROLE my_aq_adm_role;
GRANT aq_adminsistator_role TO my_aq_adm_role
´´½¨Ò»¸öÓû§µÄ½ÇÉ« "my_aq_user_role" ºÍ ÆÕͨÓû§"aquser" ,ÔÙ°ÑOracle AQµÄÓû§½ÇÉ«"aq_user_role"ºÍһЩ»ù±¾²Ù×÷ÐèÒªµÄϵͳȨÏÞÊÚȨ¸ø "my_aq_adm_role"
CREATE ROLE my_aq_user_role;
GRANT CREATE session, aq_user_role TO my_aq_user_role;
EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'my_aq_user_role',
admin_option => FALSE);
EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'my_aq_user_role',
admin_option = 'FALSE');
ÏÖÔÚÎÒÃÇ´´½¨ AQ¹ÜÀíÓû§
CREATE USER aqadm IDENTIFIED BY aqadm
DEFAULT TABLESPACE elathen
TEMPORARY TABLESPACE temp;
GRANT my_aq_adm_role TO aqadm;
GRANT connect, resource TO aqadm; ----×¢Ò⣬resource½ÇÉ«Ò»¶¨ÒªÊÚ¸øuser,Èç¹ûresource½ÇÉ«ÊÚȨ¸ø
my_aq_adm_role, user½«»áʧȥ unlimited tablespace ȨÏÞ
½Ó×ÅΪÎÒÃÇÏÂÃæexample´´½¨ÆÕͨÓû§
CREATE USER aquser IDENTIFIED BY aquser
DEFAULET TABLESPACE elathen
TEMPORARY TABLESPACE temp;
GRANT my_aq_user_role TO aquser;
ÎÒÃǽ«ÔÚÎÒÃǵÚÒ»¸öqueueÖÐʹÓÃobject type¶ø²»ÊÇNUMBER or VARCHAR2×÷Ϊpayload£¬ÕâʹÎÒÃǸüÌù½üʵ¼ÊÓ¦Óá£(payloadÊÇÈκÎÏûÏ¢¶¼Ê¹ÓõÄÒ»ÖÖÊý¾ÝÀàÐͺͽṹ).
ÉÏÃæ×öµÄ¹¤×÷¶¼ÐèÒªÔÚDBAµÄȨÏÞÏÂ×ö£¬ÏÖÔÚÎÒÃÇÇл»µ½AQ¹ÜÀíÔ±
CONNECT aqadm/aqadm
CREATE TYPE queue_message_type AS OBJECT(
no NUBER,
title VARCHAR2(30),
text VARCHAR2(2000));
/
GRANT EXECUTE ON queue_message_type TO my_aq_user
Ïà¹ØÎĵµ£º
ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬ÔÀ´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......
½ñÌìÓÐͬÊ´òµç»°ÒªÇóÒª°Ñoracle¿âÀïµÄÒ»ÕűíµÄ¶¨Òåµ¼³ö¸ösqlÎļþ£¬µ±Ê±µÄµÚÒ»·´Ó¦¾ÍÊÇÒª½ÌËûÓÃexp/impÀ´»ñµÃ±í½á¹¹¶¨ÒåÎļþ£¬Ôڵ绰ÀïÃæ˵ÁË°ëÌ컹ÊÇûÄ̻ܽáËû£¬Ö»ºÃÅÜÁËÒ»ÌË»ú·¿¡£Â·ÉϾÍÔÚÏ룬oracleÕæÊÇÌÖÑᣬΪʲô²»ÔÚÆóÒµ¹ÜÀíÆ÷ÀïÖ±½Ó¼ÓÉϸö±í½á¹¹µ¼³öµÄÑ¡Ï»¹Òªº¦ÎÒÕâôÅÜÒ»ÌË£¬ÕæÊÇ@@#!!@!#@@@@
ÎÊÁËÏÂÒªµ¼³öÄÄ ......
´ÓOracle 9iR2 ¿ªÊ¼,ORACLEÌṩÁ˱í/±í¿Õ¼äѹËõ¼¼Êõ,ÒÔ¼õÉÙ´ÅÅÌ¿ªÏú,½ÚÊ¡¿Õ¼ä,²¢ÔÚijЩÇé¿öÏÂÌá¸ß²éѯÐÔÄÜ¡£
±íѹËõÊÇÈçºÎ¹¤×÷µÄ
ÔÚOrcle9iµÚ2°æÖУ¬±íѹËõÌØÐÔͨ¹ýɾ³ýÔÚÊý¾Ý¿â±íÖз¢ÏÖµÄÖظ´Êý¾ÝÖµÀ´½ÚÊ¡¿Õ¼ä¡£Ñ¹ËõÊÇÔÚÊý¾Ý¿âµÄÊý¾Ý¿é¼¶±ðÉϽøÐеġ£µ±È·¶¨Ò»¸ö±íÒª±»Ñ¹Ëõºó£¬Êý¾Ý¿â±ãÔÚÿһ¸öÊý¾Ý¿âÊý¾Ý¿éÖб£ ......
SQLÖеĵ¥¼Ç¼º¯Êý
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ× ......
sysdate+(5/24/60/60) ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Ãë
sysdate+5/24/60 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5·ÖÖÓ
sysdate+5/24 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Сʱ
sysdate+5 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Ìì
add_months(sysdate,-5) ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5ÔÂ
add_months(sysdate,-5*12) ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Äê
ÉÏÔÂÄ©µÄÈÕÆÚ£ºselect last_day(add_mon ......