常用SQL

fancyaphy

--查看被锁的数据库对象及进程
SELECT b.object_name,c.* FROM v$locked_object a, all_objects b, v$session c WHERE a.OBJECT_ID=b.object_id AND a.SESSION_ID=c.SID
--查看表空间的空余比例
select c.tablespace_name, total_free, total, total_free/total ratio
from (select tablespace_name,sum(bytes) total_free
from dba_free_space  group by  tablespace_name ) d,
(select a.tablespace_name, sum(a.bytes) total
from dba_data_files a , dba_tablespaces b
where a.tablespace_name=b.tablespace_name
group by a.tablespace_name) c
where d.tablespace_name(+)=c.tablespace_name
--查看临时表空间的状态
SELECT * FROM v$temp_space_header
--查看操作系统进程相关的数据库会话
SELECT b.SPID,a.*
  FROM v$session a,v$process b
WHERE a.PADDR = b.ADDR
   AND b.SPID=3043890
--查询最消耗系统资源的进程
SELECT b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS),a.SQL_TEXT,c.*
  FROM v$sqlarea a, dba_users b, v$session c
WHERE a.PARSING_USER_ID = b.user_id
   AND a.DISK_READS > 100000
   AND a.address = c.SQL_ADDRESS
   AND a.hash_value = c.SQL_HASH_VALUE
ORDER BY a.DISK_READS DESC

SELECT * FROM (SELECT sql_text,address,rank() over(ORDER BY buffer_gets desc) AS rank_bufgets,
to_char(100*ratio_to_report(buffer_gets) over(),'999,99') pct_bufgets FROM v$sql) WHERE rank_bufgets<11
--TEMP表空间的状态查询
SELECT /*+ ORDERED */
u.tablespace,
s.sid || ',' || s.serial# as sid_ser,
s.username,
s.osuser,
(SUM(u.blocks)*TO_NUMBER(a.value))/1048576 as blk_mb,
x.sql_text,
s.module || ':' || chr(10) ||'. ' || s.action as process
FROM v$sort_usage u
,v$session S
,v$sql X
,v$parameter a
WHERE s.saddr = u.session_addr
AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
AND a.name = 'db_block_size'
GROUP
BY u.tablespace
, s.sid
, s.serial#
, s.username
, s.osuser
, a.value
, x.sql_text
, s.module
, s.action
ORDER
BY u.tablespace
, s.sid
--查看回滚段的状态
SELECT a.NAME,b.EXTENTS,b.rssize,b.xacts,b.waits,b.gets,optsize,status
  FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn

SELECT * FROM v$undostat
--查看哪些进程在使用回滚段
SELECT a.NAME,b.xacts,c.sid,c.serial#,c.username,d.sql_text
  FROM v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
WHERE a.usn = b.usn
   AND b.usn = e.xidusn
   AND c.taddr = e.addr
   AND c.sql_address = d.address
   AND c.sql_hash_value = d.hash_value
ORDER BY a.NAME,c.sid,d.piece

--查看Share Pool的大小
SELECT bytes/1024/1024 FROM v$sgastat WHERE NAME='free memory' AND pool='shared pool'
SELECT NAME,VALUE/1024/1024 FROM v$sga
--查看命中率
SELECT 1-(SUM(decode(NAME,'physical reads',VALUE,0))/(SUM(decode(NAME,'db block gets',VALUE,0))+(SUM(decode(NAME,'consistent gets',VALUE,0))))) FROM v$sysstat
--catblock.sql创建收集锁相关信息的视图
--utllockt.sql报告等待锁的会话，及相应的阻塞会话
select b.sid, c.username, c.osuser, c.terminal,
       decode(b.id2,0,a.object_name,'Trans-'||to_char(b.id1)) object_name,
       decode(b.lmode,0,'--Waiting--',
                      1,'Null',
                      2,'Row Share',
                      3,'Row Excl',
                      4,'Share',
                      5,'Share Row Exc',
                      6,'Exclusive',
                      'Other') "Lock Mode",
       decode(b.request,0,' ',
                        1,'Null',
                        2,'Row Share',
                        3,'Row Excl',
                        4,'share',
                        5,'Share Row Exc',
                        6,'Exclusive',
                        'Other') "Req Mode")
  from dba_objects a, v$lock b, v$session c
where a.object_id(+) = b.id1
   and b.sid = c.sid
   and c.username is not null
order by b.sid, b.id2

select s.username, a.sid, a.owner||'.'||a.object object,
       s.lockwait, t.sql_text
  from v$sqltext t, v$session s, v$access a
where t.address = s.sql_address
   and t.hash_value = s.sql_hash_value
   and s.sid = a.sid
   and a.owner != 'SYS'
   and upper(substr(a.object,1,2)) != 'V$'
   
SELECT vs.* FROM v$session vs,v$process vp WHERE vs.PADDR = vp.ADDR AND vp.SPID IN ()
--查看占用I/O的进程
SELECT a.FILE#,a.NAME,a.status,a.bytes,b.phyrds,b.PHYWRTS
  FROM v$datafile a,v$filestat b
WHERE a.FILE#=b.FILE#

tonyk5364

学习Oracle基本架构有一定帮助，谢了。

uftong

想找个老师学习一下SQL，发现很多地方都用得到