| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Shrink datafiles
John
Thanks...I am still using this script very much and it came from this great
list.....
Regards
Rafiq
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Wed, 19 Jun 2002 07:48:44 -0800
This script was posted by Mohammed Rafiq last yea. It should meet your requirements
John
>>>>>>>>>>>>>>>>>>
set verify off
set pages 60
col file_size_mb format 99999
prompt This script provides a report useful for resizing datafiles
prompt You should perform a 'ALTER TABLESPACE tsname COALESCE' before
running
prompt this script to ensure you are getting all free space at end of the
file
accept table_space prompt "Enter a tablespace_name or all: "
select ddf.file_name,
dfs.file_id,
ddf.blocks,
(ddf.blocks*value)/1024/1024 file_size_mb,
dfs.block_id block_hwm,
ddf.blocks-dfs.block_id fat_blocks,
floor(((ddf.blocks-dfs.block_id)*value)/1024/1024)
fat_mb,
ceil(((ddf.blocks*value)/1024/1024 -
((ddf.blocks-dfs.block_id)*value)/1024/1024)) resize_to
from dba_free_space dfs,
dba_data_files ddf,
v$parameter
and (ddf.tablespace_name = UPPER('&&table_space')
or 'ALL' = UPPER('&&table_space'))
and dfs.tablespace_name = ddf.tablespace_name
and dfs.file_id = ddf.file_id
and dfs.block_id = (select max(block_id)
from dba_free_space
where file_id = dfs.file_id)
order by fat_blocks desc;
<<<<<<<<<<<<<<<
-----Original Message-----
Sent: 19 June 2002 15:28
To: Multiple recipients of list ORACLE-L
Does anyone have a script that will show how much I can shrink datafiles? I
have
some bloated databases that I need to reclaim space from and thought if
someone
had one handy I wouldn't have to write it:)
Thanks, Dave
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Turner INET: turner_at_tellme.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: John.Hallas_at_vodafone.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Jun 19 2002 - 16:59:20 CDT
 |
 |