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)