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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Patricia Ashe > INET: [EMAIL PROTECTED] > > 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: [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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mark Leith > INET: [EMAIL PROTECTED] > > 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: [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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hillman, Alex > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > ---
RE: move schema away from system table space
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: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] 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: [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).
RE: move schema away from system table space
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: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] 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: [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).
RE: move schema away from system table space
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: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] 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: [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).
RE: move schema away from system table space
1. Export the user's schema ( exp help=y ) 2. issue in sqlplus: drop user_name cascade; 3. then remake the user with the new table space as the default tablespace ( all permissions there - none elsewhere ). 4. imp the user from the export dump with ignore=y regards - john f. lewis -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: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Lewis INET: [EMAIL PROTECTED] 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: [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).
move schema away from system table space
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: [EMAIL PROTECTED] 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: [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).
