一些有用的SQL，都是Oraclemanage常用的。列在这里做参考，因为太难记了。时时更新。1、监控当前数据库谁在运行什么SQL语句SELECTosuser,username,sql_textfromv$sessiona,v$sqltextbwherea.sql_address=b.addressorderbyaddress,piece;
2、查看碎片程度高的表SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);
3。表空间使用状态
selecta.file_id"FileNo",a.tablespace_name"Tablespace_name",round(a.bytes/1024/1024,4)"TotalMB",round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4)"UsedMB",round(sum(nvl(b.bytes,0))/1024/1024,4)"FreeMB",round(sum(nvl(b.bytes,0))/a.bytes*100,4)"%Free"fromdba_data_filesa,dba_free_spacebwherea.file_id=b.file_id(+)groupbya.tablespace_name,a.file_id,a.bytesorderbya.tablespace_name
4。查看USER
SELECTOSUSER,SERIAL#FROMV$SESSION,V$SQLWHEREV$SESSION.SQL_ADDRESS=V$SQL.ADDRESSANDV$SESSION.STATUS='ACTIVE';
5。监控SGA的命中率selecta.value+b.value"logical_reads",c.value"phys_reads",round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"fromv$sysstata,v$sysstatb,v$sysstatcwherea.statistic#=38andb.statistic#=39andc.statistic#=40;
6。监控SGA中字典缓冲区的命中率selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio",(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"fromv$rowcachewheregets+getmisses<>0groupbyparameter,gets,getmisses;
7。监控SGA中共享缓存区的命中率，应该小于1%selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent"fromv$librarycache;
8。监控内存和硬盘的排序比率监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_sizeSELECTname,valueFROMv$sysstatWHEREnameIN('sorts(memory)','sorts(disk)');
9。哪笔数据正在被人update，而且是被谁正在updateselecta.os_user_name,a.oracle_username,a.object_id,c.object_name,c.object_typefromv$locked_objecta,dba_objectscwherea.object_id=c.object_id资料引用:knowsky/385333.html
10、查看sql语句占用的空间selectaddress,hash_value,disk_reads/executionsdisk_reads,elapsed_time/1000000/executionsas"ELAPSD_TIME(s)",buffer_gets/executionsbgets_per,executions,first_load_timeasfirst_time,sql_textfromv$sqlwhereexecutions>0and(disk_reads>executions*500orbuffer_gets>executions*20000)andcommand_type=3orderby3,4;
11、表的表空间更改近日，发现pl/sql无法登陆oracle，查询之后发现，system表空间竟然满了。原来是我在建立oracle表空间建立时，出现了默认表空间归属错误，使用sys用户登陆该SID的DBA后，使用语句select*fromdba_segmentswhereTABLESPACE_NAME='SYSTEM';查询发现该表字段OWNER中，出现了不属于SYSTEM的用户。然后使用下面语句将不属于SYSTEM的表（字段SEGMENT_NAME）,转移到指定的表空间内。ALTERTABLETABLE_NAMEMOVETABLESPACETABLESPACE_NAME;
12、ALTERINDEXNameALTERINDEX--改变一个索引的定义SynopsisALTERINDEXnameaction[,...]ALTERINDEXnameRENAMETOnew_name这里的action是下列之一：OWNERTOnew_ownerSETTABLESPACEindexspace_nameORA-01502错误成因和解决方法方法2：通过常见所以彻底解决这个问题selectindex_name,index_type,tablespace_name,table_type,statusfromuser_indexeswheretablespace_name='USERS'andstatus='UNUSABLE'altersessionsetskip_unusable_indexes=false;alterindexSMS_AUDITrebuild;首先，先设置“skip_unusable_indexes=false”，也就是不跳过失效索引SQL>altersessionsetskip_unusable_indexes=false;Sessionaltered.SQL>
然后重建这个失效的索引SQL>alterindexidxtrebuild;Indexaltered.SQL>selectindex_name,index_type,tablespace_name,table_type,statususer_indexeswhereindex_name='IDXT';from
INDEX_NAMEINDEX_TYPETABLESPACE_NAMETABLE_TYPESTATUS-------------------------------------------------------------------------------------------------------IDXTNORMALDATA_DYNAMICTABLEVALIDSQL>我们看到重建索引后，索引的状态就正常了。现在插入数据，看看是正常：SQL>insertintotvalues(12);1rowcreated.SQL>commit;Commitcomplete.SQL>看来，重建索引才是解决这类问题的彻底的方法。
SQL>createtablet(anumber);Tablecreated.现在，我们建立一个唯一索引来看看：SQL>createuniqueindexidx_tont(a);Indexcreated.SQL>selectindex_name,index_type,tablespace_name,table_type,statususer_indexeswhereindex_name='T';norowsselectedfrom
SQL>selectindex_name,index_type,tablespace_name,table_type,statususer_indexeswhereindex_name='IDX_T';
from
INDEX_NAMEINDEX_TYPETABLESPACE_NAMETABLE_TYPESTATUS-------------------------------------------------------------------------------------------------------IDX_TNORMALDATA_DYNAMICTABLEVALIDSQL>insertintotvalues(1);1rowcreated.SQL>commit;Commitcomplete.将索引手工修改为unusable状态（模拟发生索引失效的情况）：SQL>alterindexidx_tunusable;Indexaltered.SQL>selectindex_name,index_type,tablespace_name,table_type,statususer_indexeswhereindex_name='IDX_T';from
INDEX_NAMEINDEX_TYPETABLESPACE_NAMETABLE_TYPESTATUS-------------------------------------------------------------------------------------------------------IDX_TNORMALDATA_DYNAMICTABLEUNUSABLE
ORA-28000:theaccountislocked.要对账户解锁，可在数据库管理员账户(sys/system)中使用“alteruser”命令的accountunlock子句。如下所示：alteruserusernameaccountunlock;账户解锁后，username账户再一次被允许连接。13、计算各表使用空间语句selectsegment_name,sum(bytes)/1024/1024/1024fromuser_segmentsgroupbysegment_name14、存储过程中增加每步执行结果日志信息和异常捕捉createorreplaceproceduremakedataiscn1number;cn2number;
begininsertintot_primary_1select*fromt_primary;cn1:=sql%rowcount;--获取上面语句执行的条数（必须在commit之前赋值）commit;insertintot_log(name,cnt)values('1',cn1);commit;insertintot_primary_2select*fromt_primary_2;cn2:=sql%rowcount;commit;insertintot_log(name,cnt)values('2',cn2);commit;end;15、压缩表所占空间的语句（压缩表可以提高查询速度，但插入和删除速度会大大受影响）压缩一个已经存在但并未压缩的表使用altertable..movecompress使一个已存在但未压缩的表转换为压缩表.SQL>altertabletmp_testmovecompress;同样,也可以使用altertable..movenocompress来解压一个已经压缩的表:SQL>altertabletmp_testmovenocompress;确定表是否被压缩:确定一个表是否使用了压缩,查询user_tables,compression字段表明表是否被压缩.SQL>selecttable_name,compressionfromuser_tableswheretable_namenotlike'BIN%';TABLE_NAMECOMPRESS-------------------------------------CLASSESENABLEDROOMSENABLEDSTUDENTSDISABLEDMAJOR_STATSDISABLED16、插入效率高的语句
INSERT/*+APPEND*/
INTOSALES_HISTORY_COMPSELECT*FROMSALES_HISTORY;
17、杀死锁/或杀指定进程通过下面语句查询出死锁的sessionselect*
fromv$sessiont1,v$locked_objectt2wheret1.sid=t2.SESSION_ID;查看当前执行的进程：select*fromv$access查看想要杀死的进程的SID和serial#字段：select*fromv$sessionwheresidin(刚才你查出来的sid）;杀session：altersystemkillsession'sid,serial#';需要dba权限查看session状态现在应该是killedselectstatusfromv$sessionwheresidin(刚才你查出来的sid);系统级别上的杀session：查找到你想要杀的session对应的sidselectpaddrfromv$sessionwheresid=????selectspidfromv$processwhereaddress=上面查询的paddr查找到对应的spid，然后在数据库所在的操作系统上的dos环境下执行orakill数据库实例名对应spid
18、DBA密码遗忘及用户被锁解决方法DOS环境下：sqlplus/nologSQL>connusername/password@SID（登录DBA权限工号）SQL>alteruserusernameidentifiedbypassword;（密码重置）SQL>alteruserusernameaccountunlock;（解锁）19、存储过程样本范例存储过程编写可参考此范本：createorreplaceprocedurepr_sms_interface(stat_dateinvarchar2,serv_numberinvarchar2,message_ininvarchar2)/**head*@namepr_sms_interface*@caption应用层短信接口*@type接口*@parameterstat_dateinvarchar2统计日期格式：yyyymmdd*@parameteroi_returnoutnumber执行状态码，整数，0正常，-1出错*@description*@targetdb_app#pr_sms_interface*@source*@middle*@version1.0*@author
*@create-date2010-09-28*@todo无*@version*@mender*@modify_date*@modify_desc*@copyright*/-********************************************************************************--程序名称:db_app.pr_sms_interface--功能描述:应用层短信接口--输入参数:stat_date-统计日期--输出参数:oi_return-执行状态码，整数，0正常，-1出错--输入资源:--输出资源:--中间资源:--创建人员:姜春涛--创建日期:2010-09-28--版本说明:v1.0--修改人员:--修改日期:--修改原因:--版本说明:--公司名称:南京联创-********************************************************************************isvs_task_namevs_table_namevs_messagevi_task_idvi_row_countbeginvarchar2(30);varchar2(30);varchar2(200);integer;number;-----任务名称表名称日志信息日志id临时结果
vs_task_name:='pr_sms_interface';vs_table_name:='pr_sms_interface';--程序开始日志db_app.ps_log(vs_task_name,vs_table_name,stat_date,1,null,vi_task_id);
selectkt.sms_id.nextval@dblink_hljcrmintovi_row_countfromdual;commit;insertintoods.sms_info@odsdb(id,msisdn,flag,msg,sts,get_date,send_date,pri)selectvi_row_count,serv_number,'ODS',message_in,'A',sysdate,sysdate,0fromdual;commit;--备份发送接口信息insertintodb_app.tr_sms_info(id,msisdn,flag,msg,sts,get_date,send_date,pri)selectvi_row_count,serv_number,'ODS',message_in,'A',sysdate,
sysdate,0fromdual;commit;--程序结束日志db_app.ps_log(null,null,null,2,null,vi_task_id);-------------------------------------------------------------程序结束日志db_app.ps_log(null,null,null,2,null,vi_task_id);--成功返回return;exceptionwhenothersthen--得到出错信息vs_message:=substr(sqlerrm,1,200);--回滚事务rollback;--程序出错日志db_app.ps_log(null,null,null,3,vs_message,vi_task_id);--出错返回return;end;
