常用oracle 运维sql
一、删除多余trc文件
#清除三天以前所有修改过的文件
find ./ -mtime +3 -name "*.trrgs rm -f
find ./ -mtime +3 -name "*.trm"|xargs rc"|xam -f
find ./ -mtime +3 -name "*.aud"|xargs rm -f
find ./ -mtime +3 -name "log*.xml"|xargs rm -f
#清除三天以前所有被创建的文件
find ./ -ctime +3 -name "*.trrgs rm -f
find ./ -ctime +3 -name "*.trm"|xargs rc"|xam -f
find ./ -ctime +3 -name "*.aud"|xargs rm -f
find ./ -ctime +3 -name "log*.xml"|xargs rm -f
#清除三天以前所有被访问的文件
find ./ -atime +3 -name "*.trrgs rm -f
find ./ -atime +3 -name "*.trm"|xargs rc"|xam -f
find ./ -atime +3 -name "*.aud"|xargs rm -f
find ./ -atime +3 -name "log*.xml"|xargs rm -f
#将+3的+变成-，变成三天以内，一般不建议这么做
二、实时监控SQL命令的执行执行情况
select * from(select a.sid session_id,a.sql_id,a.status,a.cpu_time/1000000 cpu_sec,a.buffer_gets,a.disk_reads,a.sql_text sql_text from v$sql_monitor a,v$sql b where a.sql_id=b.sql_id order by a.cpu_time desc) where rownum<20;
select * from(select a.sid session_id,a.sql_id,a.status,a.cpu_time/1000000 cpu_sec,a.buffer_gets,a.disk_reads,a.sql_text sql_text from v$sql_monitor a,v$sql b where a.sql_id=b.sql_id and a.status='EXECUTING' order by a.disk_reads desc) where rownum<=20;
select * from (select s.sid,s.username,s.sql_id,sa.elapsed_time/1000000,sa.buffer_gets,sa.sql_text from v$sqlarea sa,v$session s where s.sql_hash_value=sa.hash_value and s.sql_address=sa.address and s.username is not null order by sa.cpu_time desc) where rownum <=10;
三、锁处理
==================查询存在的锁====================
set linesize 1000 pagesize 1000
col blkg_user for a10
col blkg_machine for a10
col blkg_sid for a200
col wait_user for a10
col wait_machine for a10
col wait_sid for a100
col obj_own for a10
col obj_name for a10
col blkg_sql for a50
col wait_sql for a50
select
s1.username blkg_user, s1.machine blkg_machine
,s1.sid blkg_sid, s1.serial# blkg_serialnum
,s1.process blkg_OS_PID
,substr(b1.sql_text,1,50) blkg_sql
,chr(10)
,s2.username wait_user, s2.machine wait_machine
,s2.sid wait_sid, s2.serial# wait_serialnum
,s2.process wait_OS_PID
,substr(w1.sql_text,1,50) wait_sql
,lo.object_id blkd_obj_id
,do.owner obj_own, do.object_name obj_name
from v$lock l1
,v$session s1
,v$lock l2
,v$session s2
,v$locked_object lo
,v$sqlarea b1
,v$sqlarea w1
,dba_objects do
where s1.sid = l1.sid
and s2.sid = l2.sid
and l1.id1 = l2.id1
and s1.sid = lo.session_id
and lo.object_id = do.object_id
and l1.block = 1
and s1.prev_sql_addr = b1.address
and s2.sql_address = w1.address
and l2.request > 0;
==================死锁查询===================
select object_name,machine,s.sid,s.serial# from gv$locked_object l ,dba_objects o,gv$session s where l.object_id=o.object_id and l.session_id=s.sid;
#kill
alter system kill session 'sid,serial#' immediate;
四、磁盘组创建
create diskgroup <DGNAME> external redundancy disk '/dev/raw/raw8'
attribute 'compatible.rdbms'='11.1','compatible.asm'='11.1';
五、清理监听日志
LSNRCTL>set current_listener <listener_name>
LSNRCTL>set log_file <new filename>
And to save the change to the listener.ora file:
LSNRCTL>save_config
LSNRCTL>
LSNRCTL> set current_listener <listenername>
LSNRCTL> set log_status off
In another window rename the log file. Then return to previous window
LSNRCTL> set log_status on
which will create a new and empty listener.log file which will continue logging.
六、字符集
select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
#发现是 AMERICAN_AMERICA. 现在把它改成
#2. 修改Oracle用户环境变量
vim ~/.bash_profile
export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
:wq
source ~/.bash_profile
#3. 再查询字符集
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
#还需有一种pdb中修改字符集
#查看字符集(先查cdb，之后进入pdb，查询pdb）
select userenv('language') from dual;
#重启至限制模式
alter pluggable database orcl close immediate instances=all;
alter pluggable database orcl open read write restrricted;
#进入pdb修改
alter session set container=prcl;
select userenv('language') from dual;
alter database cheracter set internal_user zhs16gbk;
#重启确定
alter pluggable database orcl close immediate;
alter pluggable database orcl open instances=all;
select userenv('language') from dual;
alter pluggable database orcl save state insrances=all;
七、查看表空间
set linesize 200
col tablespace_name for a20
select df.tablespace_name,
count(*) files,
round(sum(df.bytes) / 1048576) size_mb,
round(sum(free.bytes) / 1048576, 2) free_mb,
100 - round(100.0 * sum(free.bytes) / sum(df.bytes), 2) pct_used,
round(100.0 * sum(free.bytes) / sum(df.bytes), 2) pct_free
from dba_data_files df,
(select tablespace_name,
file_id,
sum(bytes) bytes,
max(bytes) maxbytes
from dba_free_space
group by tablespace_name, file_id) free
where df.tablespace_name = free.tablespace_name(+)
and df.file_id = free.file_id(+)
group by df.tablespace_name
order by pct_used desc
SELECT a.tablespace_name "表空间名",
total/（1024*1024*1024） "表空间大小",
free/（1024*1024*1024） "表空间剩余大小",
(total - free)/（1024*1024*1024） "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
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 ;
#可能会报错，关于字符集的错误
#ERROR at line 2:
#ORA-00911: invalid character
八、查询阻塞的会话信息
select sql_id,blocking_instance,blocking_session,count(*) from gv$session where event='enq: TX - row lock contention' group by sql_id,blocking_instance,blocking_session;
select inst_id,RESOURCE_NAME,CURRENT_UTILIZATION now,MAX_UTILIZATION max,LIMIT_VALUE limit from gv$resource_limit where RESOURCE_NAME in ('processes','sessions');
#################会话进程信息####################
================获取阻塞会话===================
col user_name format a10
col owner format a10
col object_name format a10
col object_type format a10
set line 300
set pagesize 1000
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.BLOCKING_SESSION
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC;
===================已阻塞的会话信息===================
select sql_id,blocking_instance,blocking_session,count(*) from gv$session where event='enq: TX - row lock contention' group by sql_id,blocking_instance,blocking_session;
九、asm磁盘组容量
set lines 300 pages 300
select GROUP_NUMBER,NAME,TOTAL_MB/1024 total_GB,FREE_MB/1024 free_GB,USABLE_FILE_MB/1024 usable_GB from v$asm_diskgroup;
十、分布式事务锁
select * from dba_2pc_pending;
select local_tran_id,state from dba_2pc_pending;
#-结果为空和commited为正常，collecting和prepared为异常
prepared为异常#
rollback force '1387.28.29060';
十一、检查是否有回滚段异常
select segment_name,status from dba_rollback_segs where status not in ('ONLINE','OFFLINE');
exec strmadmin.print_transaction('1387.28.29060');
#如果状态为need recover的回滚段，需要切换undo 表空间到备用undo表空间
十二、dblink
set pages 300 lines 300
col db_link for a30
col owner for a10
col username for a20
col host for a200
select owner,db_link,username,host from dba_db_links order by CREATED;
================查询的blink数量========================
SELECT COUNT(*) FROM DBA_DB_LINKS;
十三、会话
================统计会话量==================
select inst_id,count(*) from gv$process group by inst_id;
select inst_id,count(*) from gv$session group by inst_id;
================实时会话数==================
select count(*),inst_id,'ACTIVE' from gv$session where status='ACTIVE' group by inst_id;
select count(*),inst_id,'INACTIVE' from gv$session where status='INACTIVE' group by inst_id;
十四、等待事件
#查看等待事件
col sql_id for a20
col event for a25
col program for a20
col sql_text for a50
set line 300
set pagesize 1000
select * from (select s.sql_id,w.event,count(*),sum(w.SECONDS_IN_WAIT) as sec,s.PROGRAM ,q.sql_text from v$session_wait w,v$session s,v$sql q
where w.sid=s.sid and w.WAIT_CLASS<>'Idle' and q.sql_id=s.sql_id group by w.event,s.sql_id,q.sql_text,s.PROGRAM order by 3,2,1)
where event not like 'SQL*Net%';
#查看等待时间最多的5个系统等待事件
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
十五、dbtime
WITH SYSSTAT AS
(SELECT SN.BEGIN_INTERVAL_TIME BEGIN_INTERVAL_TIME,
SN.END_INTERVAL_TIME END_INTERVAL_TIME,
SS.STAT_NAME STAT_NAME,
SS.VALUE E_VALUE,
LAG(SS. VALUE, 1) OVER(ORDER BY SS.SNAP_ID) B_VALUE
FROM DBA_HIST_SYS_TIME_MODEL SS, DBA_HIST_SNAPSHOT SN
WHERE TRUNC(SN.BEGIN_INTERVAL_TIME) >= SYSDATE - 40
AND SS.SNAP_ID = SN.SNAP_ID
AND SS.DBID = SN.DBID
AND SS.INSTANCE_NUMBER = SN.INSTANCE_NUMBER
AND SS.DBID = (SELECT DBID FROM V$DATABASE)
AND SS.INSTANCE_NUMBER = (SELECT INSTANCE_NUMBER FROM V$INSTANCE)
AND SS.STAT_NAME = 'DB time')
SELECT TO_CHAR(BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi') ||
TO_CHAR(END_INTERVAL_TIME, ' hh24:mi') DATE_TIME,
STAT_NAME,
ROUND((E_VALUE - NVL(B_VALUE, 0)) / 60 / 1000 / 1000, 2) DBTIME_VALUE
FROM SYSSTAT
WHERE (E_VALUE - NVL(B_VALUE, 0)) > 0
AND NVL(B_VALUE, 0) > 0;
=============================
set linesize 200;
set pagesize 20000;
col DATE_TIME for a45;
col STAT_NAME for a10;
WITH sysstat AS (
select
sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.VALUE e_value,
lag (ss. VALUE, 1) over (ORDER BY ss.snap_id) b_value
FROM
DBA_HIST_SYS_TIME_MODEL ss,
dba_hist_snapshot sn
WHERE
trunc (sn.begin_interval_time) >= sysdate - 1
AND ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.dbid = (SELECT dbid FROM v$database)
AND ss.instance_number = 1
AND ss.stat_name = 'DB time'
) SELECT
to_char (
BEGIN_INTERVAL_TIME,
'yyyy-mm-dd hh24:mi'
) || to_char (
END_INTERVAL_TIME,
' hh24:mi'
) date_time,
stat_name,
round(
(e_value - nvl(b_value, 0))/60/1000/1000 ,2
) dbtime_value
FROM
sysstat
WHERE
(e_value - nvl(b_value, 0)) > 0
AND nvl (b_value, 0) > 0;
十六、数据库状态
SELECT INSTANCE_NAME, HOST_NAME, STARTUP_TIME, STATUS, DATABASE_STATUS FROM V$INSTANCE;
SELECT NAME, LOG_MODE, OPEN_MODE FROM V$DATABASE;
===========检查发生坏块的数据库对象====================
SELECT TABLESPACE_NAME, SEGMENT_TYPE, OWNER, SEGMENT_NAME
FROM DBA_EXTENTS
WHERE FILE_ID = 7
AND 344 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
十七、查看运行很久的sql
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
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;
================查找前十条新能最差的sql=======================
SELECT * FROM (SELECT SQL_ID,PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10 ;
===============磁盘读取最高的sql语句=========================
SELECT SQL_ID,SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=5;
SELECT SQL_ID,SQL_TEXT FROM V$SQLAREA WHERE ROWNUM<=5 ORDER BY DISK_READS;
==============sql已经不在v$sql中，通过dba_hist_sqlstat和sql_hist_sqltext查看====================
select s.sql_id,s.plan_hash_value,t.sql_text,s.snap_id from dba_hist_sqlstat s,dba_hist_sqltext t where s.dbid = t.dbid and s.sql_id = t.sql_id and sql_text like 'select /* target sql */%' order by s.sql_id;
十八、查用户
select username from dba_users where username like '%USERINFO%' order by username;十九、出报告
@?/rdbms/admin/awrrpt.sql@?/rdbms/admin/sddmrpt.sql
@?/rdbms/admin/ashrpt.sql
