²éѯÊý¾Ý¿âÖи÷ÖÖ´íÎó´úÂëµÄ×ܺͣº

select error_code,count(*) from sm_histable0513 group by error_code order by error_code;

²éѯ±¨±íÊý¾Ý¿âÖл°µ¥Í³¼ÆÖÖÀà²éѯ¡£

select sum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111 select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype

oracle³£ÓÃSQLÓï¾ä

1¡¢Á¬½ÓSQL*Plus system/manager 2¡¢ÏÔʾµ±Ç°Á¬½ÓÓû§SQL> show user

3¡¢²é¿´ÏµÍ³ÓµÓÐÄÄЩÓû§SQL> select * from all_users;

4¡¢Ð½¨Óû§²¢ÊÚȨSQL> create user a identified by a;£¨Ä¬ÈϽ¨ÔÚSYSTEM±í¿Õ¼äÏ£©SQL> grant connect,resource to a; 5¡¢Á¬½Óµ½ÐÂÓû§SQL> conn a/a

6¡¢²éѯµ±Ç°Óû§ÏÂËùÓжÔÏóSQL> select * from tab; 7¡¢½¨Á¢µÚÒ»¸ö±íSQL> create table a(a number); 8¡¢²éѯ±í½á¹¹SQL> desc a

9¡¢²åÈëмǼSQL> insert into a values(1); 10¡¢²éѯ¼Ç¼SQL> select * from a; 11¡¢¸ü¸Ä¼Ç¼SQL> update a set a=2; 12¡¢É¾³ý¼Ç¼SQL> delete from a; 13¡¢»Ø¹öSQL> roll;SQL> rollback; 14¡¢Ìá½»SQL> commit;

¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª ¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª-

Óû§ÊÚȨ:GRANT ALTER ANY INDEX TO ¨Duser_id ¨DGRANT ¨Ddba ¡¬ TO ¨Duser_id ¨D;ALTER USER ¨Duser_id ¡¬ DEFAULT ROLE ALL´´½¨Óû§:CREATE USER ¨Duser_id ¡¬ PROFILE ¨DDEFAULT ¡¬ IDENTIFIED BY ¡¬ DEFAULT TABLESPACE ¨DUSERS ¡¬ TEMPORARY TABLESPACE ¨DTEMP ¡¬ ACCOUNT UNLOCK;GRANT ¨DCONNECT ¡¬ TO ¨Duser_id ¨D;Óû§ÃÜÂëÉ趨:ALTER USER ¨DCMSDB ¡¬ IDENTIFIED BY ¨Dpass_word ¨D±í¿Õ¼ä´´½¨:CREATE TABLESPACE ¨Dtable_space ¡¬ LOGGING DATAFILE ?C:/ORACLE/ORADATA/dbs/table_space.ora? SIZE 5M ¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª 1¡¢²é¿´µ±Ç°ËùÓжÔÏó SQL > select * from tab;

2¡¢½¨Ò»¸öºÍa±í½á¹¹Ò»ÑùµÄ¿Õ±í

SQL > create table b as select * from a where 1=2;

SQL > create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2; 3¡¢²ì¿´Êý¾Ý¿âµÄ´óС£¬ºÍ¿Õ¼äʹÓÃÇé¿ö

SQL > col tablespace format a20SQL > select b.file_id ÎļþID, b.tablespace_name ±í¿Õ¼ä, b.file_name ÎïÀíÎļþÃû,

b.bytes ×Ü×Ö½ÚÊý, (b.bytes-sum(nvl(a.bytes,0))) ÒÑʹÓÃ,

sum(nvl(a.bytes,0)) Ê£Óà, sum(nvl(a.bytes,0))/(b.bytes)*100 Ê£Óà°Ù·Ö±È from dba_free_space a,dba_data_files b where

a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name / dba_free_space ¨C±í¿Õ¼äÊ£Óà¿Õ¼ä×´¿ö dba_data_files ¨CÊý¾ÝÎļþ¿Õ¼äÕ¼ÓÃÇé¿ö 4¡¢²é¿´ÏÖÓлعö¶Î¼°Æä״̬ SQL > col segment format a30SQL > SELECT

SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS; 5¡¢²é¿´Êý¾ÝÎļþ·ÅÖõÄ·¾¶

SQL > col file_name format a50SQL > select

tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

6¡¢ÏÔʾµ±Ç°Á¬½ÓÓû§ SQL > show user

7¡¢°ÑSQL*Plusµ±¼ÆËãÆ÷

SQL > select 100*20 from dual; 8¡¢Á¬½Ó×Ö·û´®

SQL > select ÁÐ1 | |ÁÐ2 from ±í1;SQL > select concat(ÁÐ1,ÁÐ2) from ±í1; 9¡¢²éѯµ±Ç°ÈÕÆÚ

SQL > select to_char(sysdate,?yyyy-mm-dd,hh24:mi:ss?) from dual; 10¡¢Óû§¼ä¸´ÖÆÊý¾Ý

