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: [EMAIL PROTECTED] > 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: [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).
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
