oracle 运维查询语句

环境：oracle11g

资源占用sql查询

1.查看会话内存占用情况

SELECT server "连接类型",
       s.username,
       OSUSEr,
       NAME,
       VALUE/1024/1024 "占用内存MB",
       s.SID "会话ID",
       s.serial#,
       spid "操作系统进程ID",
       p.PGA_USED_MEM,
       p.PGA_ALLOC_MEM,
       p.PGA_FREEABLE_MEM,
       p.PGA_MAX_MEM,
       sql_id
  FROM v$session s, v$sesstat st, v$statname sn, v$process p
WHERE st.SID = s.SID
   AND st.statistic# = sn.statistic#
   AND sn.NAME LIKE ‘session pga memory‘
   AND p.addr = s.paddr
ORDER BY VALUE DESC

进程杀死方法： 如果想杀死指定会话：

alter system kill session ‘sid,serial#‘;

内存没有释放执行如下步骤；

查看killed状态的会话，

select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status=‘KILLED‘ ;

其中orcl是数据库的sid，spid是上面查询出来的

linux下：kill -9 spid；即可杀死会话

2.查看值得怀疑的sql并列出load占用比

select substr(to_char(s.pct,‘99.00‘),2)||‘%‘load,
       s.executions executes,
       p.sql_text
from(select address,
               disk_reads,
               executions,
               pct,
               rank()over(order by disk_reads desc) ranking
         from(select address,
                       disk_reads,
                       executions,
                      100*ratio_to_report(disk_reads)over() pct
                 from sys.v_$sql
                where command_type!=47)
        where disk_reads>50*executions) s,
       sys.v_$sqltext p
where s.ranking<=5
  and p.address=s.address
order by 1, s.address, p.piece;

3.查询消耗内存多的SQL

select b.username,
       a. buffer_gets,
       a.executions,
       a.disk_reads / decode(a.executions, 0, 1, a.executions),
       a.sql_text SQL
  from v$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
   and a.disk_reads > 10000
order by disk_reads desc;

4.查看逻辑读多的SQL

select*
from(select buffer_gets, sql_text
         from v$sqlarea
        where buffer_gets>500000
        order by buffer_gets desc)
where rownum<=30;

5. 查看执行次数多的SQL

select sql_text, executions
  from (select sql_text, executions from v$sqlarea order by executions desc)
where rownum < 81;

6.查看读硬盘（dist_reads）较多的SQL

select sql_text, disk_reads
from(select sql_text, disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;

7.查询排序多的SQL

select sql_text, sorts
from(select sql_text, sorts from v$sqlarea order by sorts desc)
where rownum<21;

表空间资源查询

1.查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),‘990.99‘) || ‘%‘ "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;

select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name

查询表空间总容量

select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;

2.查询临时表空间

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

3.查询临时表空间temp的空间大小

select tablespace_name,file_name,bytes/1024/1024/1024 file_size,autoextensible from dba_temp_files;

增加临时表空间文件（增大）

alter tablespace TEMP add tempfile‘/home/oracle/db/oradata/wan/temp02.dbf‘ size 20g autoextend on next 100m maxsize 30g;

4.查询当前使用临时表空间最多的的信息sql信息。sid等

select ash.TEMP_SPACE_ALLOCATED / 1024 / 1024 / 1024 G,
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.TOP_LEVEL_SQL_ID,
ash.PLSQL_ENTRY_OBJECT_ID,
ash.PLSQL_OBJECT_ID,
ash.MODULE,
ash.MACHINE
from gv$active_session_history ash
where ash.SESSION_TYPE = ‘FOREGROUND‘
and ash.TEMP_SPACE_ALLOCATED is not null
--and ash.PLSQL_ENTRY_OBJECT_ID is null
--and ash.PLSQL_OBJECT_ID is null
--and ash.SQL_ID=ash.TOP_LEVEL_SQL_ID
order by 1 desc

根据sid等查询具体的sql语句

select sql_text from v$sqltext where sql_id=‘66czz93a40tdf‘ order by piece;

用户信息，连接数，会话查询

1.查看用户的角色信息

select * from dba_role_privs where grantee=‘用户名‘;

2.查看用户及用户状态

select username,account_status,lock_date from dba_users;

alter user vanytech account unlock; --解锁用户

3.查看当前用户的缺省表空间

select username,default_tablespace from user_users;

3.查询所有用户的默认表空间信息

select username,default_tablespace from dba_users;

4.查询当前连接数

select count(*) from v$process;  --当前的数据库连接数
select count(*) from v$session;	 --当前数据库会话连接数
select count(*) from v$session where status=‘ACTIVE‘;　--并发连接数

5.查询数据库允许的最大连接数

select value from v$parameter where name =‘processes‘; --数据库允许的最大连接数
alter system set processes = 300 scope = spfile;	--修改数据库最大连接情况
(需要重启数据库才能实现连接数的修改)

重启数据库：
shutdown immediate;
startup;

6.查看当前有哪些用户正在使用数据

SELECT osuser, a.username,cpu_time/executions/1000000||‘s‘, sql_fulltext,machine from v$session a, v$sqlarea b where a.sql_address =b.address order by cpu_time/executions desc;

7.查看当前数据库建立的会话情况：

select sid,serial#,username,program,machine,status from v$session;

数据增量查询

1.确认使用的表空间

mobileapp

2.查询每日数据量大小

SELECT a.snap_id,       c.tablespace_name ts_name,       to_char(to_date(a.rtime, ‘mm/dd/yyyy hh24:mi:ss‘), ‘yyyy-mm-dd hh24:mi‘) rtime,       round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,       round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,       round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,             2) ts_free_mb,       round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used  FROM dba_hist_tbspc_space_usage a,        (SELECT tablespace_id,               substr(rtime, 1, 10) rtime,               max(snap_id) snap_id          FROM dba_hist_tbspc_space_usage nb         group by tablespace_id, substr(rtime, 1, 10)) b,         dba_tablespaces c,         v$tablespace d where a.snap_id = b.snap_id   and a.tablespace_id = b.tablespace_id   and a.tablespace_id=d.TS#   and d.NAME=c.tablespace_name       and  to_date(a.rtime, ‘mm/dd/yyyy hh24:mi:ss‘) >=sysdate-30   order by a.tablespace_id,to_date(a.rtime, ‘mm/dd/yyyy hh24:mi:ss‘) desc;

对比表空间的每日大小，得出每日数据增量，然后*30得出每月大概数据量

TIPS:未完

oracle 运维查询语句

原文：https://www.cnblogs.com/yhy223/p/14628697.html