RE: Better Way Of DeFragmentation

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Fri, 21 Sep 2001 08:51:34 -0700
Message-ID: <F001.00394CB8.20010921085020@fatcity.com>

Agreed.

-----Original Message-----

Sent: Thursday, September 20, 2001 3:55 PM To: Multiple recipients of list ORACLE-L

True. If he clones the whole database each way. My understanding was that he planned just to move the schema into a dummy database, resize the objects, do a cold backup, and move the datafiles from the cold backup that contained the appropriate tablespaces. I don't think that that would work.

                    "Miller, Jay"

                    <JayMiller_at_TDWater        To:     Multiple recipients of
list ORACLE-L      
                    house.com>                <ORACLE-L_at_fatcity.com>

                    Sent by:                  cc:

                    root_at_fatcity.com          Subject:     RE: Better Way Of

DeFragmentation

                    09/20/2001 02:31

                    PM

                    Please respond to

                    ORACLE-L

My understanding that he was copying the control files also - i.e., cloning the 2nd database back to the first machine. This works since he said it's usually read only so there wouldn't have been any changes between when he cloned 1 to 2 and when he clones 2 to 1.

-----Original Message-----

Sent: Thursday, September 20, 2001 11:31 AM To: Multiple recipients of list ORACLE-L

Consider going to tablespaces where all the extents are of a uniform size. I.e., all objects in any one tablespace have the same INITIAL and NEXT storage parameters. These tablespaces will never need to be defragmented.

I'm not sure that your outlined procedure will work. I think that your cold backup from the 2nd machine will be out of synch (timestamp-wise) with your control files. It may be possible to do some recovery to overcome this but I'm not familiar with that.

You could do an export from the 2nd machine, drop the tablespace(s) on the 1st machine, and do in import.

If you have data that is changing a lot over time, then you could get set all the objects' NEXT parameter to a uniform size and the tablespaces will coalesce naturally over time.

                    Harvinder Singh

                    <Harvinder.Singh_at_Metr        To:     Multiple

recipients
of list ORACLE-L

                    aTech.com>                   <ORACLE-L_at_fatcity.com>

                    Sent by:                     cc:

                    root_at_fatcity.com             Subject:     Better Way Of

DeFragmentation

                    09/20/2001 10:35 AM

                    Please respond to

                    ORACLE-L

Hi,

We have to defragment one of our production databases..(objects are not properly sized)..
Since i can't shutdown database for more than 2 hrs and database is read only and DML
statements run thru batch jobs 2 days in month. We r thinking of using the following scheme.

export the particular application schema name NMDD of production database.
On second machine run the DDL script which creates all the objects with proper sizing.
Import the data from export taken in step 1.
take the cold backup of 2nd machine.
copy the backup files from 2nd machine to production machine.....

Is there any better way to get rid of fragmentation without using any third party tool..
or Is there any flaw in above procedure.

Thanks
-Harvinder
--

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

Author: Harvinder Singh
INET: Harvinder.Singh_at_MetraTech.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

--

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

Author:
INET: tday6_at_csc.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: Miller, Jay
INET: JayMiller_at_TDWaterhouse.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

--

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

Author:
INET: tday6_at_csc.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

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