SQL > copy from user1 to user2 create table2 using select * from table1; 11¡¢ÊÓͼÖв»ÄÜʹÓÃorder by£¬µ«¿ÉÓÃgroup by´úÌæÀ´´ïµ½ÅÅÐòÄ¿µÄ SQL > create view a as select b1,b2 from b group by b1,b2; 12¡¢Í¨¹ýÊÚȨµÄ·½Ê½À´´´½¨Óû§

SQL > grant connect,resource to test identified by test; SQL > conn test/test

13¡¢²é³öµ±Ç°Óû§ËùÓбíÃû¡£ select unique tname from col;

¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¨C

/* ÏòÒ»¸ö±í¸ñÌí¼Ó×ֶΠ*/alter table alist_table add address varchar2(100); /* ÐÞ¸Ä×Ö¶ÎÊôÐÔ×Ö¶ÎΪ¿Õ */alter table alist_table modify address varchar2(80); /* ÐÞ¸Ä×Ö¶ÎÃû×Ö */create table alist_table_copy as select

ID,NAME,PHONE,EMAIL,QQ as QQ2, /*qq ¸ÄΪqq2*/ADDRESS from alist_table; drop table alist_table;rename alist_table_copy to alist_table/* Ð޸ıíÃû */ ¿ÕÖµ´¦ÀíÓÐʱҪÇóÁÐÖµ²»ÄÜΪ¿Õcreate table dept (deptno number(2) not null, dname char(14), loc char(13));

ÔÚ»ù±íÖÐÔö¼ÓÒ»ÁÐalter table deptadd (headcnt number(3));

ÐÞ¸ÄÒÑÓÐÁÐÊôÐÔalter table deptmodify dname char(20);×¢£ºÖ»Óе±Ä³ÁÐËùÓÐÖµ¶¼Îª¿Õʱ£¬²ÅÄܼõСÆäÁÐÖµ¿í¶È¡£Ö»Óе±Ä³ÁÐËùÓÐÖµ¶¼Îª¿Õʱ£¬²ÅÄܸıäÆäÁÐÖµÀàÐÍ¡£Ö»Óе±Ä³ÁÐËùÓÐÖµ¶¼Îª²»¿Õʱ£¬²ÅÄܶ¨Òå¸ÃÁÐΪnot null¡£Àý£ºalter table dept modify (loc char(12));alter table dept modify loc char(12);alter table dept modify (dname char(13),loc char(12));

²éÕÒδ¶ÏÁ¬½Óselect process,osuser,username,machine,logon_time ,sql_textfrom

v$session a,v$sqltext b where a.sql_address=b.address;

¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¨C1.ÒÔUSER_¿ªÊ¼µÄÊý¾Ý×ÖµäÊÓͼ°üº¬µ±Ç°Óû§ËùÓµÓеÄÐÅÏ¢, ²éѯµ±Ç°Óû§ËùÓµÓеıíÐÅÏ¢:select * from

user_tables;2.ÒÔALL_¿ªÊ¼µÄÊý¾Ý×ÖµäÊÓͼ°üº¬ORACLEÓû§ËùÓµÓеÄÐÅÏ¢,²éѯÓû§ÓµÓлòÓÐȨ·ÃÎʵÄËùÓбíÐÅÏ¢:select * from all_tables;

3.ÒÔDBA_¿ªÊ¼µÄÊÓͼһ°ãÖ»ÓÐORACLEÊý¾Ý¿â¹ÜÀíÔ±¿ÉÒÔ·ÃÎÊ:select * from dba_tables;

4.²éѯORACLEÓû§£ºconn sys/change_on_installselect * from dba_users;conn system/manager;select * from all_users;

5.´´½¨Êý¾Ý¿âÓû§£ºCREATE USER user_name IDENTIFIED BY

password;GRANT CONNECT TO user_name;GRANT RESOURCE TO user_name;ÊÚȨµÄ¸ñʽ: grant (ȨÏÞ) on tablename to username;ɾ³ýÓû§(»ò±í):drop user(table) username(tablename) (cascade);6.Ïò½¨ºÃµÄÓû§µ¼ÈëÊý¾Ý±íIMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:/EXPDAT.DMP COMMIT = Y7.Ë÷Òýcreate index [index_name] on [table_name]( ¨Dcolumn_name ¨D)intersectÔËËã ·µ»Ø²éѯ½á¹ûÖÐÏàͬµÄ²¿·Ö

exp:¸÷¸ö²¿ÃÅÖÐÓÐÄÄЩÏàͬµÄ¹¤ÖÖ selectjob fromaccount intersect selectjob fromresearch intersect selectjob fromsales;

minusÔËËã

