ora10g SQL.docx
《ora10g SQL.docx》由会员分享，可在线阅读，更多相关《ora10g SQL.docx（64页珍藏版）》请在冰豆网上搜索。
ora10gSQL
/*******************************************************/
/*文件名：
ora_10g.sql*/
/*作者：
赵元杰E_Mail：
zyj5681@*/
/*修改：
2008.3.25*/
/*功能：
列出ORACLE性能调整所需的必要信息*/
/*使用：
*/
/*1）：
将本文档另存为“纯文本”文件*/
/*2）：
用SQL*Plus登录到SYSDBA帐户*/
/*3）：
SQL>startora_10g.txt*/
/*4）：
分析输出的结果（C盘上的ora_10g.lst文件）*/
/*******************************************************/
PROMPT*-------------------------*
PROMPT*---应用系统调整分析脚本---*
PROMPT*-------------------------*
PROMPT**
ACCEPTuser_namePROMPT'请输入需要分析用户名字（大写）:
'
PROMPT*-------------------------*
PROMPT*正在产生脚本，请等待。
。
。
*
PROMPT*-------------------------*
PROMPT
setpagesize100
setlinesize160
--截断查询结果最后的空格settrimspoolon
settrimspoolon
settrimouton
--显示执行脚本的命令setechoon
setechoon
settimingon
altersessionsetnls_date_format='yyyy.mm.dd';
--
spoolC:
\10g_info
--**数据库实例基本情况
--**ORACLE系统实例名字:
selectNAME,CREATED,LOG_MODE,
to_char（sysdate,'yyyy/mm/dd:
HH24:
MI'）"SYS_DATE"
fromv$database;
--**ORACLE实例安装的产品列表:
COLPARAMETERfora40
COLVALUEfora30
select*fromv$option;
--**Oracle及工具Tools版本信息:
colBANNERfora60
selectBANNERfromv$version;
--**Oracle实例是否归档:
selectdbid,name,log_modefromv$database;
--**ORACLE系统用户情况:
colUSERNAMEfora20
colDEFAULT_TABLESPACEfora20
colTEMPORARY_TABLESPACEfora20
colPROFILEfora20
selectUSERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,
PROFILE,CREATEDFROMdba_users;
--**ORACLE系统资源文件情况:
colRESOURCE_NAMEfora28
colLIMITfora20
SELECT*fromdba_profilesorderbyPROFILE;
--**ORACLE系统字符集情况
COLVALUE$fora40
SELECTname,value$fromsys.props$;
--**ORACLE数据库连接的信息
colownerfora20
coldb_linkfora38
colusernamefora20
SELECTowner,db_link,usernamefromdba_db_links;
--**ORACLE数据库用户权限报告:
colUsernamefora20
colOwnerfora14
colObjfora20
colObj_Privfora10
colSys_Privfora20
colGranted_Rolefora22
selectusername"Username",
owner,
table_name"Obj",
privilege"Obj_Priv",
''"Sys_Priv",
''"Granted_Role",
1"dummy"
fromdba_usersu,dba_tab_privst
whereu.username=t.grantee
andu.username=upper（'&&user_name'）
union
selectusername,
'',
'',
'',
privilege,
'',
2"dummy"
fromdba_usersu,dba_sys_privss
whereu.username=s.grantee
andu.username=upper（'&&user_name'）
union
selectusername,
'',
'',
'',
'',
granted_role,
3"dummy"
fromdba_usersu,dba_role_privsr
whereu.username=r.grantee
andu.username=upper（'&&user_name'）
orderby1,7;
--**ORACLE实例后台进程信息
colDESCRIPTIONfora40
colnamefora30
SELECT*fromv$bgprocess;
--**ORACLE系统参数情况
Showparameter;
--**ORACLE系统参数情况2（9i/10g视图）
COLSIDfora30
COLNAMEfora30
COLVALUEfora40
SELECTsid,name,valueFROMV$SPPARAMETER;
--**Oracle系统数据文件及表空间情况
--**ORACLE系统数据文件情况:
COLFILE_NAMEFORA50
COLTABLESPACE_NAMEFORA16
COLBYTESFOR999,999,999,999
COLMAXBYTESFOR999,999,999,999
SELECTFILE_NAME,
TABLESPACE_NAME,
BYTES,
AUTOEXTENSIBLE"AUTO",
MAXBYTES
FROMDBA_DATA_FILESORDERBYTABLESPACE_NAME;
--**表空间的自由空间情况:
colTABLESPACE_NAMEfora20
colFREE_MBfora14
colMAX_BYTESfor99,999,999,999
colAVG_BYTESfor99,999,999,999
colCOUNTfor999,999,999
colTABS_TYPEfora12
SELECTf.tablespace_name"TABLESPACE_NAME",
Decode（d.EXTENT_MANAGEMENT,'DICTIONARY','DICT','LOCAL'）"TABS_TYPE",
TRUNC（SUM（f.bytes/1024000）,2）||'MB'"FREE_MB",
MAX（f.bytes）"MAX_BYTES",
AVG（f.bytes）"AVG_BYTES",COUNT（f.tablespace_name）"COUNT"
FROMdba_free_spacef,dba_tablespacesd
Wheref.tablespace_name=d.tablespace_name
GROUPBYf.tablespace_name,d.EXTENT_MANAGEMENT;
--**是否存在空间无法扩展的情况:
Col"段名"fora20
Col"段类型"fora12
Col"段所有者"fora12
Col"表空间名"fora18
col"段所有者"fora18
col"初始扩展"for999,999,999
col"下次"for999,999,999
col"增涨"for999,999
col"最大字节"for999,999,999,999
Selectsegment_name"段名",segment_type"段类型",
Owner"段所有者",a.tablespace_name"表空间名",
Initial_extent"初始扩展",next_extent"下次",pct_increase"增涨",
b.bytes"最大字节"
Fromdba_segmentsa,
（selecttablespace_name,max（bytes）bytesfromdba_free_space
groupbytablespace_name）b
wherea.tablespace_name=b.tablespace_nameandnext_extent>b.bytes;
--**检查对象扩展是否接近MAXEXTENTS:
col"已扩展"for999,999,999
col"最大扩展"for999,999,999,999
SELECTowner"段所有者",
segment_name"段名",
segment_type"段类型",
extents"已扩展",
max_extents"最大扩展",
tablespace_name"表空间名"
FROMdba_segments
WHEREextents>=（max_extents-5）;
--**检查自由表空间接近10%的情况:
col"表空间"fora18
col"文件名"fora50
col"总空间（Mb）"for999,999,999,999
col"自由空间（Mb）"for999,999,999,999
col"自由空间%"for9999.99
SELECTa.tablespace_name"表空间",
a.file_name"文件名",
a.avail"总空间（Mb）",
nvl（b.free,0）"自由空间（Mb）",
nvl（round（（（free/avail）*100）,2）,0）"自由空间%"
from（selecttablespace_name,substr（file_name,1,45）file_name,
file_id,round（sum（bytes/（1024*1024））,3）avail
fromsys.dba_data_files
groupbytablespace_name,substr（file_name,1,45）,
file_id）a,
（selecttablespace_name,file_id,
round（sum（bytes/（1024*1024））,3）free
fromsys.dba_free_space
groupbytablespace_name,file_id）b
wherea.file_id=b.file_id（+）
orderby1,2;
--**检查是否存在需要合并的表空间:
col"自由空间"fora16
col最小字节for999,999,999
col最大字节for999,999,999,999
col分布在for999,999,999
SELECTf.tablespace_name"表空间",
TRUNC（SUM（f.bytes/1024000）,2）||'MB'"自由空间",
MIN（f.bytes）"最小字节",MAX（f.bytes）"最大字节",
AVG（f.bytes）"平均字节",COUNT（f.tablespace_name）"分布在"
FROMdba_free_spacef,dba_tablespacesd
Wheref.tablespace_name=d.tablespace_name
HavingCOUNT（f.tablespace_name）>1
GROUPBYf.tablespace_name,d.EXTENT_MANAGEMENT;
--**查询表空间的大小,分布等信息:
COLFREE_BLKfor999,999,999
COLFREE_Mfor999,999,999
COLnum_chunksfor999,999,999
SELECTtablespace_name,sum（blocks）free_blk,trunc（sum（bytes）/
（1024*1024））free_m
max（bytes）/（1024）asbig_chunk_k,count（*）num_chunks
FROMdba_free_space
GROUPBYtablespace_name;
--**查询表空间的最大连续块,使用百分比:
COLmax_blocksfor999,999,999
COLcount_blocksfor999,999,999
COLsum_free_blockfor999,999,999
SELECTtablespace_name,max_blocks,count_blocks,sum_free_blocks
to_char（100*sum_free_blocks/sum_alloc_blocks,'99.99'）||'%'ASpct_free
FROM（SELECTtablespace_name
sum（blocks）ASsum_alloc_blocks
FROMdba_data_files
GROUPBYtablespace_name
）
（SELECTtablespace_nameASfs_ts_name
max（blocks）ASmax_blocks
count（blocks）AScount_blocks
sum（blocks）ASsum_free_blocks
FROMdba_free_space
GROUPBYtablespace_name）
WHEREtablespace_name=fs_ts_name;
--**检查表空间碎片1MB以上:
SELECTtablespace_name"表空间",
count（*）"分布在",sum（bytes）"自由空间（Mb）"
fromdba_free_space
wherebytes>1000000groupbytablespace_name;
--**表空间碎片情况信息:
columntablespace_nameformata15heading'TablespaceName'
columnfile_nameformata50heading'DataFileName'
columnfile_idformat9,999heading'File|ID'
columnblock_idformat99,999heading'Block|ID'
columnblocksformat999,999heading'Blocks'
columnbytesformat999,999,999heading'Bytes'
/*结果过多
breakonfile_nameskip1
computesumofbytesonfile_name
selectdf.file_name,
fs.tablespace_name,
fs.file_id,
fs.block_id,
fs.blocks,
fs.bytes
fromsys.dba_free_spacefs,sys.dba_data_filesdf
wheredf.file_id=fs.file_id
orderbyfs.tablespace_name,fs.file_id,fs.block_id;
*/
--**表空间有效空间信息:
COLUMNtablespace_nameFORMATa25
COLUMNallocation_typeFORMATA4TRUHEADINGALLO
COLUMNcontentsFORMATA4TRUHEADINGMGMT
COLUMNTot_SizeFORMAT999,999HEADING"TOTAL（M）"
COLUMNTot_FreeFORMAT999,999HEADING"FREE（M）"
COLUMNPct_FreeFORMAT999HEADING"FREE%"
COLUMNFragmentsFORMAT99,999HEADING"FRAGMTS"
COLUMNLarge_ExtFORMAT999,999HEADING"LARG（M）"
setfeedbackoffpages999trimson
SELECTa.tablespace_nametablespace_name,SUM（a.tots）/1048576Tot_Size,
SUM（a.sumb）/1048576Tot_Free,
SUM（a.sumb）*100/sum（a.tots）Pct_Free,
SUM（a.largest）/1048576Large_Ext,SUM（a.chunks）Fragments,
b.contents,b.allocation_type
FROM（SELECttablespace_name,0tots,SUM（bytes）sumb,
MAX（bytes）largest,COUNT（*）chunks
FROMdba_free_spacea
GROUPBYtablespace_name
UNION
SELECTtablespace_name,SUM（bytes）tots,0,0,0
FROMdba_data_files
GROUPBYtablespace_name
UNION
SELECTtablespace_name,SUM（bytes）tots,0,0,0
FROMdba_temp_files
GROUPBYtablespace_name）a,dba_tablespacesb
WHEREb.tablespace_name=a.tablespace_name
GROUPBYa.tablespace_name,b.contents,b.allocation_type
/
--**产生表空间的合并命令:
SELECT'Altertablespace'||TABLESPACE_NAME||'coalesce;'"合并表空间命令"
fromDBA_FREE_SPACE_COALESCED
wherePERCENT_EXTENTS_COALESCED<100
orPERCENT_BLOCKS_COALESCED<100;
--**查询数据文件的I/O情况信息:
ColumnTableSpace_NameFormatA25Heading"Tablespace"
ColumnNameFormatA30Heading"FileName"
ColumnTotalFormat99,999,999,990Heading"Total"
ColumnPhyrdsFormat99,999,999,990Heading"Physical|Reads"
ColumnPhywrtsFormat99,999,999,990Heading"Physical|Writes"
ColumnPhyblkrdFormat99,999,999,990Heading"Physical|BlockReads"
ColumnPhyblkwrtFormat99,999,999,990Heading"Physical|BlockWrites"
ColumnAvg_Rd_TimeFormat90.99Heading"平均|读时间|每块"
ColumnAvg_Wrt_TimeFormat90.99Heading"平均|写时间|每块"
SelectC.TableSpace_Name,
B.Name,
A.Phyblkrd+A.PhyblkwrtTotal,
A.Phyrds,
A.Phywrts,
A.Phyblkrd,
A.Phyblkwrt,
（（A.ReadTim/Decode（A.Phyrds,0,1,A.Phyblkrd））/100）Avg_Rd_Time,
（（A.WriteTim/Decode（A.PhyWrts,0,1,A.PhyblkWrt））/100）Avg_Wrt_Time
FromV$FileStatA,V$DataFileB,Sys.DBA_Data_FilesC
WhereB.File#=A.File#
AndB.File#=C.File_Id
OrderByTableSpace_Name;
--**查询数据文件I/O统计信息:
grantselectanydictionarytoSYSTEM;
CREATEVIEW
升级会员 