ORACLE SQLPLUS³£ÓÃÃüÁî¼°²éѯ
1.Æô¶¯Í£Ö¹TNS¼àÌý
lsnrctl start
lsnrctl stop
2.Æô¶¯Oracle Services
net start OracleServiceOrcl
net stop OracleServiceOrcl
SQL> show all --²é¿´ËùÓÐ68¸öϵͳ±äÁ¿Öµ
SQL> show user --ÏÔʾµ±Ç°Á¬½ÓÓû§
SQL> show error¡¡¡¡ --ÏÔʾ´íÎó
SQL> set heading off --½ûÖ¹Êä³öÁбêÌ⣬ĬÈÏֵΪON
SQL> set feedback off --½ûÖ¹ÏÔʾ×îºóÒ»ÐеļÆÊý·´À¡ÐÅÏ¢£¬Ä¬ÈÏֵΪ"¶Ô6¸ö»ò¸ü¶àµÄ¼Ç¼£¬»ØËÍON"
SQL> set timing on --ĬÈÏΪOFF£¬ÉèÖòéѯºÄʱ£¬¿ÉÓÃÀ´¹À¼ÆSQLÓï¾äµÄÖ´ÐÐʱ¼ä£¬²âÊÔÐÔÄÜ
SQL> set sqlprompt "SQL> " --ÉèÖÃĬÈÏÌáʾ·û£¬Ä¬ÈÏÖµ¾ÍÊÇ"SQL> "
SQL> set linesize 1000 --ÉèÖÃÆÁÄ»ÏÔʾÐÐ¿í£¬Ä¬ÈÏ100
SQL> set autocommit ON --ÉèÖÃÊÇ·ñ×Ô¶¯Ìá½»£¬Ä¬ÈÏΪOFF
SQL> set pause on --ĬÈÏΪOFF£¬ÉèÖÃÔÝÍ££¬»áʹÆÁÄ»ÏÔʾֹͣ£¬µÈ´ý°´ÏÂENTER¼ü£¬ÔÙÏÔʾÏÂÒ»Ò³
SQL> set arraysize 1 --ĬÈÏΪ15
SQL> set long 1000 --ĬÈÏΪ80
Áгöµ±Ç°Óû§ÏÂËùÓÐͬÒå´ÊµÄ¶¨Ò壬¿ÉÓÃÀ´²âÊÔͬÒå´ÊµÄÕæʵ´æÔÚÐÔ
select 'desc '||tname from tab where tabtype='SYNONYM';
²éѯµ±Ç°Óû§ÏÂËùÓбíµÄ¼Ç¼Êý
select 'select '''||tname||''',count(*) from '||tname||';' from tab where tabtype='TABLE';
°ÑËùÓзûºÏÌõ¼þµÄ±íµÄselectȨÏÞÊÚÓèΪpublic
select 'grant select on '||table_name||' to public;' from user_tables where ¡¶Ìõ¼þ¡·;
ɾ³ýÓû§Ï¸÷ÖÖ¶ÔÏó
select 'drop '||tabtype||' '||tname from tab;
ɾ³ý·ûºÏÌõ¼þÓû§
select 'drop user '||username||' cascade;' from all_users where user_id>25;
ÏÂÃæ°´Àà±ðÁгöһЩORACLEÓû§³£ÓÃÊý¾Ý×ÖµäµÄ²éѯʹÓ÷½·¨¡£
1¡¢Óû§
²é¿´µ±Ç°Óû§µÄȱʡ±í¿Õ¼ä
SQL>select username,default_tablespace from user_users;
²é¿´µ±Ç°Óû§µÄ½ÇÉ«
SQL>select * from user_role_privs;
²é¿´µ±Ç°Óû§µÄϵͳȨÏÞºÍ±í¼¶È¨ÏÞ
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
2¡¢±í
²é¿´Óû§ÏÂËùÓеıí
SQL>select * from user_tables;
²é¿´Ãû³Æ°üº¬log×Ö·ûµÄ±í
SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;
²é¿´Ä³±íµÄ´´½¨Ê±¼ä
SQL>select object_name,created from user_objects where object_name=upper('&tabl
Ïà¹ØÎĵµ£º
update customers a
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
where exists (select 1
from ......
´ÓÉÏÖÜ¿ªÊ¼£¬ÎÒÏÂÔØÁËС²¼ÀÏʦµÄ½²¿ÎÊÓƵ£¬¿ªÊ¼ÁËѧϰORACLEµÄ¼Æ»®¡£
ѧϰORACLEµÄÄ¿µÄ£¬Ò»·½ÃæÊÇÌá¸ß×Ô¼ºµÄ¼¼ÊõÄÜÁ¦£¬¹¤×÷ÉϾ³£¿ÉÒÔÓõõ½£¨ËäÈ»²»ÊÇרÃÅDBAµÄ¹¤×÷£¬µ«ÊǾ³£ÐèҪʹÓÃÊý¾Ý¿â×÷Ϊ±¨±íͳ¼Æ·ÖÎöµÄ¹¤¾ß£©£»ÁíÒ»·½Ã棬ҲÊÇΪÌø²Û×öºÃ¼¼ÊõÄÜÁ¦µÄ´¢±¸£¬ÏÖÔڵŤ×÷ÒѾ¿ìÈ ......
Basic Steps for Manual Online Reorganization Commands and procedures used:
1.DBMS_REDEFINITION.CAN_REDEF_TABLE
2.CREATE TABLE …
3.DBMS_REDEFINITION.START_REDEF_TABLE
4.DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS and DBMS_REDEFINITION.CONS_ORIG_PAGRAMS
SELECT object_name,base_table_name, ......
µ¥Öµº¯ÊýÔÚ²éѯÖзµ»Øµ¥¸öÖµ£¬¿É±»Ó¦Óõ½select£¬where×Ӿ䣬start withÒÔ¼°connect by ×Ó¾äºÍhaving×Ӿ䡣
(Ò»).ÊýÖµÐͺ¯Êý(Number Functions)
ÊýÖµÐͺ¯ÊýÊäÈëÊý×ÖÐͲÎÊý²¢·µ»ØÊýÖµÐ͵ÄÖµ¡£¶àÊý¸ÃÀຯÊýµÄ·µ»ØÖµÖ§³Ö38λСÊýµã£¬ÖîÈ磺COS, COSH, EXP, LN, LOG,
SIN, SINH, SQRT, TAN, and TANH Ö ......
Oracle ÖеÄÊ÷²éѯºÍ connect by
ʹÓà connect by ºÍ start with À´½¨Á¢ÀàËÆÓÚÊ÷µÄ±¨±í²¢²»ÄÑ£¬Ö»Òª×ñÑÒÔÏ»ù±¾ÔÔò¼´¿É£º
ʹÓà connect by ʱ¸÷×Ó¾äµÄ˳ÐòӦΪ£º
select
from
where
start with
connect by
order by
prior ʹ±¨±íµÄ˳ÐòΪ´Ó¸ùµ½Ò¶£¨Èç¹û prior ÁÐÊǸ¸±²£©»ò´ÓÒ¶µ½¸ù£¨Èç¹û prior ÁÐÊǺó´ú£©¡£
......