    
スポンサードリンク
表領域縮小可能サイズ確認手順
本項では、表領域のリサイズを行う前に縮小可能サイズを確認する方法を紹介します。
以下に実行例をします。
（実行例１）PL/SQLを用いて確認する方法
＜KROWN#45850＞
DATAFILEを縮小したい場合、縮小するサイズによっては、ORA-3297(file contains used data beyond requested
RESIZE value) が発生します。これは、縮小したい領域内の一部がデータセグメントにより使用されているためです。そこで、以下のSQLを実行することで、DATAFILEのHWM(High Water Mark)の確認が可能になります。dba_free_spaceより、各datafile内に存在する最後のobjectの位置を特定し、それ以降が縮小可能領域として計算されます。
|
SQL> set serveroutput on
SQL> execute dbms_output.enable(2000000);
PL/SQLプロシージャが正常に完了しました。
SQL>declare
cursor c_dbfile is
select tablespace_name
,file_name
,file_id
,bytes
from sys.dba_data_files
where status !='INVALID'
order by tablespace_name,file_id;
cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;
blocksize number;
filesize number;
extsize number;
begin
/* get the blocksize of the database, needed to calculate the startaddress */
select value
into blocksize
from v$parameter
where name = 'db_block_size';
/* retrieve all datafiles */
for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
<<outer>>
for c_rec2 in c_space(c_rec1.file_id)
loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize
then
filesize := (c_rec2.block_id - 1)*blocksize;
else
/* in order to shrink the free space must be uptil end of file */
exit outer;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can not be resized, no free space at end of file.')
;
dbms_output.put_line('.');
else
if filesize < 2*blocksize
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized uptil: '||2*blocksize
||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
dbms_output.put_line('.');
else
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized uptil: '||filesize
||' Bytes, Actual size: '||c_rec1.bytes);
dbms_output.put_line('.');
end if;
end if;
end loop;
end;
/
=======================================================================================
出力例：
Tablespace: BIGPARTYWEEK1 Datafile: /ots1/app/oracle/product/8.1.7/dbs/bw1.dbf
Can be resized uptil: 49152 Bytes, Actual size: 1040384
.
Tablespace: IFS_LOB_N Datafile: /ots3/oradata/v817/oradata/v817/ifs_lob_n.dbf
Can not be resized, no free space at end of file.
.
Tablespace: IFS_MAIN Datafile: /ots3/oradata/v817/oradata/v817/ifs_main.dbf
Can be resized uptil: 23027712 Bytes, Actual size: 52428800
.
Tablespace: INDX Datafile: /ots2/oradata/v817/oradata/v817/indx01.dbf
Can be resized uptil: 16384 Bytes, Actual size: 56623104 Bytes
.
Tablespace: OEM_REPOSITORY Datafile:
/ots1/oradata/v817/oradata/v817/oem_repository.dbf
Can not be resized, no free space at end of file.
=======================================================================================
SQL>
|
※"Can be resized uptil" が 縮小できるサイズになります。"Actual size"
は現在のサイズになります。 "Can not be resized, no free space at end of file."
は、縮小不可を意味します。 9iからの新機能であるマルチ・ブロック・サイズ(非標準ブロック・サイズ)を 使用しているDATAFILEについては算出されませんので、予めご了承ください。
Can not be resized, no free space at end of file. が出力されます。 また、UNIFORM SIZE指定のローカル管理表領域に対しても、正しく算出されない場合があります。このPL/SQLでCan
not・・・になる場合は、実行例２で確認してください。
（実行例２）v$datafileとdba_free_spaceから縮小可能な最大サイズの取得方法
＜KROWN#11919＞
RESIZEによってファイルサイズを縮小する場合は最後尾のFREE BLOCK分のみ縮小することが可能です。DATAFILE内に不連続なFREE
BLOCKが存在している場合は、現在使用中のエクステントのサイズの合計まで縮小することはできません。
|
（１）データファイルのサイズ、ファイル ID を確認します。
SQL> column name format a50
SQL> select file#, bytes, name from v$datafile where name like '%USERS%';
FILE# BYTES NAME
---------- ---------- --------------------------------------------------
4 104857600 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
5 1073741824 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS2.DBF
6 10485760 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS99.DBF
SQL>
|
（２）FREE SPACE を確認します。
SQL> column tablespace_name format a20
SQL> select * from dba_free_space where tablespace_name = 'USERS' order by block_id;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
-------------------- ---------- ---------- ---------- ---------- ------------
USERS 6 128 9437184 1152 6
USERS 4 360 101908480 12440 4
SQL>
|
●縮小可能なサイズの算出方法
<FREE SPACE の最後の開始位置(*1)> -1＞ * DB_BLOCK_SIZE までの大きさとなります。
上記の例の場合、FILE_IDが「6」のデータファイルは、(128-1) * 8 K = 1016 KB になります。
（DB_BLOCK_SIZE を 8K として計算した場合。）
|
上記の手順で縮小可能サイズを把握できたら、「表領域縮小手順（resize）」で実際にresizeを実行します。
◎-表領域の概要と管理
◎-UNDO表領域（UNDO TABLESPACE）とは？
◎-一時表領域（TEMPORARY TABLESPACE）とは？
◎-Oracle表領域の作成（CREATE TABLESPACE）
◎-Oracle表領域の変更（ALTER TABLESPACE）
◎-Oracle表領域の削除（DROP TABLESPACE）
◎-SYSTEM表領域が増加する理由
◎-SYSAUX表領域が増加する理由
◎-表領域の使用率と空き領域確認方法
◎-表領域の物理データファイル確認方法
◎-表領域拡張手順（データファイル追加）
◎-表領域縮小可能サイズ確認手順
◎-表領域縮小手順（resize）
|
スポンサードリンク
|
