Oracle DB运维常用的SQL语句

1. List item

本文介绍一些Oracle DB日常运维最常用到（使用频率很高）的SQL语句。

1、查看表空间的名称及大小

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;

2、查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

3、查看回滚段名称及大小

select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;

4、查看控制文件

select name from v$controlfile;

5、查看日志文件

select member from v$logfile;

6、查看表空间的使用情况

col tablespace_name for a30 ;
set pages 20000;
comp sum of “Total_GB” “Used_GB”
“Free_GB” on report
set lines 200;
break on report
select b.TablespaceName “Tablespace_name”,round(sum(b.UsedByte) / 1024/1024/1024) “Total_GB”,
round((sum(b.UsedByte)-sum(a.FreeByte))/ 1024/1024/1024) “Used_GB”,
round(sum(a.FreeByte) / 1024/1024/1024) “Free_GB”,
round(sum(b.ExtensibleByte) / 1024/1024/1024) “Extend_GB”,
round(sum(a.FreeByte + b.ExtensibleByte) * 100 / sum(b.UsedByte + b.ExtensibleByte), 2) “Free(%)”,
sum(a.Extend) TotalExtends
from (select sum(bytes) FreeByte,
count() Extend,file_id FileID,tablespace_name TablespaceName
from dba_free_space
group by file_id,tablespace_name
union all
select sum(bytes_free) FreeByte,
count() Extend,file_id FileID,
tablespace_name TablespaceName
from v$temp_space_header
group by file_id,
tablespace_name) a,(select decode(autoextensible, ‘YES’,
decode(sign(maxbytes - bytes), 1, maxbytes

• bytes, 0), 0) ExtensibleByte,
  bytes UsedByte,
  file_id FileID,
  tablespace_name TablespaceName
  from dba_data_files
  union all
  select decode(autoextensible, ‘YES’,
  decode(sign(maxbytes - bytes), 1, maxbytes
• bytes, 0), 0) ExtensibleByte,
  bytes UsedByte,
  file_id FileID,
  tablespace_name TablespaceName
  from dba_temp_files) b
  where b.FileID = a.FileID(+) and
  b.TablespaceName= a.TablespaceName(+)
  group by b.TablespaceName
  order by b.TablespaceName;
  或者，以下语句更简洁一点：
  SELECT
  df.tablespace_name AS “Tablespace”,
  totalusedspace AS “Used MB”,
  (df.totalspace - tu.totalusedspace) AS “Free MB”,
  df.totalspace AS “Total MB”,
  ROUND(100 * (totalusedspace / totalspace)) AS “% Used”
  FROM
  (SELECT
  tablespace_name,
  ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM
  dba_data_files
  GROUP BY
  tablespace_name) df,
  (SELECT
  ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
  tablespace_name
  FROM
  dba_segments
  GROUP BY
  tablespace_name) tu
  WHERE
  df.tablespace_name = tu.tablespace_name;

7、查看数据库库对象

select owner, object_type, status, count(*)
count# from all_objects group by owner, object_type, status;

8、查看数据库的版本

Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)=‘Oracle’;

9、查看数据库的创建日期和归档方式

Select Created, Log_Mode From V$Database;

10、捕捉执行很久的SQL

column username format a12
column opname format a16
column progress format a8
select username,sid,opname, round(sofar*100 / totalwork,0) || ‘%’ as progress, time_remaining,sql_text
from v$sessio{n}_{l}ongops,v$sql
where time_remaining <> 0
and sql_address = address and sql_hash_value = hash_value ；

11，查看数据表分区的参数信息

SELECT partition_name, high_value, high_value_length, tablespace_name,pct_free, pct_used, ini_trans, max_trans, initial_extent,next_extent, min_extent, max_extent, pct_increase, FREELISTS,
freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,empty_blocks, avg_space, chain_cnt,
avg_row_len, sample_size,last_analyzed
FROM dba_tab_partitions
WHERE table_name = &table_name AND table_owner = &table_owner
ORDER BY partition_position ;

12，查看还没提交的事务

select * from v$locke{d}_{o}bject;select\ast fromv$transaction;

13，查找object为哪些进程所用

