<!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->常用SQL查询：<br /><br />1、查看表空间的名称及大小<br /><br />select t.tablespace_name, round&#40;sum&#40;bytes/&#40;1024*1024&#41;&#41;,0&#41; ts_size<br />from dba_tablespaces t, dba_data_files d<br />where t.tablespace_name = d.tablespace_name<br />group by t.tablespace_name;<br /><br />2、查看表空间物理文件的名称及大小<br /><br />select tablespace_name, file_id, file_name,<br />round&#40;bytes/&#40;1024*1024&#41;,0&#41; total_space<br />from dba_data_files<br />order by tablespace_name;<br /><br />3、查看回滚段名称及大小<br /><br />select segment_name, tablespace_name, r.status, <br />&#40;initial_extent/1024&#41; InitialExtent,&#40;next_extent/1024&#41; NextExtent, <br />max_extents, v.curext CurExtent<br />From dba_rollback_segs r, v$rollstat v<br />Where r.segment_id = v.usn&#40;+&#41;<br />order by segment_name;<br /><br />4、查看控制文件<br /><br />select name from v$controlfile;<br /><br />5、查看日志文件<br /><br />select member from v$logfile;<br /><br />6、查看表空间的使用情况<br /><br />select sum&#40;bytes&#41;/&#40;1024*1024&#41; as free_space,tablespace_name <br />from dba_free_space<br />group by tablespace_name;<br /><br />SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,<br />&#40;B.BYTES*100&#41;/A.BYTES &#34;% USED&#34;,&#40;C.BYTES*100&#41;/A.BYTES &#34;% FREE&#34;<br />FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C<br />WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; <br /><br />7、查看数据库库对象<br /><br />select owner, object_type, status, count&#40;*&#41; count# from all_objects group by owner, object_type, status;<br /><br />8、查看数据库的版本　<br /><br />Select version FROM Product_component_version <br />Where SUBSTR&#40;PRODUCT,1,6&#41;=&#39;Oracle&#39;;<br /><br />9、查看数据库的创建日期和归档方式<br /><br />Select Created, Log_Mode, Log_Mode From V$Database; <br /><br />10、捕捉运行很久的SQL<br /><br />column username format a12 <br />column opname format a16 <br />column progress format a8 <br /><br />select username,sid,opname, <br /> &nbsp; &nbsp; &nbsp; round&#40;sofar*100 / totalwork,0&#41; || &#39;%&#39; as progress, <br /> &nbsp; &nbsp; &nbsp; time_remaining,sql_text <br />from v$session_longops , v$sql <br />where time_remaining &#60;&#62; 0 <br />and sql_address = address <br />and sql_hash_value = hash_value <br />/<br />11。查看数据表的参数信息<br />SELECT &nbsp; partition_name, high_value, high_value_length, tablespace_name,<br /> &nbsp; &nbsp; &nbsp; &nbsp; pct_free, pct_used, ini_trans, max_trans, initial_extent,<br /> &nbsp; &nbsp; &nbsp; &nbsp; next_extent, min_extent, max_extent, pct_increase, FREELISTS,<br /> &nbsp; &nbsp; &nbsp; &nbsp; freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,<br /> &nbsp; &nbsp; &nbsp; &nbsp; empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,<br /> &nbsp; &nbsp; &nbsp; &nbsp; last_analyzed<br /> &nbsp; &nbsp;FROM dba_tab_partitions<br /> &nbsp; --WHERE table_name = &#58;tname AND table_owner = &#58;towner<br />ORDER BY partition_position<br /><br />12.查看还没提交的事务<br />select * from v$locked_object;<br />select * from v$transaction;<br /><br />13。查找object为哪些进程所用<br />select <br />p.spid,<br />s.sid,<br />s.serial# serial_num,<br />s.username user_name,<br />a.type &nbsp;object_type,<br />s.osuser os_user_name,<br />a.owner,<br />a.object object_name,<br />decode&#40;sign&#40;48 - command&#41;,<br />1,<br />to_char&#40;command&#41;, &#39;Action Code #&#39; || to_char&#40;command&#41; &#41; action,<br />p.program oracle_process,<br />s.terminal terminal,<br />s.program program,<br />s.status session_status &nbsp; <br />from v$session s, v$access a, v$process p &nbsp; <br />where s.paddr = p.addr and<br /> &nbsp; &nbsp; &nbsp;s.type = &#39;USER&#39; and &nbsp; &nbsp;<br /> &nbsp; &nbsp; &nbsp;a.sid = s.sid &nbsp; and<br /> &nbsp; a.object=&#39;SUBSCRIBER_ATTR&#39;<br />order by s.username, s.osuser<br /><br />14。回滚段查看<br />select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents <br />Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, <br />v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, <br />sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs, <br />v$rollname where v$rollname.name&#40;+&#41; = sys.dba_rollback_segs.segment_name and <br />v$rollstat.usn &#40;+&#41; = v$rollname.usn order by rownum<br /><br />15。耗资源的进程（top session）<br />select s.schemaname schema_name, &nbsp; &nbsp;decode&#40;sign&#40;48 - command&#41;, 1, <br />to_char&#40;command&#41;, &#39;Action Code #&#39; || to_char&#40;command&#41; &#41; action, &nbsp; &nbsp;status <br />session_status, &nbsp;        s.osuser os_user_name, &nbsp;        s.sid, &nbsp; &nbsp; &nbsp; &nbsp; p.spid , &nbsp; &nbsp; &nbsp; &nbsp;        s.serial# serial_num, &nbsp;        <br />nvl&#40;s.username, &#39;&#91;Oracle process&#93;&#39;&#41; user_name, &nbsp;        s.terminal terminal, &nbsp; &nbsp;<br />s.program program, &nbsp;        st.value criteria_value &nbsp;from v$sesstat st, &nbsp;        v$session s &nbsp;, v$process p &nbsp; <br />where st.sid = s.sid and &nbsp;        st.statistic# = to_number&#40;&#39;38&#39;&#41; and &nbsp;        &#40;&#39;ALL&#39; = &#39;ALL&#39; <br />or s.status = &#39;ALL&#39;&#41; and p.addr = s.paddr order by st.value desc, &nbsp;p.spid asc, s.username asc, s.osuser asc<br /><br />16。查看锁（lock）情况<br />select /*+ RULE */ ls.osuser os_user_name, &nbsp;        ls.username user_name, &nbsp;        <br />decode&#40;ls.type, &#39;RW&#39;, &#39;Row wait enqueue lock&#39;, &#39;TM&#39;, &#39;DML enqueue lock&#39;, &#39;TX&#39;, <br />&#39;Transaction enqueue lock&#39;, &#39;UL&#39;, &#39;User supplied lock&#39;&#41; lock_type, &nbsp;        <br />o.object_name object, &nbsp;        decode&#40;ls.lmode, 1, null, 2, &#39;Row Share&#39;, 3, <br />&#39;Row Exclusive&#39;, 4, &#39;Share&#39;, 5, &#39;Share Row Exclusive&#39;, 6, &#39;Exclusive&#39;, null&#41; <br />lock_mode, &nbsp; &nbsp;o.owner, &nbsp;        ls.sid, &nbsp;        ls.serial# serial_num, &nbsp;        ls.id1, &nbsp;        ls.id2 &nbsp; &nbsp;<br />from sys.dba_objects o, &#40; &nbsp;        select s.osuser, &nbsp; &nbsp;s.username, &nbsp; &nbsp;l.type, &nbsp; &nbsp;        <br />l.lmode, &nbsp; &nbsp;s.sid, &nbsp; &nbsp;s.serial#, &nbsp; &nbsp;l.id1, &nbsp; &nbsp;l.id2 &nbsp;        from v$session s, &nbsp; &nbsp;        <br />v$lock l &nbsp;        where s.sid = l.sid &#41; ls &nbsp;where o.object_id = ls.id1 and &nbsp; &nbsp;o.owner <br />&#60;&#62; &#39;SYS&#39; &nbsp; order by o.owner, o.object_name<br /><br />17。查看等待（wait）情况<br />SELECT v$waitstat.class, v$waitstat.count count, SUM&#40;v$sysstat.value&#41; sum_value <br />FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN &#40;&#39;db block gets&#39;, <br />&#39;consistent gets&#39;&#41; group by v$waitstat.class, v$waitstat.count<br /><br />18。查看sga情况<br />SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC<br /><br />19。查看catched object<br />SELECT owner, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;name, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;db_link, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;namespace, &nbsp;<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;type, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sharable_mem, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;loads, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;executions, &nbsp; <br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; locks, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pins, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;kept &nbsp; &nbsp; &nbsp; &nbsp;FROM v$db_object_cache<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br />20。查看V$SQLAREA<br />SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, <br />VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, <br />USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,<br /> BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA<br /> <br />21。查看object分类数量<br />select decode &#40;o.type#,1,&#39;INDEX&#39; , 2,&#39;TABLE&#39; , 3 , &#39;CLUSTER&#39; , 4, &#39;VIEW&#39; , 5 , <br />&#39;SYNONYM&#39; , 6 , &#39;SEQUENCE&#39; , &#39;OTHER&#39; &#41; object_type , count&#40;*&#41; quantity from <br />sys.obj$ o where o.type# &#62; 1 group by decode &#40;o.type#,1,&#39;INDEX&#39; , 2,&#39;TABLE&#39; , 3 <br />, &#39;CLUSTER&#39; , 4, &#39;VIEW&#39; , 5 , &#39;SYNONYM&#39; , 6 , &#39;SEQUENCE&#39; , &#39;OTHER&#39; &#41; union select <br />&#39;COLUMN&#39; , count&#40;*&#41; from sys.col$ union select &#39;DB LINK&#39; , count&#40;*&#41; from <br /><br />22。按用户查看object种类<br />select u.name schema, &nbsp;        sum&#40;decode&#40;o.type#, 1, 1, NULL&#41;&#41; indexes, &nbsp;        <br />sum&#40;decode&#40;o.type#, 2, 1, NULL&#41;&#41; tables, &nbsp;        sum&#40;decode&#40;o.type#, 3, 1, NULL&#41;&#41; <br />clusters, &nbsp;        sum&#40;decode&#40;o.type#, 4, 1, NULL&#41;&#41; views, &nbsp;        sum&#40;decode&#40;o.type#, 5, 1, <br />NULL&#41;&#41; synonyms, &nbsp;        sum&#40;decode&#40;o.type#, 6, 1, NULL&#41;&#41; sequences, &nbsp;        <br />sum&#40;decode&#40;o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1&#41;&#41; <br />others &nbsp; from sys.obj$ o, sys.user$ u &nbsp; where o.type# &#62;= 1 and &nbsp; &nbsp;u.user# = <br />o.owner# and &nbsp;        u.name &#60;&#62; &#39;PUBLIC&#39; &nbsp; group by u.name &nbsp; &nbsp;order by <br />sys.link$ union select &#39;CONSTRAINT&#39; , count&#40;*&#41; from sys.con$<br /><br />23。有关connection的相关信息<br />1）查看有哪些用户连接<br />select s.osuser os_user_name, &nbsp; &nbsp;decode&#40;sign&#40;48 - command&#41;, 1, to_char&#40;command&#41;,<br /> &#39;Action Code #&#39; || to_char&#40;command&#41; &#41; action, &nbsp; &nbsp;        p.program oracle_process, &nbsp; &nbsp;        <br />status session_status, &nbsp; &nbsp;s.terminal terminal, &nbsp; &nbsp;s.program program, &nbsp; &nbsp;<br />s.username user_name, &nbsp; &nbsp;s.fixed_table_sequence activity_meter, &nbsp; &nbsp;&#39;&#39; query, &nbsp; &nbsp;<br />0 memory, &nbsp; &nbsp;0 max_memory, &nbsp; &nbsp; 0 cpu_usage, &nbsp; &nbsp;s.sid, &nbsp;        s.serial# serial_num &nbsp; &nbsp;<br />from v$session s, &nbsp; &nbsp;v$process p &nbsp; where s.paddr=p.addr and &nbsp; &nbsp;s.type = &#39;USER&#39; &nbsp;<br /> order by s.username, s.osuser<br />2）根据v.sid查看对应连接的资源占用等情况<br />select n.name, <br /> &nbsp;v.value, <br /> &nbsp;n.class,<br /> &nbsp;n.statistic# &nbsp;<br />from &nbsp;v$statname n, <br /> &nbsp;v$sesstat v <br />where v.sid = 71 and <br /> &nbsp;v.statistic# = n.statistic# <br />order by n.class, n.statistic#<br />3）根据sid查看对应连接正在运行的sql<br />select /*+ PUSH_SUBQ */<br /> &nbsp;command_type, <br /> &nbsp;sql_text, <br /> &nbsp;sharable_mem, <br /> &nbsp;persistent_mem, <br /> &nbsp;runtime_mem, <br /> &nbsp;sorts, <br /> &nbsp;version_count, <br /> &nbsp;loaded_versions, <br /> &nbsp;open_versions, <br /> &nbsp;users_opening, <br /> &nbsp;executions, <br /> &nbsp;users_executing, <br /> &nbsp;loads, <br /> &nbsp;first_load_time, <br /> &nbsp;invalidations, <br /> &nbsp;parse_calls, <br /> &nbsp;disk_reads, <br /> &nbsp;buffer_gets, <br /> &nbsp;rows_processed,<br /> &nbsp;sysdate start_time,<br /> &nbsp;sysdate finish_time,<br /> &nbsp;&#39;&#62;&#39; || address sql_address,<br /> &nbsp;&#39;N&#39; status <br />from v$sqlarea<br />where address = &#40;select sql_address from v$session where sid = 71&#41;<br /><br />24．查询表空间使用情况<br />select a.tablespace_name &#34;表空间名称&#34;,<br />        100-round&#40;&#40;nvl&#40;b.bytes_free,0&#41;/a.bytes_alloc&#41;*100,2&#41; &#34;占用率&#40;%&#41;&#34;,<br />        round&#40;a.bytes_alloc/1024/1024,2&#41; &#34;容量&#40;M&#41;&#34;,<br />        round&#40;nvl&#40;b.bytes_free,0&#41;/1024/1024,2&#41; &#34;空闲&#40;M&#41;&#34;,<br />        round&#40;&#40;a.bytes_alloc-nvl&#40;b.bytes_free,0&#41;&#41;/1024/1024,2&#41; &#34;使用&#40;M&#41;&#34;,<br />        Largest &#34;最大扩展段&#40;M&#41;&#34;,<br />        to_char&#40;sysdate,&#39;yyyy-mm-dd hh24&#58;mi&#58;ss&#39;&#41; &#34;采样时间&#34; <br />from &nbsp;&#40;select f.tablespace_name,<br /> &nbsp; &nbsp;sum&#40;f.bytes&#41; bytes_alloc,<br /> &nbsp; &nbsp;sum&#40;decode&#40;f.autoextensible,&#39;YES&#39;,f.maxbytes,&#39;NO&#39;,f.bytes&#41;&#41; maxbytes <br />        from dba_data_files f <br />        group by tablespace_name&#41; a,<br />        &#40;select &nbsp;f.tablespace_name,<br /> &nbsp; &nbsp; sum&#40;f.bytes&#41; bytes_free <br />        from dba_free_space f <br />        group by tablespace_name&#41; b,<br />        &#40;select round&#40;max&#40;ff.length&#41;*16/1024,2&#41; Largest,<br /> &nbsp; &nbsp;ts.name tablespace_name <br />        from sys.fet$ ff, sys.file$ tf,sys.ts$ ts <br />        where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts# <br />        group by ts.name, tf.blocks&#41; c <br />where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name<br /><br />25. 查询表空间的碎片程度 <br /><br />select tablespace_name,count&#40;tablespace_name&#41; from dba_free_space group by tablespace_name <br />having count&#40;tablespace_name&#41;&#62;10; <br /><br />alter tablespace name coalesce; <br />alter table name deallocate unused; <br /><br />create or replace view ts_blocks_v as <br />select tablespace_name,block_id,bytes,blocks,&#39;free space&#39; segment_name from dba_free_space <br />union all <br />select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents; <br /><br />select * from ts_blocks_v; <br /><br />select tablespace_name,sum&#40;bytes&#41;,max&#40;bytes&#41;,count&#40;block_id&#41; from dba_free_space <br />group by tablespace_name;<br /><br />26。查询有哪些数据库实例在运行<br />select inst_name from v$active_instances;<br /><br /><br /><br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WWW.LOVEUNIX.COM<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;        MADE BY SDAWNYJ<br /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EMAIL&#58;NONAMEBABY@SOHU.COM<br />2003-11-22<br /><br />更新历史：<br />2003-11-22 &nbsp; 完成。<br />2004-1-15 &nbsp;修改26条的错误。<br />active_instances 应为 v$active_instances<br />多谢chinaunix的jiguang发现并反映该错误。<br /><!--c2--></div><!--ec2-->

