oracle日常运维常用SQL集合

---查询用户会话

select username,serial#, sid from v$session; 

alter system kill session 'sid,serial'; 

alter system kill session '1735,54989';---删除相关用户会话

--建议以后台登陆删除用户会话

--1、查询oracle的连接数

select count(*) from v$session;

--2、查询oracle的并发连接数

select count(*) from v$session where status='ACTIVE';

--3、查看不同用户的连接数

select username,count(username) from v$session where username is not null group by username;

--4、查看所有用户：

select * from all_users;

--5、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

select * from dba_sys_privs;

select * from user_sys_privs;

查看表空间、文件存放位置和ASM磁盘查询

 select tablespace_name,

        round(max_gb,2) total_gb,

        round(used_gb,2) used_gb,

        round(max_gb - used_gb,2) free_gb,

        round(100 * used_gb / max_gb,2) pct_used,

        total_files

   from (select a.tablespace_name tablespace_name,

                round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),

                      2) used_gb,

                round(a.maxbytes / power(2, 30), 2) max_gb,

                a.files total_files

           from (select f.tablespace_name,

                        sum(f.bytes) bytes_alloc,

                        sum(decode(f.autoextensible,

                                   'YES',

                                   f.maxbytes,

                                   'NO',

                                   f.bytes)) maxbytes,

                        count(1) files

                   from dba_data_files f

                  group by tablespace_name) a,

                (select f.tablespace_name, sum(f.bytes) bytes_free

                   from dba_free_space f

                  group by tablespace_name) b

          where a.tablespace_name = b.tablespace_name(+))

  order by 5 desc;

/*数据库文件存储位置*/

select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

---ASM磁盘监控

select * from v$asm_disk;

select group_number,sum(os_mb/1024) os,sum(total_mb/1024) total,sum(free_mb/1024) free from v$asm_disk

group by group_number;

select NAME,TOTAL_MB/1024/1024 TOTAL_TB,FREE_MB/1024/1024 FREE_TB,round(100 * (TOTAL_MB-FREE_MB) / TOTAL_MB,2) pct_used from v$asm_diskgroup;