谁正在访问数据库?
Select csid cserial#cusernameaobject_idbobject_name
cprogramcstatusdnamecosuser
from v$Locked_object a
All_objects b
v$session c
audit_actions d
where aobject_id=bobject_id
and asession_id =csid(+)
and mand=daction;
alter system kill session &&;
Select asidaserial#ausernameastatusaprogrambnameaosuser
from v$session aaudit_actions b
where mand=baction
And username=&;
谁被锁住?
Select asidaserial#ausernameALOCKWAITastatusaprogrambname
from v$session aaudit_actions b
where mand=baction
AND LOCKWAIT IS NOT NULL;
谁在锁表?
Select asidaserial#ausernameALOCKWAITastatusaprogrambname
from v$session aaudit_actions b
where mand=baction
AND STATUS=ACTIVE;
Select sid serial# object_name row_wait_block#
row_wait_row# row_wait_file#
from all_objects v$session
where row_wait_obj#=object_id and type=USER
and lockwait is not null ;
Select slusername slsid slserial#
from v_$lock l v$session s
where exists (select * from v_$lock l v$session s
where lsid=ssid and lid=l
and slockwait=lkaddr
and request=
and lsid=ssid) ;
select count(*) from v$session;
select count(*) from sysv_$process;
select count(*) from sysv_$transaction;
ZYP_
查看哪些包要固定
COLUMN OWNER FORMAT A
Select owner name type
source_size+code_size+parsed_size+error_size BYPES
from dba_object_size
where type=PACKAGE BODY ORDER BY DESC ;
查看一个用户拥有哪些表空间的实体信息:
Select tablespace_name owner segment_namesegment_type
from dba_segments
where ownerSyS
and segment_type_ROLLBACK
order by tablespace_name owner segment_name ;
break on owner on segment_name
COLUMN segment_name FORMAT A
cOLUMN tablespace_name FORMAT A
COLUMN file_name FORMAT A
SELECT Aowner asegment_name btablespace_name bfile_name
sum(abytes) bytes
from dba_extents a dba_data_files b
where afile_idbfile_id group by aowner asegment_name
btablespace_name bfile_name ;
看内存缓沖区使用效率的指数是命中率HITS:
Hits=Logical_reads/(logical_reads+physical_reads)
其中logical_reads=db_block_gets+consistent_reads
select curvalue db convalue con phyvalue phy
(curvalue+convalue)/curvalue+convalue+phyvalue)* HITS
from v$sysstat cur v$sysstat con v$sysstat phy
where CURNAME=db block gets AND
CONNAME=consistent gets AND
PHYNAME=physical reads ;
如何检测ROLLBACK SEGMENT竞争?
select class count from v$waitstat
where class in
(system undo header system undo block
undo header undo block) ;
select sum(value) from v$sysstat where name in
(db block gets consistents gets) ;
若count/sum(value)大于%则应考虑增加ROLLBACK SEGMENT
查看有事务在哪几个回退段中
COLUMN u FORMAT A
COLUMN s FORMAT A
COLUMN s FORMAT A
select osuser o username u segment_name s sasql_text
from v$session s v$transaction t dba_rollback_segs r v$sqlarea sa
where staddr=taddr and tsidusn=rsegmant_id(+)
and ssql_address=saaddress(+) ;
