DBA常用Sql语句

查表空间名称
SQL>select ttablespace_name round(sum(bytes(1024*1024))0) ts_size
from dba_tablespaces t dba_data_files d where ttablespace_name dtablespace_name group by ttablespace_name
 
查表空间物理文件名称
SQL>select tablespace_name file_id file_nameround(bytes(1024*1024)0) total_space from dba_data_files order by tablespace_name
 
查回滚段名称
SQL>select segment_name tablespace_name rstatus
(initial_extent1024) InitialExtent(next_extent1024) NextExtent
max_extents vcurext CurExtent From dba_rollback_segs r vrollstat v
Where rsegment_id vusn(+) order by segment_name
 
查某回滚段里面跑什事物者正执行什sql语句
SQL>select dsql_textaname
from vrollname avtransaction bvsession cvsqltext d
where ausnbxidusn and baddrctaddr and csql_address
daddress and csql_hash_valuedhash_value
and ausn1
(备注：usn写成行)
 
查控制文件
SQL>select * from vcontrolfile
 
查日志文件
SQL> col member format a50
SQL>select * from vlogfile
 
查前SQL*PLUS户sidserial#
SQL>select sid serial# status from vsession where audsiduserenv('sessionid')
 
查前数库字符集 
SQL>select userenv('language') from dual
SQL>select userenv('lang') from dual
 
判断前正种SQL优化方式
explain plan产生EXPLAIN PLAN检查PLAN_TABLE中ID0POSITION列值
SQL>select decode(nvl(position1)1'RBO'1'CBO') from plan_table where id0
 
查系统前新SCN号：
SQL>select max(ktuxescnw * power(232) + ktuxescnb) from xktuxe
 
 
ORACLE中查找TRACE文件脚
SQL>select u_dumpvalue || '' || instancevalue || '_ora_' ||
vprocessspid || nvl2(vprocesstraceid  '_' || vprocesstraceid null ) || 'trc'Trace File from vparameter u_dump cross join vparameter instance cross join vprocess join vsession on vprocessaddr vsessionpaddr where u_dumpname 'user_dump_dest' and
instancename 'instance_name' and vsessionaudsidsys_context('userenv''sessionid')
 
SQL>select dvalue || 'ora_' || pspid || 'trc' trace_file_name
from (select pspid from sysv_mystat msysv_session s
sysv_process p where mstatistic# 1 and
ssid msid and paddr spaddr) p(select value from sysv_parameter where name 'user_dump_dest') d
 
查客户端登陆IP址
SQL>select sys_context('userenv''ip_address') from dual
 
生产数库中创建追踪客户端IP址触发器：
SQL>create or replace trigger on_logon_trigger
after logon on database
begin
  dbms_application_infoset_client_info(sys_context('userenv' 'ip_address'))
end
 
查询前日期
SQL> select to_char(sysdate'yyyymmddhh24miss') from dual
 
查表空间应数文件名：
SQL>select distinct file_nametablespace_nameAUTOEXTENSIBLE from dba_data_files
 
查表空间情况
SQL>select sum(bytes)(1024*1024) as free_spacetablespace_name
from dba_free_space group by tablespace_name
 
SQL>SELECT ATABLESPACE_NAMEABYTES TOTALBBYTES USED CBYTES FREE
(BBYTES*100)ABYTES USED(CBYTES*100)ABYTES FREE
FROM SYSSMTS_AVAIL ASYSSMTS_USED BSYSSMTS_FREE C
WHERE ATABLESPACE_NAMEBTABLESPACE_NAME AND ATABLESPACE_NAMECTABLESPACE_NAME
 
SQL>column tablespace_name format a18
SQL>column Sum_M format a12
SQL>column Used_M format a12
SQL>column Free_M format a12
column pto_M format 999
SQL>select stablespace_nameceil(sum(sbytes10241024))||'M' Sum_Mceil(sum(sUsedSpace10241024))||'M' Used_Mceil(sum(sFreeSpace10241024))||'M' Free_M sum(sUsedSpace)sum(sbytes) PTUSED
from (select bfile_idbtablespace_namebbytes
(bbytessum(nvl(abytes0))) UsedSpace
sum(nvl(abytes0)) FreeSpace(sum(nvl(abytes0))(bbytes)) * 100 FreePercentRatio from sysdba_free_space asysdba_data_files b
where afile_id(+)bfile_id group by bfile_idbtablespace_namebbytes
order by btablespace_name) s group by stablespace_name order by sum(sFreeSpace)sum(sbytes) desc
 
查数文件hwm（resize空间）文件头
SQL>SELECT v1file_namev1file_id
num1 totle_space
num3 free_space

