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 doe
sage-
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 kno
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 - b
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
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
ovember 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 produ
>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 temp
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 tablespa
You can't do it en-mass. Why not write a script to do this.
select 'alter index '||index_name||' rebuild ;'
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 la
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
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 hav
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 th
:[EMAIL PROTECTED]>
809-565-3121
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ken Janusz
Enviado el: Tuesday, 27 November, 2001 9:25 AM
Para: Multiple recipients of list ORACLE-L
Asunto: Altering Indexes
I have this large DB (approx. 250 table
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_ind
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
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 consum
16 matches
Mail list logo
