Re: Altering Indexes
alter index schema.index_name rebuild new_tablespace. You can create a script to move all of them as follows: select 'alter index schema.'||index_name||' rebuild tablespace NEW_TABLESPACE;' from dba_indexes where owner='SCHEMA'; hth, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 9:25 AM I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Ruth Gramolini 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: Altering Indexes
Ken,
Why not generate the alter index {} rebuild tablespace {} commands using
sql,
break the resulting commands up into several different files, and run them
at the same time?
Probably the easiest (and fastest) way to go.
The generate script would be:
set head off
set pages 1000
spool move_indexes.sql
select 'alter index ' || index_name || ' rebuild tablespace
{new_tablespace_name};'
from user_indexes
spool off
Take the move_indexes.sql script, and break it up into, say 5 files and run
5 sql sessions at once.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, November 27, 2001 9:25 AM
To: Multiple recipients of list ORACLE-L
I have this large DB (approx. 250 tables) that has the tables and indexes in
the same tablespace (not my design). Is there a way I can move all of the
indexes to a separate tablespace en-mass? I know I can move them one at a
time with the alter index command, but that would be rather time consuming.
Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient System, Inc.
Minneapolis, MN
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ken Janusz
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: Mercadante, Thomas F
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: Altering Indexes
Ken, Try executing the following: select 'I am rebuilding my index '||index_name||' and putting it in another tablespace;' from user_indexes / and of course, spool the sucker, set heading off, set pagesize. Merry Spooling and Happy Selecting [EMAIL PROTECTED] 11/27/01 08:25AM I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Jeff Wiegard 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: Altering Indexes
I've never heard of a mass move index utility. What I did in a similar situation is script it, with something like this: SET head OFF feed ON echo OFF verify OFF pagesize 0 linesize 65 trimspool on -- spool ind_fix.sql -- select 'alter index '||index_name||' rebuild ' ||CHR(10)||' tablespace ts name here' ||CHR(10)||' storage (storage stuff here);' from user_indexes / -- spool off -- SET head ON feed ON echo ON verify ON pagesize 50 linesize 200 Then run the generated script. This was on a database with over 2300 indexes (and 1400 tables). Most weren't that large, so it didn't take too long. I was doing this to put the indexes in a tablespace with uniform extent sizes, so the storage parameters were the same for every index. Kent I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kent Wayson 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: Altering Indexes
You can't do it en-mass. Why not write a script to do this. select 'alter index '||index_name||' rebuild new_tbsp;' from user_indexes; or somthing similar. Terry -Original Message- Sent: Tuesday, November 27, 2001 8:25 AM To: Multiple recipients of list ORACLE-L I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Ball, Terry 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: Altering Indexes
no way that I know of, you need to move each index on its own --- Ken Janusz [EMAIL PROTECTED] wrote: I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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). __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Altering Indexes
maybe even moving the largest tables to their own tablespace first. Just remember that if you move a table, all its indexes will be invalid, so you may want to rebuild the indexes for each table right after the table is moved. If you just move the indexes, only the index being moved is temporarily unavailable. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kent Wayson 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: Altering Indexes
Dennis: Thanks for the advice. Fortunately I am doing a DB conversion from IMS / Lotus Notes to 8.1.7. This is being done on a dedicated server which only I have access to and there is no application software connected to it. When I finish the data conversion the data will be exported (for want of a better word) to the production DB where it will be tested. So, taking time do something is not a problem. Currently the only data I have on the system is a small lookup table. So, the process would probably go rather quickly. Ken -Original Message- Sent: Tuesday, November 27, 2001 9:41 AM To: Multiple recipients of list ORACLE-L Subject:RE: Altering Indexes Ken - No, there isn't a magic single command. Moving the indexes, even with alter index, cause a lot of work for Oracle, and can consume quite a bit of time. As the typical cautious production DBA, I do not like to start a really large monolithic process that might make my system unavailable to the users for an unknown period of time. I would recommend that you use SQL to create a script to alter the indexes. This will allow you to Pareto's rule to do the many small indexes first, then work up to the larger indexes that use increasing amounts of time. Also, I would recommend considering several index tablespaces, maybe even moving the largest tables to their own tablespace first. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 27, 2001 8:25 AM To: Multiple recipients of list ORACLE-L I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: DENNIS WILLIAMS 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: Ken Janusz 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: Altering Indexes
Just a question off the wall here - kind of related: Does anybody know why Oracle does NOT give the option to have a DEFAULT INDEX TABLESPACE along with TEMPORARY and DEFAULT (for Tables for example).. I know, I know it's just another thing to add to your syntax - but it would be a FAR better way of doing things wouldn't it? I would much prefer to say: create user mark identified by beer default table tablespace USER_DATA quota 100 M on USER_DATA default index tablespace USER_IDXS quota 100 M on USER_IDXS temporary tablespace TEMP; as this totally takes away the nightmare of having them all created in one single tablespace.. Anyone with an in with Oracle know the answer? Anyone care to speculate? : Cheers Mark -Original Message- Sent: 27 November 2001 15:20 To: Multiple recipients of list ORACLE-L You can't do it en-mass. Why not write a script to do this. select 'alter index '||index_name||' rebuild new_tbsp;' from user_indexes; or somthing similar. Terry -Original Message- Sent: Tuesday, November 27, 2001 8:25 AM To: Multiple recipients of list ORACLE-L I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Ball, Terry 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: Altering Indexes
Wonderful idea, Mark. We have scores of users who create their own tables, indexes etc since we are a scientific research institution. It's hard to get users to put in that extra code to place the index in it's own tablespace. Shirley -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 12:51 PM To: Multiple recipients of list ORACLE-L Subject:RE: Altering Indexes Just a question off the wall here - kind of related: Does anybody know why Oracle does NOT give the option to have a DEFAULT INDEX TABLESPACE along with TEMPORARY and DEFAULT (for Tables for example).. I know, I know it's just another thing to add to your syntax - but it would be a FAR better way of doing things wouldn't it? I would much prefer to say: create user mark identified by beer default table tablespace USER_DATA quota 100 M on USER_DATA default index tablespace USER_IDXS quota 100 M on USER_IDXS temporary tablespace TEMP; as this totally takes away the nightmare of having them all created in one single tablespace.. Anyone with an in with Oracle know the answer? Anyone care to speculate? : Cheers Mark -Original Message- Sent: 27 November 2001 15:20 To: Multiple recipients of list ORACLE-L You can't do it en-mass. Why not write a script to do this. select 'alter index '||index_name||' rebuild new_tbsp;' from user_indexes; or somthing similar. Terry -Original Message- Sent: Tuesday, November 27, 2001 8:25 AM To: Multiple recipients of list ORACLE-L I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Ball, Terry 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: Taylor, Shirley 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
Re: Altering Indexes
Mark Leith wrote: Just a question off the wall here - kind of related: Does anybody know why Oracle does NOT give the option to have a DEFAULT INDEX TABLESPACE along with TEMPORARY and DEFAULT (for Tables for example).. I know, I know it's just another thing to add to your syntax - but it would be a FAR better way of doing things wouldn't it? I would much prefer to say: create user mark identified by beer default table tablespace USER_DATA quota 100 M on USER_DATA default index tablespace USER_IDXS quota 100 M on USER_IDXS temporary tablespace TEMP; as this totally takes away the nightmare of having them all created in one single tablespace.. Anyone with an in with Oracle know the answer? Anyone care to speculate? : Cheers Mark Mark, Good suggestion. As far as one usually finds more indices in the data tablespace than the reverse, I have flirted with the idea of making the tablespace devoted to indices the default one, but it's changing the problem. But I have something to suggest : create user mark identified by beer default table tablespace SYSTEM quota 100 M on USER_DATA quota 100 M on USER_IDXS temporary tablespace TEMP; (assuming of course that you do not have the UNLIMITED TABLESPACE privilege). My bet is that it won't take long before you remember to always specify the tablespace, yek, yek, yek. In case using SYSTEM would make you (understandably) uncomfortable, you can create say a 50K BARELAND tablespace on which nobody has quotas. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Altering Indexes
The idea is wonderful and also very old. It was one of the first enhancement requests submitted. As far as I can tell Oracle has never shown an interest in it. Indeed it appears to be going the other way. Take LOB's for instance. You can place the LOB segment in a separate tablespace from the rest of the table's data, but the lob_index is going to go in the same tablespace as the lob_segment. The documentation states: This clause [lob_index] is deprecated as of Oracle8i. Oracle generates an index for each LOB column. Oracle names and manages the LOB indexes internally. Although it is still possible for you to specify this clause, Oracle Corporation strongly recommends that you no longer do so. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 27, 2001 9:32 AM To: Multiple recipients of list ORACLE-L Wonderful idea, Mark. We have scores of users who create their own tables, indexes etc since we are a scientific research institution. It's hard to get users to put in that extra code to place the index in it's own tablespace. Shirley -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 12:51 PM To: Multiple recipients of list ORACLE-L Subject:RE: Altering Indexes Just a question off the wall here - kind of related: Does anybody know why Oracle does NOT give the option to have a DEFAULT INDEX TABLESPACE along with TEMPORARY and DEFAULT (for Tables for example).. I know, I know it's just another thing to add to your syntax - but it would be a FAR better way of doing things wouldn't it? I would much prefer to say: create user mark identified by beer default table tablespace USER_DATA quota 100 M on USER_DATA default index tablespace USER_IDXS quota 100 M on USER_IDXS temporary tablespace TEMP; as this totally takes away the nightmare of having them all created in one single tablespace.. Anyone with an in with Oracle know the answer? Anyone care to speculate? : Cheers Mark -Original Message- Sent: 27 November 2001 15:20 To: Multiple recipients of list ORACLE-L You can't do it en-mass. Why not write a script to do this. select 'alter index '||index_name||' rebuild new_tbsp;' from user_indexes; or somthing similar. Terry -Original Message- Sent: Tuesday, November 27, 2001 8:25 AM To: Multiple recipients of list ORACLE-L I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Ball, Terry 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
RE: Altering Indexes
I thought that, given disks with 256KB to 4M of ondisk cache and disk arrays with another 1M-16M of cache and storage boxes like EMC,Hitachi, etc with up to 16 GB of CACHE and the UBC and the Oracle BC and Henry Poras' recent post that tables and indexes in the same tspace didn't matter quite as much as it did when I was a youngster? Not that it doesn't matter, just that it's not in the Top Three Evildoers List anymore. - ross -Original Message- Sent: Tuesday, November 27, 2001 3:40 PM To: Multiple recipients of list ORACLE-L The idea is wonderful and also very old. It was one of the first enhancement requests submitted. As far as I can tell Oracle has never shown an interest in it. Indeed it appears to be going the other way. Take LOB's for instance. You can place the LOB segment in a separate tablespace from the rest of the table's data, but the lob_index is going to go in the same tablespace as the lob_segment. The documentation states: This clause [lob_index] is deprecated as of Oracle8i. Oracle generates an index for each LOB column. Oracle names and manages the LOB indexes internally. Although it is still possible for you to specify this clause, Oracle Corporation strongly recommends that you no longer do so. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 27, 2001 9:32 AM To: Multiple recipients of list ORACLE-L Wonderful idea, Mark. We have scores of users who create their own tables, indexes etc since we are a scientific research institution. It's hard to get users to put in that extra code to place the index in it's own tablespace. Shirley -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 12:51 PM To: Multiple recipients of list ORACLE-L Subject:RE: Altering Indexes Just a question off the wall here - kind of related: Does anybody know why Oracle does NOT give the option to have a DEFAULT INDEX TABLESPACE along with TEMPORARY and DEFAULT (for Tables for example).. I know, I know it's just another thing to add to your syntax - but it would be a FAR better way of doing things wouldn't it? I would much prefer to say: create user mark identified by beer default table tablespace USER_DATA quota 100 M on USER_DATA default index tablespace USER_IDXS quota 100 M on USER_IDXS temporary tablespace TEMP; as this totally takes away the nightmare of having them all created in one single tablespace.. Anyone with an in with Oracle know the answer? Anyone care to speculate? : Cheers Mark -Original Message- Sent: 27 November 2001 15:20 To: Multiple recipients of list ORACLE-L You can't do it en-mass. Why not write a script to do this. select 'alter index '||index_name||' rebuild new_tbsp;' from user_indexes; or somthing similar. Terry -Original Message- Sent: Tuesday, November 27, 2001 8:25 AM To: Multiple recipients of list ORACLE-L I have this large DB (approx. 250 tables) that has the tables and indexes in the same tablespace (not my design). Is there a way I can move all of the indexes to a separate tablespace en-mass? I know I can move them one at a time with the alter index command, but that would be rather time consuming. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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: Ball, Terry 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
