如何找出数据文件的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  <<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 suclearcase/" target="_blank" >ccessfully completed.

 

SQL>

SQL>

SQL>

 

原文转自：http://www.ltesting.net