Rebuilding/Moving master table, snapshot log and snapshot

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).