<!--emo&:wub:--><img src='style_emoticons/default/wub.gif' border='0' style='vertical-align:middle' alt='wub.gif' /><!--endemo--> ；真的太好了！！<br /><br /> <!--emo&:grin:--><img src='style_emoticons/default/grin.gif' border='0' style='vertical-align:middle' alt='grin.gif' /><!--endemo--> 太感谢了！！！

很多图形工具都有这一块的功能了

是啊，写出语句来主要是为了用起来方便，呵呵

很不错。实用就是最好。

经典，非常好。

Oracle的东西忘记差不多了，哎~~~ <!--emo&:huh:--><img src='style_emoticons/default/huh.gif' border='0' style='vertical-align:middle' alt='huh.gif' /><!--endemo-->

<!--QuoteBegin-threehair+2004-10-11 12:02:02--><div class='quotetop'>QUOTE(threehair @ 2004-10-11 12:02:02)</div><div class='quotemain'><!--QuoteEBegin-->Oracle的东西忘记差不多了，哎~~~ <!--emo&:huh:--><img src='style_emoticons/default/huh.gif' border='0' style='vertical-align:middle' alt='huh.gif' /><!--endemo--><br />[right][snapback]396720[/snapback][/right]<br /><!--QuoteEnd--></div><!--QuoteEEnd--><br /><br />╭⌒╮ ╭⌒╮╭⌒╮ <br />╱◥███◣╭╭ ⌒╮◢███◤\╭⌒╮<br />︱田︱田 田| 　　 　︱田 田︱田︱<br />关门，上锁，钥匙已生锈。<br />世事静方见，人情淡始长！<br /><br /> <!--emo&:P--><img src='style_emoticons/default/tongue.gif' border='0' style='vertical-align:middle' alt='tongue.gif' /><!--endemo-->  <!--emo&:grin:--><img src='style_emoticons/default/grin.gif' border='0' style='vertical-align:middle' alt='grin.gif' /><!--endemo-->

thank<br />

偶是菜鸟，请说明一下啊！ <!--emo&:P--><img src='style_emoticons/default/tongue.gif' border='0' style='vertical-align:middle' alt='tongue.gif' /><!--endemo-->  <!--emo&:P--><img src='style_emoticons/default/tongue.gif' border='0' style='vertical-align:middle' alt='tongue.gif' /><!--endemo-->  <!--emo&:P--><img src='style_emoticons/default/tongue.gif' border='0' style='vertical-align:middle' alt='tongue.gif' /><!--endemo-->

谢谢

谢谢&#33;

THANKS

都是常用的SQL，有更加实用的吗

支持，经典！收藏了:lol

确实不错啊

现在我知道图形界面中显示的怎么用SQL语句实现了。:o:victory:

经典:P

很好的 东东

谢谢楼主的提供！

查看表空间的名称及大小，为什么要用这么复杂的，只从dba_data_files查不是就可以了吗                  
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

good!

喔喔
真是好樣的呀樓主

十分感激

Thanks !!
:)

:P :$ 好多都忘记了...多谢啊