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
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.