Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ORACLEÎﻯÊÓͼ Query RewriteµÄÒ»°ãÀí½âÖ®¶þ


   ÔÚOracleµÄQuery RewriteÖÐÖ÷ÒªÓÐÈýµã, µÚÒ»ÊÇҪʹÓÃCBO; µÚ¶þÊÇÒªÉèÖÃquery rewrite enabled²ÎÊýΪTRUE; µÚÈýÊÇÒªÏÈÔñÉèÖÃquery rewrite integrity²ÎÊýµÄÖµ(stale_tolerated, trusted, enforced). ¶ÔÓÚµÚÒ»µã, ÎÒÃÇ×îºÃanalyzeÏà¹ØµÄ±í¼°Ë÷Òý¼°MV; ¶ÔÓÚµÚ¶þµã,Õâ¸ö²ÎÊýÖ»ÓÐÁ½¸öÖµ(true, false), ºÜ¼òµ¥; ¶ÔÓÚµÚÈýµã, ÎÒÃÇÏÈÀ´¿´OracleµÄ¹Ù·½¶ÔÓÚÕâ¸ö²ÎÊýµÄ½âÊÍ:
ENFORCED
   Oracle enforces and guarantees consistency and integrity
TRUSTED
   Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.
STALE_TOLERATED
   Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.
   Õâ¸ö²ÎÊýÓеãÄÑÓÚÀí½âһЩ, µ«Ö÷ÒªºÍÊý¾ÝµÄÒ»ÖÂÐÔÓйØ, ÔÚOracleµÄQuery RewriteÖÐ, Ò»Ð©Ô¼ÊøµÄÉùÃ÷»ò״̬ºÍOracle¾öÓÚ¿É·ñQuery RewriteÓкܴóµÄ¹ØÏµ. ENFORCED±íʾOracleÖ»ÏàÐÅEnabledºÍValidatedµÄÔ¼Êø, ¶øTrustedÔòÏàÐÅRELYµÄÔ¼Êø, ¾ÍËãÕâ¸öÔ¼ÊøÃ»ÓÐEnabledºÍValidated, ÕâÁ½ÖÖ¶¼ÒªÇóMVIEWÖеÄÊý¾ÝÊǼ°Ê±Ë¢ÐµÄ,¶øSTALE_TOLERATEDÔò¿ÉÒÔÈÝÈÌÒ»ÇÐ, ¾ÍËãÖмä±íµÄÊý¾ÝÊǾɵÄ, Ö¸»ù±íÓÐÐÂÊý¾ÝÐ޸ĶøMVIEW»¹Ã»ÓÐˢеÄÇé¿öÏÂ, OracleÒ²»áÑ¡ÔñʹÓÃQuery RewriteÀ´×÷²éѯ, ÔÚÕâÖÖÇé¿öÏÂ, ²é³öÀ´µÄÊý¾Ý¿ÉÄÜÊDz»×¼µÄ. ÏÂÃæÎÒÃÇÀ´×÷Ò»¸öÀý×ÓÀ´ÏÔʾenforcedÓëtrustedµÄ²»Í¬:  
