如何找出数据文件的HWM
2024-07-21 02:06:29
供稿：网友
经常在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>
