日常工作使用work.txt内容

1.数据库版本
select * from v$instance
2.库物理logic结构
(1).重做日志
select * from v$log;
所在位置：
select * from v$logfile
select a.group#,b.member,a.bytes/1024/1024 as "size(MB)"
from v$log a,v$logfile b
where b.group#=a.group#
order by a.group#
(2).物理文件存放位置
select * from dba_data_files
(3).逻辑表空间查看
select * from dba_tablespaces //bigfile/segment man/type/status
(4)段和表空间关系
select * from dba_extents /partion/
select * from dba_segments
(5)查看临时表空间
select * from dba_temp_files
select * from v$tempfile
(6)undo表空间
select * from dba_undo_extents
3.查看用户默认平空间和临时平空间
select * from dba_users
4.数据库每月增长量
select to_char(creation_time,'RRRR MONTH')"MONTH",
SUM(BYTES)/1024/1024/1024 "GROWTH SIZE"
FROM SYS.V_$DATAFILE
WHERE CREATION_TIME>SYSDATE-365
GROUP BY to_char(creation_time,'RRRR MONTH')
5.数据文件每月成长量
select a.ts# as "tablespace number",
b.name as "tablspace name",
to_char(a.creation_time,'RRRR MONTH') "MONTH",
SUM(A.BYTES)/1024/1024/1024 "GROWTH SIZE"
FROM SYS.V_$DATAFILE A,SYS.V_$TABLESPACE B
WHERE A.CREATION_TIME>SYSDATE-365
AND A.TS#=B.TS#
GROUP BY A.TS#,B.NAME,TO_CHAR(A.CREATION_TIME,'RRRR MONTH')
6.查看警告日志文件
show parameter dump
检查近段时间告警日志有什么异常
ORA-00600或ORA-07445(留意)
上下班都检查次
7.了解重启过程，重启计划，错误
8.系统资源检查
CPU 内存 I/O
资源消耗规律（eg.月底，月中)
系统繁忙时间和繁忙度
CPU使用率规律
第三方监控软件
9.查看数据库当前等待事件
（空闲与非空闲等待事件）
锁，索引,DDL
10.处理方法
1、看系统资源，资源是否耗尽
CPU使用100%，内存快用完了，用了大量的swap，是否有异常进程，这些异常进程是oracle还是其他应用程序
2、看等待事件，看系统正处于什么状况
3、看告警日志
11.backup/restore
当前库是不是有备份
采用什么方法备份
用oracle的rman？exp/imp？冷备?
还是用第三方工具？netbackup？
还是直接用存储镜像备份？
备份的周期？多长备份一次？备份一次需要多长时间？备份到磁带库还是硬盘上？


表空间的剩余用量:
SET LINESIZE 120
select a.tablespace_name,
sum((a.tots)/1024/1024) "total size",
sum((a.sumb)/1024/1024) "total free size",
sum(a.sumb)*100/sum(a.tots) "pct_free",
sum(a.largest) "max free size",
sum(a.chunks) "free extent number"
from (select tablespace_name,
0 tots,
sum(bytes) sumb,
max(bytes) largest,
count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0
from dba_data_files
group by tablespace_name)a
group by a.tablespace_name
ORDER BY 4;

