DataFile大小調整Script
?
??? 昨天學習了Oracle中的datafile大小的增長方式，當然一定會想到如果datafile被“撐”大之后，如何縮小datafile的大小呢？怎么知道這個datafile的真正被使用的實際大小是多少呢？以下就摘錄tom用來計算datafile可縮減大小的腳本。這個腳本在我第一次看到的時候，對我有很大的啟發，甚至很大程度上改變了我的編碼習慣。看上去真的是相當賞心悅目，多年的程序員下來，一定會追求形式上的美感。
?
?
?----------- maxshrink.sql ----------------------------------
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings? format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
?????? ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
?????? ceil( blocks*&&blksize/1024/1024) currsize,
?????? ceil( blocks*&&blksize/1024/1024) -
?????? ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
???? ( select file_id, max(block_id+blocks-1) hwm
???????? from dba_extents
??????? group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
?????? ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )? || 'm;' cmd
from dba_data_files a,
???? ( select file_id, max(block_id+blocks-1) hwm
???????? from dba_extents
??????? group by file_id ) b
where a.file_id = b.file_id(+)
? and ceil( blocks*&&blksize/1024/1024) -
????? ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
?
-------------------------------------------------------------------------------
執行結果：
?
SQL> set verify off
SQL> column file_name format a50 word_wrapped
SQL> column smallest format 999,990 heading "Smallest|Size|Poss."
SQL> column currsize format 999,990 heading "Current|Size"
SQL> column savings? format 999,990 heading "Poss.|Savings"
SQL> break on report
SQL> compute sum of savings on report
SQL> column value new_val blksize
SQL> select value from v$parameter where name = 'db_block_size'
2?? /
?
VALUE
-----------
8192
?
SQL> select file_name,
? 2? ?????? ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
? 3? ?????? ceil( blocks*&&blksize/1024/1024) currsize,
? 4? ?????? ceil( blocks*&&blksize/1024/1024) -
? 5? ?????? ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
? 6??? from dba_data_files a,
? 7? ????? (select file_id, max(block_id+blocks-1) hwm
? 8? ???????? from dba_extents
? 9? ??????? group by file_id ) b
10?? where re a.file_id = b.file_id(+)
11 /
?
?????????????????????????????????????????????????? Smallest
?????????????????????????????????????????????????????? Size? Current??? Poss.
FILE_NAME???????????????????????????????????????????? Poss.???? Size? Savings
--------------------------------------------------- ------- -------- --------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\SYSTEM01.DBF????? 478????? 480??????? 2
E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\UNDOTBS01.DBF????? 15?????? 25?????? 10
E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\USERS01.DBF???????? 2??????? 5??????? 3
E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\PERFSTAT.DBF????? 144????? 500????? 356
E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\SYSAUX01.DBF????? 251????? 260??????? 9
???????????????????????????????????????????????????????????????????? --------
sum?????????????????????????????????????????????????????????????????????? 380
?
?
SQL>
column cmd format a75 word_wrapped
SQL> select 'alter database datafile '''||file_name||''' resize ' ||
? 2???????? ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )? || 'm;' cmd
? 3??? from dba_data_files a,
? 4?????? ( select file_id, max(block_id+blocks-1) hwm
? 5?????????? from dba_extents
? 6????????? group by file_id ) b
? 7?? where a.file_id = b.file_id(+)
? 8???? and ceil( blocks*&&blksize/1024/1024) -
? 9???????? ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
10? /
?
CMD
------------------------------------------------------------------------------------------
alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\SYSTEM01.DBF' resize 478m;
alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\UNDOTBS01.DBF' resize 15m;
alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\USERS01.DBF' resize 2m;
alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\PERFSTAT.DBF' resize 144m;
alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DODO\SYSAUX01.DBF' resize 251m;
?
-------------------------------------------------------------------------------------------
?
?
?
?
?
?
關于這個問題的其他討論，參見一下地址：
?
?
?
?