Oracle管理及常用基础脚本Word格式.docx
《Oracle管理及常用基础脚本Word格式.docx》由会员分享，可在线阅读，更多相关《Oracle管理及常用基础脚本Word格式.docx（57页珍藏版）》请在冰豆网上搜索。
selectnamespace,pins,reloads,invalidations
9.回滚段的争用情况
selectname,waits,gets,waits/gets"
Ratio"
fromv$rollstata,v$rollnameb
wherea.usn=b.usn;
10.监控表空间的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
orderbydf.tablespace_name;
11.监控文件系统的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#;
12.在某个用户下找所有的索引
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;
13.监控SGA中字典缓冲区的命中率
selectparameter,gets,Getmisses,getmisses/（gets+getmisses）*100"
missratio"
（1-（sum（getmisses）/（sum（gets）+sum（getmisses））））*100"
Hitratio"
fromv$rowcache
wheregets+getmisses<
>
0
groupbyparameter,gets,getmisses;
14.监控SGA中共享缓存区的命中率，应该小于1%
TotalPins"
TotalReloads"
sum（reloads）/sum（pins）*100libcache
selectsum（pinhits-reloads）/sum（pins）"
hitradio"
sum（reloads）/sum（pins）"
reloadpercent"
15.显示所有数据库对象的类别和大小
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;
16.监控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'
）;
17.监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_size
SELECTname,valueFROMv$sysstatWHEREnameIN（'
sorts（memory）'
sorts（disk）'
18.监控字典缓冲区
select（sum（pins-reloads））/sum（pins）"
libcache"
select（sum（gets-getmisses-usage-fixed））/sum（gets）"
rowcache"
fromv$rowcache;
selectsum（pins）"
executions"
cachemisseswhileexecuting"
后者除以前者,此比率小于1%,接近0%为好
selectsum（gets）"
dictionarygets"
sum（getmisses）"
dictionarycachegetmisses"
fromv$rowcache
19.找ORACLE字符集
select*fromsys.props$wherename='
NLS_CHARACTERSET'
20.监控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时，参数需加大
21.碎片程度
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）;
22.表、索引的存储情况检查
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;
23、找使用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;
2空间管理
1.察看数据库的大小，和空间使用情况
SQL>
coltablespaceformata20--在显示中指明列的输出格式
selectb.file_id --文件ID,
b.tablespace_name --表空间,
b.file_name --物理文件名,
b.bytes --总字节数,
（b.bytes-sum（nvl（a.bytes,0））） --已使用,
sum（nvl（a.bytes,0）） --剩余,
sum（nvl（a.bytes,0））/（b.bytes）*100 --剩余百分比
fromdba_free_spacea,dba_data_filesb
wherea.file_id=b.file_id
groupbyb.tablespace_name,b.file_name,b.file_id,b.bytes
orderbyb.tablespace_name
/
dba_free_space--表空间剩余空间状况
dba_data_files--数据文件空间占用情况
2.查看现有回滚段及其状态
selectsegment_name,owner,tablespace_name,segment_id,file_id,statusfromdba_rollback_segs;
3.表空间大小
selecttablespace_name,count（*）,sum（blocks）,sum（bytes）/1024/1024
fromdba_data_files
groupbytablespace_name;
4.表空间使用情况
selectdf.tablespace_name"
表空间名"
totalspace"
总空间M"
freespace"
剩余空间M"
round（（1-freespace/totalspace）*100,2）"
使用率%"
from（selecttablespace_name,round（sum（bytes）/1024/1024）totalspace
groupbytablespace_name）df,
（selecttablespace_name,round（sum（bytes）/1024/1024）freespace
fromdba_free_space
groupbytablespace_name）fs
wheredf.tablespace_name=fs.tablespace_name;
5.删除表空间
selectt.name,d.namefromv$tablespacet,v$datafiledwheret.name='
DATA_HOST_A'
andt.ts#=d.ts#;
altertablespaceDATA_HOST_Aoffline;
droptablespaceDATA_HOST_Aincludingcontents;
6.查看数据文件的位置
selecttablespace_name,file_id,bytes/1024/1024,file_namefromdba_data_filesorderbyfile_id;
7.为这个表空间增加一个数据文件
altertablespace表空间名adddatafile'
/u1/oradata/userdata_002.ora'
size50m;
--Unix
c:
\oradata\userdata_002.ora'
--WindowsNT
8.重新调整数据文件的大小
alterdatabasedatafile'
/u1/oradata/userdata_001.ora'
resize50M;
--Unix
9.临时表空间和排序表空间的使用状态
selectsubstr（vses.username,1,12）"
ORA_USER"
substr（osuser,1,12）"
OS_USER"
substR（sql_text,1,50）"
SQL_STMT"
from
V$sqlareavsql,V$sessionvses,V$sort_usagevsort
where
vsort.tablespace='
TEMP'
andvsort.sqladdr=vses.sql_addressandvsql.address=vsort.sqladdr
selecttablespace_name,extent_size,total_extents,used_extents,free_extents,max_used_size
fromv$sort_segment;
10.确定盘区和盘区内容及表或者空间的碎片程度
selectsegment_name,extenfromdba_extents--确定盘区的内容
descdba_segments--确定段的内容
--返回了结果表明碎片的严重
selectsegment_name,tablespace_name,extents,segment_typefromdba_segmentswhereextents>
4
--空间碎片严重程度
selecttablespace_name,count（tablespace_name）fromdba_free_spacegroupbytablespace_name
havingcount（tablespace_name）>
10
3基本知识
1.为一个事务指定一个回滚段
settransactionuserollbacksegmentrollback_segment_name--指定回滚段
2.如何创建和使用光标
--光标的使用
declare@namechar（30）
declare@homebasechar（40）
declare@stylechar（20）
declare@arttist_idint
createartist_cursorcursor
forselect*fromdim_age
openartist_cursor
fetchartist_cursorinto@arttist_id,@homebase,@style
while（@@sqlstatus=0）
begin
print@homebase
print@style
print@arttist_id
end
closeartist_cursor
deallocatecursorartist_cursor
go
3.如何导出和导入数据
--数据导出导入
expdss/dss@oralcequery=\"
whereday_id>
12\"
tables=（customer）file=d:
\sample.dmplog=
rows=n
full=ytables=（）owner=--三种导出方式
impuserid/pwd@oracle_sidfromuser=（dss,dwh）touser=（dss,dwh）dwhfile=sample.dmp
--分区表的到导出user1.table_name:
px分区px
expuser_id/pwd@oracle_sidtables=（user1.table_name:
px）file=sample.dmp
4.如何使数据库运行于归档模式
1.打开ini.ora文件
2.修改文件内容
log_archive_start=true
log_archive_dest_1="
location=D:
\Oracle\oradata\ORACLE\archive"
log_archive_format=%%ORACLE_SID%%T%TS%S.ARC
3.进入服务管理器输入
shutdown
startupmount
alterdatabasearchivelog
alterdatabaseopen
此时数据库运行与归档模式下可以查询输入
archiveloglist
5.如何做数据库的热备份和恢复
--要对数据库做热备份需要数据库运行在归档模式下:
--首先使表空间处于备份状态
--拷贝数据文件
--使表空间回复到正常状态
--进入sqlplus输入：
altertablespaceusersbeginbackup;
$copyd:
\oracle_home\usr.orad:
\backup\user.dbf;
altertablespaceusersendbackup;
altersystemcheckpoint;
--恢复数据库需要做如下的步骤：
--将映像备份文件拷贝到各个表空间对应的正确的位置
svrmgrl
connectinternal
startupmount
alterdatabaseopen
recoverdatabase
6.如何查看所有的表和创建结构相同的表
1、查看当前所有对象
select*fromtab;
2、建一个和a表结构一样的空表
createtablebasselect*fromawhere1=2;
createtableb（b1,b2,b3）asselecta1,a2,a3fromawhere1=2;
7.如何查看当前日期
selectto_char（sysdate,'
yyyy-mm-dd,hh24:
mi:
ss'
）fromdual;
8.如何在用户间复制数据
copyfr
升级会员 