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