檢查資料檔案使用情況和能夠resize到高水位值指令碼

資料檔案大大小及頭大小
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 Used_space,
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+)
執行以上查詢，我們可以如下資訊：
Totle_pace:該資料檔案的總大小，位元組為單位
Free_space:該資料檔案的剩於大小，位元組為單位
Used_space:該資料檔案的已用空間，位元組為單位
Data_space:該資料檔案中段資料佔用空間，也就是資料空間，位元組為單位
File_Head:該資料檔案頭部佔用空間，位元組為單位
如果是求hwM，可以確認到resize 回收到最大資料檔案的值 用如下過程
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(20);
filesize number(20);
extsize number(20);
begin
select value into blocksize
    from v$parameter
        where name = 'db_block_size';
for c_rec1 in c_dbfile loop
filesize := c_rec1.bytes;
<>
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
    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/1024/1024
   ||' M, Actual size: '||c_rec1.bytes/1024/1024||' M');
   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/1024/1024
   ||' M, Actual size: '||c_rec1.bytes/1024/1024);
   dbms_output.put_line('.');
end if;
end if;
end loop;
end;
/

 

要是在執行下面必須先set serveroutput on

如果出現以下錯誤

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 48
 

則加大

set serveroutput on  1000000

如果出現以下錯誤則用

ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 20

把數值型加大到20