select p.spid,s.sid,s.serial# serial_num,
s.username user_name,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,
decode(sign(48 - command),1,to_char(command), ‘Action Code #’ || to_char(command) ) action,
p.program oracle_process,s.terminal terminal,s.program program,s.status session_status
from v$sessions,v$access a, v$process p
where s.paddr = p.addr and s.type = ‘USER’ and a.sid = s.sid and a.object=‘SUBSCRIBER_ATTR’
order by s.username, s.osuser；

14，回滚段查看

select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extentsExtents,v$rollstat.rssize Size_in_Bytes, v$rollstat.xactsXActs,v$rollstat.gets Gets, v$rollstat.waitsWaits,v$rollstat.writes Writes,
sys.dba_rollback_segs.status status from v$rollstat,sys.db{a}_{r}ollbac{k}_{s}egs,v$rollname
where v$rollname.name(+)=sys.db{a}_{r}ollbac{k}_{s}egs.segmen{t}_{n}ameandv$rollstat.usn (+) = v$rollname.usn
order by rownum；

15,耗资源的进程（top session）

select s.schemaname schema_name, decode(sign(48 - command), 1, to_char(command), ‘Action Code #’ || to_char(command) ) action, status session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num, nvl(s.username, ‘[Oracle process]’) user_name, s.terminal terminal,
s.program program, st.value criteria_value from v$sesstatst,v$session s , v$process p
where st.sid = s.sid and st.statistic# = to_number(‘38’) and (‘ALL’ = ‘ALL’ or s.status = ‘ALL’) and p.addr = s.paddr
order by st.value desc, p.spid asc, s.username asc, s.osuser asc;

16，查看锁（lock）情况

select /*+ RULE */ ls.osuser os_user_name, ls.username user_name, decode(ls.type, ‘RW’
, ‘Row wait enqueue lock’, ‘TM’, ‘DML enqueue lock’, ‘TX’, ‘Transaction enqueue lock’, ‘UL’
, ‘User supplied lock’) lock_type, o.object_name object, decode(ls.lmode, 1, null, 2, ‘Row Share’
, 3, ‘Row Exclusive’, 4, ‘Share’, 5, ‘Share Row Exclusive’, 6, ‘Exclusive’, null) lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2 from sys.dba_objects o, ( select s.osuser, s.username, l.type, l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$sessions,v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner <> ‘SYS’
order by o.owner, o.object_name;

17，查看等待（wait）情况

SELECT v$waitstat.class,v$waitstat.count count, SUM(v$sysstat.value)su{m}_{v}alueFROMv$waitstat, v$sysstatWHEREv$sysstat.name IN (‘db block gets’, ‘consistent gets’) group by
v$waitstat.class,v$waitstat.count;

18，查看sga情况

SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC;

19，有关connection的相关信息

1）查看有哪些用户连接
select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),
‘Action Code #’ || to_char(command) ) action,p.program oracle_process, status session_status, s.terminal terminal, s.program program, s.username user_name, s.fixed_table_sequence activity_meter, ‘’ query, 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
from v$sessions,v$process p where s.paddr=p.addr and s.type = ‘USER’
order by s.username, s.osuser;
2）根据v.sid查看对应连接的资源占用等情况
select n.name, v.value, n.class,n.statistic#
from v$statnamen,v$sesstat v
where v.sid = 71 and v.statistic# = n.statistic#
order by n.class, n.statistic#;
3）根据sid查看对应连接正在运行的sql
select /*+ PUSH_SUBQ */command_type, sql_text, sharable_mem, persistent_mem, runtime_mem,
sorts, version_count, loaded_versions, open_versions, users_opening, executions, users_executing, loads, first_load_time, invalidations, parse_calls, disk_reads, buffer_gets, rows_processed,sysdate start_time,sysdate finish_time,‘>’ || address sql_address,‘N’ status from v$sqlareawhereaddress=(selectsq{l}_{a}ddressfromv$session where sid = 71);

20，查询有哪些数据库实例在运行

select inst_name from v$active_instances;
//取得服务器的IP 地址
select utl_inaddr.get_host_address from dual
//取得客户端的IP地址
select sys_context(‘userenv’,‘host’),sys_context(‘userenv’,‘ip_address’) from dual;
备注：以上语句中有的比较复杂，但本人一一测试过，效果很好，结果可读性好，大家无须记忆，收藏即可，用到的时候可以即取即用，可以收到事倍功半的效果。也可以导出AWR报告查看以上相关的绝大部分数据。

本篇结束。
码字不易，宝贵经验分享不易，请各位支持原创，转载注明出处，多多关注作者，后续不定期分享DB基本知识和排障案例及经验、性能调优等。