RE: MOVE Tables from One Tablespace to Another Tablespace !!
Need to add accounting for LOB indexes or simply not to add whenever SQL error ... alter index .. rebuild does not work for LOB indexes. Alex Hillman -Original Message- Sent: Tuesday, August 21, 2001 1:57 PM To: Multiple recipients of list ORACLE-L I just did this yesterday. Here are the scripts I used; sqlplus system/syspaswd set pagesize 0 set feedback off spool mv_tbls.sql select 'alter table '|| owner || '.' || segment_name || ' move tablespace new_tblspc;' from dba_segments where segment_type='TABLE' and tablespace_name='old_tblspc'; spool off; spool alter_idxs.sql select 'alter index ' || i.table_owner || '.' || i.index_name || ' rebuild;' from dba_indexes i, dba_segments s where s.segment_name=i.table_name and s.owner=i.table_owner and s.segment_type='TABLE' and s.tablespace_name='old_tblspc'; spool off; this is from memory, so check the spooled scripts before running... -Original Message- INF/MEKKAOUI Sent: Tuesday, August 21, 2001 5:41 AM To: Multiple recipients of list ORACLE-L hi, the command is : Alter table table_name move tablespace tablespace_name; But be careful from index corruption. Best Regards, Nabila Mekkaoui DBA Oracle -Message d'origine- De : Dash, Saroj (CAP,CEF) [mailto:[EMAIL PROTECTED]] Envoyé : mardi 21 août 2001 08:16 À : Multiple recipients of list ORACLE-L Objet : MOVE Tables from One Tablespace to Another Tablespace !! Hello All, Please tell me the detailed steps to move tables from one tablespace to another tablespace. Regards, Saroj. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dash, Saroj (CAP,CEF) 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: INF/MEKKAOUI 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: Glenn Travis 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 Tables from One Tablespace to Another Tablespace !!
I just did this yesterday. Here are the scripts I used; sqlplus system/syspaswd set pagesize 0 set feedback off spool mv_tbls.sql select 'alter table '|| owner || '.' || segment_name || ' move tablespace new_tblspc;' from dba_segments where segment_type='TABLE' and tablespace_name='old_tblspc'; spool off; spool alter_idxs.sql select 'alter index ' || i.table_owner || '.' || i.index_name || ' rebuild;' from dba_indexes i, dba_segments s where s.segment_name=i.table_name and s.owner=i.table_owner and s.segment_type='TABLE' and s.tablespace_name='old_tblspc'; spool off; this is from memory, so check the spooled scripts before running... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of INF/MEKKAOUI Sent: Tuesday, August 21, 2001 5:41 AM To: Multiple recipients of list ORACLE-L hi, the command is : Alter table table_name move tablespace tablespace_name; But be careful from index corruption. Best Regards, Nabila Mekkaoui DBA Oracle -Message d'origine- De : Dash, Saroj (CAP,CEF) [mailto:[EMAIL PROTECTED]] Envoyé : mardi 21 août 2001 08:16 À : Multiple recipients of list ORACLE-L Objet : MOVE Tables from One Tablespace to Another Tablespace !! Hello All, Please tell me the detailed steps to move tables from one tablespace to another tablespace. Regards, Saroj. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dash, Saroj (CAP,CEF) 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: INF/MEKKAOUI 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: Glenn Travis 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 Tables from One Tablespace to Another Tablespace !!
But make sure you rebuild indexes afterwards. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Tuesday, August 21, 2001 4:26 AM To: Multiple recipients of list ORACLE-L 8i -> alter tbale move tablespace new_tbs > -- > From: Dash, Saroj (CAP,CEF)[SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Tuesday, August 21, 2001 3:15 PM > To: Multiple recipients of list ORACLE-L > Subject: MOVE Tables from One Tablespace to Another Tablespace !! > > > Hello All, > > > Please tell me the detailed steps to move tables from one tablespace > to another tablespace. > > Regards, > Saroj. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Dash, Saroj (CAP,CEF) > 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: Rahul 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: Christopher Spence 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 Tables from One Tablespace to Another Tablespace !!
Saroj, And use the ALTER TABLE xxx REBUILD command to rebuild the indexes for the tables that were moved. This is because the "moved" table's records now have new rowids, which of course, do not match with what is in the indexes. You can verify this. After moving the tables, check the status of your indexes. If the index is status'ed as "UNUSABLE" then it needs to be rebuilt. Good luck!! HTH. Chris "May Oracle be with you...always" -Original Message- Sent: Tuesday, August 21, 2001 5:56 AM To: Multiple recipients of list ORACLE-L Hi, in the 2nd option , you have to use move instead of default. i.e. alter table tablename move tablespace tablespacename rukmini - Original Message - To: <[EMAIL PROTECTED]> Sent: Tuesday, August 21, 2001 1:51 PM > Hi, > > 1. Export the tables in 1st tablespace > 2. drop the tables from 1st tablespace > 2. import the tables into 2nd tablespace > or >alter table tablename default tablespace tablespacename > > rukmini > > - Original Message - > From: "Dash, Saroj (CAP,CEF)" <[EMAIL PROTECTED]> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, August 21, 2001 1:45 PM > Subject: MOVE Tables from One Tablespace to Another Tablespace !! > > > > > > Hello All, > > > > > > Please tell me the detailed steps to move tables from one tablespace to > > another tablespace. > > > > Regards, > > Saroj. > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Dash, Saroj (CAP,CEF) > > 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: Rukmini Devi 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: Grabowy, Chris 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 Tables from One Tablespace to Another Tablespace !!
Before importing, make sure you set the schema owner's quota to 0 for the first tablespace. Otherwise, import will create the tables in the first tablespace again. Dennis Rukmini Devi wrote: > Hi, > > 1. Export the tables in 1st tablespace > 2. drop the tables from 1st tablespace > 2. import the tables into 2nd tablespace > or >alter table tablename default tablespace tablespacename > > rukmini > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, August 21, 2001 1:45 PM > > > > > Hello All, > > > > > > Please tell me the detailed steps to move tables from one tablespace to > > another tablespace. > > > > Regards, > > Saroj. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler 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 Tables from One Tablespace to Another Tablespace !!
Hi, in the 2nd option , you have to use move instead of default. i.e. alter table tablename move tablespace tablespacename rukmini - Original Message - To: <[EMAIL PROTECTED]> Sent: Tuesday, August 21, 2001 1:51 PM > Hi, > > 1. Export the tables in 1st tablespace > 2. drop the tables from 1st tablespace > 2. import the tables into 2nd tablespace > or >alter table tablename default tablespace tablespacename > > rukmini > > - Original Message - > From: "Dash, Saroj (CAP,CEF)" <[EMAIL PROTECTED]> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, August 21, 2001 1:45 PM > Subject: MOVE Tables from One Tablespace to Another Tablespace !! > > > > > > Hello All, > > > > > > Please tell me the detailed steps to move tables from one tablespace to > > another tablespace. > > > > Regards, > > Saroj. > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Dash, Saroj (CAP,CEF) > > 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: Rukmini Devi 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 Tables from One Tablespace to Another Tablespace !!
Hi, 1. Export the tables in 1st tablespace 2. drop the tables from 1st tablespace 2. import the tables into 2nd tablespace or alter table tablename default tablespace tablespacename rukmini - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, August 21, 2001 1:45 PM > > Hello All, > > > Please tell me the detailed steps to move tables from one tablespace to > another tablespace. > > Regards, > Saroj. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Dash, Saroj (CAP,CEF) > 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: Rukmini Devi 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 Tables from One Tablespace to Another Tablespace !!
hi, the command is : Alter table table_name move tablespace tablespace_name; But be careful from index corruption. Best Regards, Nabila Mekkaoui DBA Oracle -Message d'origine- De : Dash, Saroj (CAP,CEF) [mailto:[EMAIL PROTECTED]] Envoyé : mardi 21 août 2001 08:16 À : Multiple recipients of list ORACLE-L Objet : MOVE Tables from One Tablespace to Another Tablespace !! Hello All, Please tell me the detailed steps to move tables from one tablespace to another tablespace. Regards, Saroj. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dash, Saroj (CAP,CEF) 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: INF/MEKKAOUI 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 Tables from One Tablespace to Another Tablespace !!
8i -> alter tbale move tablespace new_tbs > -- > From: Dash, Saroj (CAP,CEF)[SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Tuesday, August 21, 2001 3:15 PM > To: Multiple recipients of list ORACLE-L > Subject: MOVE Tables from One Tablespace to Another Tablespace !! > > > Hello All, > > > Please tell me the detailed steps to move tables from one tablespace to > another tablespace. > > Regards, > Saroj. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Dash, Saroj (CAP,CEF) > 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: Rahul 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).
