Rebuild index Script and Move tables to a different tablespace [message #193688] |
Tue, 19 September 2006 00:24 |
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 |
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;
|
|
|
|
|
|
|