To: Rosy Virani 

On Wed, 29 Sep 1999, Rosy Virani wrote:
> Hi Ari,
> 
> I was wondering, if you can help with exporting indexes.
> 
> What I want to do is export indexes, drop tablespace and recreate
> indexes.
> 
> I am also concern about constraints on those tables.
> 
> What is the best approach for achieving this task.
> 
> Thanks for help.
> 
> Rosy.
> 
What you can do is:

1) Find out what objects are in the tablespace:

SELECT * FROM DBA_OBJECTS WHERE TABLESPACE_NAME = 'the_tablespace';

2) If there are tables, clusters, or any non-index objects you will have
to export them before the next step.

3) Export all objects owned by the owners listed in step #1:

exp owner=owner_1,owner_2,... file=file1.dmp log=log1.dmp

4) Create an "indexfile" using import:

imp full=y file=file1.dmp indexfile=create_index.sql

5) Modify the "create_index.sql" script to include only indexes for those
found in Step #1.

6) Drop all of the objects found in Step #1 from within the database

8) Import all of the non-index objects found in Step #2 using the import
utility.

7) Create the indexes by running the script in Step #5

By the way, BACK UP your database before you try any of this!

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 325+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page F="../index.htm"> Back to Ari Kaplan's Home Page