在oracle中,可以利用alter語句增加表空間,語法為“alter tablespace 表空間名 add datafile ‘文件路徑’ SIZE 初始大小 AUTOEXTEND ON NEXT 自動擴(kuò)展大小”。
本教程操作環(huán)境:Windows10系統(tǒng)、Oracle 11g版、Dell G3電腦。
oracle怎樣增加表空間
Oracle 增加表空間
語法:
alter?tablespace?{表空間名字}?add?datafile?'物理數(shù)據(jù)文件路徑'?SIZE?『初始大小M』?AUTOEXTEND?ON?NEXT?『自動擴(kuò)展大小M』
例子:
alter?tablespace?MMLOTTERY?add?datafile?'+DATA/ora11g/datafile/mmlottery08.dbf'?size?30720m?autoextend?on?next?200m;
注意:如果添加表空間的文件名重復(fù),那么會報錯,如下:
sql>?alter?tablespace?MMLOTTERY?add?datafile?'+DATA/ora11g/datafile/mmlottery08.dbf'?size?30720m?autoextend?on?next?200m; alter?tablespace?MMLOTTERY?add?datafile?'+DATA/ora11g/datafile/mmlottery08.dbf'?size?30720m?autoextend?on?next?200m * ERROR?at?line?1: ORA-01537:?cannot?add?file?'+DATA/ora11g/datafile/mmlottery08.dbf'?-?file?already?part?of?database
若 datafile 加錯到表空間,則執(zhí)行刪除操作。
alter?tablespace?MMLOTTERY?drop?datafile?'+DATA/ora11g/datafile/mmlottery08.dbf';
或者
alter?database?datafile?'+DATA/ora11g/datafile/mmlottery08.dbf'?offline?drop;
拓展:
查詢指定的表空間
SQL語句:
select?tablespace_name,?file_id,?file_name,?? ????round(bytes/(1024*1024),0)?total_space_MB?? from?dba_data_files where?tablespace_name?=?'MMLOTTERY' order?by?tablespace_name;
查詢結(jié)果:
TABLESPACE_NAME????????FILE_ID?FILE_NAME???????????????????????????????????TOTAL_SPACE_MB -------------------?----------?-------------------------------------------?-------------- MMLOTTERY???????????????????18?+DATA/ora11g/datafile/mmlottery01.dbf????????????????30720 MMLOTTERY???????????????????19?+DATA/ora11g/datafile/mmlottery02.dbf????????????????30720 MMLOTTERY???????????????????20?+DATA/ora11g/datafile/mmlottery03.dbf????????????????30720 MMLOTTERY???????????????????22?+DATA/ora11g/datafile/mmlottery04.dbf????????????????30720 MMLOTTERY???????????????????23?+DATA/ora11g/datafile/mmlottery05.dbf????????????????30720 MMLOTTERY???????????????????26?+DATA/ora11g/datafile/mmlottery06.dbf????????????????30720 MMLOTTERY???????????????????27?+DATA/ora11g/datafile/mmlottery07.dbf????????????????30720 ? 7?rows?selected.
推薦教程:《Oracle視頻教程》
? 版權(quán)聲明
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載。
THE END
喜歡就支持一下吧
相關(guān)推薦