·µ»ØÔÚµÚÒ»¸ö²éѯ½á¹ûÖÐÓëµÚ¶þ¸ö²éѯ½á¹û²»ÏàͬµÄÄDz¿·ÖÐмǼ¡£ ÓÐÄÄЩ¹¤ÖÖÔڲƻᲿÖÐÓУ¬¶øÔÚÏúÊÛ²¿ÖÐûÓУ¿ exp:selectjobfromaccount minus

selectjobfromsales;

1. oracle°²×°Íê³ÉºóµÄ³õʼ¿ÚÁî? internal/oracle

sys/change_on_install system/manager scott/tiger

sysman/oem_temp

2. oracle9ias web cacheµÄ³õʼĬÈÏÓû§ºÍÃÜÂ룿 administrator/administrator

3. oracle 8.0.5Ôõô´´½¨Êý¾Ý¿â?

ÓÃorainst¡£¼ÙÈçÓÐmotif½çÃ棬¿ÉÒÔÓÃorainst /m

4. oracle 8.1.7Ôõô´´½¨Êý¾Ý¿â? dbassist

5. oracle 9i Ôõô´´½¨Êý¾Ý¿â? dbca

6. oracleÖеÄÂãÉ豸ָµÄÊÇʲô?

ÂãÉ豸¾ÍÊÇÈƹýÎļþϵͳֱ½Ó·ÃÎʵĴ¢´æ¿Õ¼ä

7. oracleÈçºÎÇø·Ö 64-bit/32bit °æ±¾£¿£¿£¿ $ sqlplus ?/ as sysdba?

sql*plus: release 9.0.1.0.0 ¨C production on mon jul 14 17:01:09 2003 (c) copyright 2001 oracle corporation. all rights reserved. connected to:

oracle9i enterprise edition release 9.0.1.0.0 ¨C production with the partitioning option

jserver release 9.0.1.0.0 ¨C production sql> select * from v$version; banner

¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª-

oracle9i enterprise edition release 9.0.1.0.0 ¨C production pl/sql release 9.0.1.0.0 ¨C production core 9.0.1.0.0 production

tns for solaris: version 9.0.1.0.0 ¨C production nlsrtl version 9.0.1.0.0 ¨C production sql>

8. svrmgrʲôÒâ˼£¿

svrmgrl£¬server manager.

9iÏÂûÓУ¬ÒѾ­¸ÄΪÓÃsqlplusÁË sqlplus /nolog ±äΪ¹éµµÈÕÖ¾Ð͵Ä

9. ÇëÎÊÈçºÎ·Ö±æij¸öÓû§ÊÇ´ÓÄĄ̈»úÆ÷µÇ½oracleµÄ? select machine , terminal from v$session;

10. ÓÃʲôÓï¾ä²éѯ×Ö¶ÎÄØ£¿

desc table_name ¿ÉÒÔ²éѯ±íµÄ½á¹¹

select field_name,¡­ from ¡­ ¿ÉÒÔ²éѯ×ֶεÄÖµ select * from all_tables where table_name like ?%? select * from all_tab_columns where table_name=???? 11. ÔõÑùµÃµ½´¥·¢Æ÷¡¢¹ý³Ì¡¢º¯ÊýµÄ´´½¨½Å±¾£¿ desc user_source user_triggers

12. ÔõÑù¼ÆËãÒ»¸ö±íÕ¼ÓõĿռäµÄ´óС£¿ select owner,table_name, num_rows,

blocks*aaa/1024/1024 ¨Dsize m¡¬, empty_blocks, last_analyzed from dba_tables

where table_name=?xxx?;

here: aaa is the value of db_block_size ; xxx is the table name you want to check

13. ÈçºÎ²é¿´×î´ó»á»°Êý£¿

select * from v$parameter where name like ?proc%?; sql>

sql> show parameter processes name type value

¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª ¡ª¡ª- ¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª aq_tm_processes integer 1 db_writer_processes integer 1 job_queue_processes integer 4

log_archive_max_processes integer 1 processes integer 200 ÕâÀïΪ200¸öÓû§¡£ select * from v$license;

ÆäÖÐsessions_highwater¼Í¼Ôø¾­µ½´ïµÄ×î´ó»á»°Êý 14. ÈçºÎ²é¿´ÏµÍ³±»ËøµÄÊÂÎñʱ¼ä£¿ select * from v$locked_object ;

15. ÈçºÎÒÔarchivelogµÄ·½Ê½ÔËÐÐoracle¡£ init.ora

log_archive_start = true restart database

16. Ôõô»ñÈ¡ÓÐÄÄЩÓû§ÔÚʹÓÃÊý¾Ý¿â select username from v$session; 17. Êý¾Ý±íÖеÄ×Ö¶Î×î´óÊýÊǶàÉÙ? ±í»òÊÓͼÖеÄ×î´óÁÐÊýΪ 1000 18. ÔõÑù²éµÃÊý¾Ý¿âµÄsid ? select name from v$database; Ò²¿ÉÒÔÖ±½Ó²é¿´ init.oraÎļþ