IDC资讯中心
经常在resize数据文件的时候，resize的尺寸不好掌握，下面提供一个方法：
sql> declare
2 cursor c_dbfile is
3 select tablespace_name
4 ,file_name
5 ,file_id
6 ,bytes
7 from sys.dba_data_files
8 where status !=invalid
9 order by tablespace_name,file_id;
10 cursor c_space(v_file_id in number) is
11 select block_id,blocks
12 from sys.dba_free_space
13 where file_id=v_file_id
14 order by block_id desc;
15 blocksize binary_integer;
16 filesize binary_integer;
17 extsize binary_integer;
18 begin
19 select value
20 into blocksize
21 from v$parameter
22 where name = db_block_size;
23 for c_rec1 in c_dbfile
24 loop
25 filesize := c_rec1.bytes;
26 <<outer>>
27 for c_rec2 in c_space(c_rec1.file_id)
28 loop
29 extsize := ((c_rec2.block_id – 1)*blocksize + c_rec2.blocks*blocksize);
30 if extsize = filesize
31 then
32 filesize := (c_rec2.block_id – 1)*blocksize;
33 else
34 exit outer;
35 end if;
36 end loop outer;
37 if filesize = c_rec1.bytes
38 then
39 dbms_output.put_line(tablespace:
40 || ||c_rec1.tablespace_name|| datafile: ||c_rec1.file_name);
41 dbms_output.put_line(can not be resized, no free space at end of file.)
42 ;
43 dbms_output.put_line(.);
44 else
45 if filesize < 2*blocksize
46 then
47 dbms_output.put_line(tablespace:
48 || ||c_rec1.tablespace_name|| datafile: ||c_rec1.file_name);
49 dbms_output.put_line(can be resized uptil: ||2*blocksize
50 || bytes, actual size: ||c_rec1.bytes|| bytes);
51 dbms_output.put_line(.);
52 else
53 dbms_output.put_line(tablespace:
54 || ||c_rec1.tablespace_name|| datafile: ||c_rec1.file_name);
55 dbms_output.put_line(can be resized uptil: ||filesize
56 || bytes, actual size: ||c_rec1.bytes);
57 dbms_output.put_line(.);
58 end if;
59 end if;
60 end loop;
61 end;
62 /
tablespace: drsys datafile: /usr/oracle/data/oradata/cint208/drsys01.dbf
can be resized uptil: 4333568 bytes, actual size: 5242880
.
tablespace: indx datafile: /usr/oracle/data/oradata/cint208/indx01.dbf
can be resized uptil: 16384 bytes, actual size: 5242880 bytes
.
tablespace: rbs datafile: /usr/oracle/data/oradata/cint208/rbs01.dbf
can be resized uptil: 57155584 bytes, actual size: 57671680
.
tablespace: system datafile: /usr/oracle/data/oradata/cint208/system01.dbf
can be resized uptil: 280182784 bytes, actual size: 283115520
.
tablespace: temp datafile: /usr/oracle/data/oradata/cint208/temp01.dbf
can be resized uptil: 16384 bytes, actual size: 5242880 bytes
.
tablespace: testspace datafile: /usr/oracle/data/oradata/cint208/testspace1.dbf
can be resized uptil: 16384 bytes, actual size: 5242880 bytes
.
tablespace: tools datafile: /usr/oracle/data/oradata/cint208/tools01.dbf
can be resized uptil: 16384 bytes, actual size: 5242880 bytes
.
tablespace: users datafile: /usr/oracle/data/oradata/cint208/users01.dbf
can be resized uptil: 23076864 bytes, actual size: 23592960
.
pl/sql procedure successfully completed.
sql>
sql>
sql>
