资源描述：
《oracle维护常用sql语句》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、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/(102
2、4*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
3、=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.BYTES
4、USED,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_
5、objectsgroupbyowner,object_type,status; 8、查看数据库的版本 SelectversionFROMProduct_component_version WhereSUBSTR(PRODUCT,1,6)='Oracle';9、查看数据库的创建日期和归档方式 SelectCreated,Log_Mode,Log_ModeFromV$Database; 10、捕捉运行很久的SQL columnusernameformata12 columnopnameformata16 columnprogress
6、formata8 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
7、_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_
8、partitions --WHEREtable_name=:tnameANDt
