Re: resize in 7.3.4 urgentresize script

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Tue, 17 Jul 2001 11:27:33 -0700
Message-ID: <F001.0034C8AC.20010717112955@fatcity.com>

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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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:

BACK UP THE DATABASE BEFORE YOU DO THIS
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.
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.
BACK UP THE DATABASE AFTER YOU DO THIS

David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002

                     Djaroud Salim
                     <SDJaroud_at_france-se       To:     Multiple recipients 
of list ORACLE-L <ORACLE-L_at_fatcity.com>
                     cours.com>                cc:
                     Sent by:                  Subject:     resize in 7.3.4 
urgent
                     root_at_fatcity.com


                     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: SDJaroud_at_france-secours.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: DBarbour_at_austin.isd.tenet.edu

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

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: 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