Re: SQL-PLUS script for moving indexes to separate tables space
Date: 1996/05/30
Message-ID: <4ojt3i$lej_at_newsbf02.news.aol.com>#1/1
You asked...
>Has anyone written an SQL-PLUS script to move an Oracle users indexes to
>a different tablespace?
Have you tried using the INDEXFILE option of the IMPORT utility? You could perform an export of the database (I believe you can do this and specify NOROWS if your DB is large) and then do an import and specify INDEXFILE=<A filename for output> and the utility will create a complete script with TABLE and INDEX creates for all tables. There will be a CONNECT statement for each user so you can find the user you want and cut out the items for that user. Then just add a DROP INDEX <Indexname> before each create index and then edit the CREATE INDEX's TABLESPACE parameter to the new tablespace. This is the whole reason Oracle created this option.
You will note that all the TABLE definitions are REM'd out but they are in there to. I use the INDEXFILE option frequently for "hack-n-slash" tuning!
Now if you really want to do things right and optimize size parameters and full I/O distribution you should check out my company's tools. (Can't help the shameless plug!!!!)
Have fun!
---------------------------^----------------------------Greg Mays - Senior Technical Consultant (gmays_at_aol.com) The Database Solutions Company (1-800-933-7668) Company HomePage: www.dbsol.com Received on Thu May 30 1996 - 00:00:00 CEST