日常维护常用sql语句-CSDN博客

1.以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：
select object_id,session_id,locked_mode from v$locked_object;
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

2.如果有长期出现的一列，可能是没有释放的锁。
我们可以用下面SQL语句杀掉长期没有释放非正常的锁：
alter system kill session 'sid,serial#';
select * from v$locked_object a,user_objects b
where a.OBJECT_ID = b.object_id(+)

3.查看当前用户运行哪些job任务
Select * From User_Jobs;

4.查看表空间的使用情况
方法一：
select tablespace_name,round(used_space*16/1024) as used,tablespace_size*16/1024 as Avail,
round(used_percent) as ud from dba_tablespace_usage_metrics order by 4;

      select a.tablespace_name,(1-(a.total)/b.total)*100 used_percent
      from
      (select tablespace_name,sum(bytes) total
      from Dba_Free_Space group by tablespace_name) a,
      (select tablespace_name,sum(bytes) total
       from dba_data_files group by tablespace_name) b
      where a.tablespace_name=b.tablespace_name;

方法二：
select a.tablespace_name,(1-(a.total)/b.total)*100 used_percent
    from
    (select tablespace_name,sum(bytes) total
       from Dba_Free_Space group by tablespace_name) a,
    (select tablespace_name,sum(bytes) total
       from dba_data_files group by tablespace_name) b
      where a.tablespace_name=b.tablespace_name;

方法三：
select a.tablespace_name,
       sum(a.total_byte)/1024/1024||'M' total_byte,
       sum(b.free_byte)/1024/1024||'M' free_byte,
       100-round(sum(b.free_byte)*100/sum(a.total_byte),2) used_rate
from (select tablespace_name, file_id, sum(bytes) total_byte
          from dba_data_files
         group by tablespace_name,file_id) a,
       (select file_id, sum(bytes) free_byte
          from dba_free_space
         group by file_id) b
where a.file_id = b.file_id
group by a.tablespace_name;

5.查看用户占用多少空间：
Select owner,Sum(bytes/1024/1024) From Dba_Segments Group By owner;

6.查看当前session
select userenv('sid') from dual;

7.数据库session连接数
select count(*) from v$session;

8.数据库的并发数
select count(*) from v$session where status='ACTIVE';

9.是否存在死锁
set linesize 200
column oracle_username for a16
column os_user_name for a12
column object_name for a30
SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,
l.session_id,s.serial#, l.locked_mode,o.object_name
FROM v$locked_object l ,dba_objects o,v$session s
where l.object_id = o.object_id and s.sid = l.session_id;

10.建议回收表空间数据文件的大小（resize ResizeCMD)
select a.file#,
        a.name,
        a.bytes / 1024 / 1024 CurrentMB,
        ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
        (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
        'alter database datafile ''' || a.name || ''' resize ' ||
        ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
   from v$datafile a,
        (select file_id, max(block_id + blocks - 1) HWM
           from dba_extents
          where file_id in (select b.file#
                              From v$tablespace a, v$datafile b
                             where a.ts# = b.ts#
                               and a.name = 'UNDOTBS1')
          group by file_id) b
where a.file# = b.file_id(+)
    and (a.bytes - HWM * block_size) > 0
order by 5;

11.查询占用undo的Sql语句及空间大小
SELECT r.name 回滚段名,
s.serial#,
s.sid,
s.username 用户名,
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
used_urec,
t.noundo,
substr(s.program, 1, 78) 操作程序
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27776156/viewspace-743032/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27776156/viewspace-743032/