oracle常见问题Oracle数据库及解决方案

数据库常见问题及解决数据库表空间使用率过高select BLOCKSSUM_BLOCKS,ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)"USED_RATE(%)",FREE_SPACE"FREE_SPACE(M)"FROM(SELECTSUM(BLOCKS)BLOCKSFROM(SELECTTABLESPACE_NAME,FROMDBA_FREE_SPACEUNIONALL--ifhavetempfileSPACE"SUM_SPACE(M)",BLOCKSSUM_BLOCKS,SPACE-USED_SPACE"FREE_SPACE(M)"FROM(SELECTSUM(BLOCKS)BLOCKSFROM(SELECTTABLESPACE,ROUND(SUM(BLOCKS*8192)/(1024*1024),FROMV$SORT_USAGE文件系统：smitjfs2mklv-y'data'-t'raw'-a'c' 修改新建逻辑卷权限:chownoracle:dba文件系统SQL>altertablespace '/oracle/proddata/user01.dbf'size20000mautoextendoff；设备 SQL>altertablespaceusersadd‘/dev/rdata’size2000mautoextend数据库发生锁表 v$locked_objecta,dba_objectsbwhereb.object_id=a.object_id;SQL>selectb.username,b.sid,b.serial#,logon_timefromv$locked_objecta,v$sessionbwherea.session_id=b.sidorderbyb.logon_time;SQL>altersystemkillsession‘sid,serial#’数据库控制文件损$rmanRecoveryManager:Release.0-64bitCopyright(c)1995,2002,OracleCorporation. s.connectedtodatabase:wksd(notmounted)SQL>CREATECONTROLFILEREUSEDATABASE"O10"NORESETLOGSNOARCHIVELOGMAXLOGFILES16MAXDATAFILESMAXINSTANCESGROUP1'D:\ORACLE\ORADATA\O10\REDO01.LOG'SIZE50M,GROUP2'D:\ORACLE\ORADATA\O10\REDO02.LOG'SIZE50MCHARACTERSETZHS16GBK;数据库归档异解决方法:用rman备份并删除归档RMAN>backuparchivelogalldeleteinputformatbackup14-6-分配的通道ORA_DISK_1SID=20设备类型ORA_DISK_1:ORA_DISK_1:输入归档日志线程=1序列=11RECID=4STAMP=753191325输入归档日志线程=1序列=12RECID=5STAMP=753296571输入归档日志线程=1序列=13RECID=6STAMP=753804159输入归档日志线程=1序列=14RECID=7STAMP=753810781ORA_DISK_1:114-6-Oracle-04031是由于在共享池中试图分配大片连续内存失败的时候，oracle首先清楚池报ora-04031错误。fromv$shared_pool_.上过小等。Ora-01555常见的原因是查询操作要去的回滚段信息已经被覆盖掉，不能完SQL>createundotablespaceundo2datafilesizeSQL>Altersystemsetundo_tablespace程序错误 将(ADDRESS(PROTOCOLTCP)(HOSTDBA)(PORT1521))一行中的hostora-12571此类错误为客户端连接报错，一般会报“TNSpacketwriterfailure”，造成连接超时。TCP.NODELAY=YES”，如果是经常出现，则是客户端与服务端字符集不一致导致网误删除 _timefromSQL>select SQL>createtablenewasselect*fromtabwhereSQL>insertintonewselect*fromoldasofscnIMP-00069NLS_LAGUAGE与数据库的国家语言支持不一致导致的。解决方法为注释掉.profile文件中NLS_LAGUAGE=***一行。再重新导入数据即可。/usr/sbin/no–r–o/usr/sbin/no–p–orfc/usr/sbin/no–p–o/usr/sbin/no–p–o/usr/sbib/no–p–o/usr/sbib/no–p–oOra-32004SQL>startupobsoleteand/ordeprecatedparameterSQL>altersystemset参数=**ora-12015Ora-12005错误：SQL>ora-12012erroronautoexecuteofjobOra-12015maynotscheduleautomaticrefreshfortimesinthe解决方法：找到错误的job dba_jobswherejob=7;查看此j