½Ó×ÅÇ°ÃæµÄÀý×Ó,ÎÒÃÇ´´½¨ÕâÑùÒ»¸öʵÌ廯ÊÓͼ:
CREATE MATERIALIZED VIEW MV_TABLE
ENABLE QUERY REWRITE
AS
SELECT U.USER#,COUNT(*) OBJCNT from USR_TABLE U,OBJ_TABLE O
WHERE U.USER#=O.USER#
group by u.user#
½ÓÏÂÀ´ÎÒÃÇ´´½¨ÕâÑùµÄÁ½¸öÔ¼Êø:
ALTER TABLE USR_TABLE ADD PRIMARY KEY (USER#) RELY DISABLE;
ALTER TABLE OBJ_TABLE ADD FOREIGN KEY (USER#) 
    REFERENCES USR_TABLE(USER#) RELY DISABLE;
ϽÓÀ´´´½¨Ò»¸öUSR_LEVELµÄ±í, ÈçÏÂËùʾ:
CREATE TABLE USR_LEVLEL AS SELECT USER#, TRUNC(USER#/10) ULEVEL from USR_TABLE;
  ÊµÑéËùÐèÒªµÄ±í¶¼½¨ÆðÀ´ÁË, ¶ÔÈý¸ö±íºÍÒ»¸öMVIEW½øÐзÖÎöºó, ÏÂÃæÀ´×ö²âÊÔ:
SQL> SHOW PARAMETE


Ïà¹ØÎĵµ£º

Oracle ÊÕ¹ºSunÖ®ºóµÄÑÝÒ壺IBM ºÍSAPºÎÈ¥ºÎ´Ó

SunÖÕÓÚµ¹ÏÂÁË£¬Ò»¸ö²»µ«´´Ôì¼¼ÊõÉñ»°£¬¸øÊÀ½çIT¼¼Êõ´øÀ´¸ïÃüÐÔÓ°ÏìµÄ¹«Ë¾µ¹ÏÂÁË£¬ÎÒ»³ÄîËü¡£
ÎÒ¸öÈËÈÏΪORACLEºÍSUNµÄºÏ²¢£¬ÊÇÁ½¼Ò
¹«Ë¾µÄÐÒÊ£¬ORACLE½øÒ»²½ÍêÉÆÁ˲úÆ·Ïߣ¬²¢ÇÒ·ûºÏÁËĿǰSaaSºÍÔÆ¼ÆËãµÄÇ÷ÊÆ£¬³ÉΪһ¸öÔÚ²úÆ·ÏߺͷþÎñÉÏ¿ÉÒÔºÍIBM½øÐоºÕùµÄ¹«Ë¾£¬¶øSUNµÄ
¼¼Êõ¿ÉÄܻᱻORACLE¹«Ë¾µÄÓªÏúÄÜÁ¦·½·¨£¬Ê¹SUNµ ......

oracle±Ê¼Ç2


µÚÒ»¿Î£º¿Í»§¶Ë
1. Sql Plus(¿Í»§¶Ë£©£¬ÃüÁîÐÐÖ±½ÓÊäÈ룺sqlplus£¬È»ºó°´ÌáʾÊäÈëÓû§Ãû£¬ÃÜÂë¡£
2. ´Ó¿ªÊ¼³ÌÐòÔËÐÐ:sqlplus£¬ÊÇͼÐΰæµÄsqlplus.
3. http://localhost:5560/isqlplus
Toad£º¹ÜÀí£¬ PlSql Developer:
µÚ¶þ¿Î£º¸ü¸ÄÓû§
1. sqlplus sys/bjsxt as sysdba
2. alter user scott account unlock;(½âËø) ......

ϸ̸OracleµÄȨÏÞ

ÎÒÃǾ­³£Ì¸¼°OracleµÄȨÏÞ£¬±ÈÈçConnect£¬DBA£¬resource£¬ sysdba, select any
table,
select_catelog_roleÕâÑùһЩȨÏÞ£¬ÈÃÎÒÃÇÑÛ»¨çÔÂҵģ¬ÄÇôÈçºÎÀ´Çø·ÖÕâЩÖÖÀà·±¶àµÄprivilegeÖв»ÖÁÓÚÑÛÃÔÀëÄØ£¬Õâ¸öÎÄÕ½«
»á¶ÔÄãÔÚÕâ·½ÃæµÄÁ˽âÆðµ½°ïÖú¡£
    
 ÒÔÉÏÎÒÃÇ¿´µ½µÄÕâЩȨÏÞ£¬¶¼ÊÇ¿ÉÒÔͨ¹ýg ......

ORACLE ORA 00132 ORA 00214

oracle¶ÏµçºóÖØÆô³öÏÖµÄÎÊÌâÒѾ­½â¾ö·½·¨
Ò»¡¢ORA-00132 
ÎÊÌâÃèÊö £ºsyntax error or unresolved network name ''
µÚ1²½£º¸´ÖÆÒ»·Ýpfile²ÎÊýÎļþ£¨×¢Ò⣺oracleÖеÄpfileÖ¸µÄ¾ÍÊÇinit<sid>.oraÎļþ£©
$sqlplus '/as sysdba';
SQL> create pfile from spfile='/u01/oracle/product/10.2.0/db_1/dbs/spf ......

Oracle Decodeº¯Êý

DECODEº¯ÊýÏ൱ÓÚÒ»Ìõ¼þÓï¾ä(IF).Ëü½«ÊäÈëÊýÖµÓ뺯ÊýÖеIJÎÊýÁбíÏà±È½Ï£¬¸ù¾ÝÊäÈëÖµ·µ»ØÒ»¸ö¶ÔÓ¦Öµ¡£º¯ÊýµÄ²ÎÊýÁбíÊÇÓÉÈô¸ÉÊýÖµ¼°Æä¶ÔÓ¦½á¹ûÖµ×é³ÉµÄÈô¸ÉÐòżÐÎʽ¡£µ±È»£¬Èç¹ûδÄÜÓëÈκÎÒ»¸öʵ²ÎÐòżƥÅä³É¹¦£¬Ôòº¯ÊýÒ²ÓÐĬÈϵķµ»ØÖµ¡£Çø±ðÓÚSQLµÄÆäËüº¯Êý£¬DECODEº¯Êý»¹ÄÜʶ±ðºÍ²Ù×÷¿ÕÖµ.
Æä¾ßÌåµÄÓï·¨¸ñʽÈçÏ£º
DE ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