Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Convert DM TSP to LM TSP w/ lots of data

RE: Convert DM TSP to LM TSP w/ lots of data

From: <Govind.Arumugam_at_alltel.com>
Date: Thu, 31 Oct 2002 18:23:34 -0800
Message-ID: <F001.004F95CC.20021031182334@fatcity.com>


Tom,

Thanks for the script authored by Cary Millsap. I just attended Cary Millsap's hotsos performance tuning clinic in Dallas, Texas. He is a great guy! You can get a lot of technical info on their web-site at www.hotsos.com

Regards,
Govind

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

Sent: Wednesday, October 30, 2002 11:04 PM To: Multiple recipients of list ORACLE-L

Govind,

It occured to me that I suggested you map the location of extents within the
data files which make up the DMT you're moving objects from but didn't tell
you how. If you have cool GUI tool like OEM Tablespace Manager to show you
precisely where each extent is located in a data file, cool. If not, this
script I lifted from Metalink will work.



Abstract:

This script provides a block-level mapping of the tables inside one or more tablespaces. You will be prompted for the name of the tablespace.

Requirements:

SELECT on DBA_EXTENTS and DBA_FRE_SPACE

Version Testing:

This script was tested on Oracle Versions 7.3.4.X - 8.1.6.XX.

Script:

REM inside one or more tablespaces.

REM


---

REM EXPLANATION:

REM    Provides a block-level mapping of tables inside of a given
tablespace.

REM

------------------------------------------------------------------------
--- REM REM
------------------------------------------------------------------------
--- REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM
------------------------------------------------------------------------
-- REM Main text of script follows: def ts = &&1 col tablespace form a15 head 'Tablespace' just c trunc col file_id form 990 head 'File' just c col block_id form 9,999,990 head 'Block Id' just c col blocks form 999,990 head 'Size' just c col segment form a38 head 'Segment' just c trunc break - on tablespace skip page - on file_id skip 1 select tablespace_name tablespace, file_id, 1 block_id, 1 blocks, '<file hdr>' segment from dba_extents where tablespace_name = upper('&ts') union select tablespace_name tablespace, file_id, 1 block_id, 1 blocks, '<file hdr>' segment from dba_free_space where tablespace_name = upper('&ts') union select tablespace_name tablespace, file_id, block_id, blocks, owner||'.'||segment_name segment from dba_extents where tablespace_name = upper('&ts') union select tablespace_name tablespace, file_id, block_id, blocks, '<free>' from dba_free_space where tablespace_name = upper('&ts') order by 1,2,3 8 / undef ts
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 30, 2002 7:48 PM Tom and Dennis, Thanks a lot for your input on this. I liked the phased approach to move objects to LMT. Govind
-----Original Message-----
Sent: Tuesday, October 29, 2002 7:29 PM To: Multiple recipients of list ORACLE-L If you have to convert in place, do it. But let's examine things a bit more closely first. You say you have tablespaces sized from 5 GB to 60 GB and you don't have much freespace as a buffer. Do you have 60 GB buffer? If so, you're home free. Create a new LMT, move the objects from the DMT to the new LMT, drop the old tablespace including contents and delete the data files. You don't have 60 GB free space? Create a map of where each object resides in the data files which make up the DMT. Create a new LMT tablespace with as many files as you can fit in your free space.. Move the object from DMT to LMT that'll allow you to shrink the data files in the DMT as much as possible. Now you have more free space. Continue adding data files to the LMT as needed as you move each object and shrink the DMT's data files. When the DMT is empty, drop it including contents and delete the data files. That's one DMT converted. Now on to the next ones. If you're just embarking on conversion to LMT and permanent rollback segments, convert your rollback tablespaces first. You'll get a performance boost from not having to go through the ST enqueue when extending or shrinking your rollback segments. Replace your temporary tablespaces with ones consisting of temp files then edit your hot backup script to stop backing up your temporary tablespaces. Your SA will love you for it. Make sure you dump your control file to trace when you change your backup script.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, October 29, 2002 11:48 AM This is what we have in mind. For the partitioned tables that are going to have the 'rotation' logic, we will convert them to LMT when they become empty. We drop the 'oldest' partition and create a new one for next month. This will be carried out every month. Partition for November data is empty now and is being converted to use LMT as of this week; Next month we will do the same for December partition; This way, we would convert all our 5 CALLDETAIL tablespaces to use LMT in 5 months time. Now, for other tablespaces that contain data and indexes, we need to decide whether we should create corresponding LMT tablespaces and move the existing objects to it or change them using the procedure that Oracle recommends; Some of these tablespaces are sized anywhere from 5Gb to 60Gb and we do not have much of freespace as buffer. Our database size is about 1.2 tera bytes. Also for this we might have to ask for a bigger maintenance window which will be hard to get. So we are toying up with the idea of converting them from DMT to LMT using 'in place' mechanism and rebuild the tables/indexes eventually as time permits. Obviously, we are not trying cause any outage knowingly. Hence these questions to the forum. Thanks, Govind
-----Original Message-----
Sent: Tuesday, October 29, 2002 8:49 AM To: Multiple recipients of list ORACLE-L Govind - I agree with Tom. Since you phrased your original question asking for those who had done the conversion, the silence was your answer. If you had also asked for replies from those who had decided to build new tablespaces and convert the data ourselves, then more of us could have replied. My reason for not using the conversion utility has to do with the smell test. After you've been a DBA for awhile, you realize you have limited time and you lose your eagerness to pursue things that will eat up enormous amounts of time and come to nothing. In my situation, I was able to move a table at a time to LMT as time permitted, and somehow I felt that the resulting data situation would be better. I believe some of the new nologging options may allow you to perform this while the table is still available for DML. When first introduced, the conversion routines had some bugs. I don't think the conversion routines are bad today, so they may work just fine. Dennis Williams DBA, 40%OCP Lifetouch, Inc. dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Tuesday, October 29, 2002 12:34 AM To: Multiple recipients of list ORACLE-L I tried having Oracle convert DMT to LMT out a few times on 10 GB tablespaces. Whether or not that's a lot of data depends on you. Worked like a champ. And this was in the early days of LMT. People who pay a lot of attention to the internals will warn you that converting a DMT to LMT is not optimal. Your bitmaps wind up at the end of the files instead of the headers. I've read posts here and elsewhere where people worried about the same issues as they did with DMT. They wanted to limit the number of extents segments grew to by creating tablespaces with different sized fixed extents. A DBA I worked with pointed out how very easy and quickly things worked with letting Oracle automatically size the next extent. I've gone that way whenever possible and have never regretted it. I've not done a rigorous benchmark but can tell you from casual observation dropping an object with lots of extents doesn't happen in the blink of an eye but doesn't lock out all other space allocation as UET/FET weren't constantly in use and the ST enque wasn't locked for exclusive use. It's prudent to follow Oracle's recommendations if conditions permit. Create a new LMT and move your data to it.
----- Original Message -----
To: Multiple <mailto:ORACLE-L_at_fatcity.com> recipients of list ORACLE-L Sent: Monday, October 28, 2002 8:43 PM I am reposting this. Has anyone tried to convert a dictionary managed table space containing lots of data to locally managed? We have tried this out successfully on empty table spaces or created a new LMT tablespace and moved the existing objects to it. Thanks, Govind -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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: Govind.Arumugam_at_alltel.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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: Tom Pall INET: tpall_at_realtime.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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: Govind.Arumugam_at_alltel.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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: Tom Pall INET: tpall_at_realtime.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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: Govind.Arumugam_at_alltel.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Thu Oct 31 2002 - 20:23:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US