DBA Tips

专注于Oracle性能优化和疑难杂症

Oracle维护常用SQL语句

/*
Oracle维护常用SQL语句

提要:
1、查看表空间的名称及大小
2、查看表空间物理文件的名称及大小
3、查看回滚段名称及大小
4、查看控制文件
5、查看日志文件
6、查看表空间的使用情况
7、查看数据库库对象
8、查看数据库的版本 
9、查看数据库的创建日期和归档方式
10、捕捉运行很久的SQL
11、查看数据表的参数信息
12.查看还没提交的事务
13、查找object为哪些进程所用
14、回滚段查看
15、耗资源的进程(top session)
16、查看锁(lock)情况
17、查看等待(wait)情况
18、查看sga情况
19、查看cached object
20、查看V$SQLAREA
21、查看有哪些用户连接
22、根据v.sid查看对应连接的资源占用等情况
23、根据sid查看对应连接正在运行的sql
*/


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

select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0)  "tablespace_size (M)"
  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、查看表空间的使用情况

SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES FREE,
       (B.BYTES * 100) / A.BYTES "% USED", (C.BYTES * 100) / A.BYTES "% FREE"
  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
       AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
 ORDER BY A.TABLESPACE_NAME;

select tablespace_name, sum(bytes) / (1024 * 1024) as "free_space (M)"
  from dba_free_space
 group by tablespace_name
 order by 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, Log_Mode From V$Database;


10、捕捉运行很久的SQL

select username,sid,opname,
       round(sofar * 100 / totalwork, 0) || '%' as progress,
       time_remaining, sql_text
  from v$session_longops, 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
 ORDER BY partition_position;


12.查看还没提交的事务

select * from v$locked_object;
select * from v$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$session s, 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.extents                 Extents,
       v$rollstat.rssize                  Size_in_Bytes,
       v$rollstat.xacts                   XActs,
       v$rollstat.gets                    Gets,
       v$rollstat.waits                   Waits,
       v$rollstat.writes                  Writes,
       sys.dba_rollback_segs.status       status
  from v$rollstat, sys.dba_rollback_segs, v$rollname
 where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
   and v$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$sesstat st, 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, o.object_name object,o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2,
       decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
       decode(ls.lmode, 1, null, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null) lock_mode
  from sys.dba_objects o, ( select s.osuser, s.username, l.type, l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s, 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) sum_value
  FROM v$waitstat, v$sysstat
 WHERE v$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、查看cached object

SELECT owner, name, db_link, namespace, type, sharable_mem,
       loads,executions,locks,pins, kept
  FROM v$db_object_cache;


20、查看V$SQLAREA

SELECT 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
  FROM V$SQLAREA;


21、查看有哪些用户连接

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$session s, v$process p
 where s.paddr = p.addr
   and s.type = 'USER'
 order by s.username, s.osuser;


22、根据v.sid查看对应连接的资源占用等情况

select n.name, v.value, n.class, n.statistic#
  from v$statname n, v$sesstat v
 where v.sid = 71
   and v.statistic# = n.statistic#
 order by n.class, n.statistic#;


23、根据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$sqlarea
 where address = (select sql_address from v$session where sid = 71)

 

阅读更多
个人分类: 1. Oracle基础知识
下一篇Oracle动态性能视图
想对作者说点什么? 我来说一句

ORACLE常用维护sql语句

2009年08月13日 13KB 下载

Oracle维护常用SQL语句.doc

2010年04月24日 57KB 下载

Oracle维护常用SQL语句汇总

2012年06月10日 13KB 下载

oralce 维护常用sql语句

2008年10月27日 7KB 下载

oracle常用sql语句

2011年11月01日 41KB 下载

oracle维护命令

2009年04月02日 11KB 下载

oracle日常维护常用的55条语句

2014年05月05日 4KB 下载

没有更多推荐了,返回首页

不良信息举报

Oracle维护常用SQL语句

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