Oracle-L: Re: Shrink datafiles

From: <Rajesh.Rao_at_jpmchase.com>
Date: Wed, 19 Jun 2002 08:18:26 -0800
Message-ID: <F001.00481553.20020619081826@fatcity.com>

To shrink the datafiles, one first needs to find the highwatermark for the datafiles in question. This can be found out from the DBA_EXTENTS view.

Find out the FILE_ID from DBA_DATA_FILES
Find out the maximum BLOCK_ID from DBA_EXTENTS for this FILE_ID, and the BLOCKS for the record with this maximum BLOCK_ID.
HIGHWATERMARK for FILE_ID = (BLOCK_ID + BLOCKS - 1) * Database (or Tablespace, if in 9i) Block Size.

Hope this helps. Anyways, knowing this list, someone must have already sent you the query, by now.

Raj

                                                                                                                    
                    David Turner                                                                                    
                    <turner_at_tellm        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>        
                    e.com>               cc:                                                                        
                    Sent by:             Subject:     Shrink datafiles                                              
                    root_at_fatcity.                                                                                   
                    com                                                                                             
                                                                                                                    
                                                                                                                    
                    June 19, 2002                                                                                   
                    10:28 AM                                                                                        
                    Please                                                                                          
                    respond to                                                                                      
                    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,

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Rajesh.Rao_at_jpmchase.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 - 11:18:26 CDT