OracleÊý¾Ý¿âÓαê
OracleÊý¾Ý¿âÓαêʹÓ÷½·¨
2009-12-30 15:27
SQLÊÇÓÃÓÚ·ÃÎÊOracleÊý¾Ý¿âµÄÓïÑÔ£¬PL/SQLÀ©Õ¹ºÍ¼ÓÇ¿ÁËSQLµÄ¹¦ÄÜ£¬ËüͬʱÒýÈëÁ˸üÇ¿µÄ³ÌÐòÂß¼, ÏÂÃæÔÚ±¾ÎÄÖн«¶ÔÓαêµÄʹÓýøÐÐһϽ²½â,Ï£Íû¿ÉÒԺʹó¼Ò¹²Í¬Ñ§Ï°½ø²½¡£
¡¡¡¡Óαê×ÖÃæÀí½â¾ÍÊÇÓζ¯µÄ¹â±ê¡£ÓαêÊÇSQLµÄÒ»¸öÄڴ湤×÷Çø£¬ÓÉϵͳ»òÓû§ÒÔ±äÁ¿µÄÐÎʽ¶¨Òå¡£ÔÚijЩÇé¿öÏ£¬ÐèÒª°ÑÊý¾Ý´Ó´æ·ÅÔÚ´ÅÅ̵ıíÖе÷µ½¼ÆËã»úÄÚ´æÖнøÐд¦Àí£¬×îºó½«´¦Àí½á¹ûÏÔʾ³öÀ´»ò×îÖÕд»ØÊý¾Ý¿â¡£ÕâÑùÊý¾Ý´¦ÀíµÄËٶȲŻáÌá¸ß£¬·ñÔòƵ·±µÄ´ÅÅÌÊý¾Ý½»»»»á½µµÍЧÂÊ¡£ÓÃÊý¾Ý¿âÓïÑÔÀ´ÃèÊöÓαê¾ÍÊÇÓ³ÉäÔÚ½á¹û¼¯ÖÐÒ»ÐÐÊý¾ÝÉϵÄλÖÃʵÌ壬ÓÐÁËÓα꣬Óû§¾Í¿ÉÒÔ·ÃÎʽá¹û¼¯ÖеÄÈÎÒâÒ»ÐÐÊý¾ÝÁË£¬½«Óαê·ÅÖõ½Ä³Ðк󣬼´¿É¶Ô¸ÃÐÐÊý¾Ý½øÐвÙ×÷£¬ÀýÈçÌáÈ¡µ±Ç°ÐеÄÊý¾ÝµÈ¡£
¡¡¡¡ÓαêÓÐÁ½ÖÖÀàÐÍ£ºÏÔʽÓαêºÍÒþʽÓαꡣÓαêÒ»µ©´ò¿ª£¬Êý¾Ý¾Í´ÓÊý¾Ý¿âÖд«Ë͵½Óαê±äÁ¿ÖУ¬È»ºóÓ¦ÓóÌÐòÔÙ´ÓÓαê±äÁ¿Öзֽâ³öÐèÒªµÄÊý¾Ý£¬²¢½øÐд¦Àí¡£
¡¡¡¡µ±ÏµÍ³Ê¹ÓÃÒ»¸öÒþʽÓαêʱ£¬¿ÉÒÔͨ¹ýÒþʽÓαêµÄÊôÐÔÀ´Á˽â²Ù×÷µÄ״̬ºÍ½á¹û£¬½ø¶ø¿ØÖƳÌÐòµÄÁ÷³Ì¡£ÒþʽÓαê¿ÉÒÔʹÓÃÃû×ÖSQLÀ´·ÃÎÊ£¬µ«Òª×¢Ò⣬ͨ¹ýSQLÓαêÃû×ÜÊÇÖ»ÄÜ·ÃÎÊÇ°Ò»¸ö´¦Àí²Ù×÷»òµ¥ÐÐSELECT²Ù×÷µÄÓαêÊôÐÔ¡£ËùÒÔͨ³£ÔÚ¸Õ¸ÕÖ´ÐÐÍê²Ù×÷Ö®ºó£¬Á¢¼´Ê¹ÓÃSQLÓαêÃûÀ´·ÃÎÊÊôÐÔ¡£ÓαêµÄÊôÐÔÓÐËÄÖÖ,·Ö±ðÊÇSQL %ISOPEN£¬SQL %FOUND£¬SQL %NOTFOUND£¬SQL %ROWCOUNT¡£
¡¡¡¡SQL%ISOPEN·µ»ØµÄÀàÐÍΪ²¼¶ûÐÍ,ÅжÏÓαêÊÇ·ñ±»´ò¿ª£¬Èç¹û´ò¿ª%ISOPENµÈÓÚtrue,·ñÔòµÈÓÚfalse,¼´Ö´Ðйý³ÌÖÐΪÕæ,½áÊøºóΪ¼Ù¡£
¡¡¡¡SQL%NOTFOUND·µ»ØֵΪ²¼¶ûÐÍ,ÅжÏÓαêËùÔÚµÄÐÐÊÇ·ñÓÐЧ£¬Èç¹ûÓÐЧ£¬Ôò%FOUNDDµÈÓÚtrue£¬·ñÔòµÈÓÚfalse,¼´Óë%FOUNDÊôÐÔ·µ»ØÖµÏà·´¡£
¡¡¡¡SQL%FOUND·µ»ØÖµµÄÀàÐÍΪ²¼¶ûÐÍ£¬ÖµÎªTRUE´ú±í²åÈë ɾ³ý ¸üлòµ¥Ðвéѯ²Ù×÷³É¹¦¡£
¡¡¡¡SQL%ROWCOUNT·µ»ØÖµÀàÐÍΪÕûÐÍ£¬·µ»Øµ±Ç°Î»ÖÃΪֹÓαê¶ÁÈ¡µÄ¼Ç¼ÐÐÊý,¼´³É¹¦Ö´ÐеÄÊý¾ÝÐÐÊý¡£ ʾÀý´úÂëÈçÏ£º
set serveroutput on;
¡¡¡¡declare
¡¡¡¡varno varchar2(20);
¡¡¡¡varprice varchar2(20);
¡¡¡¡CURSOR mycur(vartype number) is
¡¡¡¡select emp_no,emp_zc from cus_emp_basic
¡¡¡¡where com_no = vartype;
¡¡¡¡begin
¡¡¡¡if mycur%isopen = false then
¡¡¡¡open&n
Ïà¹ØÎĵµ£º
µ±Ê¹ÓÃ'for update' Ôì³É±íËøסÁË.¿ÉÒÔÓÃDBAµÇ½°´ÕÕÏÂÃæ²½Öè:
1.²é¿´Äĸö±í±»Ëø:
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;
2.²é¿´±»Ëø±íµÄsid ºÍserial# :
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$lo ......
oracleÊý¾Ý¿âͨ¹ý¿ØÖÆÎļþ±£³ÖÊý¾Ý¿âµÄÍêÕûÐÔ£¬Ò»µ©¿ØÖÆÎļþ±»ÆÆ»µÊý¾Ý¿â½«ÎÞ·¨Æô¶¯£¬Òò´Ë½¨Òé²ÉÓöà·¿ØÖÆÎļþ»òÕß±¸·Ý¿ØÖÆÎļþµÄ·½·¨¡£
¿ØÖÆÎļþÊÇÊý¾Ý¿â½¨Á¢µÄʱºò×Ô¶¯Éú³ÉµÄ¶þ½øÖÆÎļþ£¬Ö»ÄÜͨ¹ýʵÀý½øÐÐÐ޸ģ¬Èç¹ûÊÖ¶¯Ð޸ĵĻ°»áÔì³É¿ØÖÆÎļþÓëÎïÀíÐÅÏ¢²»·ûºÏ£¬´Ó¶øµ¼ÖÂÊý¾Ý¿â²»ÄÜÕý³£¹¤×÷¡£
oracleÊý¾Ý¿âͨ ......
Êý¾Ý×Öµädict×ÜÊÇÊôÓÚOracleÓû§sysµÄ¡£
1¡¢Óû§£º
¡¡select username from dba_users;
¸Ä¿ÚÁî
¡¡alter user spgroup identified by spgtest;
2¡¢±í¿Õ¼ä£º
¡¡select * from dba_data_files;
¡¡select * from dba_tablespaces;//±í¿Õ¼ä
¡¡select tablespace_name,sum(bytes), sum(blocks)
from dba_free_s ......
²é¿´±íµÄ´óС£»
select a.blocks,a.bytes,a.bytes/1024/1024 from user_segments a
where a.segment_name='B_M_DZYH_CUST_SIGN_FACT';
Ê×ÏÈ
create table TEST_1228 as select * from user_tables;
insert into TEST_1228 select * from TEST_1228 ;
insert into TEST_1228 select * from TEST_1228 ;
i ......
extent--×îС¿Õ¼ä·ÖÅ䵥λ --tablespace management
block --×îСi/oµ¥Î» --segment management
create tablespace james
datafile '/export/home/oracle/oradata/james.dbf'
size 100M ¡¡¡¡¡¡¡¡¡¡¡¡--³õʼµÄÎļþ´óС¡¡
autoextend On¡¡¡¡¡¡¡¡ --×Ô¶¯Ôö³¤
next 10M¡ ......