RE: full exp/imp of user to new tablespace; same user

[Choudhary Rajendra (TTL_LKO)]

Title: full exp/imp of user to new tablespace; same user

           if  you have to import all indexes,constraints  to a single tablespace then following procedure will work 

 

                 4) After importing data change the default tablespace to index tablespace ,ensure that no other ts

                    quota (except index ts)  is given to the schema . Grant sufficient quota on index tablespace .

                 5) do import with ignore=Y ,rows=no ,indexes=y,constraints=y ,It will create indexes and constraints on index ts.

 

                 6) Revert back the default tablespace . 

 

               

           Rajendra  

                     

                   

-----Original Message-----
From: Magaliff, Bill [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 07, 2002 8:14 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: full exp/imp of user to new tablespace; same user

Richard:

 

if all of the objects are going into a single tablespace, make sure that new user has default tablespace set properly to the new TS and that s/he has a proper quota on that TS (I start with UNLIMITED).  Also, make sure that user has a quota of ZERO on the TS from which the data was exported - otherwise the default import behavior will put the objects into the TS from which they were taken.  Then just run the import (remembering, of course, to take all your small rollback segments offline and leave just one large rollback segment on line).

 

If you want Indexes in a separate TS, then here's what I do:

 

1)    set all as listed above, but make sure the user has unlimited quotas on both data and index TS

2)    import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in

3)    import again with only the indexfile option, which will create a sql script

4)    Edit the indexfile to remove stuff you don't want. . . I use macros in my text editor to remove (in this order):

** all the lines beginning with "REM  ...xRows imported"

** "REM  CREATE TABLE " statements - start with that string and delete until the next semi-colon (end of the CREATE TABLE statement);

these first two are for clarity only - so that I end up with a script that ONLY includes what I want, not all the other stuff, even though it's commented out.

 

**  all remaining instances of the string "REM  " (REM followed by two spaces) - this you must do to get all the ALTER TABLE statements properly.

 

this leaves you with clean sql to just create constraints and indexes

 

5)    do global search/replace for the index tablespace name (which is enclosed in double-quotes), putting in the name of the TS you want.

6)    remove the line at the top that says "connect <username>" - you should connect first b4 running the script

 

7)    save the file!

8)    run the script to create all the indexes

 

HTH

 

bill

 

 

 -----Original Message-----
From: Markham, Richard [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 07, 2002 8:59 AM
To: Multiple recipients of list ORACLE-L
Subject: full exp/imp of user to new tablespace; same user

I have a full export of user to where I need to import all his objects/grants
but into a NEW tablespace.  What's the easiest way I can assure that
~everything~ is imported.  Will I need to use a combination of INDEXFILE
and other procedures or will a full export; drop tablespace; create new
tablespace; alter user's default tablespace; full import suffice for
all objects? Thanks for any tips.