常用Sql

select * from (

(select * from im_idx)

except all

(select * from im_idx)

)

select a.* from im_idx a

full join im_idx_app b on b.app_id=a.app_id

select * from im_idx_app

select sum(idx_stat) from im_idx where idx_type='05' and idx_level='02'

select idx_type, idx_level, count(idx_stat) from im_idx group by idx_type, idx_level

having count(idx_stat)>100

order by idx_type ;

select count(*) from im_idx group by idx_type, idx_level

order by idx_type

select * from im_idx_app_info

select count(*) from dba_extents

select sum(bytes)/1024/1024||'m' from dba_extents where owner='OMCW_DEV'

select * from dba_extents

select sum(bytes)/1024/1024||'m',tablespace_name from dba_extents group by tablespace_name

select * from dba_catalog

select * from dual

select * from tab;

select * from obj;

select * from dba_users where username='OMCW_DEV'

select * from dba_roles

select * from dba_role_privs where grantee='OMCW_DEV'

select * from dba_tables where owner='OMCW_DEV'

select * from dba_lock

select * from dba_locks

select * from dba_lock_internal

select * from dba_log_groups

select * from dba_errors

select * from

desc im_idx

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual

select sysdate from dual

select * from user_tab_comments where table_name like 'IM_%'

select * from user_col_comments where table_name like 'IM_%'

select * from dba_extents

select * from tabs where table_name='IM_IDX'

select count(*) from im_idx

select count(distinct tablespace_name) from tabs;

select count(*) from dba_free_space where tablespace_name='SG_MDM'

select * from dba_data_files

select

b.file_id　　        文件ID,

b.tablespace_name　　表空间,

b.file_name　　　　　物理文件名,

b.bytes/1024/1024||'mb'　　　　　　　总字节数,

(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'mb'　　　已使用,

sum(nvl(a.bytes,0))/1024/1024||'mb'　　　　　　　　剩余,

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 --表空间剩余空间状况

--dba_data_files --数据文件空间占用情况

select file_name,autoextensible,increment_by from dba_data_files;

select tablespace_name,count(*),sum(blocks),sum(bytes)/1024/1024

from dba_data_files group by tablespace_name;

savepoint

select * from dba_rollback_segs

show user;

select count(*) from im_idx_send_extent

208

select count(log_id), max(idx_id) from im_idx_send_extent group by app_id having count(app_id)>1

select count(log_id), max(log_id) from im_idx_send_extent group by app_id having count(app_id)>1

select max(log_id) from im_idx_send_extent group by app_id having count(app_id)>1

select sum(i) from(

       select count(log_id) as i, max(log_id) from im_idx_send_extent group by app_id having count(app_id)>1

)

delete from im_idx_send_extent where log_id not in (

       select max(log_id) from im_idx_send_extent group by app_id having count(app_id)>1

)

select idx_code from (

       select t.*, dense_rank() over (order by idx_code desc) rank from im_idx t

)

where rank = '5'

select idx_code from im_idx order by idx_code desc

select * from im_idx where rownum<6;

select * from (

  select * from im_idx where rownum<10

  minus

  select * from im_idx where rownum<5

)

order by idx_code desc

select * from (

       select rownum r, idx_code, idx_name from im_idx where rownum <= 20 order by idx_code

) where r>10

select * from (

       select rownum r, idx_code from im_idx where rownum<=20 order by idx_code desc

)where r>10

select rownum, idx_code from im_idx where rownum!=7

select min(distinct idx_code) from im_idx

select userenv('isdba'), userenv('sessionid'), userenv('entryid'), userenv('instance'),

userenv('language'), userenv('lang'), userenv('terminal') from dual

select vsize(user),user from dual;

select vsize(idx_name), idx_name from im_idx

select to_multi_byte('高') from dual;

select t.rowid, rowidtochar(t.rowid), t.* from im_idx t

select convert('strutz','we8hp','f7dec') "conversion" from dual;

select

to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,

to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm

from dual;

select * from v$version

select * from v$session

select * from tabs;

select username,sid,serial#,machine,program,state from v$session where username='OMCW_DEV' and program is null

order by machine

select state,count(*) from v$session group by state

select count(*) from user_extents

Oracle 10g及以上版本，Oracle9i无SYS.V_$SYSMETRIC_SUMMARY表

select

       CASE METRIC_NAME

         WHEN 'SQL Service Response Time' then

          ROUND((AVERAGE * 10), 2)

         WHEN 'Response Time Per Txn' then

          ROUND((AVERAGE * 10), 2)

         ELSE

          AVERAGE

       END AVERAGE

  from SYS.V_$SYSMETRIC_SUMMARY

 where METRIC_NAME = 'Response Time Per Txn'

--数据库平均响应时长

select table_name,num_rows from user_tables order by table_name;

select count(*) from T_CUSTOMER_H where cleardate = to_date('2014/12/8','YYYY/MM/DD');

select count(*) from M_CUSTOMERINFO_AUDIT

select * from M_CUSTOMERINFO_AUDIT

select table_name,num_rows from user_tables order by num_rows desc;

select * from user_views order by text_length desc;

select object_type from user_objects group by object_type

select * from user_objects where object_type='FUNCTION';

select * from T_FIRMHOLDSUM;

select * from T_HOLDPOSITION;

SELECT  SUM(population),

CASE country

WHEN '中国'     THEN '亚洲'

WHEN '印度'     THEN '亚洲'

WHEN '日本'     THEN '亚洲'

WHEN '美国'     THEN '北美洲'

WHEN '加拿大'  THEN '北美洲'

WHEN '墨西哥'  THEN '北美洲'

ELSE '其他' END

FROM    Table_A

GROUP BY CASE country

WHEN '中国'     THEN '亚洲'

WHEN '印度'     THEN '亚洲'

WHEN '日本'     THEN '亚洲'

WHEN '美国'     THEN '北美洲'

WHEN '加拿大'  THEN '北美洲'

WHEN '墨西哥'  THEN '北美洲'

ELSE '其他' END;

SELECT

CASE WHEN salary <= 500 THEN '1'

WHEN salary > 500 AND salary <= 600  THEN '2'

WHEN salary > 600 AND salary <= 800  THEN '3'

WHEN salary > 800 AND salary <= 1000 THEN '4'

ELSE NULL END salary_class,

COUNT(*)

FROM    Table_A

GROUP BY

CASE WHEN salary <= 500 THEN '1'

WHEN salary > 500 AND salary <= 600  THEN '2'

WHEN salary > 600 AND salary <= 800  THEN '3'

WHEN salary > 800 AND salary <= 1000 THEN '4'

ELSE NULL END;