资源描述：
《-【优秀资料】oracle常用经典sql查询》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、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_name;2、查看表空间物理文件的名称及大小selecttablespace_name,file_id,file_name,round(bytes
2、/(1()24*1024),0)total_spacefromdba_data_filesorderbytablcspacc_namc;3、查看回滚段名称及大小selectsegment_name,tablespace_name,匚status,(initial_extent/l024)InitialExtent,(next_extent/l024)NextExtent,max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id
3、=v.usn(+)orderbysegment_name;4、査看控制文件selectnamefromv$controlfile;5、查看日志文件selectmemberfromv$logfile;6、查看表空间的使用情况selectsum(bytes)/(1024*1024)asfree_space,tablespace_namefromdba_free_spacegroupbytablespace_name;SELECTA.TABLESPACE_NAME,ABYTESTOTAL,BBYTESUSED,C.BYTESFREE,
4、(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.TABLESPACENAME=C.TABLESPACENAME;7、查看数据库库对象selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowne
5、r,object_type,status;8、查看数据库的版本SelectversionFROMProduct_component_versionWhereSUBSTR(PRODUCT,16)-0racle：9.查看数据库的创建日期和归档方式SelectCreated,Log_Mode,Log_ModeFromV$Database;10、捕捉运行很久的SQLcolumnusernameformatal2columnopnameformatal6columnprogressformata8selectusername,sid,op
6、name,round(sofar*100/totalwork,0)IIasprogress,time_remaining,sql_textfromv$session」ongops,v$sqlwheretime_remaining<>0andsql_address=addressandsql_hash_value=hash_value/11O查看数据表的参数信息SELECTpartition_namc,high_valuc,high_valuc_lcngth,tablcspacc_namc,pct_free,pct_used,in
7、i_trans,max_trans,initial_extent5next_extent,niin_extent,max_extent,pct_increase5FREELISTS,freelist_groups,LOGGING,BUFFER_POOL,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,last_analyzedFROMdba_tab_partitions—WHEREtable_name=:tnameANDtable_
8、owner=:townerORDERBYpartition_position12•查看还没提交的事务select*fromv$locked_object;select*fromv$transaction;13o查找object为哪些进程所用selectp.spi
