You can only shrink data to the files high water mark.
See MetaLink docs 130866.1 and 1029252.6
Jared
"Meng, Dennis" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/16/2003 01:40 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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
>>> [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/16/2003 07:44 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Meng, Dennis
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
