Home » RDBMS Server » Server Administration » Rebuild index Script and Move tables to a different tablespace
Rebuild index Script and Move tables to a different tablespace [message #193688] Tue, 19 September 2006 00:24 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
do anybody have a script to rebuild all indexes across tablespace in a single run
e.g select all indexes and rebuild them in a separate tablespace all at once
other we have to rebuild index one by one so it is very time consuming task

do anybody have a script to move all tables to a different tablespace in a single run
e.g select all tables and move them in a separate tablespace all at once
other we have to move each table one by one so it is very time consuming task

if anybody have both of required scripts then please provide me,it would be very helpful for me
Re: Rebuild index Script and Move tables to a different tablespace [message #193737 is a reply to message #193688] Tue, 19 September 2006 03:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
They'd look a lot like:

(untested code)
DECLARE
  cursor c_index IS
    SELECT index_name
    FROM   all_indexes
    WHERE  tablespace_name = '<stuff>';
BEGIN
  FOR rec IN c_index LOOP
    EXECUTE IMMEDIATE 'alter index '||rec.index_name||' rebuild tablespace <new tablespace>';

  END LOOP;
END;


and
DECLARE
  cursor c_table IS
    SELECT table_name
    FROM   all_tables
    WHERE  tablespace_name = '<stuff>';

  cursor c_index (p_table  in  varchar2) is
    SELECT index_name
    FROM   all_indexes
    WHERE  table_name = p_table;
BEGIN
  FOR rec IN c_table LOOP
    EXECUTE IMMEDIATE 'alter table '||rec.table_name||' move tablespace <new tablespace>';

    FOR idx_Rec in c_index(rec.table_name) LOOP
      EXECUTE IMMEDIATE 'alter index '||idx_rec.index_name||' rebuild';
    END LOOP
  END LOOP;
END;
Re: Rebuild index Script and Move tables to a different tablespace [message #193863 is a reply to message #193688] Tue, 19 September 2006 13:50 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
You do know, that there should not be any need for rebuilding indexes on Oracle, besides bitmap indexes, right?

Br
Kim Anthonisen
Re: Rebuild index Script and Move tables to a different tablespace [message #193875 is a reply to message #193863] Tue, 19 September 2006 15:32 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Unless you move the table to another tablespace in which case it is required. OP seemed to ask the question backwards. Well, not literally backwards, but asked about indexes first and then tables.

It could also be that the OP wants to drop a tablespace and move all objects out of it.
Re: Rebuild index Script and Move tables to a different tablespace [message #193880 is a reply to message #193875] Tue, 19 September 2006 15:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:

.g select all tables and move them in a separate tablespace all at once
other we have to move each table one by one so it is very time consuming task

And it seems OP does not want to move 'one-by-one' but move 'all-at-once'. Smile
Re: Rebuild index Script and Move tables to a different tablespace [message #193895 is a reply to message #193880] Tue, 19 September 2006 17:25 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
IF the disk system can handle it, it could be achieved by using dbms_job to spawn an 'alter ... move' command for each table/index.

But I seriously doubt that that is what they would like. Maybe a few number of simultanious jobs....

Just guessing...
Previous Topic: Connecting to oracle
Next Topic: Monitoring Full tablescans
Goto Forum:
  


Current Time: Fri Sep 20 08:36:55 CDT 2024