RE: move schema away from system table space

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Wed, 18 Apr 2001 05:04:14 -0700
Message-ID: <F001.002EBB6A.20010418045524@fatcity.com>

Another option:

Export the users table data.
alter user USERNAME default tablespace NEW_TABLESPACE temporary tablespace TEMP_TABLESPACE;
duplicate the schema with temp table names - tablename_temp
import the data
drop the old tables from the SYSTEM tablespace
alter table TABLENAME_TEMP rename to TABLENAME;

Or:

alter user USERNAME default tablespace NEW_TABLESPACE temporary tablespace TEMP_TABLESPACE;
create table TABLENAME_TEMP as (select * from TABLENAME);
drop existing SYSTEM tables for the user
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

--

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