Re: Database Reorg Question

I agree with Jared.

I have been doing that gradually with a couple of my most critical databases. One thing to remember is that when you move a partition, you invalidate indexes and need to rebuild them.

Also, to recover space, when a tablespace is empty because you've moved everything to a new LMT (uniform I suggest) tablespace, drop the tablespace and rm the files.

Good luck and happy shuffling.
Stephen

>>> Jared.Still_at_radisys.com 01/16/03 12:39PM >>>
Sounds like a lot of work if the only real justification is to save 13G of space.

IMO the real justfication is moving to LMT.

"Meng, Dennis" <Dennis.Meng_at_alcoa.com>
Sent by: root_at_fatcity.com
 01/16/2003 07:44 AM
 Please respond to ORACLE-L  

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

        cc: 
        Subject:        Database Reorg Question

Hi all -
We have a data warehouse database that is very fragmented on the tablespace level and a lot of space can not be reused because the space

'bubbles' are too small to fit the tables. We would like to reorg and reclaim the space ( more than 13G ) by using the 'SAFE' method - moving

tables and indexes to different tablespaces according to their size and

specify storage parameters only on the tablespace level. Also we would

like to kill two birds with one stone by converting to locally managed

tablespace. So here is the plan of attack - Every weekend we will get some downtime and create a new LMT tablespace

and move some tables to this new tablespace. Eventually all tables will

reside in LMT ( except system ts of course ). I have some questions about this though : 1. Does this sound like a good plan?
2. If we use 'alter table move...' command to move the table from a conventional ts to LMT ts, are there any issues? 3. After moving the table from one ts to another, how do we reclaim the
space left by the table? The only way is to shrink the data files, correct?  

TIA   Dennis  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: 
  INET: Jared.Still_at_radisys.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.net
-- 
Author: Stephen Andert
  INET: StephenAndert_at_firsthealth.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).