如何找出数据文件的HWM

经常在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 <

  1<outer>&gt;
  2
  327  for c_rec2 in c_space(c_rec1.file_id) 
  4
  528  loop 
  6
  729  extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize); 
  8
  930  if extsize = filesize 
 10
 1131  then 
 12
 1332  filesize := (c_rec2.block_id - 1)*blocksize; 
 14
 1533  else 
 16
 1734  exit outer; 
 18
 1935  end if; 
 20
 2136  end loop outer; 
 22
 2337  if filesize = c_rec1.bytes 
 24
 2538  then 
 26
 2739  dbms_output.put_line('Tablespace: ' 
 28
 2940  ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name); 
 30
 3141  dbms_output.put_line('Can not be resized, no free space at end of file.') 
 32
 3342  ; 
 34
 3543  dbms_output.put_line('.'); 
 36
 3744  else 
 38
 3945  if filesize &lt; 2*blocksize 
 40
 4146  then 
 42
 4347  dbms_output.put_line('Tablespace: ' 
 44
 4548  ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name); 
 46
 4749  dbms_output.put_line('Can be resized uptil: '||2*blocksize 
 48
 4950  ||' Bytes, Actual size: '||c_rec1.bytes||' Bytes'); 
 50
 5151  dbms_output.put_line('.'); 
 52
 5352  else 
 54
 5553  dbms_output.put_line('Tablespace: ' 
 56
 5754  ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name); 
 58
 5955  dbms_output.put_line('Can be resized uptil: '||filesize 
 60
 6156  ||' Bytes, Actual size: '||c_rec1.bytes); 
 62
 6357  dbms_output.put_line('.'); 
 64
 6558  end if; 
 66
 6759  end if; 
 68
 6960  end loop; 
 70
 7161  end; 
 72
 7362  / 
 74
 75Tablespace:  DRSYS Datafile: /usr/oracle/data/oradata/cint208/drsys01.dbf 
 76
 77Can be resized uptil: 4333568 Bytes, Actual size: 5242880 
 78
 79. 
 80
 81Tablespace:  INDX Datafile: /usr/oracle/data/oradata/cint208/indx01.dbf 
 82
 83Can be resized uptil: 16384 Bytes, Actual size: 5242880 Bytes 
 84
 85. 
 86
 87Tablespace:  RBS Datafile: /usr/oracle/data/oradata/cint208/rbs01.dbf 
 88
 89Can be resized uptil: 57155584 Bytes, Actual size: 57671680 
 90
 91. 
 92
 93Tablespace:  SYSTEM Datafile: /usr/oracle/data/oradata/cint208/system01.dbf 
 94
 95Can be resized uptil: 280182784 Bytes, Actual size: 283115520 
 96
 97. 
 98
 99Tablespace:  TEMP Datafile: /usr/oracle/data/oradata/cint208/temp01.dbf 
100
101Can be resized uptil: 16384 Bytes, Actual size: 5242880 Bytes 
102
103. 
104
105Tablespace:  TESTSPACE Datafile: /usr/oracle/data/oradata/cint208/testspace1.dbf 
106
107Can be resized uptil: 16384 Bytes, Actual size: 5242880 Bytes 
108
109. 
110
111Tablespace:  TOOLS Datafile: /usr/oracle/data/oradata/cint208/tools01.dbf 
112
113Can be resized uptil: 16384 Bytes, Actual size: 5242880 Bytes 
114
115. 
116
117Tablespace:  USERS Datafile: /usr/oracle/data/oradata/cint208/users01.dbf 
118
119Can be resized uptil: 23076864 Bytes, Actual size: 23592960 
120
121. 
122
123PL/SQL procedure successfully completed. 
124
125SQL&gt;
126
127SQL&gt;
128
129SQL&gt;</outer>