资源描述：
《oracle日常维护脚本》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、emca-configdbcontroldbCREATESMALLFILETABLESPACE"MYMAILOPT"DATAFILE'/oracle/oradata/ora8/mymailopt'SIZE100MAUTOEXTENDONNEXT64KMAXSIZEUNLIMITEDLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTOCREATEUSER"ADMIN_51GO"PROFILE"DEFAULT"IDENTIFIEDBY"*******"DEFAULTTABLESPACE"MYMAILOPT"TEMPORARYTABL
2、ESPACE"TEMP"ACCOUNTUNLOCKGRANT"CONNECT"TO"ADMIN_51GO"GRANT"RESOURCE"TO"ADMIN_51GO"alteruseradmin_51goquotaunlimitedonmymailoptimpdpsystem/uranus_oracledirectory=data_transschemas=admin_51godumpfile=expdat.dmpexpdpsystem/orclDIRECTORY=DATA_TRANSDUMPFILE=admin_51go.dmpSCHEMAS=admin_51goSELECTUPPE
3、R(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_BYTES,RO
4、UND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY4DESC;为表空间追加数据文件ALTERTABLESPACE"MYMAIL
5、OPT"ADDDATAFILE'/oracle/oradata/ora8/mymailopt02'SIZE100MAUTOEXTENDONNEXT64KMAXSIZEUNLIMITEDCREATEBIGFILETABLESPACE"XX"DATAFILE'/u01/app/oracle/oradata/orcl/dfg'SIZE100MAUTOEXTENDONNEXT64KMAXSIZEUNLIMITEDLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO创建表空间CREATETABLESPACE"XX"DATAFILE'/u0
6、1/app/oracle/oradata/orcl/dfg'SIZE100MAUTOEXTENDONNEXT64KMAXSIZEUNLIMITEDLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO检查数据文件的状态信息包括是否自动扩展、当前大小、总共大小等。SELECTT.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES/1024/1024/1024asCurrent_size_G,D.MAXBYTES/1024/1024/1024Total_size_G,D.STATU
7、SFROMDBA_TABLESPACEST,DBA_DATA_FILESDWHERET.TABLESPACE_NAME=D.TABLESPACE_NAMEORDERBYTABLESPACE_NAME,FILE_NAME;根据osid确定某个某个用户运行哪个sqlselectS.SID,s.SQL_ID,sq.SQL_TEXT,s.USERNAMEfromV$PROCESSp,v$sessions,v$sqlsqwherespid='27096'andp.A
