ORACLE表空間擴展方法

1、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窗口-selecttablespacesin巳和md，file_riain巳round(bytes/(1024*10240)total_spacefromdba_da

2、ta.SQL統(tǒng)計表selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;g-好至隔ayssMi-TABLESPACENAMEFILEDFILENAMETQTALSPACE1NITATEST-10/home/oraclEbpfVcifdclEfcirchtdfgtiPdCigbnitXEStCiI.citif-102ANITATEST-11/home/ora匚1已掃p時口日匚1引mcidtdfgtikl0g/anit

3、atest02.dbf-1037040001547551203SYSAUX-3/home/oracle/app/oracle/oradata/gbk10g/sysaux01.dbf-4SYSTEM-1/home/ora匚1已/app/ora匚1已/oradata/gbk10g/system01.dbf-5UNDOTBS1-2/home/ora匚1已/app/ora匚1已/oradata/gbk10g/undotbs01.dbf-6USERS-9/home/ora匚1已/app/oracle/oradata/gbk1Og/usersOB.dbf-二少5:1tablespace-name,varc

4、har2C30optional,NameofthetablespacetowhichthefileOracleTableSpace01.jpg?psid第二步：增大所需表空間大?。篴lterdatabasedatafile表空間位置resize新的尺寸例如：alterdatabasedatafileoracleoradataanita_2008.dbfresize4000m對于oracleODD的表空間，除了用手動增加大小外，還可以增加數(shù)據(jù)文件等方式擴展表空間大小。方法一：增加數(shù)據(jù)文件個數(shù)altertablespace表空間名稱adddatafile新的數(shù)據(jù)文件地址size數(shù)據(jù)文件大小例如：a

5、ltertablespaceESPS_2008adddatafileoracleoradataanita_2010.dbfsize1000m方法二：設置表空間自動擴展。alterdatabasedatafile數(shù)據(jù)文件位置autoextendonnext自動擴展大小maxsize最大擴展大小例如：alterdatabasedatafileoracleoradataanita_2008.dbfautoextendonnext100mmaxsize10000m方法三：查詢表空間使用情況：selecta.tablespace_name,a.bytes/1024/1024sumMB,(a.bytes-

6、b.bytes)/1024/1024usedMB,b.bytes/1024/1024freeMB,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.

7、tablespace_nameorderby(a.bytes-b.bytes)/a.bytes)desc;SQL窗口-selecta.tablespace_nameja.bytes/1024/1024sumMB,(a.bytes-b.bytes)/1024/1024usedMBjb.byt.SQL統(tǒng)計表selecta.tablespace_name,a.bytes/1024/1024sumMB,(a.bytes-b.bytes)/1024/1024usedMB,b.bytes/1024/1024TreeMB,round(a.bytes-b.bytesya.bytesfl00,2)used%fr

8、om(selecttablespace_name,sum(bytes)bytesfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes)bytes.max(bytes)largestfromdba_free_spacegroupbytablespace_name)wherea.tablespace_name=b.tablespace_nameorderby(a.bytes-b.bytes)/a.bytes)desc;|TABLESPACENAMEsumMBusedMBfreeMBused%1SYSA

9、UX-370344.687525.312593.162SYSTEM-40002722.6251277.37568.073USERS-140287.937583865.062556422.87559.784UNDOTBS1-15475594.687514880.31253.845ANITATEST-200.2519.751.25SQL窗口-selecta.tablespace_nameja.bytes/1024/1024sumMB,(a.bytes-b.bytes)/1024/1024usedMBjb.byt.SQL窗口-selecta.tablespace_nameja.bytes/1024/1024sumMB,(a.bytes-b.bytes)/1024/1024usedMBjb.byt.=38:430:015行被選擇，耗時1.015秒SQL窗口-selecta.tablespace_nameja.bytes/1024/1024s