资源描述：
《oracle常用经典SQL查询》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、oracle常用经典SQL查询上一篇/下一篇 2009-05-2421:34:32查看(124)/评论(1)/评分(0/0)oracle常用经典SQL查询常用SQL查询： 1、查看表空间的名称及大小 selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_sizefromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_n
2、ame; 2、查看表空间物理文件的名称及大小 selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name; 3、查看回滚段名称及大小 selectsegment_name,tablespace_name,r.status,(initial_extent/1024)InitialExtent,(next_extent/1024)NextExt
3、ent,max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id=v.usn(+)orderbysegment_name; 4、查看控制文件 selectnamefromv$controlfile; 5、查看日志文件 selectmemberfromv$logfile; 6、查看表空间的使用情况 selectsum(bytes)/(1024*1024)asfree_space,tablespace_namefr
4、omdba_free_spacegroupbytablespace_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_FREECWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABL
5、ESPACE_NAME=C.TABLESPACE_NAME; 7、查看数据库库对象 selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowner,object_type,status; 8、查看数据库的版本 SelectversionFROMProduct_component_versionWhereSUBSTR(PRODUCT,1,6)='Oracle'; 9、查看数据库的创建日期和归档方式 SelectCreated,Log
6、_Mode,Log_ModeFromV$Database; 10、捕捉运行很久的SQL columnusernameformata12columnopnameformata16columnprogressformata8 selectusername,sid,opname,round(sofar*100/totalwork,0)
7、
8、'%'asprogress,time_remaining,sql_textfromv$session_longops,v$sqlwheretime_remaining<>0andsql
9、_address=addressandsql_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,B
10、UFFER_POOL,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,last_analyzedFROMdba_tab_partitions--WHEREtable_name=:tnameANDtable_owner=:townerORDERBYpartition_posit
