| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database Reorg Question
You can only shrink data to the files high water mark.
See MetaLink docs 130866.1 and 1029252.6
Jared
"Meng, Dennis" <Dennis.Meng_at_alcoa.com>
Sent by: root_at_fatcity.com
01/16/2003 01:40 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: Database Reorg Question
Since our database is fairly large and our outage window is limited, I
plan
take a phased approach and move a few tables per weekend. Unfortunately
all
the tables are sitting in the same tablespace ( I just took it over from
last DBA so don't ask ), it is impossible for me to cleanup the tablespace
all at once. In the meantime when I move tables to another tablespace I
need
to reclaim some space for the next round. I should be able to shrink the
datafiles after the table is moved correct?
Provided the datafile does not contain any other table.
Dennis
-----Original Message-----
Sent: Thursday, January 16, 2003 3:01 PM
To: Multiple recipients of list ORACLE-L
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).
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:
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). Received on Thu Jan 16 2003 - 17:34:48 CST
 |
 |