在实际工作中,可能都遇到过,想把前期的分配给数据文件的空间回收回来,比如前期没有规划好,本来一个表空间只要1G的空间就可以,但是实际上我们分配了10G,浪费了9G的空间!如果数据库可以停机的话,很好办,把数据导出来再导入到新建的表空间中,删除原来的表空间和数据文件!其实我们可以用下面的方法来尝试RESIZE来缩小数据文件:
create tablespace test_resize datafile '/data4/test_resisze.dbf' size 300M;
我们创建个TABLE存放在刚才新建的TEST_RESIZE的空间里面
create table test_resize tablespace test_resize as select * from dba_objects;
我们来计算下刚才新建的表TEST_RESIZE大小
select sum(bytes)/1024/1024 M from dba_extents where segment_name='TEST_RESIZE' and SEGMENT_TYPE='TABLE';
M
----------
2
现在用resize表空间能到达多少呢?执行下面的脚本:
set serveroutput on
execute dbms_output.enable(2000000);
declare
cursor c_dbfile is
select tablespace_name
,file_name
,file_id
,bytes
from sys.dba_data_files
where status !='INVALID'
order by tablespace_name,file_id;
cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;
blocksize number;
filesize number;
extsize number;
begin
/* get the blocksize of the database, needed to calculate the startaddress */
select value
into blocksize
from v$parameter
where name = 'db_block_size';
/* retrieve all datafiles */
for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
<<outer>>
for c_rec2 in c_space(c_rec1.file_id)
loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize
then
filesize := (c_rec2.block_id - 1)*blocksize;
else
/* in order to shrink the free space must be uptil end of file */
exit outer;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can not be resized, no free space at end of file.')
;
dbms_output.put_line('.');
else
if filesize < 2*blocksize
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized to: '||2*blocksize
||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
dbms_output.put_line('.');
else
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized to: '||filesize
||' Bytes, Actual size: '||c_rec1.bytes);
dbms_output.put_line('.');
end if;
end if;
end loop;
end;
/
看下结果
...................
Tablespace: TEST_RESIZE Datafile: /data4/test_resisze.dbf
Can be resized to: 2162688 Bytes, Actual size: 314572800
........................................................
我看到计算出来是到2162688字节,我们来验证是不是正确的,我们先加大2个字节,大小为2162690
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2162690;
Database altered.
QL> alter database datafile '/data4/test_resisze.dbf' resize 2162686;
Database altered.
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2162680;
Database altered.
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2162600;
Database altered.
我们发现比这个值还是可以resize的,那么这个是怎么算出结果的呢,我猜想可能是因为如果你缩小到比他这个参考值一个块的时候,就会报ORA-03297错误!我来证明自己的猜想,实验如下:
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select 2162688 - 8192 from dual;
2162688-8192
------------
2154496
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2154496;
alter database datafile '/data4/test_resisze.dbf' resize 2154496
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
我们发现报错了,刚好是一个数据库块,我们加到2个字节看下情况:
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2154498;
Database altered.
成功
create tablespace test_resize datafile '/data4/test_resisze.dbf' size 300M;
我们创建个TABLE存放在刚才新建的TEST_RESIZE的空间里面
create table test_resize tablespace test_resize as select * from dba_objects;
我们来计算下刚才新建的表TEST_RESIZE大小
select sum(bytes)/1024/1024 M from dba_extents where segment_name='TEST_RESIZE' and SEGMENT_TYPE='TABLE';
M
----------
2
现在用resize表空间能到达多少呢?执行下面的脚本:
set serveroutput on
execute dbms_output.enable(2000000);
declare
cursor c_dbfile is
select tablespace_name
,file_name
,file_id
,bytes
from sys.dba_data_files
where status !='INVALID'
order by tablespace_name,file_id;
cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;
blocksize number;
filesize number;
extsize number;
begin
/* get the blocksize of the database, needed to calculate the startaddress */
select value
into blocksize
from v$parameter
where name = 'db_block_size';
/* retrieve all datafiles */
for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
<<outer>>
for c_rec2 in c_space(c_rec1.file_id)
loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize
then
filesize := (c_rec2.block_id - 1)*blocksize;
else
/* in order to shrink the free space must be uptil end of file */
exit outer;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can not be resized, no free space at end of file.')
;
dbms_output.put_line('.');
else
if filesize < 2*blocksize
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized to: '||2*blocksize
||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
dbms_output.put_line('.');
else
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized to: '||filesize
||' Bytes, Actual size: '||c_rec1.bytes);
dbms_output.put_line('.');
end if;
end if;
end loop;
end;
/
看下结果
...................
Tablespace: TEST_RESIZE Datafile: /data4/test_resisze.dbf
Can be resized to: 2162688 Bytes, Actual size: 314572800
........................................................
我看到计算出来是到2162688字节,我们来验证是不是正确的,我们先加大2个字节,大小为2162690
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2162690;
Database altered.
QL> alter database datafile '/data4/test_resisze.dbf' resize 2162686;
Database altered.
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2162680;
Database altered.
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2162600;
Database altered.
我们发现比这个值还是可以resize的,那么这个是怎么算出结果的呢,我猜想可能是因为如果你缩小到比他这个参考值一个块的时候,就会报ORA-03297错误!我来证明自己的猜想,实验如下:
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select 2162688 - 8192 from dual;
2162688-8192
------------
2154496
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2154496;
alter database datafile '/data4/test_resisze.dbf' resize 2154496
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
我们发现报错了,刚好是一个数据库块,我们加到2个字节看下情况:
SQL> alter database datafile '/data4/test_resisze.dbf' resize 2154498;
Database altered.
成功
转载于:https://blog.51cto.com/invan/407762