oracle日常运维常用SQL--01

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;