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

From: Choudhary Rajendra (TTL_LKO) <rajan_at_telco.co.in>
Date: Thu, 07 Nov 2002 23:38:21 -0800
Message-ID: <F001.004FE937.20021107233821@fatcity.com>

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-----
Sent: Thursday, November 07, 2002 8:14 PM To: Multiple recipients of list ORACLE-L

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:

set all as listed above, but make sure the user has unlimited quotas on both data and index TS
import data only - I use "constraints=n grants=n indexes=n" to insure it's only table data that gets in
import again with only the indexfile option, which will create a sql script
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-----
Sent: Thursday, November 07, 2002 8:59 AM To: Multiple recipients of list ORACLE-L

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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Choudhary Rajendra (TTL_LKO)
INET: rajan_at_telco.co.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Nov 08 2002 - 01:38:21 CST