| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: tables
naveen_arora_at_my-dejanews.com wrote:
>
> hi,
> i initially created tables from a user account with tablespace A
> i now have changed the default tablespace of user to B
> is there any way to move the extends and data from tablespace A to B ?
> thanks for any help
> naveen_at_cdotd.ernet.in
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
The exp/imp solution which has been suggested to you is the good one but
I think it requires a few additional precisions. I don't think that
naming the log file something.sql, although clever, is a really good
idea, unless your favorite pastime is spending hours with your text
editor reformating SQL statements (when imp displays SQL statements,
they are cut in fixed size chunks, regardless of 'natural' boundaries,
each one enclosed between double quotes).
Basically when exp exports a table, it saves the CREATE statement which
includes everything including the tablespace name. When imp executes
this CREATE :
- either the table already exists and the the data is not loaded unless
you specify IGNORE=Y
- or the table does not exist and the table owner is allowed to create a
table in the specified tablespace, and then the table is recreated
(although in one single piece by default) more or less as it was,
- or the table does not exist and the table owner CANNOT create tables
in the specified tablespace, in which case it is created in the default
tablespace for the user (if, once again, the user can create tables in
his/her default tablespace).
This is why what you must do is :
exp username/password owner=username file=username.dmp
then, from a DBA account :
alter user username
default tablespace B
quota unlimited on B
quota 0K on A;
and then
imp username/password file=username.dmp full=Y commit=Y
Note that if you had indexes in another tablespace than A and you also
want to move them, you will have either to rebuild the indexes as
somebody suggested you, or use the INDEXFILE parameter of imp to
generate a (directly usable!) .sql which contains the CREATE INDEX
statements which you can edit and modify. In this case, you can import
without the indexes and run the .sql afterwards. This being Oracle, you
will still have problems with the indexes associated with PRIMARY KEY
and UNIQUE constraints, but, well ...
--
Regards,
Stéphane Faroult
Oriole Corporation
 |
 |