OracleÊý¾Ý¿âÖÐË÷ÒýµÄά»¤
×÷Õߣº ÈÕÆÚ£º2005-12-8 1:43:32 À´Ô´£ºInternet µã»÷£º´Î ÆÀÂÛ
¡¡¡¡±¾ÎÄÖ»ÌÖÂÛOracleÖÐ×î³£¼ûµÄË÷Òý£¬¼´ÊÇB-treeË÷Òý¡£±¾ÎÄÖÐÉæ¼°µÄÊý¾Ý¿â°æ±¾ÊÇOracle8i¡£
¡¡¡¡Ò». ²é¿´ÏµÍ³±íÖеÄÓû§Ë÷Òý
¡¡¡¡ÔÚOracleÖУ¬SYSTEM±íÊÇ°²×°Êý¾Ý¿âʱ×Ô¶¯½¨Á¢µÄ£¬Ëü°üº¬Êý¾Ý¿âµÄÈ«²¿Êý¾Ý×ֵ䣬´æ´¢¹ý³Ì¡¢°ü¡¢º¯ÊýºÍ´¥·¢Æ÷µÄ¶¨ÒåÒÔ¼°ÏµÍ³»Ø¹ö¶Î¡£
¡¡¡¡Ò»°ãÀ´Ëµ£¬Ó¦¸Ã¾¡Á¿±ÜÃâÔÚSYSTEM±íÖд洢·ÇSYSTEMÓû§µÄ¶ÔÏó¡£ÒòΪÕâÑù»á´øÀ´Êý¾Ý¿âά»¤ºÍ¹ÜÀíµÄºÜ¶àÎÊÌâ¡£Ò»µ©SYSTEM±íËð»µÁË£¬Ö»ÄÜÖØÐÂÉú³ÉÊý¾Ý¿â¡£ÎÒÃÇ¿ÉÒÔÓÃÏÂÃæµÄÓï¾äÀ´¼ì²éÔÚSYSTEM±íÄÚÓÐûÓÐÆäËûÓû§µÄË÷Òý´æÔÚ¡£
select count(*)
from dba_indexes
where tablespace_name = SYSTEM
and owner not in (SYS,SYSTEM)
/
¡¡¡¡¶þ. Ë÷ÒýµÄ´æ´¢Çé¿ö¼ì²é
¡¡¡¡OracleΪÊý¾Ý¿âÖеÄËùÓÐÊý¾Ý·ÖÅäÂß¼½á¹¹¿Õ¼ä¡£Êý¾Ý¿â¿Õ¼äµÄµ¥Î»ÊÇÊý¾Ý¿é£¨block£©¡¢·¶Î§£¨extent£©ºÍ¶Î£¨segment£©¡£
¡¡¡¡OracleÊý¾Ý¿é£¨block£©ÊÇOracleʹÓúͷÖÅäµÄ×îС´æ´¢µ¥Î»¡£ËüÊÇÓÉÊý¾Ý¿â½¨Á¢Ê±ÉèÖõÄDB_BLOCK_SIZE¾ö¶¨µÄ¡£Ò»µ©Êý¾Ý¿âÉú³ÉÁË£¬Êý¾Ý¿éµÄ´óС²»Äܸı䡣ҪÏë¸Ä±äÖ»ÄÜÖØн¨Á¢Êý¾Ý¿â¡££¨ÔÚOracle9iÖÐÓÐһЩ²»Í¬£¬²»¹ýÕâ²»ÔÚ±¾ÎÄÌÖÂ۵ķ¶Î§ÄÚ¡££©
¡¡¡¡ExtentÊÇÓÉÒ»×éÁ¬ÐøµÄblock×é³ÉµÄ¡£Ò»¸ö»ò¶à¸öextent×é³ÉÒ»¸ösegment¡£µ±Ò»¸ösegmentÖеÄËùÓпռ䱻ÓÃÍêʱ£¬OracleΪËü·ÖÅäÒ»¸öеÄextent¡£
¡¡
¡¡¡¡SegmentÊÇÓÉÒ»¸ö»ò¶à¸öextent×é³ÉµÄ¡£Ëü°üº¬Ä³±í¿Õ¼äÖÐÌض¨Âß¼´æ´¢½á¹¹µÄËùÓÐÊý¾Ý¡£Ò»¸ö¶ÎÖеÄextent¿ÉÒÔÊDz»Á¬ÐøµÄ£¬ÉõÖÁ¿ÉÒÔÔÚ²»Í¬µÄÊý¾ÝÎļþÖС£
¡¡¡¡Ò»¸öobjectÖ»ÄܶÔÓ¦ÓÚÒ»¸öÂß¼´æ´¢µÄsegment£¬ÎÒÃÇͨ¹ý²é¿´¸ÃsegmentÖеÄextent£¬¿ÉÒÔ¿´³öÏàÓ¦objectµÄ´æ´¢Çé¿ö¡£
¡¡¡¡£¨1£©²é¿´Ë÷Òý¶ÎÖÐextentµÄÊýÁ¿£º
select segment_name, count(*)
from dba_extents
where segment_type=INDEX
and owner=UPPER(&owner)
group by segment_name
/
¡¡¡¡£¨2£©²é¿´±í¿Õ¼äÄÚµÄË÷ÒýµÄÀ©Õ¹Çé¿ö£º
select
substr(segment_name,1,20) "SEGMENT NAME",
bytes,
count(bytes)
from dba_extents
where segment_name in
( select index_name
from dba_indexes
where tablespace_name=UPPER(&±í¿Õ¼ä))
group by segment_name,bytes
order by segment_name
/
¡¡¡¡Èý. Ë÷ÒýµÄÑ¡ÔñÐÔ
¡¡¡¡Ë÷ÒýµÄÑ¡ÔñÐÔÊÇÖ¸Ë÷ÒýÁÐÖв»Í¬ÖµµÄÊýÄ¿Óë±íÖмǼÊýµÄ±È¡£Èç¹ûÒ»¸ö±íÖÐ
Ïà¹ØÎĵµ£º
Ò»£ºsql loader µÄÌصã
oracle×Ô¼º´øÁ˺ܶàµÄ¹¤¾ß¿ÉÒÔÓÃÀ´½øÐÐÊý¾ÝµÄǨÒÆ¡¢±¸·ÝºÍ»Ö¸´µÈ¹¤×÷¡£µ«ÊÇÿ¸ö¹¤¾ß¶¼ÓÐ×Ô¼ºµÄÌص㡣
±ÈÈç˵expºÍimp¿ÉÒÔ¶ÔÊý¾Ý¿âÖеÄÊý¾Ý½øÐе¼³öºÍµ¼³öµÄ¹¤×÷£¬ÊÇÒ»ÖֺܺõÄÊý¾Ý¿â±¸·ÝºÍ»Ö¸´µÄ¹¤¾ß£¬Òò´ËÖ÷ÒªÓÃÔÚÊý¾Ý¿âµÄÈȱ¸·ÝºÍ»Ö¸´·½Ãæ¡£ÓÐ×ÅËٶȿ죬ʹÓüòµ¥£¬¿ì½ÝµÄÓŵ㣻ͬʱҲÓÐһР......
select trim(leading | trailing | both ' ' from ' abc d ') from dual;
È¥µô×Ö·û´® ' abc d ' µÄÇ°Ãæ/ºóÃæ/Ç°ºóµÄ¿Õ¸ñ
ÀàËƺ¯Êý£ºltrim, ......
1.µÃµ½Ò»¸ö±íµÄddlÓï¾ä£º
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999 ------ÏÔʾ²»ÍêÕû
SET PAGESIZE 1000 ----·ÖÒ³
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.S ......
DML Error Logging in Oracle 10g
Ö÷ÒªÔÚÓÚʹÓÃDBMS_ERRLOG.create_error_log Õâ¸ö°üÀ´¸ú×Ùdml´íÎóÐÅÏ¢
SQL> CREATE TABLE source (
2 id NUMBER(10) NOT NULL,
3 code VARCHAR2(10),
4 description VARCHAR2(50),
5 CONSTRAINT source_pk PRIMARY KEY (id)
6 );
±íÒÑ´´½¨¡£
SQL> DECLARE
2 TYPE t_tab IS ......
OracleÖ÷¼ü×Ô¶¯Ôö³¤
Õ⼸Ìì¸ãOracle£¬ÏëÈñíµÄÖ÷¼üʵÏÖ×Ô¶¯Ôö³¤£¬²éÍøÂçʵÏÖÈçÏ£º
create table simon_example
(
id number(4) not null primary key,
name varchar2(25)
)
-- ½¨Á¢ÐòÁУº
-- Create sequence
create sequence SIMON_SEQUENCE &nb ......