ORACLE 表空間擴展方法

ORACLE表空間擴展方法2011-10-3118:58:59我來說兩句收藏我要投稿 ［字體：小大］環(huán)境：linux系統(tǒng)工具：PL/SQLDEVELOPER第一步：查看表空間的名字及文件所在位置：selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name翱SQL窗口-selecttablespace』日m巳鹿」4冇le』日m巳rw門日(hyt日引(1024*1024)」口)total_spacefromdba_data"SQL~~~~統(tǒng)計表selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;? 好旻髓ft HgMl-TABLESPACENAMEFILE」DFILENAME JTOT.L1I^NITATEST …10/home/oracle/app/oracle/oradata/gbk10g/anitatest01.dbf…2ANITATEST …11/home/ora匚I巳/app/oracle/oradata/gbk10g/anitatest02.dbf…3SYSAUX …3/home/ora匚I巳/app/oracle/oradata/gbk10g/sysaux01.dbf…4SYSTEM …1/home/ora匚I巳/app/oracle/oradata/gbk10g/system01.dbf…5UNDOTBS1 …2/home/oracle/app/oracle/oradata/gbk10g/undotbs01.dbf…6USERS …9/home/oracle/app/oracle/oradata/gbk10g/users06.dbf…毋5:1 怕引已^口已匚已―門日rTiE』\/B「chB「2[20)joptio門乳Nbiiieof岀已怕引已乞口已匚已towlOracleTableSpace01.jpg?psid">第二步：增大所需表空間大?。篴lterdatabasedatafile表空間位置'resize新的尺寸例如：alterdatabasedatafile'\oracle\oradata\anita_2008.dbf'resize4000m對于oracle數(shù)據(jù)庫的表空間，除了用手動增加大小外，還可以增加數(shù)據(jù)文件等方式擴展表空間大小。方法一：增加數(shù)據(jù)文件個數(shù)altertablespace表空間名稱adddatafile'新的數(shù)據(jù)文件地址'size數(shù)據(jù)文件大小例如：altertablespaceESPS_2008adddatafile'\oracle\oradata\anita_2010.dbf'size1000m方法二：設置表空間自動擴展。alterdatabasedatafile'數(shù)據(jù)文件位置'autoextendonnext自動擴展大小maxsize最大擴展大小例如：alterdatabasedatafile'\oracle\oradata\anita_2008.dbf'autoextendonnext100mmaxsize10000m方法三：查詢表空間使用情況：selecta.tablespace_name,a.bytes/1024/1024"sumMB",(a.bytes-b.bytes)/1024/1024"usedMB",b.bytes/1024/1024"freeMB",round(((a.bytes-b.bytes)/a.bytes)*100,2)"used%"from(selecttablespace_name,sum(bytes)bytesfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes)bytes,max(bytes)largestfromdba_free_spacegroupbytablespace_name)bwherea.tablespace_name=b.tablespace_nameorderby((a.bytes-b.bytes)/a.bytes)desc;

.闔5QL窗口-selecta.tablespace』3171巳乩旳怕引112124扛口24"sumMB",(a.bytes-b.bytes)/1024/1024"usedMB'^b-byt..."SQL~~~~統(tǒng)計表selecta.tablespace_name,a.bytes/1024/1024"sumMB",(a.bytes-b.bytes)/1024/1024"usedMB'^b-bytes/l024/1024'TreeMB",round(((a.bytes-b.bytesya.bytesf100,2)"used%"from(selecttablespace_name,sum(bytes)bytesfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes)bytes.max(bytes)largestfromdba_free_spacegroupbytable*wherea.tablespace_name=b.tablespace_nameorderby((a.bytes-b.bytes)/a.bytes)descjS-? ma h?a-TABLESPACENAMEsumMBusedMBfreeMBused%?1SYSAUX …370344.687525.312593.162SYSTEM …40002722.6251277.37568.073USERS …140287.937583865.062556422.875/