ORACLE重设表空间的大小
You can enlarge a tablespace by:
• Change the size of a data file:
- Automatically during tablespace creation
SQL> CREATE TABLESPACE userdata02
DATAFILE '/u01/oradata/userdata02.dbf' SIZE 5M
AUTOEXTEND ON NEXT 2M MAXSIZE 200M;
- By specifying AUTOEXTEND after tablespace creation
SQL> ALTER DATABASE
DATAFILE '/u01/oradata/userdata02.dbf'
AUTOEXTEND ON NEXT 2M;
- Manually
SQL> ALTER DATABASE
DATAFILE '/u01/oradata/userdata02.dbf' RESIZE 5M;
• Add a data file to a tablespace
SQL> ALTER TABLESPACE userdata02
ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 5M;
- 收缩表空间
select a.file#,a.name,a.bytes/(1024*1024) CurrentMB,
ceil(HWM * a.block_size)/(1024*1024) ResizeTo,
(a.bytes - HWM * a.block_size)/(1024*1024) ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/(1024*1024)) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+) and (a.bytes - HWM *block_size)>0
order by 5
- 针对特殊某个表空间的收缩
select a.file#,a.name,a.bytes/(1024*1024) CurrentMB,
ceil(HWM * a.block_size)/(1024*1024) ResizeTo,
(a.bytes - HWM * a.block_size)/(1024*1024) ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/(1024*1024)) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents c where exists
(select 1 From v$tablespace d ,v$datafile e
where d.ts#=e.ts# and d.name='单独表空间名' and e.file#=c.file_id)
group by c.file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5