Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Problems on copying a database user from one tablespace to another on the same instance

Re: Problems on copying a database user from one tablespace to another on the same instance

From: David Fitzjarrell <oratune_at_aol.com>
Date: Tue, 12 Dec 2000 20:56:26 GMT
Message-ID: <9163dn$2lu$1@nnrp1.deja.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US