经常在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>
|
本类阅读TOP10