ora10g SQL.docx
- 文档编号:3786636
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:64
- 大小:43KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ora10g SQL