num1num3 USED_SPACE(HWM)
nvl(num20) data_space
num1num3nvl(num20) file_head
FROM
(SELECT file_namefile_idSUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_namefile_id) v1
(SELECT file_idSUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2
(SELECT file_idSUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1file_idv2file_id(+)
AND v1file_idv3file_id(+)
 
查数文件头
SQL>SELECT v1file_namev1file_id
num1 totle_space
num3 free_space
num1num3 Used_space
nvl(num20) data_space
num1num3nvl(num20) file_head
FROM
(SELECT file_namefile_idSUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_namefile_id) v1
(SELECT file_idSUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2
(SELECT file_idSUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1file_idv2file_id(+)
AND v1file_idv3file_id(+)
(运行查询信息：
Totle_pace该数文件总字节单位
Free_space该数文件剩字节单位
Used_space该数文件已空间字节单位
Data_space该数文件中段数占空间数空间字节单位
File_Head该数文件头部占空间字节单位)
 
数库表空间增长情况检查：
SQL>select Atablespace_name(1(Atotal)Btotal)*100 used_percent
From (select tablespace_namesum(bytes) total from dba_free_space group by tablespace_name) A(select tablespace_namesum(bytes) total  from dba_data_files group by tablespace_name) B where Atablespace_nameBtablespace_name
 
SQL>SELECT UPPER(FTABLESPACE_NAME) 表空间名
DTOT_GROOTTE_MB 表空间(M)
       DTOT_GROOTTE_MB FTOTAL_BYTES 已空间(M) TO_CHAR(ROUND((DTOT_GROOTTE_MB FTOTAL_BYTES) DTOT_GROOTTE_MB * 100 2) '99099') FTOTAL_BYTES 空闲空间(M)
       FMAX_BYTES 块(M)  FROM (SELECT TABLESPACE_NAME
ROUND(SUM(BYTES) (1024 * 1024) 2) TOTAL_BYTES
ROUND(MAX(BYTES) (1024 * 1024) 2) MAX_BYTES
FROM SYSDBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
       (SELECT DDTABLESPACE_NAMEROUND(SUM(DDBYTES) (1024 * 1024) 2) TOT_GROOTTE_MB FROM SYSDBA_DATA_FILES DD
 GROUP BY DDTABLESPACE_NAME) D WHERE DTABLESPACE_NAME FTABLESPACE_NAME
ORDER BY 4 DESC
 
查表空间占磁盘情况 
SQL>col tablespace_name format a20
SQL>select  bfile_id  file_ID
btablespace_name  tablespace_name
bbytes  Bytes
(bbytessum(nvl(abytes0)))  used
 sum(nvl(abytes0))  free
 sum(nvl(abytes0))(bbytes)*100 Percent
     from dba_free_space adba_data_files b 
     where afile_idbfile_id 
     group by btablespace_namebfile_idbbytes 
     order by bfile_id
 
数库象扩展表空间free扩展值检查：
SQL>select atable_name anext_extent atablespace_name
from all_tables a(select tablespace_name max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where ftablespace_name atablespace_name and anext_extent > fbig_chunk
union select aindex_name anext_extent atablespace_name
from all_indexes a(select tablespace_name max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where ftablespace_name atablespace_name and anext_extent > fbig_chunk
 
Disk Read高SQL语句获取：
SQL>select sql_text from (select * from vsqlarea order by disk_reads)
where rownum<5
 
查找前十条性差sql
SQL>SELECT * FROM  (SELECT PARSING_USER_ID
 EXECUTIONSSORTSCOMMAND_TYPEDISK_READS
sql_text FROM  vsqlarea ORDER BY disk_reads DESC) 
 WHERE ROWNUM<10
 
等时间5系统等事件获取：
SQL>select * from (select * from vsystem_event where event not like 'SQL' order by total_waits desc) where rownum<5
 
查前等事件会话
SQL>col username format a10
SQL>set line 120
SQL>col EVENT format a30
SQL>select SESidsUsernameSEEventseTotal_WaitsSETime_WaitedSEAverage_Wait
from vsession Svsession_event SE where SUsername is not null and SESidSSid
and SStatus'ACTIVE' and SEEvent not like 'SQL*Net'
 
SQL>select sid event p1 p2 p3 wait_time seconds_in_wait state from vsession_wait where event not like 'message' and event not like 'SQL*Net' and event not like 'timer' and event 'wakeup time manager'
 
找连接会话关前等事件：
SQL>select SWSidSUsernameSWEventSWWait_TimeSWStateSWSeconds_In_Wait SEC_IN_WAIT
from vsession Svsession_wait SW where SUsername is not null and SWSidSSid
and SWevent not like 'SQL*Net' order by SWWait_Time Desc
 
Oracle回滚段状态检查：
SQL>select  segment_nameownertablespace_nameinitial_extentnext_extentdba_rollback_segsstatus from dba_rollback_segsvdatafile where file_idfile#
 
Oracle回滚段扩展信息检查
SQL>col name format a10
SQL>set linesize 140        
SQL>select substr(name140) nameextentsrssizeoptsizeaveactiveextendswrapsshrinkshwmsize
from vrollname rnvrollstat rs where (rnusnrsusn)
 
extents回滚段中盘区数量
Rssize字节单位回滚段尺寸
optsize：optimal参数设定值
Aveactive回滚段中删盘区时释放字节单位均空间
Extends系统回滚段增加盘区次数
Shrinks系统回滚段中清盘区（回滚段收缩）次数回滚段次清盘区时系统会回滚段中消盘区
Hwmsize回滚段尺寸限回滚段达尺寸
(果回滚段均尺寸接OPTIMAL值说明OPTIMAL值设置正确果回滚段动态增长次数收缩次数高需提高OPTIMAL值)
 
查回滚段情况户正回滚段资源
SQL>select susername uname from vtransaction tvrollstat r
vrollname uvsession s where staddrtaddr and
txidusnrusn and rusnuusn order by susername
 
 
查某shared_server正忙什
SQL>SELECT ausernameamachineaprogramasid
aserial#astatuscpiececsql_text
FROM vsession avprocess bvsqltext c
WHERE bspid13161 AND baddrapaddr
AND asql_addresscaddress(+) ORDER BY cpiece 
 
数库享池性检查
SQL>Select namespacegetsgethitratiopinspinhitratioreloads
Invalidations from vlibrarycache where namespace in
('SQLAREA''TABLEPROCEDURE''BODY''TRIGGER')
 
检查数重载率
SQL>select sum(reloads)sum(pins)*100 reload ratio from
vlibrarycache
 
检查数字典命中率
SQL>select 1sum(getmisses)sum(gets) data dictionary hit
ratio from vrowcache
(library cache gethitratiopinhitratio应该90数重载率reload ratio应该1数字典命中率data dictionary hit ratio应该85)
 
检查享存剩余情况
SQL>select request_misses request_failures from vshared_pool_reserved 
(享存剩余情况 request_misses request_failures应该接0)
 
数高速缓区性检查
SQL>select 1pvalue(bvalue+cvalue) db buffer cache hit
ratio from vsysstat pvsysstat bvsysstat c where
pname'physical reads' and bname'db block gets' and
cname'consistent gets'
 
检查buffer pool HIT_RATIO执行
SQL>select name (physical_reads(db_block_gets+consistent_gets))
MISS_HIT_RATIO FROM vbuffer_pool_statistics WHERE (db_block_gets+ consistent_gets)> 0
(正常时db buffer cache hit ratio 应该90正常时buffer pool MISS_HIT_RATIO 应该10)
 
数库回滚段性检查
检查Ratio执行
SQL>select sum(waits)* 100 sum(gets) Ratio sum(waits)
   Waits sum(gets) Gets from vrollstat
检查countvalue执行
SQL>select classcount from vwaitstat where class like 'undo'
SQL>select value from vsysstat where name'consistent gets'
(两者value值相)
 
检查average_wait执行
SQL>select eventtotal_waitstime_waitedaverage_wait from vsystem_event
where event like 'undo'
 
检查RBS header get ratio执行
SQL>select nnamesusnswraps decode(swaits011 swaitssgets)RBS
 header get ratio from vrollstat svrollname n where susnnusn
(正常时Ratio应该1 countvalue应该001average_wait0该值越越RBS header get ratio应该95)
 
杀会话脚
SQL>select ASIDBSPIDASERIAL#alockwaitAUSERNAMEAOSUSERalogon_timealast_call_et3600 LAST_HOURASTATUS
'orakill '||sid||' '||spid HOST_COMMAND
'alter system kill session '''||Asid||''||ASERIAL#||'''' SQL_COMMAND
from vsession AVPROCESS B where APADDRBADDR AND SID>6
 
查排序段性
SQL>SELECT name value FROM vsysstat WHERE name IN ('sorts (memory)' 'sorts (disk)') 
 
查数库库象
SQL>select owner object_type status count(*) count# from all_objects group by owner object_type status
 
查数库版　
SQL>Select * from vversion
 
查数库创建日期档方式
SQL>Select Created Log_Mode Log_Mode From VDatabase
 
捕捉运行久SQL
SQL>column username format a12
SQL>column opname format a16
SQL>column progress format a8
SQL>select usernamesidopnameround(sofar*100 totalwork0) || '' as progresstime_remainingsql_text from vsession_longops vsql where time_remaining <> 0 and sql_addressaddress and sql_hash_value hash_value
 
查数表参数信息
SQL>SELECT partition_name high_value high_value_length tablespace_namepct_free pct_used ini_trans max_trans initial_extentnext_extent min_extent max_extent pct_increase FREELISTSfreelist_groups LOGGING BUFFER_POOL num_rows blocksempty_blocks avg_space chain_cnt avg_row_len sample_sizelast_analyzed FROM dba_tab_partitions
WHERE table_name tname AND table_owner towner
ORDER BY partition_position
 
查没提交事务
SQL>select * from vlocked_object
SQL>select * from vtransaction
 
查找object进程
SQL>select pspidssidsserial# serial_numsusername user_name
atype  object_typesosuser os_user_nameaowneraobject object_namedecode(sign(48 command)1
to_char(command) 'Action Code #' || to_char(command) ) action
pprogram oracle_processsterminal terminalsprogram programsstatus session_status  from vsession s vaccess a vprocess p  where spaddr paddr and stype 'USER' and asid ssid  and aobject'SUBSCRIBER_ATTR'order by susername sosuser
 
查回滚段
SQL>col name format a10
SQL>set linesize 100
SQL>select rownum sysdba_rollback_segssegment_name Name vrollstatextents Extents vrollstatrssize Size_in_Bytes vrollstatxacts XActs vrollstatgets Gets vrollstatwaits Waits vrollstatwrites Writes sysdba_rollback_segsstatus status from vrollstat sysdba_rollback_segs vrollname where vrollnamename(+) sysdba_rollback_segssegment_name and vrollstatusn (+) vrollnameusn order by rownum
 
查耗资源进程(top session)
SQL>select sschemaname schema_namedecode(sign(48 command) 1 to_char(command) 'Action Code #' || to_char(command) ) actionstatus session_statussosuser os_user_namessidpspidsserial# serial_numnvl(susername'[Oracle process]') user_namesterminal terminalsprogram programstvalue criteria_value from vsesstat stvsession svprocess p where stsid ssid and ststatistic# to_number('38') and  ('ALL''ALL' or sstatus 'ALL') and paddrspaddr order by stvalue descpspid ascsusername ascsosu
ser asc
 
根PID查找相应语句
SQL>SELECT ausername
       amachineaprogramasidaserial#astatuscpiececsql_text
  FROM vsession avprocess bvsqltext c WHERE bspidspid
   AND baddrapaddr AND asql_addresscaddress(+) ORDER BY cpiece
 
根SID找ORACLE某进程
SQL> select prospid from vsession sesvprocess pro where sessid21 and sespaddrproaddr
 
监控前数库谁运行什SQL语句
SQL>SELECT osuser username sql_text from vsession a vsqltext b
where asql_address baddress order by address piece
 
查数库中某户正运行什SQL语句
SQL>SELECT SQL_TEXT FROM VSQLTEXT T VSESSION S WHERE TADDRESSSSQL_ADDRESS
AND THASH_VALUESSQL_HASH_VALUE  AND SMACHINE'XXXXX' OR USERNAME'WACOS'
 
查出前台正发出sql语句
SQL> select user_namesql_text from vopen_cursor where sid in(select sid from (select sidserial# from vsession where status'ACTIVE'))
 
查询前执行SQL语句：
 
SQL> select program sql_address from vsession where paddr in (select addr
from vprocess where spid3556)
 
PROGRAM                                          SQL_ADDRESS

sqlplus@ctc20 (TNS V1V3)                        000000038FCB1A90
 
SQL> select sql_text from vsqlarea where address'000000038FCB1A90'
 
找出消耗CPU高进程应SQL语句：
SQL>set line 240
SQL>set verify off
SQL>column sid format 999
SQL>column pid format 999
SQL>column S_# format 999
SQL>column username format A9 heading ORA User
SQL>column program  format a29
SQL>column SQL      format a60
SQL>COLUMN OSname format a9 Heading OS User
SQL>SELECT Ppid pidSsid sidPspid spidSusername username
Sosuser osnamePserial# S_#PterminalPprogram  program
PbackgroundSstatusRTRIM(SUBSTR(asql_text 1 80))  SQL
FROM vprocess P vsession Svsqlarea A WHERE Paddr spaddr
AND Ssql_address aaddress (+)  AND Pspid LIKE '&1'
 
Enter value for 1 PID（里输入占CPU高进程应PID）
 
 
SQL>set termout off
SQL>spool maxcputxt
SQL>SELECT '++'||Susername username
RTRIM(REPLACE(asql_textchr(10)''))||''FROM vprocess P vsession S
vsqlarea A WHERE Paddr spaddr AND Ssql_address aaddress (+)
AND Pspid LIKE '&&1'
Enter value for 1 PID（里输入占CPU高进程应PID）
spool off(句放执行)
 
CPU率高2条SQL语句获取
执行：top通top获CPU占率高进程pid
SQL>select sql_textspidvsessionprogramprocess from vsqlareavsessionvprocess where vsqlareaaddressvsessionsql_address and vsqlareahash_valuevsessionsql_hash_value
and vsessionpaddrvprocessaddr and vprocessspid in (pid)
 
SQL>col machine format a30
SQL>col program format a40
SQL>set line 200
SQL>select sidserial# usernameosusermachineprogramprocessto_char(logon_time'yyyymmdd hh24miss') from vsession where paddr in(select addr from vprocess where spid in([spid]))
 
SQL>select sql_text from vsqltext_with_newlines
where hash_value(select SQL_HASH_VALUE from vsession where sid&sid)
order by piece
 
 
查锁（lock）情况
SQL>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_typeoobject_name objectdecode(lslmode 1 null 2'Row Share'3'Row Exclusive'4'Share'5'Share Row Exclusive'6'Exclusive'null)lock_modeoownerlssidlsserial# serial_numlsid1lsid2 from sysdba_objects o(select sosusersusernameltypellmodessidsserial#lid1lid2 from vsession svlock l where ssidlsid)ls where oobject_idlsid1 and oowner<>'SYS' order by oowner oobject_name
 
SQL>select  sysv_sessionosusersysv_sessionmachinevlocksid
sysv_sessionserial#decode(vlocktype'MR''Media Recovery'
'RT''Redo Thread''UN''User Name''TX' 'Transaction''TM''DML'
'UL''PLSQL User Lock''DX''Distributed Xaction''CF''Control File'
'IS''Instance State''FS''File Set''IR''Instance Recovery'
'ST''Disk Space Transaction''TS''Temp Segment''IV''Library Cache Invalidation''LS''Log Start or Switch''RW''Row Wait''SQ''Sequence Number''TE''Extend Table''TT''Temp Table''Unknown') LockType
rtrim(object_type) || ' ' || rtrim(owner) || '' || object_name object_namedecode(lmode 0 'None'1 'Null'2 'RowS'3 'RowX'4 'Share'
5 'SRowX'6 'Exclusive''Unknown') LockModedecode(request 0 'None'1 'Null'2 'RowS'3 'RowX' 4 'Share'5 'SRowX'
6 'Exclusive' 'Unknown') RequestModectime block b

from vlock all_objects sysv_session
where vLocksid > 6
and sysv_sessionsid vlocksid
and vlockid1 all_objectsobject_id
 
 
DBA角色 查前数库里锁情况SQL语句：
SQL>col owner for a12
SQL>col object_name for a16
SQL>select bownerbobject_namelsession_idllocked_mode
from vlocked_object l dba_objects b
where bobject_idlobject_id
SQL>select t2usernamet2sidt2serial#t2logon_time
from vlocked_object t1vsession t2
where t1session_idt2sid order by t2logon_time
 
SQL>Select sql_address from vsession where sid
SQL>Select * from vsqltext where address
 
SQL>select COMMAND_TYPEPIECEsql_text from vsqltext where address(select sql_address from vsession a where sid18)   
 
SQL>select object_id from vlocked_object
SQL>select object_nameobject_type from dba_objects where object_id’’
果长期出现列没释放锁面SQL语句杀掉长期没释放非正常锁：
SQL>alter system kill session 'sidserial#'
查等（wait）情况
SQL>SELECT vwaitstatclassvwaitstatcount count SUM(vsysstatvalue) sum_value FROM vwaitstatvsysstat WHERE vsysstatname IN('db block gets''consistent gets') group by vwaitstatclassvwaitstatcount
查sga情况
SQL>SELECT NAME BYTES FROM SYSV_SGASTAT ORDER BY NAME ASC
查catched object
SQL>SELECT ownernamedb_linknamespacetypesharable_memloads              executionslockspinskept FROM vdb_object_cache
          
查VSQLAREA
SQL>SELECT SQL_TEXTSHARABLE_MEMPERSISTENT_MEMRUNTIME_MEMSORTS
VERSION_COUNTLOADED_VERSIONSOPEN_VERSIONSUSERS_OPENINGEXECUTIONS
USERS_EXECUTINGLOADSFIRST_LOAD_TIMEINVALIDATIONSPARSE_CALLS
DISK_READSBUFFER_GETSROWS_PROCESSED FROM VSQLAREA
 
查object分类数量
select decode(otype#1'INDEX'2'TABLE'3'CLUSTER'4'VIEW'5'SYNONYM'6 'SEQUENCE''OTHER') object_type count(*) quantity from sysobj o where otype# > 1 group by decode(otype#1'INDEX'2'TABLE'3'CLUSTER' 4'VIEW'5'SYNONYM'6'SEQUENCE''OTHER') union select 'COLUMN' count(*) from syscol union select 'DB LINK' count(*) from all_objects
关connection相关信息
1）查户连接
select sosuser os_user_namedecode(sign(48 command)1to_char(command)
 'Action Code #' || to_char(command))actionpprogram oracle_process
status session_statussterminal terminalsprogram program      
susername user_namesfixed_table_sequence activity_meter''query  
0 memory0 max_memory0 cpu_usagessidsserial# serial_num   
from vsession svprocess p where spaddrpaddr and stype 'USER' 
 order by susername sosuser

2）根vsid查应连接资源占等情况
select nnamevvaluenclassnstatistic# 
from vstatname nvsesstat v where vsid18 and vstatistic# nstatistic# order by nclass nstatistic#

3）根sid查应连接正运行sql
select *+ PUSH_SUBQ * command_typesql_textsharable_mem persistent_memruntime_memsortsversion_count
loaded_versionsopen_versionsusers_openingexecutions users_executingloadsfirst_load_timeinvalidations parse_callsdisk_readsbuffer_getsrows_processedsysdate start_timesysdate finish_time'>'|| address sql_address
'N' status from vsqlarea where address (select sql_address from vsession where sid8)
 
根pid查sql语句
SQL>select sql_text from vsql
where address in
(select sql_address from vsession
where sid in
(select sid from vsession where paddr in (select addr from vprocess where spid&pid)))
 
查询表空间情况
SQL>select atablespace_name 表空间名称
100round((nvl(bbytes_free0)abytes_alloc)*1002) 占率()
round(abytes_alloc102410242) 容量(M)
round(nvl(bbytes_free0)102410242) 空闲(M)
round((abytes_allocnvl(bbytes_free0))102410242) (M)
Largest 扩展段(M)to_char(sysdate'yyyymmdd hh24miss') 采样时间 from (select ftablespace_namesum(fbytes) bytes_alloc
sum(decode(fautoextensible'YES'fmaxbytes'NO'fbytes)) maxbytes
from dba_data_files f group by tablespace_name) a
(select ftablespace_namesum(fbytes) bytes_free
from dba_free_space f group by tablespace_name) b
(select round(max(fflength)*1610242) Largesttsname 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
 
SQL>SELECT UPPER(FTABLESPACE_NAME) 表空间名
       DTOT_GROOTTE_MB 表空间(M)
       DTOT_GROOTTE_MB FTOTAL_BYTES 已空间(M)

       TO_CHAR(ROUND((DTOT_GROOTTE_MB FTOTAL_BYTES) DTOT_GROOTTE_MB * 100
                     2)
               '99099')
       FTOTAL_BYTES 空闲空间(M)
       FMAX_BYTES 块(M)
  FROM (SELECT TABLESPACE_NAME
               ROUND(SUM(BYTES) (1024 * 1024) 2) TOTAL_BYTES
               ROUND(MAX(BYTES) (1024 * 1024) 2) MAX_BYTES
          FROM SYSDBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
       (SELECT DDTABLESPACE_NAME
               ROUND(SUM(DDBYTES) (1024 * 1024) 2) TOT_GROOTTE_MB
          FROM SYSDBA_DATA_FILES DD
         GROUP BY DDTABLESPACE_NAME) D
 WHERE DTABLESPACE_NAME FTABLESPACE_NAME
 ORDER BY 4 DESC
 
查询表空间碎片程度
 
SQL>select tablespace_namecount(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10
 
SQL>alter tablespace name coalesce
SQL>alter table table_name deallocate unused
 
SQL>create or replace view ts_blocks_v as
select tablespace_nameblock_idbytesblocks'free space' segment_name from dba_free_space union all
select tablespace_nameblock_idbytesblockssegment_name from dba_extents
 
SQL>select * from ts_blocks_v
 
SQL>select tablespace_namesum(bytes)max(bytes)count(block_id) from dba_free_space group by tablespace_name
 
SQL>select 'alter tablespace '||TABLESPACE_NAME||' coalesce'
from DBA_FREE_SPACE_COALESCED where PERCENT_EXTENTS_COALESCED<100
or PERCENT_BLOCKS_COALESCED<100
 
空间碎片部分组成范围数量范围尺寸等
fsfifree space fragmentation index（空间碎片索引）值直观体现

fsfi100*sqrt(max(extent)sum(extents))*1sqrt(sqrt(count(extents)))
rem fsfi value compute
rem fsfisql
column fsfi format 99999
select tablespace_namesqrt(max(blocks)sum(blocks))*
(100sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name order by 1
spool fsfirep


spool off
 
出fsfi值100（理想单文件表空间）着范
围增加fsfi值缓慢降着范围尺寸减少fsfi值会迅速降
某数库运行脚fsfisqlfsfi值：
tablespace_name fsfi

rbs 7406
system 10000
temp 2282
tools 7579
users 10000
user_tools 10000
ydcx_data 4734
ydcx_idx 5719
ydjf_data 3380
ydjf_idx 7555
统计出数库fsfi值作参数着足够
效空间fsfi值超30表空间中少会遇见效空间问题
空间接参数时需做碎片整理
 
 
查询数库实例运行
SQL>select inst_name from vactive_instances
 
DBA角色 查前数库里锁情况：
SQL>select object_idsession_idlocked_mode from vlocked_object
SQL>select t2usernamet2sidt2serial#t2logon_time  from  vlocked_object t1vsession t2
where t1session_idt2sid order by t2logon_time
 
查表否分区表：
例子：
SQL>select TABLE_NAMEPARTITIONED from user_tables where TABLE_NAME'LOCALUSAGE'
TABLE_NAME                     PAR
          
LOCALUSAGE                     YES
 
查分区表分区名相应表空间名：
SQL>select TABLE_NAME PARTITION_NAMETABLESPACE_NAME from user_tab_partitions where table_name like USAGE’
 
查索引否分区索引：
SQL>SELECT INDEX_NAME TABLE_NAME STATUS PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME LIKE 'USAGE'
果返回PATITIONEDYES请执行语句查询分区索引类型：SELECT index_nametable_namelocality FROM user_part_indexes
 
DualOracle中实际存表户均读取常没目标表Select中
查系统时间：
SQL>select to_char(sysdate'yymmdd hh24miss') shijian from dual
 
查索引段中extent数量：
SQL>select segment_namecount(*) from dba_extents
where segment_type'INDEX' and owner'SCOTT' group by segment_name
 
查系统表中户索引（检查system表空间户索引存）：
SQL>select count(*) from dba_indexes where tablespace_name’SYSTEM’ and owner NOT IN(SYS’’SYSTEM’)
 
查wacos表空间索引扩展情况：
SQL>SELECT SUBSTR(segment_name120) SEGMENT NAMEbytes COUNT(bytes)
FROM dba_extents WHERE segment_name IN( SELECT index_name FROM dba_indexes
WHERE tablespace_name 'WACOS') GROUP BY segment_namebytes ORDER BY segment_name
 
查表空间数文件读写性
SQL>Select namephyrdsphywrtsavgiotimminiotimmaxiowtmmaxiortm from vfilestatvdatafile where vfilestatfile#vdatafilefile#
 
SQL>Select fsname namefphyrdsfphyblkrdfphywrtsfphyblkwrt freadtimfwritetim
from vfilestat f vdatafile fs where ffile# fsfile# order by fsname
(注意：果phyblkrdphyrds接话表明表空间中存全表扫描表表需调整索引优化SQL语句)
 
转换表空间local方式理
SQL> exec sysdbms_space_admintablespace_migrate_to_local('TBS_TEST')
 
查户时段
SQL>SELECT usernamesidserial#sql_addressmachineprogramtablespacesegtype
contents FROM vsession sevsort_usage su WHERE sesaddrsusession_addr     
 
查占io较正运行session
SQL>SELECT sesidseserial#prSPIDseusernamesestatusseterminalseprogram  seMODULEsesql_addresssteventstp1textsiphysical_readssiblock_changes FROM vsession sevsession_wait stvsess_io sivprocess pr WHERE stsidsesid  AND stsidsisid AND sePADDRprADDR AND sesid>6 AND stwait_time0 AND stevent NOT LIKE 'SQL' ORDER BY physical_reads DESC
 
查找前十条性差sql
SQL>SELECT * FROM(SELECT PARSING_USER_ID EXECUTIONSSORTSCOMMAND_TYPEDISK_READSsql_text FROM  vsqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<10
 
删户表语句
SQL>select 'drop table '||table_name||' cascade constraints' from user_tables
 
 
查LOCK杀掉会话：
SQL>set linesize 132 pagesize 66
 
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode beading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading Username
column terminal heading Term format a6
column tab format a35 heading table Name
column owner format a9
column Address format a18
 
SQL>select nvl(SUSERNAME'Internal') username
       nvl(STERMINAL'None') terminal
       LSID||''||SSERIAL# Kill
       U1NAME||''||substr(T1NAME120) tab
       decode(LLMODE 1'No Lock'
                     2'Row Share'
                     3'Row Exclusive'
                     4'Share'
                     5'Share Row Exclusive'
                     6'Exclusive'null) lmode
       decode(LREQUEST1'No Lock'
                        2'Row Share'
                        3'Row Exclusive'
                        4'Share'
                        5'Share Row Exclusive'
                        6'Exclusive'null) request
       from VLOCK L
            VSESSION S
            SYSUSER U1
            SYSOBJ  T1
       where LSID SSID
       and T1OBJ# decode(LID20LID1LID2)
       and U1USER# T1OWNER#
       and STYPE 'BACKGROUND'
       order by 125
 
 
alter system kill session ' '
 
column username format A15
column sid      format 9990 heading SID
column type     format A4
column lmode    format 990  heading 'HELD'
column request  format 990  heading 'REQ'
column id1      format 9999990
column id2     format 9999990
break on id1 skip 1 dup
spool tfslckwtlst
 
SQL>select snusername
       msid
       mtype
       DECODE(mlmode0'None'
                    1'Null'
                    2'Row Share'
                    3'Row Excl'
                    4'Share'
                    5'SRow Excl'
                    6'Exclusive'
              lmodeltrim(to_char(lmode'990'))) lmode
      DECODE(mrequest0'None'
                      1'Null'
                      2'Row Share'
                      3'Row Excl'
                      4'Share'
                      5'SRow Excl'
                      6'Exclusive'
                      requestltrim(to_char(mrequest'990'))) request
      mid1
      mid2
      from vsession sn
           vlock    m
      where (snsid msid and mrequest 0)
       or   (snsid msid and
             mrequest 0 and lmode 4 and
             (id1 id2) in (select sid1
                                  sid2
                              from vlock s
             where request 0 and sid1 mid1 and sid2 mid2)
            )
       order by id1id2mrequest
       spool off
       clear breaks
 
查WACOS表空间索引
SQL>select  'analyze index '||segment_name||' validate structure' from dba_segments where tablespace_name’WACOS’and  segment_type’INDEX’
 
样识IO竞争负载衡
SQL>col 文件名 format a35
SQL>select
    dfname 文件名
    fsphyrds 读次数
    fsphywrts 写次数
    (fsreadtimdecode(fsphyrds01fsphyrds)) 读时间
    (fswritetimdecode(fsphywrts01fsphywrts)) 写时间
from
    vdatafile df
    vfilestat fs
where dffile#fsfile#
order by dfname

文件名                                           读次数     写次数     读时间     写时间

CORACLEORADATAORADBDR01DBF                   885        883          0          0
CORACLEORADATAORADBINDX01DBF                 885        883          0          0
CORACLEORADATAORADBOEM_REPOSITORYORA         885        883          0          0
CORACLEORADATAORADBRBS01DBF                  925      22306          0          0
CORACLEORADATAORADBSYSTEM01DBF             50804     155025          0          0
CORACLEORADATAORADBTEMP01DBF                 887        894          0          0
CORACLEORADATAORADBTOOLS01DBF                886        892          0          0
CORACLEORADATAORADBUSERS01DBF                885        883          0          0
 
已选择8行
 
中：ORADB数库名例中数库默认安装没进行优化调整
      直system表空间做操作导致system表空间数文件SYSTEM01DBF读写次数
      说明量system表空间做系统关操作应户建立单独表空间
 
 
查session正回滚段
SQL>col 回滚段名 format a10
SQL>col SID format 9990
SQL>col 户名 format a10
SQL>col 操作程序 format a80
SQL>col status format a6 trunc
 
SQL>SELECT  rname 回滚段名
    ssid
    sserial#
    susername 户名
    tstatus
    tcr_get
    tphy_io
    tused_ublk
    tnoundo
    substr(sprogram 1 78) 操作程序
FROM   sysv_session ssysv_transaction tsysv_rollname r
WHERE  taddr staddr and txidusn rusn
ORDER  BY tcr_gettphy_io
 
45检查谁Lock什象
set line 200
col OSUser format a10
col OraUser format a10
col Obj Locked format a30
select *+RULE*smachine sosuser OSUser susername OraUser ssid SessionID
sserial# Serial sprocess ProcessID sstatus Statuslname Obj Locked

lmode_held Lock Mode
from vsession sdba_dml_locks lvprocess p
where lsession_id ssid and paddr spaddr


造成等LOCK信息LOCK类型等：
SQL>col event format a30
SQL>set line 160
SQL>col machine format a10
SQL>col username format a15
SQL>select bsidbserial#busernamemachineeventwait_timechr(bitand(p116777216)16777215)||chr(bitand(p1 16711680)65535) Enqueue Type from vsession_wait avsession b
where event not like 'SQL*N' and event not like 'rdbms' and asidbsid
and bsid>8 and event'enqueue' order by username

List of the locked Oracle objects
SQL>set line 120
SQL>column object_name  format a32
SQL>column OS_USER_NAME format a12
SQL>column orauser      format a12
column sql_text     format a32
column serial#      format 999999
column sid          format 99999
SQL>SELECT OS_USER_NAME ORACLE_USERNAME AS orauser ssid oobject_name
   oobject_type sserial# asql_text
   FROM vlocked_object l dba_objects o vsession s vsqlarea a
   WHERE lobject_id oobject_id
   AND   sSQL_ADDRESS    aaddress
   AND lSESSION_ID ssid
   SELECT 'ALTER SYSTEM KILL SESSION '''||TO_CHAR(ssid)||''||TO_CHAR(sserial#)||''''
 
   AS  Statement to kill
   FROM vlocked_object l dba_objects o vsession s
   WHERE lobject_id oobject_id
   AND lSESSION_ID ssid
 
 
oracle数库性监控SQL
监控事例等
SQL>select eventsum(decode(wait_Time001)) Prevsum(decode(wait_Time010)) Currcount(*) Tot from vsession_Wait group by event order by 4
回滚段争情况
SQL>select name waits gets waitsgets Ratio from vrollstat a vrollname b where ausn busn 
监控表空间 IO 例
SQL>select dftablespace_name namedffile_name filefphyrds pyr
fphyblkrd pbrfphywrts pyw fphyblkwrt pbw from vfilestat f dba_data_files df where ffile# dffile_id
order by dftablespace_name
监控文件系统 IO 例
SQL>select substr(afile#12) # substr(aname130) Name 

astatusabytesbphyrdsbphywrts from vdatafile a vfilestat b
where afile# bfile# 
某户找索引
SQL>select user_indexestable_name user_indexesindex_nameuniqueness column_name from user_ind_columns user_indexes where user_ind_columnsindex_name user_indexesindex_name
and user_ind_columnstable_name user_indexestable_name 
order by user_indexestable_type user_indexestable_name
user_indexesindex_name column_position
监控 SGA 命中率
SQL>select avalue + bvalue logical_reads cvalue phys_reads
round(100 * ((avalue+bvalue)cvalue) (avalue+bvalue)) BUFFER HIT RATIO from vsysstat a vsysstat b vsysstat c where astatistic# 38 and bstatistic# 39 and cstatistic# 40 
监控 SGA 中字典缓区命中率
SQL>select parameter getsGetmisses getmisses(gets+getmisses)*100 miss ratio(1(sum(getmisses) (sum(gets)+sum(getmisses))))*100 Hit ratio from vrowcache where gets+getmisses <>0 group by parameter gets getmisses 
监控 SGA 中享缓存区命中率应该1
SQL>select sum(pins) Total Pins sum(reloads) Total Reloads
sum(reloads)sum(pins) *100 libcache from vlibrarycache
SQL>select sum(pinhitsreloads)sum(pins) hit radiosum(reloads)sum(pins) reload percent from vlibrarycache
显示数库象类
SQL>select count(name) num_instances type sum(source_size) source_sizesum(parsed_size) parsed_size sum(code_size) code_size sum(error_size) error_sizesum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2
监控 SGA 中重做日志缓存区命中率应该1
SQL>SELECT name gets misses immediate_gets immediate_misses
Decode(gets00missesgets*100) ratio1 Decode(immediate_gets+immediate_misses00 immediate_misses(immediate_gets+immediate_misses)*100) ratio2
FROM vlatch WHERE name IN ('redo allocation' 'redo copy') 
监控存硬盘排序率 10增加 sort_area_size 
SQL>SELECT name value FROM vsysstat WHERE name IN ('sorts (memory)' 'sorts (disk)') 

监控前数库谁运行什SQL语句
SQL>SELECT osuser username sql_text from vsession a vsqltext b
where asql_address baddress order by address piece
监控字典缓区
SQL>SELECT (SUM(PINS RELOADS)) SUM(PINS) LIB CACHE FROM VLIBRARYCACHE
SQL>SELECT (SUM(GETS GETMISSES USAGE FIXED)) SUM(GETS) ROW CACHE FROM VROWCACHE
SQL>SELECT SUM(PINS) EXECUTIONS SUM(RELOADS) CACHE MISSES WHILE EXECUTING FROM VLIBRARYCACHE(者前者率1接0)
SQL>SELECT SUM(GETS) DICTIONARY GETSSUM(GETMISSES) DICTIONARY CACHE GET MISSES FROM VROWCACHE
查找ORACLE字符集
SQL>select * from sysprops where name'NLS_CHARACTERSET' 
监控 MTS
SQL>select busy(busy+idle) shared servers busy from vdispatcher
(值05时参数需加)

SQL>select sum(wait)sum(totalq) dispatcher waits from vqueue where type'dispatcher'
SQL>select count(*) from vdispatcher
SQL>select servers_highwater from vmts
(servers_highwater接mts_max_servers时参数需加)
碎片程度
SQL>select tablespace_namecount(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10
SQL>alter tablespace name coalesce
SQL>alter table name deallocate unused
SQL>create or replace view ts_blocks_v as
select tablespace_nameblock_idbytesblocks'free space' segment_name from dba_free_space
    union all
    select tablespace_nameblock_idbytesblockssegment_name from dba_extents
    select * from ts_blocks_v
SQL>select tablespace_namesum(bytes)max(bytes)count(block_id) from dba_free_space group by tablespace_name
查碎片程度高表
SQL>SELECT segment_name table_nameCOUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS' 'SYSTEM') GROUP BY
segment_name HAVING COUNT(*)(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name)
17 表索引存储情况检查
SQL>select segment_namesum(bytes)count(*) ext_quan from dba_extents where tablespace_name'&tablespace_name' and segment_type'TABLE' group by tablespace_namesegment_name
SQL>select segment_namecount(*) from dba_extents where segment_type'INDEX' and owner'&owner' group by segment_name
18找CPU户session
SQL>select asidspidstatussubstr(aprogram140) progaterminalosuservalue60100 value from vsession avprocess bvsesstat c
where cstatistic#12 and csidasid and apaddrbaddr order by value desc
(12cpu used by this session)
 
表空间统计
 A    脚说明：
常脚显示出数库中表空间状态表空间已空间百分空闲空间数现表空间块
B脚原文
SELECT upper(ftablespace_name) 表空间名
       dTot_grootte_Mb 表空间(M)
       dTot_grootte_Mb ftotal_bytes 已空间(M)
       to_char(round((dTot_grootte_Mb ftotal_bytes) dTot_grootte_Mb * 1002)'99099')
       ftotal_bytes 空闲空间(M)
       fmax_bytes 块(M)
 FROM     
    (SELECT tablespace_name
            round(SUM(bytes)(1024*1024)2) total_bytes
            round(MAX(bytes)(1024*1024)2) max_bytes
      FROM sysdba_free_space
     GROUP BY tablespace_name) f
    (SELECT ddtablespace_name round(SUM(ddbytes)(1024*1024)2) Tot_grootte_Mb
      FROM   sysdba_data_files dd
      GROUP BY ddtablespace_name) d
WHERE dtablespace_name ftablespace_name   
ORDER BY 4 DESC
 
查法扩展段
A  脚说明：
ORACLE段表段索引法扩展时取决表空间中剩余空间少取剩余空间中块否够表索引NEXT值时表空间剩余G空闲空间时ORACLE提示某表索引法扩展点时说明空间碎片太脚找出法扩展段信息
B脚原文：
SELECT segment_name
             segment_type
             owner
             atablespace_name tablespacename
             initial_extent1024 inital_extent(K)
             next_extent1024 next_extent(K)
             pct_increase
             bbytes1024 tablespace max free space(K)
             bsum_bytes1024 tablespace total free space(K)
  FROM dba_segments a
       (SELECT tablespace_nameMAX(bytes) bytesSUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b
 WHERE atablespace_namebtablespace_name
   AND next_extent>bbytes
 ORDER BY 431
 
查段(表段索引段)空间
A  脚说明：
时想知道表索引占少M空间脚满足求<>中容换
B脚原文：
SELECT owner
              segment_name
              SUM(bytes)10241024
    FROM dba_segments
   WHERE owner
        And segment_name
  GROUP BY ownersegment_name
  ORDER BY 3 DESC
 
查数库中表锁
A  脚说明：
 方面语句样式式样认实信需说锁DBA定涉容相知道某表session锁定脚
B脚原文：
  SELECT AOWNER  
               AOBJECT_NAME  
               BXIDUSN  
              BXIDSLOT  
              BXIDSQN  
              BSESSION_ID  
              BORACLE_USERNAME  
              BOS_USER_NAME  
              BPROCESS  
              BLOCKED_MODE  
              CMACHINE  
              CSTATUS  
              CSERVER  
              CSID  
              CSERIAL#  
              CPROGRAM 
    FROM ALL_OBJECTS A  
         VLOCKED_OBJECT B  
         SYSGV_SESSION C
   WHERE ( AOBJECT_ID BOBJECT_ID )
     AND (BPROCESS CPROCESS )
     AND 
   ORDER BY 12  
 
处理存储程锁
A  脚说明：
   实际程中重新编译某存储程理总处等状态会报法锁定象时脚找锁定程sid需注意查vaccess视图慢需耐心
B脚原文：
SELECT * FROM VACCESS WHERE owner