Oracle经典解决之道汇总Word格式文档下载.docx

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