RE: move schema away from system table space

You can move tables on 8i , but not indexes, you have to rebuild indexes in order to move them to other TS.

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 18, 2001 4:20 PM

> If you are on 8i you can move tables and indexes to different tablespaces
> (very easy to do it with EZSQL for example) and then change default
> tablespacefrom system to new one.
>
> Alex Hillman
>
> -----Original Message-----
> Sent: Wednesday, April 18, 2001 8:55 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Another option:
>
> 1. Export the users table data.
> 2. alter user USERNAME default tablespace NEW_TABLESPACE temporary
> tablespace TEMP_TABLESPACE;
> 3. duplicate the schema with temp table names - tablename_temp
> 4. import the data
> 5. drop the old tables from the SYSTEM tablespace
> 6. alter table TABLENAME_TEMP rename to TABLENAME;
>
> Or:
>
> 1. alter user USERNAME default tablespace NEW_TABLESPACE temporary
> tablespace TEMP_TABLESPACE;
> 2. create table TABLENAME_TEMP as (select * from TABLENAME);
> 3. drop existing SYSTEM tables for the user
> 4. alter table TABLENAME_TEMP rename to TABLENAME;
>
> With both of these methods you will not need to shutdown the system at
all.
> The second option should be done at a quite time, as you *could*
(depending
> on the amount of data within these tables) be doing a huge select, and you
> don't want users updating to the table in SYSTEM, as you will loose data
in
> the new tables. If you do have users that are connected 24*7, this could
be
> difficult, but, if you are prepared to make them wait a while, you could
> lock the table in exclusive mode whilst doing the switch and drop.
>
> HTH
>
> Mark
>
>
> -----Original Message-----
> Ashe
> Sent: Wednesday, April 18, 2001 12:36
> To: Multiple recipients of list ORACLE-L
>
>
> Best way is to use export/import utilities. This can be done with server
on
> line and you will get all table data if done at quite time when table is
not
> being updated. See Oracle Backup and Recovery Guide for info on exp/imp.
> PA
> -----Original Message-----
> Sent: Tuesday, April 17, 2001 3:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi All,
> I need to move a user/schema away from the system
> tablespace to a newly created user tablespace. Could
> somebody point me to the references on how to do the
> job? Since a production server is running off the
> schema, is it possible to do the move without having
> to shut down the server?
> Thanks a lot.
> WL
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Auctions - buy the things you want at great prices
> http://auctions.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: lwm
> INET: wmli_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
> --
> Author: Patricia Ashe
> INET: pashe_at_TRUELINK.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
> --
> Author: Mark Leith
> INET: mark_at_cool-tools.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
> --
> Author: Hillman, Alex
> INET: Alex.Hillman_at_usmint.treas.gov
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: Claudio Roca
  INET: croca_at_afip.gov.ar

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).