如何找出資料檔案的HWM

來源:互聯網
上載者:User
資料
經常在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>

 


相關文章

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

11.11 Big Sale for Cloud

Get Unbeatable Offers with up to 90% Off,Oct.24-Nov.13 (UTC+8)

Get It Now >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。