确认数据文件的使用量:
select
d.tablespace_name tablespace,
d.file_name filename,
d.bytes "total file size",
nvl((d.bytes-s.bytes),d.bytes) "used size",
trunc(((nvl((d.bytes-s.bytes),d.bytes))/d.bytes)*100) PCT_USED
FROM sys.dba_data_files d,
v$datafile v,
(select file_id,sum(bytes) bytes
from sys.dba_free_space
group by file_id)s
where (s.file_id(+)=d.file_id)
and (d.file_name=v.name)
union
select d.tablespace_name tablespace,
d.file_name filename,
d.bytes "total file size",
nvl(t.bytes_cached,0) "used size",
trunc((t.bytes_cached/d.bytes)*100) pct_used
from sys.dba_temp_files d,v$temp_extent_pool t,v$tempfile v
where (t.file_id(+)=d.file_id)
and (d.file_id=v.file#);

查看数据库缓冲快区的HIT RATIO
select 1-(phy.value/(cur.value+con.value)) "cache hit ratio",
round((1-(phy.value/(cur.value+con.value)))*100,2) "ratio"
from v$sysstat cur,v$sysstat con,v$sysstat phy
where cur.name='DB BLOCK GETS'
AND CON.NAME='CONSISTENT GETS'
AND PHY.NAME='PHYSICAL READS'

确认使用者使用PGA的大小
select username,name,value
from v$statname n,v$session s,v$sesstat t
where s.sid=t.sid
and n.statistic#=t.statistic#
and s.type='USER'
AND S.USERNAME IS NOT NULL
AND N.NAME='SESSION PGA MEMORY'
AND USERNAME NOT LIKE 'SYSTEM'
AND T.VALUE>30000;

查看前10名使用CPU用量的联机
select *
from (select
'p',
s.sid,
s.status,
t.value/100/60 "cpu time",
floor(last_call_et/60) "last call et",
to_char(s.logon_time,'mm/dd hh24:mi') "logon time",
s.username,
s.process,
p.spid,
s.module,
s.machine,
s.sql_hash_value
from v$sesstat t,v$session s,v$process p
where t.statistic#=12
and s.sid=t.sid
and s.paddr=p.addr
and s.type='USER'
and s.sql_hash_value!=1425819161
union
select
'N',
s.sid,
s.status,
t.value*-1/100/60 "cpu time",
floor(last_call_et/60) "last call et",
to_char(s.logon_time,'mm/dd hh24:mi') "logon time",
s.username,
s.process,
p.spid,
s.module,
s.machine,
s.sql_hash_value
from v$sesstat t,v$session s,v$process p
where t.statistic#=12
and s.sid=t.sid
and s.paddr=p.addr
and s.type='USER'
and s.sql_hash_value!=1425819161
and t.value<0
order by 4 desc)
where rownum<11;

查询前10名排序量最大使用者联机
select *
from (select s.sid,
s.status,
b.spid,
s.sql_hash_value sesshash,
u.sqlhash sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024)mbused,
sum(u.extents) noexts,
u.segtype,
s.module||' - '||s.program proginfo
from v$sort_usage u,v$session s,v$parameter p,
v$process b
where u.session_addr=s.saddr
and p.name='DB_BLOCK_SIZE'
and b.addr=s.paddr
group by s.sid,
s.status,
b.spid,
s.sql_hash_value,
u.sqlhash,
s.username,
u.tablespace,
u.segtype,
s.module||' - '||s.program
order by 8 desc,4)
where rownum<11;

查询前10名数据库等待事件
select *
from(select event,total_waits,time_waited,average_wait
from v$system_event
where event not like '%IPC%'
AND EVENT NOT LIKE 'SQL*NET%'
AND EVENT NOT LIKE 'PIPE%'
AND EVENT NOT LIKE 'NULL%'
AND EVENT NOT LIKE 'WAKEUP%'
AND EVENT NOT LIKE 'PX%'
AND EVENT NOT LIKE '%TIMER%'
ORDER BY 3 DESC)
WHERE ROWNUM<11;

查询前10名使用CURSOR最多的使用者联机
select * from (
select s.sid,
v0.value cum_open_cursors,
v1.value cur_open_cursors,
v2.value cache_hits,
v3.value cache_count,
v4.value hard_parse,
v5.value-v4.value soft_parse,
s.osuser,s.machine,
nvl(s.module,s.program) proginfo
from v$sesstat v0,v$sesstat v1,v$sesstat v2,v$sesstat v3,
v$sesstat v4,v$sesstat v5,
v$session s
where v0.statistic#=2
and v0.sid=s.sid
and v1.statistic#=3
and v1.sid=s.sid
and v2.statistic#=191
and v2.sid=s.sid
and v3.statistic#=192
and v3.sid=s.sid
and v4.statistic#=180
and v4.sid=s.sid
and v5.statistic#=179
and v5.sid=s.sid
order by 3 desc,7 desc)
where rownum<11;