oracle常見問題Oracle數(shù)據(jù)庫及解決方案

數(shù)據(jù)庫常見問題及解決數(shù)據(jù)庫表空間使用率過高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文件系統(tǒng)：smitjfs2mklv-y'data'-t'raw'-a'c' 修改新建邏輯卷權限:chownoracle:dba文件系統(tǒng)SQL>altertablespace '/oracle/proddata/user01.dbf'size20000mautoextendoff；設備 SQL>altertablespaceusersadd‘/dev/rdata’size2000mautoextend數(shù)據(jù)庫發(fā)生鎖表 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#’數(shù)據(jù)庫控制文件損$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;數(shù)據(jù)庫歸檔異解決方法:用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是由于在共享池中試圖分配大片連續(xù)內(nèi)存失敗的時候，oracle首先清楚池報ora-04031錯誤。fromv$shared_pool_.上過小等。Ora-01555常見的原因是查詢操作要去的回滾段信息已經(jīng)被覆蓋掉，不能完SQL>createundotablespaceundo2datafilesizeSQL>Altersystemsetundo_tablespace程序錯誤 將(ADDRESS(PROTOCOLTCP)(HOSTDBA)(PORT1521))一行中的hostora-12571此類錯誤為客戶端連接報錯，一般會報“TNSpacketwriterfailure”，造成連接超時。TCP.NODELAY=YES”，如果是經(jīng)常出現(xiàn)，則是客戶端與服務端字符集不一致導致網(wǎng)誤刪除 _timefromSQL>select SQL>createtablenewasselect*fromtabwhereSQL>insertintonewselect*fromoldasofscnIMP-00069NLS_LAGUAGE與數(shù)據(jù)庫的國家語言支持不一致導致的。解決方法為注釋掉.profile文件中NLS_LAGUAGE=***一行。再重新導入數(shù)據(jù)即可。/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參數(shù)=**ora-12015Ora-12005錯誤：SQL>ora-12012erroronautoexecuteofjobOra-12015maynotscheduleautomaticrefreshfortimesinthe解決方法：找到錯誤的job dba_jobswherejob=7;查看此j