Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problems on copying a database user from one tablespace to another on the same instance
In our last gripping episode "Richard Sutherland" <rsutherland_at_In-
net> wrote:
> Tablespaces don't belong to users/schemas. Users can be granted space
> quotas in certain tablespaces, but other users can use those same
> tablespaces. In other words, there is no way to make a tablespace
private
> to a user. When tables/indexes are created, if a tablespace is not
> specifed, the table/index is put into the user's default tablespace.
Other
> than that, there is no relationship between a user and a tablespace.
>
> However, an export records the tablerspace of the object(s) being
exported,
> and the import will put them in the same tablespace, even if you
import them
> as a different user.
>
> One way to accomplish a change in tablespaces is to edit the export
file,
> and change the TABLESPACE clause.
>
> Another way is to import them into the original tablespace (assuming
NEWUSER
> has quota therein), then issue ALTER TABLE MOVE <newtablespace> [for
tables]
> or ALTER INDEX REBUILD <newtablspace> [for indexes]. If the objects
are
> partitioned, you will need to issue these statements at the partition
level.
>
> A third option is to issue CREATE TABLE newtable TABLESPACE
<newtablespace>
> AS SELECT * FROM <user1's table>.
>
> Richard Sutherland
> rvsutherland_at_yahoo.com
>
> Peter Kroiss <pk_at_faw.uni-linz.ac.at> wrote in message
> news:3a33d96d_at_alijku02.edvz.uni-linz.ac.at...
> > I tried to export a user with
> > BUFFER=8192
> > FILE=G:\export.dmp
> > COMPRESS=Y
> > GRANTS=Y
> > INDEXES=Y
> > ROWS=Y
> > CONSTRAINTS=Y
> > LOG=G:\export.log
> > OWNER=(USER1)
> > RECORDLENGTH=8192
> >
> > and then reimport the data into a new user on a new tablespace with
> > BUFFER=8192
> > FILE=G:\export.dmp
> > SHOW=N
> > IGNORE=N
> > GRANTS=N
> > INDEXES=N
> > ROWS=Y
> > LOG=g:\import.log
> > DESTROY=N
> > FULL=N
> > FROMUSER=(USER1)
> > TOUSER=(NEWUSER)
> > RECORDLENGTH=8192
> > COMMIT=N
> >
> > The problem is that the import always creates the tables in the the
> > tablespace from user1 and not in the default-tablespace
> > of the new user (NEWUSER).
> >
> > Peter
> >
> >
> >
>
>
And yet a fourth option is to take the expected tablespace offline, thus leaving only the default tablespace for NEWUSER available. Import should place the tables in the default tablespace for NEWUSER since the expected tablespace for the tables does not exist or is not available.
All of the previous suggestions are correct, and I would actually lean toward editing the script generated from the export file using the indexfile poarameter to imp. This is simply another option.
-- David Fitzjarrell Oracle Certified DBA Sent via Deja.com http://www.deja.com/Received on Tue Dec 12 2000 - 14:56:26 CST