文档库

最新最全的文档下载
当前位置:文档库 > ORACLE常用操作

ORACLE常用操作

1、查询占用IO资源前五的sql
select sql_text,disk_reads from (
select q.SQL_TEXT,q.DISK_READS,dense_rank() over (order by disk_reads desc ) disk_read_rank from v$sql q)
where disk_read_rank <= 5 ;
2、查询sql资源的占用情况
select s.SID, s.serial#,spid 操作系统ID,osuser 系统用户,s.MACHINE 计算机名,s.program 工具,http://www.wendangku.net/doc/72a4068f7fd5360cbb1adb01.html ername 数据库用户,sq.CPU_TIME 消耗CPU时间,sq.SQL_TEXT 执行的sql,
NAME 会话PGA内存, round(st.VALUE / 1024 / 1024,2) 会话占有内存MB
from v$session s
left join v$sesstat st on s.SID = st.SID
left join v$statname sn on sn.STATISTIC# = st.STATISTIC#
left join v$process p on p.ADDR = s.PADDR
left join V$SQL sq on sq.ADDRESS = s.SQL_ADDRESS and sq.HASH_VALUE = s.SQL_HASH_VALUE
where http://www.wendangku.net/doc/72a4068f7fd5360cbb1adb01.html like 'session pga memory';
3、查看产生死锁的信息
select http://www.wendangku.net/doc/72a4068f7fd5360cbb1adb01.html ername || '@' || s1.machine || '(SID='||s1.sid||') is blocking'
|| http://www.wendangku.net/doc/72a4068f7fd5360cbb1adb01.html ername ||'@'|| s2.machine||'(SID='||s2.sid||')' AS blocking_status
from v$lock l1,v$session s1,v$lock l2,v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request>0
and l1.id1=l2.id1 and l2.id2=l2.id2;
4、查看session中SQL的资源使用情况
select
SE.SID,
SE.SERIAL#,
P.SPID,
http://www.wendangku.net/doc/72a4068f7fd5360cbb1adb01.html ERNAME,
SE.STATUS,
SE.TERMINAL,
SE.PROGRAM,
SE.MODULE,
SE.SQL_ADDRESS,
ST.EVENT,
ST.P1TEXT,
ST.P1,
ST.P2,
ST.P3,
ST.STATE,
ST.SECONDS_IN_WAIT,
S.PHYSICAL_READS,
S.BLOCK_CHANGES
from v$session se, v$session_wait st, v$sess_io s,v$process p
where se.SID=st.SID
and se.SID=s.SID
and se.PADDR=p.ADDR
and se.SID>6
and st.WAIT_TIME=0
and st.EVENT not like '%SQL%'
5、清掉buffer cache中的数据
alter session set event ' immediate trace name flush_cache';
6、ORA-00845错误处理
(1)立即生效:
umount tmpfs
mount -t tmpfs shmfs size=15G /dev/shm
(2)永久生效,需要重启服务器
修改 /etc/fstab文件,在其中加入一行:
tmpfs /dev/shm tmpfs defaults,size=15000M 0 0
7、RAC 数据启动和关闭的方法grid用户
(1)关闭节点
srvctl stop instance -d SGTMS -i SGTMS1
srvctl stop nodeapps -n tmsdata01
(2)启动节点
srvctl start instance -d SGTMS -i SGTMS1
srvctl start nodeapps -n tmsdata01
(3) 查看数据库服务的状态
crsctl status resource -t
8、查询数据库死锁
select object_name as 对象名称,
s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id
and l.session_id=s.sid and s.paddr=p.addr;
9、节点1重启后无法启动,报错信息如下,crsd服务没有启动
[grid@tmsdata01 ~]$ srvctl start instance -d SGTMS -i SGTMS1
PRCD-1027 : Failed to retrieve database SGTMS
PRCR-1070 : Failed to check if resource ora.sgtms.db is registered
Cannot communicate with crsd
切换到root用户下 在/opt/app/11.2.0/

免费下载Word文档免费下载: ORACLE常用操作

(共5页)