Oracle维护常用SQL语句.docx
《Oracle维护常用SQL语句.docx》由会员分享，可在线阅读，更多相关《Oracle维护常用SQL语句.docx（12页珍藏版）》请在冰豆网上搜索。
Oracle维护常用SQL语句
Oracle维护常用SQL语句
0、ORA-28000:
theaccountislocked
ALTERUSERusernameACCOUNTUNLOCK;
1、查看表空间的名称及大小
selectt.tablespace_name,round（sum（bytes/（1024*1024））,0）ts_size
fromdba_tablespacest,dba_data_filesd
wheret.tablespace_name=d.tablespace_name
groupbyt.tablespace_name;
2、查看表空间物理文件的名称及大小
selecttablespace_name,file_id,file_name,
round（bytes/（1024*1024）,0）total_space
fromdba_data_files
orderbytablespace_name;
3、查看回滚段名称及大小
selectsegment_name,tablespace_name,r.status,
（initial_extent/1024）InitialExtent,（next_extent/1024）NextExtent,
max_extents,v.curextCurExtent
Fromdba_rollback_segsr,v$rollstatv
Wherer.segment_id=v.usn（+）
orderbysegment_name;
4、查看控制文件
selectnamefromv$controlfile;
5、查看日志文件
selectmemberfromv$logfile;
6、查看表空间的使用情况
selectsum（bytes）/（1024*1024）asfree_space,tablespace_name
fromdba_free_space
groupbytablespace_name;
SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,
（B.BYTES*100）/A.BYTES"%USED",（C.BYTES*100）/A.BYTES"%FREE"
FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREEC
WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;
7、查看数据库库对象
selectowner,object_type,status,count（*）count#fromall_objectsgroupbyowner,object_type,status;
8、查看数据库的版本
SelectversionFROMProduct_component_version
WhereSUBSTR（PRODUCT,1,6）=''Oracle'';
9、查看数据库的创建日期和归档方式
SelectCreated,Log_Mode,Log_ModeFromV$Database;
10、捕捉运行很久的SQL
columnusernameformata12
columnopnameformata16
columnprogressformata8
selectusername,sid,opname,
round（sofar*100/totalwork,0） ''%''asprogress,
time_remaining,sql_text
fromv$session_longops,v$sql
wheretime_remaining<>0
andsql_address=address
andsql_hash_value=hash_value
/
11、查看数据表的参数信息
SELECTpartition_name,high_value,high_value_length,tablespace_name,
pct_free,pct_used,ini_trans,max_trans,initial_extent,
next_extent,min_extent,max_extent,pct_increase,FREELISTS,
freelist_groups,LOGGING,BUFFER_POOL,num_rows,blocks,
empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,
last_analyzed
FROMdba_tab_partitions
--WHEREtable_name=:
tnameANDtable_owner=:
towner
ORDERBYpartition_position
12、查看还没提交的事务
select*fromv$locked_object;
select*fromv$transaction;
13、查找object为哪些进程所用
select
p.spid,
s.sid,
s.serial#serial_num,
s.usernameuser_name,
a.typeobject_type,
s.osuseros_user_name,
a.owner,
a.objectobject_name,
decode（sign（48-command）,
1,
to_char（command）,''ActionCode#'' to_char（command））action,
p.programoracle_process,
s.terminalterminal,
s.programprogram,
s.statussession_status
fromv$sessions,v$accessa,v$processp
wheres.paddr=p.addrand
s.type=''USER''and
a.sid=s.sidand
a.object=''SUBSCRIBER_ATTR''
orderbys.username,s.osuser
14、回滚段查看
selectrownum,sys.dba_rollback_segs.segment_nameName,v$rollstat.extents
Extents,v$rollstat.rssizeSize_in_Bytes,v$rollstat.xactsXActs,
v$rollstat.getsGets,v$rollstat.waitsWaits,v$rollstat.writesWrites,
sys.dba_rollback_segs.statusstatusfromv$rollstat,sys.dba_rollback_segs,
v$rollnamewherev$rollname.name（+）=sys.dba_rollback_segs.segment_nameand
v$rollstat.usn（+）=v$rollname.usnorderbyrownum
15、耗资源的进程（topsession）
selects.schemanameschema_name,decode（sign（48-command）,1,
to_char（command）,''ActionCode#'' to_char（command））action,status
session_status,s.osuseros_user_name,s.sid,p.spid,s.serial#serial_num,
nvl（s.username,''[Oracleprocess]''）user_name,s.terminalterminal,
s.programprogram,st.valuecriteria_valuefromv$sesstatst,v$sessions,v$processp
wherest.sid=s.sidandst.statistic#=to_number（''38''）and（''ALL''=''ALL''
ors.status=''ALL''）andp.addr=s.paddrorderbyst.valuedesc,p.spidasc,s.usernameasc,s.osuserasc
16、查看锁（lock）情况
select/*+RULE*/ls.osuseros_user_name,ls.usernameuser_name,
decode（ls.type,''RW'',''Rowwaitenqueuelock'',''TM'',''DMLenqueuelock'',''TX'',
''Transactionenqueuelock'',''UL'',''Usersuppliedlock''）lock_type,
o.object_nameobject,decode（ls.lmode,1,null,2,''RowShare'',3,
''RowExclusive'',4,''Share'',5,''ShareRowExclusive'',6,''Exclusive'',null）
lock_mode,o.owner,ls.sid,ls.serial#serial_num,ls.id1,l
升级会员 