Wednesday, April 6, 2011

Move all the Indexes of Schema to Different Tablespace


Once my friend ask me  " Is it beneficial to move all indexes of schema to other tablespace  " .
The answered depends on what you're trying to accomplish.There would be no performance benefit to doing this. There would almost certainly be no reliability/ recoverability benefit. There may be some benefit to the DBA's sense of organization but it's exceptionally unlikely that there will be any practical benefits.

If he really need that then he may do this :

BEGIN
  FOR idx IN (SELECT * FROM dba_indexes WHERE owner = <<schema name>> AND tablespace_name = <<old tablespace name>>)
  LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' REBUILD TABLESPACE <<new tablespace name>>';
  END LOOP;
END;


Note:  If the Database is in ARCHIVELOG mode it may generate a lot of Archived Logs.

No comments: