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 :

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

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

No comments: