 经常在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 < 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> |
喜欢本文，那就收藏到： |
.text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://my.poco.cn/fav/storeIt.php?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t)+'&img=http://www.h-strong.com/blog/logo.gif','keyit','scrollbars=no,width=475,height=575,status=no,resizable=yes'));keyit.focus(); "添加到POCO网摘"){kind=logo}
Rss Feed
