Good evening dear sir from down under (It's 22:07, here in Connecticut).
Yes, it is safe to do. The only case when you do have to be careful is when
the table you want to take for a ride into another tablespace is an index
organized table and is being used. Then, you have to specify "online" option.
I was moving aound indexes, tables and partitions, without any problems. I
even moved a SYS table (AUD$) into another tablespace. No problem whatsoever.
On 2003.10.06 22:34, "Kaing, Leng" wrote:
> Good morning, everyone,
>
> > Env: Oracle 8.1.7.4, Sun 5.8
> >
> > We have a master table with a FREST REFRESH based on PRIMARY KEY. We would
> like to rebuild the master table, snapshot log and snapshot to reclaim
> space.
> There is no advanced replication involved. It is simply a snapshot (or
> materialized view) that's refreshed at certain intervals from a dbms_job.
> >
> > We'd like to know whether we can safely rebuild the tables using the ALTER
> TABLE MOVE command as such:
> >
> > 1) stop all client connects, and DBMS_JOBS on master and destination site
> > 2) issue another refresh of snapshot so that the MLOG$ table is cleared.
> > 3) on master site, issue the command: ALTER TABLE <tablename> MOVE
> TABLESPACE <new tablespace> NOLOGGING;
> > 4) on master site, issue the command: ALTER TABLE MLOG$_<tablename> MOVE
> TABLESPACE <new tablespace> NOLOGGING;
> > 5) on the destination site, issue the command: ALTER TABLE <tablename>
> MOVE
> TABLESPACE <new tablespace> NOLOGGING;
> > 6) reenable DBMS_JOBS and client connections
> >
> Have you done it before? Is it safe to do?
>
> TIA,
>
> Leng.
>
> > ----------------------------------------------------------
> > Leng Kaing
> > Email: leng.kaing_at_team.telstra.com
> > Phone: +61-3-9203-7589
> > Mobile: +61-417-371-348
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Kaing, Leng
> INET: Leng.Kaing_at_team.telstra.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).
>
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: mgogala_at_adelphia.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).
Received on Mon Oct 06 2003 - 22:14:30 CDT
