Here is what this Tom Kyte script will give you:
- The block size your db is using
- The possible savings by shrinking each datafile and a total possible savings
- The SQL to shrink each datafile as far as it can go
-- This script was written by Tom Kyte and retrieved from asktom.oracle.com
set pages 0
set lin 150
set verify off
column file_name format a60 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"
column sum format 999,999,999
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 a95 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
/
11 comments:
Dear Craig,
The first two scripts were good. The third one is throwing an error. Can you please suggest how to overcome the error.
SQL> column cmd format a75 word_wrapped
SQL> select
2 'alter database datafile '''/u01/oracle/oradata/VVAP1/VVA_IMA_DEC07_F1'''resize '
3 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd
from
4 5 dba_data_files a,
6 ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b
7 where
8 a.file_id = b.file_id(+) and
9 ceil( blocks*&&blksize/1024/1024) -
10 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
11 /
'alter database datafile '''/u01/oracle/oradata/XXXX/XXXXXX'''resize '
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
Thanks in advance.
Joe
The last section of code needed some tweaking. Here is the tweak:
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
/
It's my blog, I can respond a year late if I want to : - )...Tom's script works fine as posted, but only if you remove extraneous return characters. Sorry about that.
"It's my blog, I can respond a year late if I want to"
Stupid blog then...
worthless comment...
Thanks for the info - really useful.
Thank you very much!
"Stupid blog than"...ignorant people will have ignorant comments.
Thnak you for the sripts ...
Thanks for posting...haters gonna hate.
great post - ignore the haters - thanks!!! feel free to post things 20 years later if you think it's helpful! (what the @!#&#@!* above doesn't realize is that it's up to end reader to determine the relevance of the post and whether or not to use the knowledge) again - thanks!
They see you rollin... They hatin'...
Post a Comment