Oracle经典解决之道汇总Word格式文档下载.docx
《Oracle经典解决之道汇总Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《Oracle经典解决之道汇总Word格式文档下载.docx（45页珍藏版）》请在冰豆网上搜索。
selectname,waits,gets,waits/getsratiofromv$rollstata,v$rollnamebwherea.usn=b.usn;
3.监控表空间的I/O比例：
selectdf.tablespace_namename,df.file_name"
file"
f.phyrdspyr,f.phyblkrdpbr,f.phywrtspyw,
f.phyblkwrtpbw
fromv$filestatf,dba_data_filesdf
wheref.file#=df.file_id
4.监空文件系统的I/O比例：
selectsubstr（a.file#,1,2）"
#"
substr（a.name,1,30）"
name"
a.status,a.bytes,
b.phyrds,b.phywrts
fromv$datafilea,v$filestatb
wherea.file#=b.file#
5.在某个用户下找所有的索引：
selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_name
fromuser_ind_columns,user_indexes
whereuser_ind_columns.index_name=user_indexes.index_name
anduser_ind_columns.table_name=user_indexes.table_name
orderbyuser_indexes.table_type,user_indexes.table_name,
user_indexes.index_name,column_position;
6.监控SGA的命中率
selecta.value+b.value"
logical_reads"
c.value"
phys_reads"
round（100*（（a.value+b.value）-c.value）/（a.value+b.value））"
BUFFERHITRATIO"
fromv$sysstata,v$sysstatb,v$sysstatc
wherea.statistic#=38andb.statistic#=39
andc.statistic#=40;
7.监控SGA中字典缓冲区的命中率
selectparameter,gets,Getmisses,getmisses/（gets+getmisses）*100"
missratio"
（1-（sum（getmisses）/（sum（gets）+sum（getmisses））））*100"
Hitratio"
fromv$rowcache
wheregets+getmisses<
>
groupbyparameter,gets,getmisses;
8.监控SGA中共享缓存区的命中率，应该小于1%
selectsum（pins）"
TotalPins"
sum（reloads）"
TotalReloads"
sum（reloads）/sum（pins）*100libcache
fromv$librarycache;
selectsum（pinhits-reloads）/sum（pins）"
hitradio"
sum（reloads）/sum（pins）"
reloadpercent"
9.显示所有数据库对象的类别和大小
selectcount（name）num_instances,type,sum（source_size）source_size,
sum（parsed_size）parsed_size,sum（code_size）code_size,sum（error_size）error_size,
sum（source_size）+sum（parsed_size）+sum（code_size）+sum（error_size）size_required
fromdba_object_size
groupbytypeorderby2;
10.监控SGA中重做日志缓存区的命中率，应该小于1%
SELECTname,gets,misses,immediate_gets,immediate_misses,
Decode（gets,0,0,misses/gets*100）ratio1,
Decode（immediate_gets+immediate_misses,0,0,
immediate_misses/（immediate_gets+immediate_misses）*100）ratio2
FROMv$latchWHEREnameIN（'
redoallocation'
'
redocopy'
）;
11.监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_size
SELECTname,valueFROMv$sysstatWHEREnameIN（'
sorts（memory）'
sorts（disk）'
12.监控当前数据库谁在运行什么SQL语句
SELECTosuser,username,sql_textfromv$sessiona,v$sqltextb
wherea.sql_address=b.addressorderbyaddress,piece;
13.监控字典缓冲区
SELECT（SUM（PINS-RELOADS））/SUM（PINS）"
LIBCACHE"
FROMV$LIBRARYCACHE;
SELECT（SUM（GETS-GETMISSES-USAGE-FIXED））/SUM（GETS）"
ROWCACHE"
FROMV$ROWCACHE;
SELECTSUM（PINS）"
EXECUTIONS"
SUM（RELOADS）"
CACHEMISSESWHILEEXECUTING"
后者除以前者,此比率小于1%,接近0%为好。
SELECTSUM（GETS）"
DICTIONARYGETS"
SUM（GETMISSES）"
DICTIONARYCACHEGETMISSES"
FROMV$ROWCACHE
14.找ORACLE字符集
select*fromsys.props$wherename='
NLS_CHARACTERSET'
;
15.监控MTS
selectbusy/（busy+idle）"
sharedserversbusy"
fromv$dispatcher;
此值大于0.5时，参数需加大
selectsum（wait）/sum（totalq）"
dispatcherwaits"
fromv$queuewheretype='
dispatcher'
selectcount（*）fromv$dispatcher;
selectservers_highwaterfromv$mts;
servers_highwater接近mts_max_servers时，参数需加大
16.碎片程度
selecttablespace_name,count（tablespace_name）fromdba_free_spacegroupbytablespace_name
havingcount（tablespace_name）>
10;
altertablespacenamecoalesce;
altertablenamedeallocateunused;
createorreplaceviewts_blocks_vas
selecttablespace_name,block_id,bytes,blocks,'
freespace'
segment_namefromdba_free_space
unionall
selecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;
select*fromts_blocks_v;
selecttablespace_name,sum（bytes）,max（bytes）,count（block_id）fromdba_free_space
groupbytablespace_name;
查看碎片程度高的表
SELECTsegment_nametable_name,COUNT（*）extents
FROMdba_segmentsWHEREownerNOTIN（'
SYS'
SYSTEM'
）GROUPBYsegment_name
HAVINGCOUNT（*）=（SELECTMAX（COUNT（*））FROMdba_segmentsGROUPBYsegment_name）;
17.表、索引的存储情况检查
selectsegment_name,sum（bytes）,count（*）ext_quanfromdba_extentswhere
tablespace_name='
&
tablespace_name'
andsegment_type='
TABLE'
groupbytablespace_name,segment_name;
selectsegment_name,count（*）fromdba_extentswheresegment_type='
INDEX'
andowner='
owner'
groupbysegment_name;
18、找使用CPU多的用户session
12是cpuusedbythissession
selecta.sid,spid,status,substr（a.program,1,40）prog,a.terminal,osuser,value/60/100value
fromv$sessiona,v$processb,v$sesstatc
wherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;
20.监控log_buffer的使用情况：
（值最好小于1%，否则增加log_buffer的大小）
selectrbar.name,rbar.value,re.name,re.value,（rbar.value*100）/re.value||'
%'
"
radio"
fromv$sysstatrbar,v$sysstatre
whererbar.name='
redobufferallocationretries'
andre.name='
redoentries'
19、查看运行过的SQL语句：
SELECTSQL_TEXT
FROMV$SQL
常用用户SQL
SQL语句：
表：
select*fromcat;
select*fromtab;
selecttable_namefromuser_tables;
视图：
selecttextfromuser_viewswhereview_name=upper（'
view_name'
索引：
selectindex_name,table_owner,table_name,tablespace_name,statusfromuser_indexesorderbytable_name;
触发器：
selecttrigger_name,trigger_type,table_owner,table_name,statusfromuser_triggers;
快照：
selectowner,name,master,table_name,last_refresh,nextfromuser_snapshotsorderbyowner,next;
同义词：
select*fromsyn;
序列：
select*fromseq;
数据库链路：
select*fromuser_db_links;
约束限制：
selectTABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS
fromuser_constraintsWHERETABLE_name=upper（'
TABLE_Name'
本用户读取其他用户对象的权限：
select*fromuser_tab_privs;
本用户所拥有的系统权限：
select*fromuser_sys_privs;
用户：
select*fromall_usersorderbyuser_id;
表空间剩余自由空间情况：
selecttablespace_name,sum（bytes）总字节数,max（bytes）,count（*）fromdba_free_spacegroupbytablespace_name；
数据字典：
selecttable_namefromdictorderbytable_name;
锁及资源信息：
select*fromv$lock;
不包括DDL锁
数据库字符集：
selectname,value$fromprops$wherename='
inin.ora参数：
selectname,valuefromv$parameterorderbyname;
SQL共享池：
selectsql_textfromv$sqlarea;
数据库：
select*fromv$database
控制文件：
select*fromV$controlfile;
重做日志文件信息：
select*fromV$logfile;
来自控制文件中的日志文件信息：
select*fromV$log;
来自控制文件中的数据文件信息：
select*fromV$datafile;
NLS参数当前值：
select*fromV$nls_parameters;
ORACLE版本信息：
select*fromv$version;
描述后台进程：
select*fromv$bgprocess;
查看版本信息：
select*fromproduct_component_version;
查询表结构
selectsubstr（table_name,1,20）tabname,
substr（column_name,1,20）column_name,
rtrim（data_type）||'
（'
||data_length||'
）'
fromsystem.dba_tab_columns
whereowner='
username'
表空间使用状态
selecta.file_id"
FileNo"
a.tablespace_name"
Tablespace_name"
round（a.bytes/1024/1024,4）"
TotalMB"
round（（a.bytes-sum（nvl（b.bytes,0）））/1024/1024,4）"
UsedMB"
round（sum（nvl（b.bytes,0））/1024/1024,4）"
FreeMB"
round（sum（nvl（b.bytes,0））/a.bytes*100,4）
"
%Free"
fromdba_data_filesa,dba_free_spaceb
wherea.file_id=b.file_id（+）
groupbya.tablespace_name,
a.file_id,a.bytesorderbya.tablespace_name
查询某个模式下面数据不为空的表
declare
CursorcisselectTNAMEfromtab;
vCountNumber;
table_nmVarchar2（100）;
sqvarchar2（300）;
begin
forrincloop
table_nm:
=r.TNAME;
sq:
='
select
count（*）
from'
||table_nm;
executeimmediatesqintovCount;
ifvCount>
0then
dbms_output.put_line（r.tname）;
endif;
endloop;
end;
_
客户端主机信息
SELECT
SYS_CONTEXT（'
USERENV'
'
TERMINAL'
）TERMINAL,
HOST'
）HOST,
OS_USER'
）OS_USER,
IP_ADDRESS'
）IP_ADDRESS
FROMDUAL
安装Oracle后，经常使用的修改表空间的SQL代码
配置：
WindowsNT4.0中文版
5块10.2GBSCSI硬盘
分：
C:
盘、D:
盘、E:
盘、F:
盘、G:
盘
Oracle8.0.4forWindowsNT
NT安装在C:
\WINNT，Oracle安装在C:
\ORANT
目标：
因系统的回滚段太小，现打算生成新的回滚段，
建立大的、新的表空间（数据表空间、索引表空间、回滚表空间、临时表空间、）
建两个数据表空间、两个索引表空间，这样建的目的是根据实际应用，
如：
现有10个应用用户，每个用户是一个独立子系统（如：
商业进销存MIS系统中的财务、收款、库存、人事、总经理等）
尤其大型商场中收款机众多，同时访问进程很多，经常达到50-100个进程同时访问，
这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里（多个用户表空间放在不同的物理磁盘上），
减少了用户之间的I/O竞争、读写数据与写读索引的竞争（用户表空间、索引表空间也分别放在不同的物理磁盘上）
规划：
盘、NT系统，Oracle系统
D:
盘、数据表空间1（3GB、自动扩展）、回滚表空间1（1GB、自动扩展）
E:
盘、数据表空间2（3GB、自动扩展）、回滚表空间2（1GB、自动扩展）
F:
盘、索引表空间1（2GB、自动扩展）、临时表空间1（0.5GB、不自动扩展）
G:
盘、索引表空间2（2GB、自动扩展）、临时表空间2（0.5GB、不自动扩展）
注：
这只是一个简单的规划，实际规划要依系统需求来定，尽量减少I/O竞争
实现：
1、首先查看系统有哪些回滚段及其状态。
SQL>
colownerformata20
colstatusformata10
colsegment_nameformata20
coltablespace_nameformata20
SELECTOWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM（BYTES）/1024/1024M
2
FROMDBA_SEGMENTS
3
WHERESEGMENT_TYPE='
ROLLBACK'
4
GROUPBYOWNER,SEGMENT_NAME,TABLESPACE_NAME
5
/
OWNER
SEGMENT_NAME
TABLESPACE_NAME
M
---------------------------------------------------------------------
SYS
RB1
ROLLBAC
升级会员 