资源描述：
《oracle维护常用sql语句》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
Oracle维护常用SQL语句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
