RE: coalesce tablespace

Michal: You mention the select for the view dba_free_space_coalesced. I'm not sure what you're actually doing. However, if I "set time on" and issue the command

        select * from dba_free_space_coalesced

here's elapsed time on my VMS system

        start:  16:07:44
        end:    16:07:59

here's elapsed time on my Solaris system
        start:  16:05:21        
        end     16:05:29

The 2 databases are sized comparably. (The Solaris box has more power)

Just selecting from the view should be almost instantaneous. Sounds like something else is going on on the VMS box.

HTH.
Barb

> ----------
> From: Mohammad Rafiq[SMTP:rafiq9857_at_hotmail.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Monday, September 24, 2001 4:07 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: coalesce tablespace
>
> Is it working or not? Have you done coalesing or not?
> As regard timings, it depends on system and number of objects on a
> database?
>
> Regards
>
> MOHAMMAD RAFIQ
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Mon, 24 Sep 2001 06:35:26 -0800
>
> Hi,
> how is defined view dba_free_space_coalesced?
> What reason can be, that SELECT response on this view takes me in databese
> on Win NT several seconds while in database on OpenVMS it takes several
> minutes (databases are similar).
> Thanks for suggestions
> Michal
>
>
>
>
>
>
> -----Původní zpráva-----
> Od: Mohammad Rafiq [mailto:rafiq9857_at_hotmail.com]
> Odesláno: 20. září 2001 22:51
> Komu: Multiple recipients of list ORACLE-L
> Předmět: RE: coalesce tablespace
>
>
> Following script may be used to check whether coalesing is required or
> not.
> If lasr column not 100% then coalesce that tbs....
>
> select substr(tablespace_name,1,10)TS_NAME,total_extents
> "Total_Extnts",extents_coalesced,round(percent_extents_coalesced,0)
> from dba_free_space_coalesced
> /
>
>
> MOHAMMAD RAFIQ
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Thu, 20 Sep 2001 12:21:21 -0800
>
> It would take contiguous free extents and make them larger extents, which
> would be more likely to reuse. Especially if there are many smaller ones,
> this moot if using LMT.
>
> It is a very quick procedure and good to do occasional, you can check in
> dba_data_files_coalesced to see if the number is far from 100%, if it is
> less than 75% or so, just throw a coalesce on the tablespace.
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
>
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax: (707) 885-2275
>
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
>
>
> -----Original Message-----
> Sent: Thursday, September 20, 2001 3:40 PM
> To: Multiple recipients of list ORACLE-L
>
> We have tablespaces in acceptance and production that are being resized
> for
> growth. Pctincrease is set at 0. Would it also help to coalesce the
> tablespace? What are the benefits of this command?
> Thanks,
> Sandi
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Sandi.McClary_at_flyingj.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: Christopher Spence
> INET: cspence_at_FuelSpot.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).
>
>
> _________________________________________________________________
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?ISO-8859-2?Q?Skurský_Michal? INET: skursky_at_brn.pvt.cz
>
> 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).
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
  INET: bakerb_at_rockymountainnews.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).