回滚段查看
select rownum sysdba_rollback_segssegment_name Name v$rollstatextents
Extents v$rollstatrssize Size_in_Bytes v$rollstatxacts XActs
v$rollstatgets Gets v$rollstatwaits Waits v$rollstatwrites Writes
sysdba_rollback_segsstatus status from v$rollstat sysdba_rollback_segs
v$rollname where v$rollnamename(+) = sysdba_rollback_segssegment_name and
v$rollstatusn (+) = v$rollnameusn order by rownum
耗资源的进程(top session)
select sschemaname schema_name decode(sign( command)
to_char(command) Action Code # || to_char(command) ) action status
session_status sosuser os_user_name ssid pspid sserial# serial_num
nvl(susername [Oracle process]) user_name sterminal terminal
sprogram program stvalue criteria_value from v$sesstat st v$session s v$process p
where stsid = ssid and ststatistic# = to_number() and (ALL = ALL
or sstatus = ALL) and paddr = spaddr order by stvalue desc pspid asc susername asc sosuser asc
查看锁(lock)情况
select /*+ RULE */ lsosuser os_user_name lsusername user_name
decode(lstype RW Row wait enqueue lock TM DML enqueue lock TX
Transaction enqueue lock UL User supplied lock) lock_type
oobject_name object decode(lslmode null Row Share
Row Exclusive Share Share Row Exclusive Exclusive null)
lock_mode oowner lssid lsserial# serial_num lsid lsid
from sysdba_objects o ( select sosuser susername ltype
llmode ssid sserial# lid lid from v$session s
v$lock l where ssid = lsid ) ls where oobject_id = lsid and oowner
<> SYS order by oowner oobject_name
查看等待(wait)情况
SELECT v$waitstatclass v$unt count SUM(v$sysstatvalue) sum_value
FROM v$waitstat v$sysstat WHERE v$sysstatname IN (db block gets
consistent gets) group by v$waitstatclass v$unt
查看sga情况
SELECT NAME BYTES FROM SYSV_$SGASTAT ORDER BY NAME ASC
查看catched object
SELECT owner name db_link namespace
type sharable_mem loads executions
locks pins kept FROM v$db_object_cache
查看V$SQLAREA
SELECT SQL_TEXT SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS
VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING EXECUTIONS
USERS_EXECUTING LOADS FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS
BUFFER_GETS ROWS_PROCESSED FROM V$SQLAREA
查看object分类数量
select decode (otype#INDEX TABLE CLUSTER VIEW
SYNONYM SEQUENCE OTHER ) object_type count(*) quantity from
sysobj$ o where otype# > group by decode (otype#INDEX TABLE
CLUSTER VIEW SYNONYM SEQUENCE OTHER ) union select
COLUMN count(*) from l$ union select DB LINK count(*) from
按用户查看object种类
select uname schema sum(decode(otype# NULL)) indexes
sum(decode(otype# NULL)) tables sum(decode(otype# NULL))
clusters sum(decode(otype# NULL)) views sum(decode(otype#
NULL)) synonyms sum(decode(otype# NULL)) sequences
sum(decode(otype# NULL NULL NULL NULL NULL NULL ))
others from sysobj$ o sysuser$ u where otype# >= and uuser# =
oowner# and uname <> PUBLIC group by uname order by
syslink$ union select CONSTRAINT count(*) from n$
有关connection的相关信息
)查看有哪些用户连接
select sosuser os_user_name decode(sign( command) to_char(command)
Action Code # || to_char(command) ) action pprogram oracle_process
status session_status sterminal terminal sprogram program
susername user_name sfixed_table_sequence activity_meter query
memory max_memory cpu_usage ssid sserial# serial_num
from v$session s v$process p where spaddr=paddr and stype = USER
order by susername sosuser
)根据vsid查看对应连接的资源占用等情况
select nname
vvalue
nclass
nstatistic#
from v$statname n
v$sesstat v
where vsid = and
vstatistic# = nstatistic#
order by nclass nstatistic#
)根据sid查看对应连接正在运行的sql
select /*+ PUSH_SUBQ */
command_type
sql_text
sharable_mem
persistent_mem
runtime_mem
sorts
version_count
loaded_versions
open_versions
users_opening
executions
users_executing
loads
first_load_time
invalidations
parse_calls
disk_reads
buffer_gets
rows_processed
sysdate start_time
sysdate finish_time
> || address sql_address
N status
from v$sqlarea
where address = (select sql_address from v$session where sid = )
查询表空间使用情况select atablespace_name 表空间名称
round((nvl(bbytes_free)/abytes_alloc)*) 占用率(%)
round(abytes_alloc//) 容量(M)
round(nvl(bbytes_free)//) 空闲(M)
round((abytes_allocnvl(bbytes_free))//) 使用(M)
Largest 最大扩展段(M)
to_char(sysdateyyyymmdd hh:mi:ss) 采样时间
from (select ftablespace_name
sum(fbytes) bytes_alloc
sum(decode(fautoextensibleYESfmaxbytesNOfbytes)) maxbytes
from dba_data_files f
group by tablespace_name) a
(select ftablespace_name
sum(fbytes) bytes_free
from dba_free_space f
group by tablespace_name) b
(select round(max(fflength)*/) Largest
tsname tablespace_name
from sysfet$ ff sysfile$ tfsysts$ ts
where tsts#=ffts# and fffile#=tfrelfile# and tsts#=tfts#
group by tsname tfblocks) c
where atablespace_name = btablespace_name and atablespace_name = ctablespace_name
查询表空间的碎片程度
select tablespace_namecount(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_nameblock_idbytesblocksfree space segment_name from dba_free_space
union all
select tablespace_nameblock_idbytesblockss
