ORACLE DBA常用SQL语句

ORACLEDBA常用SQL语句查看表空间的名称及大小:SQLselectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_sizefromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;查看表空间物理文件的名称及大小:SQLselecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;查看回滚段名称及大小:SQLselectsegment_name,tablespace_name,r.status,(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id=v.usn(+)orderbysegment_name;如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句:SQLselectd.sql_text,a.namefromv$rollnamea,v$transactionb,v$sessionc,v$sqltextdwherea.usn=b.xidusnandb.addr=c.taddrandc.sql_address=d.addressandc.sql_hash_value=d.hash_valueanda.usn=1;(备注：你要看哪个，就把usn=?写成几就行了)查看控制文件:SQLselect*fromv$controlfile;查看日志文件:SQLcolmemberformata50SQLselect*fromv$logfile;如何查看当前SQL*PLUS用户的sid和serial#:SQLselectsid,serial#,statusfromv$sessionwhereaudsid=userenv('sessionid');如何查看当前数据库的字符集:SQLselectuserenv('language')fromdual;SQLselectuserenv('lang')fromdual;怎么判断当前正在使用何种SQL优化方式:用explainplan产生EXPLAINPLAN，检查PLAN_TABLE中ID=0的POSITION列的值。SQLselectdecode(nvl(position,-1),-1,'RBO',1,'CBO')fromplan_tablewhereid=0;如何查看系统当前最新的SCN号：SQLselectmax(ktuxescnw*power(2,32)+ktuxescnb)fromx$ktuxe;在ORACLE中查找TRACE文件的脚本:SQLselectu_dump.value||'/'||instance.value||'_ora_'||v$process.spid||nvl2(v$process.traceid,'_'||v$process.traceid,null)||'.trc'TraceFilefromv$parameteru_dumpcrossjoinv$parameterinstancecrossjoinv$processjoinv$sessiononv$process.addr=v$session.paddrwhereu_dump.name='user_dump_dest'andinstance.name='instance_name'andv$session.audsid=sys_context('userenv','sessionid');SQLselectd.value||'/ora_'||p.spid||'.trc'trace_file_namefrom(selectp.spidfromsys.v_$mystatm,sys.v_$sessions,sys.v_$processpwherem.statistic#=1ands.sid=m.sidandp.addr=s.paddr)p,(selectvaluefromsys.v_$parameterwherename='user_dump_dest')d;如何查看客户端登陆的IP地址:SQLselectsys_context('userenv','ip_address')fromdual;如何在生产数据库中创建一个追踪客户端IP地址的触发器：SQLcreateorreplacetriggeron_logon_triggerafterlogonondatabasebegindbms_application_info.set_client_info(sys_context('userenv','ip_address'));end;查询当前日期:SQLselectto_char(sysdate,'yyyy-mm-dd,hh24:mi:ss')fromdual;查看所有表空间对应的数据文件名：SQLselectdistinctfile_name,tablespace_name,AUTOEXTENSIBLEfromdba_data_files;查看表空间的使用情况:SQLselectsum(bytes)/(1024*1024)asfree_space,tablespace_namefromdba_free_spacegroupbytablespace_name;SQLSELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,(B.BYTES*100)/A.BYTES%USED,(C.BYTES*100)/A.BYTES%FREEFROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREECWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;SQLcolumntablespace_nameformata18;SQLcolumnSum_Mformata12;SQLcolumnUsed_Mformata12;SQLcolumnFree_Mformata12;columnpto_Mformat9.99;SQLselects.tablespace_name,ceil(sum(s.bytes/1024/1024))||'M'Sum_M,ceil(sum(s.UsedSpace/1024/1024))||'M'Used_M,ceil(sum(s.FreeSpace/1024/1024))||'M'Free_M,sum(s.UsedSpace)/sum(s.bytes)PTUSEDfrom(selectb.file_id,b.tablespace_name,b.bytes,(b.bytes-sum(nvl(a.bytes,0)))UsedSpace,sum(nvl(a.bytes,0))FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes))*100FreePercentRatiofromsys.dba_free_spacea,sys.dba_data_filesbwherea.file_id(+)=b.file_idgroupbyb.file_id,b.tablespace_name,b.bytesorderbyb.tablespace_name)sgroupbys.tablespace_nameorderbysum(s.FreeSpace)/sum(s.bytes)desc;查看数据文件的hwm（可以resize的最小空间）和文件头大小:SQLSELECTv1.file_name,v1.file_id,num1totle_space,num3free_space,num1-num3USED_SPACE(HWM),nvl(num2,0)data_space,num1-num3-nvl(num2,0)file_headFROM(SELECTfile_name,file_id,SUM(bytes)num1FROMDba_Data_FilesGROUPBYfile_name,file_id)v1,(SELECTfile_id,SUM(bytes)num2FROMdba_extentsGROUPBYfile_id)v2,(SELECTfile_id,SUM(BYTES)num3FROMDBA_FREE_SPACEGROUPBYfile_id)v3WHEREv1.file_id=v2.file_id(+)ANDv1.file_id=v3.file_id(+);查看数据文件大小及头大小:SQLSELECTv1.file_name,v1.file_id,num1totle_space,num3free_space,num1-num3Used_space,nvl(num2,0)data_space,num1-num3-nvl(num2,0)file_headFROM(SELECTfile_name,file_id,SUM(bytes)num1FROMDba_Data_FilesGROUPBYfile_name,file_id)v1,(SELECTfile_id,SUM(bytes)num2FROMdba_extentsGROUPBYfile_id)v2,(SELECTfile_id,SUM(BYTES)num3FROMDBA_FREE_SPACEGROUPBYfile_id)v3WHEREv1.file_id=v2.file_id(+)ANDv1.file_id=v3.file_id(+);(运行以上查询，我们可以如下信息：Totle_pace:该数据文件的总大小，字节为单位Free_space:该数据文件的剩于大小，字节为单位Used_space:该数据文件的已用空间，字节为单位Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位File_Head:该数据文件头部占用空间，字节为单位)数据库各个表空间增长情况的检查：SQLselectA.tablespace_name,(1-(A.total)/B.total)*100used_percentFrom(selecttablespace_name,sum(bytes)totalfromdba_free_spacegroupbytablespace_name)A,(selecttablespace_name,sum(bytes)totalfromdba_data_filesgroupbytablespace_name)BwhereA.tablespace_name=B.tablespace_name;SQLSELECTUPPER(F.TABLESPACE_NAME)表空间名,D.TOT_GROOTTE_MB表空间大小(M),D.TOT_GROOTTE_MB-F.TOTAL_BYTES已使用空间(M),TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')使用比,F.TOTAL_BYTES空闲空间(M),F.MAX_BYTES最大块(M)FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_