From: David Fitzjarrell <oratune@aol.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Problems on copying a database user from one tablespace to another on the same instance
Date: Tue, 12 Dec 2000 20:56:26 GMT
Organization: Deja.com
Lines: 94
Message-ID: <9163dn$2lu$1@nnrp1.deja.com>
References: <3a33d96d@alijku02.edvz.uni-linz.ac.at> <t386pen7i30h98@corp.supernews.com>
NNTP-Posting-Host: 64.19.63.135
X-Article-Creation-Date: Tue Dec 12 20:56:26 2000 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
X-Http-Proxy: 1.1 x62.deja.com:80 (Squid/1.1.22) for client 64.19.63.135
X-MyDeja-Info: XMYDJUIDddf_dba


In our last gripping episode "Richard Sutherland" <rsutherland@In-
Touch.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@yahoo.com
>
> Peter Kroiss <pk@faw.uni-linz.ac.at> wrote in message
> news:3a33d96d@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/