如何找出数据文件的HWM

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

经常在resize数据文件的时候，resize的尺寸不好掌握，下面提供一个方法：

 

SQL>declare

 2 cursorc_dbfileis

 3 selecttablespace_name

 4 ,file_name

 5 ,file_id

 6 ,bytes

 7 fromsys.dba_data_files

 8 wherestatus!='INVALID'

 9 orderbytablespace_name,file_id;

 10 cursorc_space(v_file_idinnumber)is

 11 selectblock_id,blocks

 12 fromsys.dba_free_space

 13 wherefile_id=v_file_id

 14 orderbyblock_iddesc;

 15 blocksizebinary_integer;

 16 filesizebinary_integer;

 17 extsizebinary_integer;

 18 begin

 19 selectvalue

 20 intoblocksize

 21 fromv$parameter

 22 wherename='db_block_size';

 23 forc_rec1inc_dbfile

 24 loop

 25 filesize:=c_rec1.bytes;

 26 <<outer>>

 27 forc_rec2inc_space(c_rec1.file_id)

 28 loop

 29 extsize:=((c_rec2.block_id-1)*blocksize+c_rec2.blocks*blocksize);

 30 ifextsize=filesize

 31 then

 32 filesize:=(c_rec2.block_id-1)*blocksize;

 33 else

 34 exitouter;

 35 endif;

 36 endloopouter;

 37 iffilesize=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('Cannotberesized,nofreespaceatendoffile.')

 42 ;

 43 dbms_output.put_line('.');

 44 else

 45 iffilesize<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('Canberesizeduptil:'||2*blocksize

 50 ||'Bytes,Actualsize:'||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('Canberesizeduptil:'||filesize

 56 ||'Bytes,Actualsize:'||c_rec1.bytes);

 57 dbms_output.put_line('.');

 58 endif;

 59 endif;

 60 endloop;

 61 end;

 62 /

 

Tablespace: DRSYSDatafile:/usr/oracle/data/oradata/cint208/drsys01.dbf

Canberesizeduptil:4333568Bytes,Actualsize:5242880

.

Tablespace: INDXDatafile:/usr/oracle/data/oradata/cint208/indx01.dbf

Canberesizeduptil:16384Bytes,Actualsize:5242880Bytes

.

Tablespace: RBSDatafile:/usr/oracle/data/oradata/cint208/rbs01.dbf

Canberesizeduptil:57155584Bytes,Actualsize:57671680

.

Tablespace: SYSTEMDatafile:/usr/oracle/data/oradata/cint208/system01.dbf

Canberesizeduptil:280182784Bytes,Actualsize:2831155201<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>