如何找出数据文件的HWM

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航