You may use following script to know high water mark of datafile and can
resize upto that size...
Regards
Rafiq
undefine table_space
set verify off
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
where v$parameter.name = 'db_block_size'
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;
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 17 Jul 2001 10:31:41 -0800
You can issue the command:
Alter database datafile '/u001/oradata/mydatafile01.dbf' resize 1MB
Some caveats:
1. BACK UP THE DATABASE BEFORE YOU DO THIS
2. The space you are dropping must be contiguous free space at the
end of the datafile. Check dba_free_space to see how much you can
decrease and if colaesce might help you.
3. If you are using multiple dbwriters, you might have problems
accessing a resized datafile. It's an Oracle bug I saw in one of my v7x
databases (that I haven't had a problem with in 8i). You'll have
to shiutdown and restart.
4. BACK UP THE DATABASE AFTER YOU DO THIS
David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002
Djaroud Salim
<SDJaroud@france-se To: Multiple recipients
of list ORACLE-L <[EMAIL PROTECTED]>
cours.com> cc:
Sent by: Subject: resize in 7.3.4
urgent
[EMAIL PROTECTED]
07/17/2001 12:05 PM
Please respond to
ORACLE-L
hi,
i want if any body try to resize a datafile to a lower value.
i'm on oracle 7.3.4 on hpunix 10.0
thanks in advance
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Djaroud Salim
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
